Record Locking and Deadlocking - Softkhazana

Record locking mechanism is the most common method to implement the concurrency control in DBMS. Locking means record must be locked (i.e. access disallowed) for other users, when any user opens it for update.

Suppose in the above example, as soon as an opens his account through his ATM card. The record is locked for person B, when A completes his transaction, the balance is updated to 2000 $ and record is unlocked. Now when B opens his account, the system will show the balance of B cannot overdraw 2000 $ and thus it. 

Types of Record Locking Record locking can be of following two types:

Shared Locks 

Shared locks are also called Read Locks. When shared lock is applied by a transaction, other users or transactions can be read but not update the locked record shared locks are applied in case when you want to open same record for reading such that no other user changes these records during this time. Other transactions can also apply shared locks but not exclusive locks on these records. 

Exclusive Locks 

Exclusive locks are also called Write Locks. In case of exclusive locking, other transactions cannot be read or update the locked record. Exclusive lock is applied in situations when you want to change or update record such that no other user or transaction is able to read or update this record during this time. Other transactions cannot apply any type of lock on exclusively locked records. It note that locks are applied for short time only as other users are restricted for certain operations during this time. 

Deadlock 

Record locking mechanism may create an undesirable situation where two or more users are waiting for the completion of each other's actions. This situation is called deadlock. In order to explain the concept of deadlock, let's take an example. Suppose there are two users Jone and Tony. They are working in a multi-user environment and try to access a common database table "student". Jone accesses record number 3 and exclusively lock it. Similarly, Tony accesses record number 6 and he exclusively locks it. Now Jone needs to read record number 6 before releasing record number 3 and Tony needs to read record number 3 before releasing record number 6.Both are waiting for each other's actions, which will never occur. A deadlock has been created and working of both the users has been stopped. More users may get involved in this deadlocked situation. A deadlock situation may also arise in real world. Suppose two trains approach each other from opposite sides on a single track. Each train wishes other one to get back to make way for it. But this will never happen. A deadlock has happened. 

Controlling Deadlock 

There are different methods to control the situation of deadlock. The most common method is Deadlock Prevention. In deadlock prevention it is required that all the needed records are locked in the beginning of a transaction. No record is locked afterwards until the completion of the transaction. If at the beginning of transaction, some records (which are needed) are already locked, and then the transaction must wait to start until all the needed records are released. Another method is Deadlock Resolution in which DBMS takes its responsibility, to solve the deadlocked situations. Users don't need to take any action.