Object Locking in Relational Database Transactions

Jun 8, 2021 by Robert Gravelle

Recently, we’ve had a few blogs about database transactions and they enforce the the four ACID (Atomicity Consistency Isolation Durability) properties. In today’s blog, we’ll be taking a look at another mechanism employed by relational databases (RDBMS) to enforce ACID properties, namely, Object Locking. Specifically, we’ll learn what it is, what role(s) it plays in RDBMS transactions, and some of the side effects locking may cause. While Database Object Locking can be a fairly technical and complicated subject, we’re going to break it down into layman’s terms here and keep things as simple as possible.

Simply put, Object Locking is a way to prevent simultaneous access to data in a database, in order to avoid data inconsistencies. To illustrate how Object Locking works, imagine two bank tellers attempting to update the same bank account for two different transactions. Both tellers retrieve (i.e. copy) the account’s record. Teller A applies and saves a transaction. Teller B applies a different transaction to his/her saved copy, and saves the result, overwriting the transaction entered by teller A. Now the record no longer reflects the first transaction, as if it had never even happened!

The fix is to lock the record whenever it is being modified by any user, so that no other user can alter it at the same time. This prevents records from being overwritten incorrectly, but allows only one record to be processed at a time, locking out other users who need to edit records at the same instance. Hence, anyone attempting to retrieve the same record for editing is denied write access because of the lock (depending on the exact implementation, they may be still be able to view the record in a read-only state). Once the record is saved (or edits are canceled), the lock is released. By preventing records from being saved so as to overwrite other changes, data integrity (the I in ACID) is preserved.

The above example demonstrated an instance of record-level locking. Now imagine if the two bank tellers above were serving two different customers, but both their accounts were contained in one ledger. In that situation, then the entire ledger – or, one or more database tables – would need to be locked for editing. As you can imagine, locking entire tables can lead to a lot of unnecessary waiting. If the tellers could remove one page from the ledger, containing the account of the current customer (plus a few other accounts, perhaps), then multiple customers can be serviced concurrently, provided that each customer’s account is found on a different page than the others. If both customers have accounts on the same page, then only one may be serviced at a time. This is an analogy of page-level locking in a database.

There are four types of locks. Here they are in increasing granularity:

• database locks
• table locks
• page locks
• row locks

The utilization of granular locks creates the possibility for a situation called “deadlock”. A deadlock may occur when incremental locking (locking one entity, then locking one or more additional entities) is utilized. To illustrate, my wife and I often transfer money between our personal accounts. If we were to each ask a teller to obtain our individual account information so we could transfer some money into the other spouse’s account, the two accounts would essentially be locked. Then, when our tellers attempted to transfer money into each other’s accounts, they would each find the other account to be “in use”, forcing them to wait for the accounts to be freed up. Unknowingly, the two tellers are waiting for each other, and neither of them would be able complete their transaction until the other gives up and returns the account! Thankfully, various techniques have been devised to circumvent such problems. These will be addressed in the next installment.

In today’s blog, we established what Object Locking is in Relational Databases, the different types of locks, and deadlocking. In the next installment, we’ll review some collision resolution strategies, as well as pessimistic versus optimistic locking.

Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.