Feature: Optimistic Concurrency Support via Concurrency Tokens

[rent-a-developer]

Feature: Optimistic Concurrency Support via Concurrency Tokens

Summary

Add support for optimistic concurrency control in DbConnectionPlus using concurrency token columns.
When an entity is updated or deleted, DbConnectionPlus should ensure the entity’s concurrency token(s) match the current value(s) in the database row. If it doesn’t, the operation must fail with a concurrency exception.

---

Motivation / Problem

DbConnectionPlus currently updates and deletes rows without verifying whether the row has been modified by another process since it was last read. This can lead to lost updates and silent overwrites.

By supporting concurrency tokens, DbConnectionPlus can detect conflicting updates and provide consistent optimistic concurrency behavior similar to EF Core.

---

Requirements

1) Concurrency token kinds

1.1 Native database-generated concurrency tokens (row version)

Such tokens are automatically generated and updated by the database system when a row is inserted or updated.
Example: SQL Server timestamp or rowversion column.

1.2 Application-managed concurrency tokens

Such tokens are managed by the application rather than by the database.
Example: A GUID column.

2) Configuration

2.1 Attribute-based configuration

Support marking a property as a row version concurrency token using System.ComponentModel.DataAnnotations.TimestampAttribute:

public class Product
{
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

Support marking a property as a concurrency token using System.ComponentModel.DataAnnotations.ConcurrencyCheckAttribute:

public class Product
{
    [ConcurrencyCheck]
    public byte[] ConcurrencyToken { get; set; }
}

2.2 Fluent configuration

Support configuring concurrency tokens via fluent API:

DbConnectionExtensions.Configure(config =>
{
    config.Entity<Product>()
        .Property(p => p.RowVersion )
        .IsRowVersion();

    config.Entity<Product>()
        .Property(p => p.ConcurrencyToken )
        .IsConcurrencyToken();
});

Acceptance criteria

• Concurrency token configuration must be reflected in entity metadata.

---

3) Update behavior

If an entity has one or more configured concurrency token properties:

• The UPDATE must only succeed if the concurrency token value(s) in the entity match the value(s) currently stored in the database.
• If the values do not match, DbConnectionPlus must:
  • not perform the update
  • throw DbUpdateConcurrencyException

Implementation requirement

• Concurrency token values must be included in the WHERE clause of the generated UPDATE statement.
• DbConnectionPlus must validate concurrency by checking the number of affected rows returned by the database.
  • If affectedRows == 0, treat it as a concurrency conflict and throw DbUpdateConcurrencyException.

---

4) Delete behavior

If an entity has one or more configured concurrency token properties:

• The DELETE must only succeed if the concurrency token value(s) in the entity match the value(s) currently stored in the database.
• If the values do not match, DbConnectionPlus must:
  • not perform the delete
  • throw DbUpdateConcurrencyException

Implementation requirement

• Concurrency token values must be included in the WHERE clause of the generated DELETE statement.
• DbConnectionPlus must validate concurrency by checking the number of affected rows returned by the database.
  • If affectedRows == 0, throw DbUpdateConcurrencyException.

---

5) Refresh row version concurrency tokens after update

After a successful update of an entity with row version concurrency token properties, DbConnectionPlus must:

• Retrieve the new row verion values generated by the database.
• Automatically update the entity instance’s row version properties with the fresh values returned.
• Ensure the entity reflects the current state of the database, preventing stale row version values in the in-memory entity.

---

Batch Operations

When updating or deleting multiple entities in a batch:

• DbConnectionPlus must stop processing the batch as soon as it detects a concurrency conflict.
• Updates/deletes that completed successfully before the failing entity should remain persisted.
• The operation must throw DbUpdateConcurrencyException for the first entity that fails concurrency validation.

---

Testing

Add unit + integration tests across all supported database systems to verify:

Metadata / configuration

• Properties marked with [ConcurrencyCheck] are correctly recognized as concurrency tokens.
• Properties marked with [Timestamp] are correctly recognized as row version concurrency tokens.
• Fluent configuration via .IsConcurrencyToken() is correctly applied.
• Fluent configuration via .IsRowVersion() is correctly applied.

Update/Delete behavior

• Updates include concurrency token values in the WHERE clause.
• Deletes include concurrency token values in the WHERE clause.
• Operations succeed when values match.
• Operations fail when values differ.

Row version concurrency token refresh

• After successful updates, row version concurrency token properties on the entity are updated with fresh values from the database.

Exception behavior

• DbUpdateConcurrencyException is thrown when concurrency checks fail.
• Batch operations stop at the first conflict and preserve prior successful operations.