Types of lock in RDBMS

   Types of lock.
•    The DBMS mainly uses the following types of locking techniques.
1)    Binary locking
2)    Exclusive locking
3)    Shared Locking
4)    Two-phase locking
5)    Three-Phase  locking

1)    Binary locking  :
•    In binary locking, there are two state of locking namely (a) locked (or ‘1’) or (b) unlocked (‘0’).
•    If an object of a database table, page, tuple (row) or attribute(field) is locked by a transaction,- no other transaction can use that object. A distinct lock is associated with each database item .
•    If the value of lock on data item x is 1, item x cannot be accessed by a database operation that requires  the item. If an object (or data item) x is unlocked, any transaction can lock the object for its use.
•    Two operations, lock_item(data item) and unlock_item(data item) are used with binary locking. A transaction requests access to a data item X by first issuing a lock_item(x) operation. if lock(x)=1, the transaction is forced to wait. If LOCK(x)=0, it is set to 1 and the transaction is allowed to access item X.
2)    Shared /Exclusive(or read/write) Locking :
•    A shared /exclusive lock use multiple-mode lock. In this type of locking, there are three locking operations  namely (a) Read_lock(A),(b) Write_lock(B), and Unlock (A).
•     A read-locked item is also called shared-locked, because other  transaction are allowed to read the item.
•     A write- locked is called exclusive lock , because a single transaction exclusively holds the lock exists when access is specifically reserved for the transaction that locked the object.
•     The exclusive lock must be used when there is a chance of conflict. An exclusive lock is used when a transaction wants to write a data item and no locks are currently held on that data item by any other   transaction If transaction T2 updates data item A, then an exclusive lock is required by transaction T2 over data item A .
•    The exclusive lock is granted if and only if no other locks are held on the data item.
•    A shared lock exists when concurrent transaction are granted READ access on the  basis of a common lock. A shared lock is used when a transaction wants to Read data from the  database and no exclusive lock is held on that data item.
•    For example, if transaction  T1 has shared lock on data item A, and transaction T2 wants to Read data item A, transaction T2 may also obtain a shared lock on data item A .
•    If an exclusive or  shared lock is already held on data item A by transaction T1 an exclusive lock cannot be granted on transaction T2.

3)    Two-phase locking (2PL) : 
•    Two – phase locking is method  or a protocol of controlling concurrent processing in which all locking operation recede the first unlocking operation. Thus a transaction is said to follow the two-phase locking protocol if all locking operations precede the first unlock operation in the transaction. 2-PL has the following two phase :
a.    A  growing phase, in which a transaction acquires all the required locks without unlocking any data once all locks have been acquired, the transaction is in it’s locked  point .
b.    A shrinking phase , in which a transaction releases  all locks and cannot obtain any new lock.
•    The  above two-phase locking  is governed by following rules.
o    Two transactions cannot have conflicting locks.
o    No unlocks operation can precede a lock operation in the same transaction.
o    No data are affected until  all locks are obtained, that is until the transaction is in its locked point .
•    Following fig. 1.1 illustrates schematic of two- phase  locking. In case of a strict two-phase locking the interleaving is not allowed . fig 1.2 shows a  schedule with strict two-phase locking in which transaction T1 would obtain an exclusive lock on A first and then Read and write A.
•    fig 1.3 illustrates an example of strict two-phase locking with serial execution in which  first  strict locking is done as explained above , then transaction T2 would request an exclusive lock on A.
•    However, this request cannot be granted until transaction T1 releases its exclusive lock on A , and the DBMS therefore, suspends  transaction T2 transaction T1 now proceeds to obtain an exclusive lock on B, Read and write B, then finally commits , at which time its locks are released. The lock request  of transaction T2 is now granted , and it proceeds.