OptimisticConcurrencyException while deleting

Hi there,

while deleting a record I get the OptimisticConcurrencyException. The PK is generated by the Database and I use "Soft Delete" . There is a trigger at the table that updates some fields when the "DeletedOn" field has a value.

The generated SQL looks fine:

UPDATE PersonalQualifikationen SET DeletedOn = '07.03.2012 14:37:20' WHERE (PersonalQualifikationen.LockVersion = 0 AND PersonalQualifikationen.PersonalQualifikationsId = 12)

When I submit that sql via "SQL Server Management Studio" it updates the record, the trigger updates some other fields.

I see that the record is updated more than one time, because of the trigger. Is that a problem?

Jupp, that is a problem.

When i set SET NOCOUNT ON / OFF within the trigger, I get no exception.

While looking a bit closer at the delete SQL, I wounder why the LockVersion is not incremented?

First user deletes a record, a second one updates it. Will there be a OptimisticConcurrencyException for the second one?

Yes, it is the trigger. We detect concurrency issues by issuing the UPDATE with a WHERE clause, and checking the number of rows modified. Maybe we should be checking for 1 or more rows modified, rather than exactly 1; we'll have a chat about whether this might have any compatibility implications.

I think it's an oversight that we don't update the LockVersion when doing a soft delete, and I don't think you'd get the exception in the 'user 1 deletes, user 2 updates' scenario. The record would remain soft-deleted, but user 2 would not be alerted. How important a consideration is this for you?

Hi Ivan,

the LockVersion should be incremented that is important to handle the OptimisticConcurrencyException consistent.

The trouble is that this has knock-on effects to other cases. Consider:

• User 1 and user 2 both load entity, currently at version 5
• User 1 soft-deletes entity, bumping the version to 6 (per proposed change)
• User 2 attempts to soft-delete entity

With the proposed change, the second delete will fail because the entity version in the database is different from the entity version loaded by user 2. This feels wrong, and is a breaking change.

I might look at changing the update case to check DeletedOn as well as LockVersion, rather than changing the delete case to bump the LockVersion.

Ugh, that won't work either. That will break code that relies on being able to update soft-deleted entities (or, as it is technically known, Evil And Wrong Code, but I have a feeling we have customers who write it all the same).

I've provisionally implemented it so that it will bump the version during soft-delete as you requested, but that delete statements will check "same version or already deleted" rather than just "same version." That appears not to break any existing scenarios. It will be in the next nightly build.

Thanks, Ivan!