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.