0

It is my first time using EF Core and DDD concepts. Our database is Microsoft SQL Server. We use optimistic concurrency based on the RowVersion for user requests. This handles concurrent read and writes by users.

With the DDD paradigma user changes are not written directly to the database nor is the logic handled in database with a stored procedure. It is a three step process:

  1. get aggregate from repository that pulls it from the database
  2. update aggregate through domain commands that implement business logic
  3. save aggregate back to repository that writes it to the database

The separation of read and write in the application logic can lead again to race conditions between parallel commands.

Since the time between read and write in the backend is normally fairly short, those race conditions can be handled with optimistic and also pessimistic locking.

To my understanding optimistic concurrency using RowVersion is sufficient for lost update problem, but not for write skew as is shown in Martin Kleppmann's book "Designing Data-Intensive Applications". This would require locking the read records.

To prevent write skew a common solution is to lock the records in step 1 with FOR UPDATE or in SQL Server with the hints UPDLOCK and HOLDLOCK.

EF Core does neither support FOR UPDATE nor SQL Server's WITH.

If I'm not able to lock records with EF Core does it mean there is no way to prevent write skew except using Raw SQL or Stored Procedures?

If I use RowVersion, I first check the RowVersion after getting the aggregate from the database. If it doesn't match I can fail fast. If it matches it is checked through EF Core in step 3 when updating the database. Is this pattern sufficient to eliminate all race conditions except write skew?

Since the write skew race condition occurs when read and write is on different records, it seems that there can always be a transaction added maybe later during development that makes a decision on a read. In a complex system I would not feel safe if it is not just simple CRUD access. Is there another solution when using EF Core to prevent write skew without locking records for update?

M. Koch
  • 525
  • 4
  • 20

1 Answers1

0

If you tell EF Core about the RowVersion attribute, it will use it in any update statement. BUT you have to be careful to preserve the RowVersion value from your data retrieval. The usual work pattern would retrieve the data, the user potentially edits the data, and then the user saves the data. When the user saves the data, you would normally have EF retrieve the entity, update the entity with the user's changes, and save the updates. EF uses the RowVersion in a Where clause to ensure nothing has changed since you read the data. This is the tricky part- you want to make sure the RowVersion is still the same as your initial data retrieval, not the second retrieval used to update the entity before saving.

pjs
  • 363
  • 1
  • 7
  • I am aware of this process. The problems are in the update process on the server side, when two transactions interleave. My feeling is that when transactions read and write the same record only, RowVersion is sufficent. It gets problematic when a decision is based on the read, but the update is on other records. – M. Koch Mar 11 '22 at 16:23
  • You would need a row version attribute on each participating entity. You could use a transaction in your code to group the updates and make it all-or-none, or you can use a transaction in a database stored procedure. – pjs Mar 12 '22 at 13:03
  • I don't understand how this will help to prevent write skew. – M. Koch Mar 16 '22 at 15:11