This post is about row update locking within a relational database instance.
But before we delve into the nuances of locking, it is important to lay out the context of this discussion.
We will be only talking about locking for the purposes of resolving competing updates to the same data. The practice of a writer locking out reads for the same data (or vice versa) is unnecessary; similarly locking a whole table for one or more row updates is beyond rudimentary. Both practices have a devastating effect on performance amongst concurrent users and frankly, if you are running a shared database that invokes either of these dubious practices then it’s time to move on, you can do a lot better. OK, now that’s out of the way, let’s get into the detail of row update locking.
Locking can be pessimistic or optimistic – in the former we prevent any nastiness happening from competing updates and in the latter we clean up any nastiness that did happen because we didn’t stop it. In that sense, locking can be thought of as collision prevention (pessimistic locking with no nastiness) or collision detection (optimistic locking with nastiness clean-up).
There has been some debate over which type of locking/collision management affords the best performance and because it depends on so many factors, databases typically offer a choice. But how do you choose?
The first factor to be considered is the amount of real contention that goes on. If the probability that any two users are likely to update the same row at the same time then prevention is more efficient than clean up; whereas if real contention is exceptional then it may be more efficient to deal with the consequences of a collision rather than prevent it.
It’s a bit like operating traffic lights at a road junction. If the traffic is sufficiently light you can get away with turning the lights off and clearing up after occasional accidents – but if the traffic gets heavy you’d better turn them back on to avoid carnage.
If you really don’t know what the likelihood of a collision is, then it’s probably better to opt for prevention for all the reasons about to be outlined.
Collision detection (optimistic locking) requires the application using the database to deal with the consequences of a collision. If updates from two users do collide, then one of them has to be denied their update and informed that the data has changed so that they can choose to re-submit an action, change an action or cancel it entirely. Realistically, the database cannot decide on the user’s behalf and the user or application will have to resolve it.
Collision detection is easy for a database that performs an update-in-place (see previous post). In this case, writers place a new version number against each row updated so that a process can take note of a row version at read time and see if it has changed at update time (which implies a collision happened since the read). But collision detection gets a whole lot harder for a database that appends updates. Here, the original version of the data never changes and the update is placed elsewhere. Hence a writer has to go look and see if there have been any colliding appends since the original version was read and if there is a lot of update activity this can become a costly overhead. Plus what happens if the collision occurs between the collision check and the append? We now have to marshal access to the append area to prevent this and it starts to sound a lot more like collision prevention.
Ideally, any form of collision prevention (pessimistic locking) would be sufficiently fast and lightweight such that it presents a marginal cost in cases where genuine contention does not occur. But pessimistic locking is often considered a major overhead for a number of reasons.
It may be that lock requests become serialised because they are managed by a centralised lock manager and processes that have no natural contention are then forced to wait on each other.
It may be that locks are such a limited resource that processes have to wait for their availability or enlarge the granularity of each lock in order to use less of them. Again, this creates artificial contention which will hit performance.
However, these performance issues arise from the locking implementations used and not from the principle of pessimistic locking itself.
Then there are deadlocks. A deadlock occurs where two or more processes hold mutual locks between each other such that no process can proceed, unless one or more processes relinquish one or more locks allowing the other processes to continue.
If there’s only one knife and one fork on a dining table and I grab the fork at the same time you grab the knife, then neither of us can eat until one of us gives up an item of cutlery. We could be obstinate and both starve; or we could be altruistic and both starve; or we could defer to the choice of a third party in which case only one of us will starve. In the same way, deadlock detection has to choose which process will be forced to relinquish its locks and will use a graph of processes and locks to determine which process has the least to lose and the most to offer. This can be complicated and time consuming if the graph is large with lots of locks to consider.
However, if locks are sufficiently granular then deadlocks can only occur where there is genuine conflict and then it is quite reasonable to back out one or more of the conflicting parties in that case.
But if each party requests all of its locks through a single atomic request which is either granted or denied in whole then deadlocks cannot occur between those parties. In our dining example, we can each make a single request – for both a knife and a fork; one of us will succeed and the other will fail – but there is no deadlock. In the context of updating rows in a table, the rows to be updated are identified first and then locked atomically – rather than requesting locks incrementally. Deadlocks could still arise where multiple tables are being updated because each set of locks for each table will be requested separately. However, the deadlock graph is much simpler because it need only consider a handful of table dependencies rather than thousands of individual lock dependencies and this makes deadlock detection a much faster proposition.
Plus requesting locks in a bulk manner will minimise the number of requests made – which in turn reduces request serialization and amortizes the cost of lock requests.
Then there are a couple of classic red herrings often raised about pessimistic locking…
A user who can issue ad-hoc SQL, locks a set of rows for update and then goes off to lunch before finishing his transaction – leaving anybody else needing to update the same rows waiting until he returns later in the afternoon.
If this is just a test system, then who cares? If this is a production system then who allows users access to the SQL prompt anyway? If you do that kind of thing on a production system then it is probably already toast for a whole bunch of reasons unrelated to locking.
Another objection is that long-lasting web transactions may disconnect and leave table rows locked.
The clue to the misdirection here is the phrase long-lasting web transaction. Nobody (of sane mind) designs a web transaction to be long-lasting or allows it to span across temporary database connections. Web transactions should be encased in procedures such that they either happen or don’t – they can’t partially happen. Browsing available stock, selecting a stock item and purchasing a stock item are all separate and independent transactions. You never lock rows in a database for the period between adding an item to a cart and paying for it.
This all rather begs the question of why would you use collision detection (optimistic locking) with append semantics if you also have a fast and efficient method of preventing collisions? You wouldn’t.
It may be easier to ask for forgiveness rather than permission – but only if you’re not doing it all the time.