While we at Carfey Software prefer to run our transactions using READ COMMITTED isolation level, we have taken steps to ensure that our optimistic updates using a version column will work even with READ UNCOMMITTED. How does READ UNCOMMITTED impact optimistic updates? You might start to see that it is the dirty reads, or reads of data changes that won’t necessarily be committed, that cause an issue.
To ensure a safe, accurate optimistic update strategy, you must ensure that whatever value you use for a version column will never again be used on that row – using a GUID is overkill but would do the trick, we use a permanently incrementing value by table. This is because a dirty read of a version column could grab an uncommitted row that is eventually rolled back and a differing transaction modifying and committing the row resulting in a different version represented by the same version number. Let’s visualize the problem.
 |  |
We’re running READ UNCOMMITTED. Our starting state is modified by a first transaction and we’re using a common strategy of incrementing the version number on the update. Then a second transaction comes along and gets a dirty read.
 |  |
Afterwards, the first transaction is rolled back. And before transaction 2 does anything, a third transaction reads, writes an increase of 5 to the score and then commits, incrementing the version to 1. When our second transaction comes in for a write and wants to increment the score by 3, it verifies that the version is 1, it incorrectly assumes it can safely make its write and actually only increases the score by 1.
 |  |
You might think that with dirty reads, safe optimistic updates aren’t possible. But all you need to do is make sure that a version is never used again for a row of data. In our above scenario, if we don’t reuse the version 1, the optimistic write would fail to validate and force a reread. Be aware that if you’re running READ UNCOMMITTED, many ORMs’ optimistic update strategies don’t protect against the above scenario.
As we said, we use optimistic updates as a minimum. Our next post will review cases where we do use pessimistic blocking.