Databases Concurrency Control
Purpose of Concurrency Control What is lock & unlock
Types of locking and system lock table binary locks & its rules
shared/exclusive lock(read/write lock) & its rules conversion of locks
Two-Phase locking(2PL) & guarantees serializability growing phase & shrinking phase
variation of two phase locking Basic Two-Phase Locking
Conservative Two-Phase Locking Strict Two-Phase Locking
Purpose of Concurrency Control
• To enforce Isolation (through mutual exclusion) among conflicting transactions.
• To preserve database consistency through consistency preserving execution of transactions.
• To resolve read-write and write-write conflicts.
Two-Phase Locking Techniques
Locking is an operation which secures (a) permission to Read or (b) permission to Write a data item for a transaction.
Example: Lock (X). Data item X is locked in behalf of the requesting transaction.
Unlocking is an operation which removes these permissions from the data item. Example: Unlock (X). Data item X is made available to all other transactions.
Binary Locks.
A binary lock can have two states or
values: locked and unlocked (or
1 and 0, for
simplicity).
A distinct lock is associated with each
database item
X.
If the
value of the lock on
X is 1, item X
cannot be accessed by a database operation that
requests the item.
If the value of the lock on
X is 0, the item can
be accessed when
requested, and the lock value is
changed to 1.
Rules for binary lock
If the simple binary locking scheme described here is
used, every transaction must obey the following rules:
1. A transaction T must issue the operation
lock_item(X) before any
read_item(
X) or
write_item(X) operations are performed in T.
2. A transaction T must issue the operation
unlock_item(X) after all read_item(X)
and
write_item(
X) operations are completed in T.
3. A transaction T will not issue a lock_item(X)
operation if it already holds the
lock on item
X.
Implementation of Binary Lock
It is quite simple to implement a binary lock;
all that is needed is a binary-valued variable,
LOCK, associated with each data item
X in the
database.
In its simplest
form, each lock can be a record
with three fields:
<Data_item_name, LOCK, Locking_transaction>
plus a queue for transactions that are waiting to
access the item.
The system needs to maintain
only these
records for the items that are currently locked
in a
Shared/Exclusive (or Read/Write) Locks.
The binary locking scheme is too restrictive for database items because at most, one transaction can hold a lock on a given item.
We should allow several transactions to access the same item
X if they all access X for reading purposes only.
This is because read operations on the same item by different transactions are not conflicting.
However, if a transaction is to write an item X, it must have exclusive access to X.
For this purpose, multiple-mode lock is used. shared/
exclusive or read/ write locks—there are three locking operations: read_lock(X), write_lock(X), and unlock(X).
A read-locked item is also called share-locked because other transactions are allowed to read the item, whereas a write-locked item is called exclusive-locked because a single transaction
Rules followed on shared/exclusive locking
1. A transaction T must issue the operation read_lock(X) or
write_lock(X) before any read_item(X) operation is performed in T.
2. A transaction T must issue the operation write_lock(X) before any write_item(X) operation is performed in T.
3. A transaction T must issue the operation unlock(X) after all read_item(X) and write_item(X) operations are completed in T.3
4. A transaction T will not issue a read_lock(X) operation if it already holds a read (shared) lock or a write (exclusive) lock on item X. This rule may be relaxed, as we discuss shortly.
5. A transaction T will not issue a write_lock(X) operation if it already holds a read (shared) lock or write (exclusive) lock on item X. This rule may also be relaxed, as we discuss shortly.
Lock conversion
Lock upgrade: existing read lock to write lock
if Ti has a read-lock (X) and Tj has no read-lock (X) (i
j)
then
convert read-lock (X) to write-lock (X)
else
force Ti to wait until Tj unlocks X
Lock downgrade: existing write lock to read lock
Ti has a write-lock (X) (*no transaction can
have any lock on X*)
Two-Phase Locking Techniques: The algorithm
Two Phases: (a) Locking (Growing) (b) Unlocking (Shrinking).
Locking (Growing) Phase: A transaction applies locks (read or write) on desired data items one at a time.
Unlocking (Shrinking) Phase: A transaction unlocks its locked data items one at a time.
Requirement: For a transaction these two phases must be mutually exclusively, that is, during locking phase unlocking phase must not start and during unlocking phase locking phase must not begin.
Example for transaction which do not obey Two-Phase Locking
Transactions T1 and T2 in Figure 22.3(a) do not follow the two-phase locking protocol because the write_lock(X) operation follows the unlock(Y) operation in T1,
How to make previous Phase locking into which obeying Two-Phase Locking (Basic Two-Two-Phase Locking) – not a dead lock free
If we enforce two-phase locking, the transactions can be rewritten as T1 and T2, as shown in Figure 22.4
It can be proved that, if every transaction in a schedule follows the two-phase locking protocol, the schedule is guaranteed to be serializable, obviating the need to test for serializability of schedules.
