DATA INTEGRITY - Database Design and Development: An Essential Guide for IT Professionals - page 461

T1 has S-lock(D)—T2 requests X-lock(D)—T2 waits—T3 requests S-lock(D)—
T3 acquires S-lock(D)—T2 still waits—T4 requests S-lock(D)—T4 acquires
S-lock(D)—T2 still waits
In this situation, we say that T2 is in starvation mode, unable to acquire a lock
and proceed with its operations. Starvation of transactions can be avoided if lock
requests are handled with the following rules:
• When a transaction T requests a lock on a data item D in a particular mode,
grant the lock provided
• There are no other transactions holding a lock on D in a mode that conflicts
with the mode of the lock request by T, and
• There is no other transaction Tn waiting for a lock on D, where Tn made its
lock request earlier than T.
Application of Lock-Based Techniques
Lock-based concurrency control techniques depend on proper management and
control of locking and unlocking of data items. Before a transaction attempts to
perform a database operation, the transaction must request a lock on the data item.
A management facility in the DBMS must grant the lock if the data item is not
already locked by another transaction. Only after receiving a lock on a data item,
may a transaction proceed to perform a database operation. After completion of
the database operation, the lock on the data item must be released.
Simple Locking Let us now turn our attention to the application of locking and
unlocking in a few examples and appreciate the significance of concurrency control
protocols. We will begin with a simple binary locking scheme. Revisit the example
of concurrent transactions for accessing inventory record of database textbooks.
Figure 15-19 illustrates a simple locking protocol for two concurrent transaction
accessing inventory record of database textbooks.
Let us refer to the inventory record accessed as data item D. In the simple locking
scheme shown in the figure, every transaction T must obey the following plain set
of rules:
1. T must issue lock(D) before any read(D) or write(D) in T.
2. T must issue unlock(D) after all read(D) and write(D) are completed in T.
3. T will not issue unlock(D) unless it already holds a lock on D.
The lock manager will enforce these rules. If you look at the figure closely, you
note that a transaction T holds the lock on D between the initial locking and the
final unlocking. In this simple concurrency control protocol, only one transaction
can access D during any period. Two transactions cannot access D concurrently.
In practice, this protocol is too restrictive. What if two concurrent transactions
just want to read D? Even then, this protocol will delay one transaction until the
other completes. Therefore, we need to employ both S-locks and X-locks. With
S-locks, you can allow multiple transactions with only read operations to proceed
concurrently.