How to Implement Optimistic Concurrency Control in EF Core 10 Using SQL Server RowVersion (2026)
Two people open the same note. The first saves a correction, and the second submits an older screen over it. Use a SQL Server rowversion as an EF Core concurrency token and carry it through HTTP as an ETag. A stale If-Match then fails before it overwrites the newer text.
How does rowversion prevent a lost update?
SQL Server changes an eight-byte rowversion when the row is updated. EF Core includes the original version in the UPDATE predicate. A row that changed since the client read it will not match. When EF expected one row and SQL updates none, SaveChanges raises DbUpdateConcurrencyException.
| Input or outcome | API response | Client action |
|---|---|---|
| Missing If-Match | 428 | Load the resource and ETag |
| Unsupported or malformed tag | 400 | Supply the documented strong tag format |
| Stale tag or row removed after read | 412 | Fetch the current state and resolve |
| Valid update | 204 and new ETag | Use the new tag for another edit |
Build the SQL Server note API
Use the .NET 10 SDK and a disposable SQL Server database. The sample uses EF Core 10’s SQL Server provider and creates one note in Development. Save the code as Program.cs. Supply ConnectionStrings__Notes using a local secret and require TLS for deployed database access.
The API accepts one quoted strong ETag with canonical base64 and rejects lists, wildcard and weak tags by contract. Attach the stub with the client’s original version, mark only the text as modified, and let SQL check version and update atomically. This loopback demo has one public fixture; a production update must apply row-level authorization or use a tenant-inclusive key and predicate. An ETag does not grant write access.
Reproduce a concurrent edit
Send two GET requests for /notes/1. Both should receive the same ETag before any write. Use it in two PUT requests. Keep the double quotes from the header value when setting TAG.
Expect 204 and a new ETag from the first write and 412 from the second. The last GET must retain the first edit. Test against SQL Server, including two separate DbContexts reading the same row before saving in sequence. EF InMemory and SQLite do not implement SQL Server rowversion. Remove the header to verify 428 and submit a weak tag to verify the sample’s 400 contract.
Decide how to resolve conflicts
For user edits, show the latest value and let the user merge or retry with the new ETag. Blindly refreshing the concurrency token and saving the same body can discard the other user’s work. A background process may retry a safe commutative operation after recomputing it against the latest row. Set a bounded retry budget and distinguish concurrency from transient network faults.
Do not interpret rowversion as a timestamp. It helps detect changes and does not express wall time. Treat the ETag as a validator for the selected representation. If a public response varies with content negotiation or fields outside the row, ensure the tag represents the selected bytes and related state. Avoid using one rowversion across unrelated representations without that analysis.
Why did a small update conflict?
The token protects the whole row, so a change to another property can invalidate it. If separate fields need independent editing, model separate resources or apply a deliberate merge policy.
How do I add a rowversion to an existing table?
Generate and review the SQL Server migration. Rehearse schema locks and deployment sequencing on representative data. Deploy readers that can tolerate the old API contract until clients can supply If-Match.
Test the race at the database boundary
Open two separate requests with the same ETag. Release them at the same time from a test harness and record both responses. The sample should accept one and reject the stale writer with 412. Check the persisted text and verify the winner’s response ETag matches a fresh GET. Repeat after a delete to confirm the condition fails when the row no longer exists. Run the same checks through any gateway so a proxy does not strip If-Match or coalesce responses.
If the API processes an edit and then loses its response, retrying with the original tag will usually yield a precondition failure. The client should fetch current state and check whether its intended change already landed. For workflows that require a reliable mutation receipt independent of state comparison, use a separately designed idempotency key. That solves retry ambiguity while rowversion solves lost updates.
Takeaway. Carry the version through the API, enforce it atomically in SQL, and give clients enough information to recover without overwriting another user’s edit.
Comments
Post a Comment