Optimistic Updates and Transaction Isolation

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.

Database Concurrency

As previously discussed, the database is a crucial component of our new scheduler and for most business systems. With an appropriately designed data model, well written SQL and well-thought out transaction lifecycles and their makeup, most database implementations will provide little cause for complaint, even with many concurrent requests.

But you need to think carefully about the potential issues you will face when it comes to concurrent writes and even writes that were based on reads that have since become stale. What this boils down to is ensuring you have a strategy to ensure that writes are performed based on an expected prior state. These strategies are commonly known as pessimistic and optimistic concurrency control.

Pessimistic control (or pessimistic locking) is so named because it takes the view that concurrent requests will frequently be trying to overwrite each other’s changes. This solution is to place a blocking lock on the data releasing the lock once the changes are complete or undone. A read lock is sufficient despite the frequency with which a dummy write is used to lock. All requests would similarly require a blocking lock before beginning the read/write process. This approach requires state to be maintained from the read all the way through to the write. This is essentially impossible and entirely impractical for a stateless application that are all web applications. It also would have significant performance implications if requests were continually waiting to acquire locks.

Optimistic control takes the view that is just as safe as pessimistic control, but assumes that concurrent writes to the same data are likely to be infrequent and will verify during the write that the state continues to be as it was when the read was first made. Let’s look at a simple example to see what we’re talking about.

We’re tracking scores. I get a request to give William Thomas 5 more points. At the same time, you get a request to give him 3 more points. We both look at his current score and adjust accordingly. If we don’t verify that the score as it currently stands is still 87, one of us will incorrectly increment the score.

An optimistic update would ensure that the score would only update if the score at the time was still 87. Optimistic control has clear benefits when it sufficiently meets your requirements. It doesn’t block giving you a performance benefit over pessimistic, but it’s just as safe.

Let’s briefly review the strategies for optimistic writes, be they updates or deletes.

  • Verify all updating row values against expected values
  • Verify only modified values against expected values
  • Verify a version column is as expected that changes with all writes

Both the first and second choices require that potentially a lot of previous state must be maintained along with the primary key. The third option only requires that our key and the version of the row be maintained. At Carfey Software, we use this third option for all writes. We view straight updates based on primary key of no real use in a concurrent application.

Choosing a version column has some interesting implications on the transaction isolation level that is in play. Our next post will review those implications.