Commercial demands for ensuring smooth functionality and highly efficient run-time servers, make it highly prime for Database Designers to work out systems and code which cleverly avoid any kinds of inconsistencies in multi-user transactions, if not doubt the standard of memory management in read-heavy, write-heavy and all such commercial databases. This article shall give an adequate introduction to the classic locking architecture that was proposed and implemented for the database developer community.
We should try to overlook the techniques of locking in general, i.e., based on the granularity or level of the database. This is so we may deal with it separately when looking at the feasibility of the types of locks.
Definition :
The formal definition of a lock is as follows:
A Lock is a variable assigned to any data item in order to keep track of the status of that data item so that isolation and non-interference is ensured during concurrent transactions.
At its basic, a database lock exists to prevent two or more database users from performing any change on the same data item at the very same time. Therefore, it is correct to interpret this technique as a means of synchronizing access, which is in stark contrast to other sets of protocols such as those using timestamps and multiversion timestamps. In layman’s terms, this may be further simplified to the metaphorical ‘lock’ that is put on a data item so that no other user may unlock the ability to perform any update query.
Case 1: Simultaneous access to transactions is not permitted. Case 2: putting a lock on other transactions is feasible
In Case 2, which has been portrayed above, if the user/session on the right attempts an update, it will be met with a LOCK WAIT state or otherwise be STALLED until access to the data item is unlocked. In some situations – if the stall exceeds a time limit – the session is terminated and an error statement is returned.
We shall look at the multiple ways these locks have been introduced in the industry as solutions to concurrency in transactions.
Binary Locks :
Remember that a lock is fundamentally a variable which holds a value. A binary lock is a variable capable of holding only 2 possible values, i.e., a 1 (depicting a locked state) or a 0 (depicting an unlocked state). This lock is usually associated with every data item in the database ( maybe at table level, row level or even the entire database level).
Should item X be unlocked, then a corresponding object lock(X) would return the value 0. So, the instant a user/session begins updating the contents of item X, lock(X) is set to a value of 1. Due to this, for as long as the update query lasts, no other user may access the item X – even read or write to it!
There are 2 operations used to implement binary locks. They are lock_data( ) and unlock_data( ). The algorithms have been discussed below (only algorithms have been entertained due to the diversity in DBMS scripts):
The locking operation :
lock_data(X): label: if lock(X) == 0 < then lock(X) = 1; >else //when lock(X) == 1 or item X is locked < wait (until item is unlocked or lock(X)=0) //wait for the user to finish the update query go to label >
Note that ‘label:‘ is literally a label for the line which can be referred to at a later step to transfer execution to. The ‘wait’ command in the else block basically puts all other transactions wanting to access X in a queue. Since it monitors or keeps other transactions scheduled until access to the item is unlocked, it is often taken to be outside the lock_data(X) operation i.e., defined outside.
The unlocking operation :
unlock_data(X): lock(X) = 0 //we unlock access to item X if (transactions are in queue)Merits of Binary Locks :
Drawbacks of Binary Locks :
Shared or Exclusive Locks :
The incentive governing these types of locks is the restrictive nature of binary locks. Here we look at locks which permit other transactions to make read queries since a READ query is non-conflicting. However, if a transaction demands a write query on item X, then that transaction must be given exclusive access to item X. Ergo, we require a kind of multi-mode lock which is what shared/exclusive locks are. They are also known as Read/Write locks.
Unlike binary locks, Read/Write locks may be set to 3 values, i.e., SHARED, EXCLUSIVE or UNLOCKED. Hence, our lock, i.e., lock(X), may reflect either of the following values:
A Shared/Exclusive lock may hold any of the 3 states.
The most popular way of implementing these locks is by introducing a LOCK-TABLE which keeps track of the number of read-locks on the data items and the transactions with write-locks on different items. The table has been described below.
Note that if an item is write-locked, it is logically supposed to have no reads on account of the fact that it is now exclusive. As a result, the ‘Locking Transaction’ column holds only one value – the transaction ID of the current transaction. If an item is read-locked, it is shared by multiple transactions and therefore, the ‘Locking Transaction’ column lists the transaction IDs of all the transactions. Since there are 3 states that the lock may hold, there must be 3 operations which would execute the change to those states. These are as follows :
The read_lock operation –
read_lock(X): label: if lock(X) = "unlocked" < then lock(X) = "read-locked"; no_of_reads = 1; //since only the first transaction in queue is now able to read item X >else if lock(X) = "read-locked" < then no_of_reads +=1; //simply increment as a new transaction is now reading the item X >else //lock(X) write-locked < wait (until lock(X) is "unlocked");//transactions observe a LOCK WAIT during this time go to label; >
When the lock(X) is set to “write-locked” (in the final else clause), the item is exclusively being accessed by a transaction. In order for other transactions to access it, the LOCK WAIT must end (updating process must finish) and lock(X) = “unlocked”. This is what we wait for in the next line.
The write_lock operation –
write_lock(X): label: if lock(X) = "unlocked" < then lock(X) = "write-locked" >else //if a read-lock is issued to item X < wait (until lock(X) is "unlocked"); //so that the lock manager may wake up the next transaction go to label; >
If an item is unlocked, we simply write-lock it to grant exclusive access to the current transaction. Now the lock manager system must put all other transactions in a queue. If the item is in a read-lock state, the write-lock may NOT be directly issued. The item must first be unlocked before it can be write-locked. In doing so, the lock manager system also wakes up the queued transactions.
The unlock operation –
unlock(X): if lock(X) = "write-locked" < then lock(X) = "unlocked"; //the transactions in queue, if any, may now access item X in the manner they demand >else if lock(X) = "read-locked" < then no_of_reads-=1; //the transaction is done reading. if no_of_reads == 0 //no transactions reading the item < lock(X) = "unlocked"; //transactions in queue, if any, may now access item X in the manner they demand >>
The first case is straightforward enough. However, in the second case, we must check for the condition that there are no more current transactions sharing or reading item X. If item X is being read, we leave the situation and simply decrement the no_of_reads as the last transaction has terminated. The point here is that an item may be “unlocked only if:
Here are a few rules that Shared/Exclusive Locks must obey:
When we relax these rules, a new dimension of interchanging the status of locks on items can be introduced. This has been explained in the following article: Lock Based Concurrency Control Protocol in DBMS
Drawbacks of Shared/Exclusive Locks :
Certify Locks :
The motivation behind introducing certify locks is the failure of previously mentioned locks to deliver an efficient and promising architecture which does not compromise on speed of processing transactions. Here we briefly look at a form of multiple-mode locking scheme which allows for the lock to be characterized by 3 locked states and 1 unlocked state.
Transactions may issue any of 3 locked states or 1 unlocked state
The states an item may be issued are :
Here is how a certify lock is used in multi-version concurrency control techniques:
In Step 2, two versions of Item X are created i.e., the committed and local versions.In Step 4, the transaction must issue a certify lock on all writes it has made. Only then can we proceed to the next step. The updates on Local version are now final.
In order for multiple transactions to access the contents of data item X, a compatibility table must be drawn so that any collision or error is not returned, which may delay the process.
Locks on X
