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

or interference. Without concurrency control techniques, simultaneous processing
of transactions is not possible.
Broadly, there are two basic approaches to concurrency control: one is a pes-
simistic or conservative approach and the other is an optimistic approach. The pes-
simistic approach takes the view that you must take every precaution to prevent
interference and conflicts among concurrent transactions. Such preventive measures
involve heavy overhead in transaction processing. In the optimistic approach, you
assume that interference and conflicts among concurrent transactions are rare and
that therefore enormous system overhead for preventing conflicts is not warranted.
Instead, you take action as and when such infrequent conflicts do arise.
The pessimistic or conservative method makes use of concurrency control pro-
tocols that ensure serializability. One set of protocols relate to locking of data items;
the other set relate to timestamping of transactions. We will discuss both concur-
rency control techniques. We will also cover the optimistic approach to concurrency
control. You will learn about the applicability of these techniques to specific data-
base environments.
Lock-Based Resolution
Why do conflicts arise while transactions process concurrently? Think of the under-
lying reason. The operations in a schedule interact with the same data item; they
need to read or write or do both operations on the same data item. We reviewed
the three types of problems that can be caused by concurrent processing above.
An obvious method to ensure serializability is to require that operations inter-
act with a particular data item in a mutually exclusive manner. While one transac-
tion is accessing a data item, no other transaction must be allowed to modify that
data item. Allow a transaction to perform an operation on a data item only if the
transaction holds a lock on that data item.
Locking and Locks Locking is a preventive procedure intended to control con-
current database operations. When one transaction performs database operations
on a data item, a lock prevents another transaction from performing database oper-
ations on that data item. Locking is meant to ensure database consistency and cor-
rectness. Unlocking is the function of releasing a lock on a data item so that other
transactions may now perform database operations on that data item.
A lock is simply a variable associated with a data item. This variable indicates
the status of a data item as to whether or not it is open for any operation to be per-
formed on it. A binary lock constitutes a simple locking mechanism. It can have two
values representing the locked or unlocked states of a data item. For example, if the
value of the lock variable for a data item is 1, the data item is locked and may not
be accessed; if the value is 0, the data item is unlocked and available for access. A
binary lock enforces mutual exclusion of transactions. In the database environment,
the lock manager component of the DBMS keeps track of locks and exercises
control over data items.
Locking Schemes Binary locking is a straightforward locking scheme. The
locking rules are simple: At most only one transaction can hold a lock on a partic-
ular data item; if a transaction holds a binary lock on a data item, it has exclusive