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

control over the data item until the lock is released. But a transaction-processing
database environment needs more sophisticated locking schemes. If transaction T1
just needs to read a data item, it does not have to lock the data item from another
transaction T2 that also just wants to read the data item. Let us explore the locking
schemes commonly available in database management systems.
Two basic types of locks are available:
Exclusive lock. Also referred to as a write lock and indicated as an X-lock, this
locking mechanism provides exclusive control over the data item. When a transac-
tion holds an X-lock on a data item, no other transaction can obtain a lock of any
type on the data item until the first transaction releases the X-lock.
Shared lock. Also referred to as a read lock and indicated as an S-lock, this lock
type allows other transactions to share the data item for access. While a transaction
T1 holds an S-lock on a data item, another transaction T2 can also acquire an
S-lock on that data item, but T2 cannot obtain an X-lock on that data item until
all shared locks on the data item are released. When a transaction acquires an
S-lock on a data item, it is allowed to read the data item but not to write to that
data item.
Let us consider locking and unlocking of a data item D by a transaction T using
X- and S-locks. The locking scheme provided by the DBMS works as follows:
1. T must acquire an S-lock(D) or X-lock(D) before any read(D) operation in
T.
2. T must acquire an X-lock(D) before any write(D) operation in T.
3. T need not request an S-lock(D) if it already holds an X-lock (D) or an
S-lock(D).
4. T need not request an X-lock(D) if it already holds an X-lock (D).
5. T must issue an unlock(D) after all read(D) and write(D) operations are
completed in T.
Locking Levels In our discussion on locking, we have mentioned locking a data
item. What is a data item? What is the unit of data we need to lock and unlock?
Naturally, the granularity size of data a transaction needs to lock depends on the
intention and scope of the transaction. DBMSs support multiple levels of granu-
larity for locking. Figure 15-18 shows a hierarchy of locking levels provided for a
database system.
When multiple transactions execute concurrently, some may need to lock only at
the record level and a few at the field level. Transactions very rarely need to lock
at the entire table level in normal transaction processing. The entire database is
hardly ever locked by a single transaction. Multiple granularity level protocols
are flexible to allow locking at different levels even for transactions in a single
schedule.
Lock Management The DBMS must ensure that only serializable and recover-
able schedules are permitted while processing concurrent transactions. No opera-
tions of committed transactions can be lost while undoing aborted transactions. A