This is a part of a series of blog posts on data access with Dapper. To see the full list of posts, visit the Dapper Series Index Page.
In today’s post, we explore a pattern to prevent multiple users (or processes) from accidentally overwriting each other’s change. Given our current implementation for updating the Aircraft
record, there is potential for data loss if there are multiple active sessions are attempting to update the same Aircraft
record at the same time. In the example shown below, Bob accidentally overwrites Jane’s changes without even knowing that Jane made changes to the same Aircraft
record
The pattern we will use here is Optimistic Offline Lock, which is often also referred to as Optimistic Concurrency Control.
Modifying the Database and Entities
To implement this approach, we will use a rowversion column in SQL Server. Essentially, this is a column that automatically version stamps a row in a table. Any time a row is modified, the rowversion
column will is automatically incremented for that row. We will start by adding the column to our Aircraft
table.
ALTER TABLE Aircraft ADD RowVer rowversion |
Next, we add a RowVer
property to the Aircraft
table. The property is a byte
array. When we read the RowVer
column from the database, we will get an array of 8 bytes.
public class Aircraft |
Finally, we will modify the query used to load Aircraft
entities so it returns the RowVer
column. We don’t need to change any of the Dapper code here.
public async Task<Aircraft> Get(int id) |
Adding the Concurrency Checks
Now that we have the row version loaded in to our model, we need to add the checks to ensure that one user doesn’t accidentally overwrite another users changes. To do this, we simply need to add the RowVer
to the WHERE
clause on the UPDATE
statement. By adding this constraint to the WHERE
clause, we we ensure that the updates will only be applied if the RowVer
has not changed since this user originally loaded the Aircraft
entity.
public async Task<IActionResult> Put(int id, [FromBody] Aircraft model) |
So, the WHERE
clause stops the update from happening, but how do we know if the update was applied successfully? We need to let the user know that the update was not applied due to a concurrency conflict. To do that, we add OUTPUT inserted.RowVer
to the UPDATE
statement. The effect of this is that the query will return the new value for the RowVer
column if the update was applied. If not, it will return null.
public async Task<IActionResult> Put(int id, [FromBody] Aircraft model) |
Instead of calling ExecuteAsync
, we call ExecuteScalarAsync<byte[]>
. Then we can check if the returned value is null
and raise a DBConcurrencyException
if it is null. If it is not null, we can return the new RowVer
value.
Wrapping it up
Using SQL Server’s rowversion
column type makes it easy to implement optimistic concurrency checks in a .NET app that uses Dapper.
If you are building as REST api, you should really use the ETag header to represent the current RowVer for your entity. You can read more about this pattern here.