Event Store and transactions

EventStoreDB
#1

Hi!

Once more it is me again :slight_smile:

Following use case:

  • Once a day we have to import events from multiple external sources into the Event Store.

  • Performance is not that important when importing, performance is only important later on when processing the events.

  • We are not allowed to lose any event in this import process, never, ever.

  • If our process dies for some reason we must now what has been the last committed event for each source.

Using NEventStore up to now we’ve enlisted the NEventStore in the ambient transaction, i.e. the following code ensured the process’ consistency:

using(var tx = new TransactionScope(…))

{

// (1) load some events from external source X

// (2) write the events to the eventstore

// (3) write new “checkpoint” for source X to a database

tx.Commit(); // transactional safe eventstore-write + checkpoint-write

}

How would one do that with the EventStore where you cannot (?) use TransactionScopes. Is there any other way to connect a SQL server transaction with an Event Store transaction?

Other ideas?

Best regards,

Dominik

#2

I think you may be able to add some metadata with the checkpoint to the events you write to the event store. Then, read the last event to find out the checkpoint before writing new events in your daily batch job. It doesn’t seem (to me) that you need an ambient transaction to deal with this.

#3

You can however use a transaction if you want. There is an example somewhere showing how to do this.

In general though the event store gives you idempotency which would allow you to do this process multiple times without an issue if coded properly. The basic idea is the exact opposite of what was discussed in the other thread with checkpointing events out of the event store.

#4

Could you point to the example with saving events to EventStore and data to SQL database in one transaction?

#5

James Nugent has a code sample, I tried to find it but my google-fu failed. He is on this list and likely will reply

#6

I wrote a small NON-PRODUCTION example of enlisting in a distributed transaction with TransactionScope a few years back: https://gist.github.com/jen20/1de2dc287e880fd7f16c

That said, I strongly recommend using a different approach, and generating consistent message IDs, taking advantage of Event Store’s built in idempotent behaviour on writing. I also wrote example of how to generate consistent message IDs: https://gist.github.com/jen20/33d28a6ed7415f1aaa58

James

#7

Hi James,

Could you elaborate how consistent message ids and idempotent writing will help with saving data in SQL Server and EventStore with consistency in one call to backend?

#8

Currently my scenario is following:
In one WebApi call data is saved to SQL Server and some events to EventStore.
Saving to SQL Server is opaqued in SQL transaction.
Order of actions are 1) Opening SQL transaction 2) SQL inserts/updates 3) saving events to event store 4) SQL transaction commit

Therefore, the only possibility of data inconsistency is failure on SQL transaction commit, when some transaction exception will occurred like timeout/deadlock.

Could you hint some ideas how can I deal with such situation? (currently moving SQL stuff to event handlers is not an option)

#9

Hi Adrian,

We’re having the same use case as you. Did you find a solution for this?

Cheers,

Peter

#10

Hey @Peter, I believe you’ll find what you need by looking at that link from the documentation: https://developers.eventstore.com/clients/grpc/appending-events.html#handling-concurrency

1 Like
#11

Hi @yorick.laupa, thanks for the link! This works when working with ESDB alone though. My question was about combining SQL and ESDB into the same transaction. Reading on it seems like a big no however, also by the ESDB grpc doesn’t seem to support transactions anymore. Reading more on consistency I need to look at this solution in another way (https://groups.google.com/g/dddcqrs/c/aUltOB2a-3Y/m/0p0PQVNFONQJ). Cheers, Peter

#12

  • Appends to a stream in ESDB are atomic with addtional of optimistic locks.

  • in your case, without having more details, is such a design ok : ?

  1. Append Event to ESDB ( with the data you need in SQL )
  2. Catchup subscription =>
  • transaction open
  • insert / update data in SQL db
  • update checkpoint in SQL db
  • commit

this will ensure better resilienty & recovey from failures.

#13

Hi @yves.lorphelin, thanks! I just setup a prototype using this design :smiley:

I think it can definitely work. I just need to rethink how we handle consistency.

Cheers,

Peter

1 Like