Lock:

Lock is a variable associated with a data item that describes the status of the item with
respect to possible operations that can applied to it.

– One lock with each data item.

– The lock is used to synchronize the access to the data item.

Types of Locks

1. Binary Locks

2. Multiple-mode Locks

Binary Locks

The lock can have two states (locked, unlocked) or (1, 0) for simplicity.

– It is simple but restrictive.

– A distinct lock is associated with each database item X.

– The current value of the lock of item X is LOCK(X).

– If LOCK(X) is 0, item X can be accessed when required.

– If LOCK(X) is 1, item X cannot be accessed when required.

– Hence, binary lock enforces mutual exclusion on the item.

– Two operations, lock_item(X) and unlock_item(X) are used with the binary
locking.

In binary locking, every transaction must follow the following rules:

1. A transaction T must issue lock_item(X) before any read_item(X) or

2. A transaction T must issue unlock_item(X) after all read_item(X) and

4. A transaction T will not issue an unlock_item(X) unless it already holds the lock
on item X.

5. The rules can be forced by the lock manager module of the DBMS.

6. Thus, at most one transaction can hold the lock on an item which leads to no two
transactions can accessed the same item concurrently.
 In R/W locking the system must follow these rules:
1. A transaction T must issue read_lock(X) or write_lock(X) before any read_item(X)
performed in T.
2. A transaction T must issue write_lock(X) before any write_item(X) performed in T.
3. A transaction T must issue unlock(X) after all read_item(X) and write_item(X)
performed in T.
4. A transaction T will not issue read_lock(X) if it already holds a read lock or write lock on
item X.
5. A transaction T will not issue write_lock(X) if it already holds a read lock or write lock
on item X.
Conversion of locks
A Transaction is allowed under certain conditions to convert the lock from one state to another.
• Upgrading:
– Convert the lock from shared to exclusive by issuing write_lock(X) after its
read_lock(X).
– The transaction must be the only one has the read lock or it must wait.
• Downgrading:
– Convert the lock from exclusive to shared by issuing read_lock(X) after the
write_lock(X).
• Notes:
– Upgrading and downgrading relax rule 4 and 5 of the R/W locking scheme.
– The lock table structure must hold the transaction ID.
Two-Phase Locking (2PL)
• A transaction is said to follow the 2PL protocol if all locking operations precede the first
unlock operation of the transaction.
• The transaction is divided into two phases:
1. Growing or Expanding phase (first phase) [where new locks can be issued and
non can be released]
 2. Shrinking phase (second phase)[where existing locks can be released and no new
locks can be granted]
• In case of lock conversion:
1. Upgrading must be during Growing phase.
2. Degrading must be during shrinking phase.
Example:
T1’ T2’
read_lock(Y); read_lock(X);
read_item(Y); read_item(X);
write_lock(X); write_lock(Y);
unlock(Y); unlock(X);
read_item(X); read_item(Y);
X:=X+Y; Y:=X+Y;
write_item(X); write_item(Y);
unlock(X): unlock(Y);

• 2PL limits the concurrency by early locking all items even it may not need all of them
early and by delaying unlocking all items until locking all the item it needs even it may
not need the locked item.
• The algorithm is called the Basic 2PL and there are many variations to it.
Conservative 2PL:
• It is called also Static 2PL.
• It requires a transaction to lock all the items it accesses before the transaction
begins execution (by declaring its read and write sets).
• If the transaction cannot lock any item, it must wait until lock all the items.
• It is difficult in practice because it is not possible in most cases to get the read and
write sets.
• The conservative 2PL is a deadlock-free protocol.
• The transaction starts as in its shrinking phase
Strict 2PL:
• It is the most popular variation of 2PL in practice.
• It guarantees strict schedule.
• It requires that a transaction T does not release any of its exclusive locks until it
commits or aborts.
• Strict 2PL is not a deadlock-free protocol.
Rigorous 2PL:
• It is a more restrictive variation of Strict 2PL.
• It guarantees strict schedule also.
 • It requires that a transaction T does not release any of its locks (shared or
exclusive) until after it commits or aborts.
• It is easier to be implemented than the strict 2PL.
• The transaction is in its expanding phase until it ends.
Note:
• The concurrency control subsystem of the DBMS is responsible for generating the
required locks according to the locking protocol used.
• The use of locks can lead to two additional problems: Deadlock - Starvation

Deadlock: Deadlock occurs when each transaction T in a set of two or more transactions is
waiting for some item that is locked by some other transaction T’ in the set.
T1’ T2’
read_lock(Y);
read_item(Y);
read_lock(X);
read_item(X);
write_lock(X);
write_lock(Y);