Optimistic Offline Lock

[bricelam]

Many databases (including ~PostgreSQL~ and SQLite) don't have an equivalent to SQL Server's rowversion type. We could improve our support for optimistic concurrency on those stores by implementing the Optimistic Offline Lock pattern in the framework.

This would be similar to having a rowversion property in the entity except that EF would manage the value. When the entity is saved, somewhere in the update pipeline, we would automatically increment the property's value. The DML would look something like this:

UPDATE Person
SET Name = "Brice", RowVersion = 24
WHERE RowVersion = 23;

[anpete]

+1

It's also common to have a convention for this. I.e. Look for a property called "Version", "LockVersion" etc.

[divega]

Not sure it is a constrain but we don't need to express RowVersion = RowVersoin + 1 in the update pipeline, just passing the value 24 for the example above would work, correct?

[anpete]

Yes, it just means we need to be able to do the client side increment for all potential types.

[roji]

+1, would help for PostgreSQL which has no rowversion (although a workaround with generating values on update via triggers is probably possible)

[pantonis]

+1, We need it :) Not all databases are so productive working with them like sql server. PostgreSQL like roji mentioned does not support rowversion.

[roji]

FYI I found out PostgreSQL does have an automatically-updated xmin column which could be used as a concurrency token, much like SqlServer's rowversion, so this may be less important for PostgreSQL. See https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/19.

[bubibubi]

+1 If you implement this please keep in mind that properties marked as Timestamp are array of bytes. Changing the value could be not only an increment.

[anpete]

@bricelam I implemented this in the Oracle sample provider by auto-generating triggers in Migrations when creating row version columns.

[LeVraiSylvain]

Is there any vote system to increase the priority of this feature?

Thx

[ajcvickers]

@m4rs2k Yes, 👍 this issue.

[asbjornu]

As reported in #32083, I'm very eager to see this feature implemented. Beyond waiting for it to be implemented in EF Core (which looks like it's going to take a while), is it possible to implement through some custom code? If so, how would that look like?

[roji]

@asbjornu you can override SaveChanges and have code there which sets the value of the concurrency token to an incremented value. Or if you want this to happen in the database, you can set up triggers to do that there.

[ComptonAlvaro]

Hello,

I would like to present a scenario I am considering.

Suppose I have Order and OrderLine entities, and there's a rule that an order cannot have more than 5 items. I would follow these steps:

1. The application layer has a method AddLineToOrder().
2. In AddLineToOrder(), I retrieve the order, including all its lines, using a repository.
3. I call the AddLine() method of the order object.
4. If the order has fewer than 5 lines, the line is added.
5. The AddLineToOrder() method calls the Commit() method of the repository, which uses Entity Core's SaveChanges().

My question is, how should I override the SaveChanges() in the repository? In my case, the Order entity does not have a property for concurrency because I consider this the responsibility of the repository. Therefore, I define a shadow property for concurrency in the repository using the fluent API for the Order entity, which is the root entity. This shadow property is a long property that should be incremented each time I update the order or modify some information of the aggregation, such as adding a new line.

However, when I add a new line, the order itself is not modified; it is only used to ensure data coherence. Thus, SaveChanges() will not detect any change in the Order entity and will not check for concurrency.

So, how should I override the SaveChanges() method? Should I iterate through all the entities in the context and increment the value of the entities that have a concurrency property?

Thanks so much.

[roji]

@ComptonAlvaro first, to ensure your business rule of no more than 5 items per order, I'd recommend considering an insert trigger. That would allow you to ensure, at the database level, that your invariant is always preserved.

Second, it seems you're asking for how to manage a concurrency token - that doesn't seem really related to the 5-items-per-order rule above. In any case, yes - you can have a shadow property that's configured as a concurrency token with EF, and then override SaveChanges to enumerate over all modified entities in the context and update the concurrency token.