Managing Multiuser Databases - Database Processing: Fundamentals, Design, and Implementation - page 402

Locks with small granularity are difficult to administer (the DBMS has to track and check many
more details), but conflicts are less common.
Locks also vary by type. An exclusive lock locks the item from any other access. No other
transaction can read or change the data. A shared lock locks the item from change but not
from read; that is, other transactions can read the item as long as they do not attempt to alter it.

Serializable Transactions
When two or more transactions are processed concurrently, the results in the database should
be logically consistent with the results that would have been achieved had the transactions
been processed in an arbitrary, serial fashion. A scheme for processing concurrent transac-
tions in this way is said to be serializable .
Serializability can be achieved by a number of different means. One way is to process the
transaction using two-phase locking . With this strategy, transactions are allowed to obtain
locks as necessary, but once the first lock is released, no other lock can be obtained. Transactions
thus have a growing phase , during which the locks are obtained, and a shrinking phase , during
which the locks are released.
A special case of two-phase locking is used with a number of DBMS products. With it,
locks are obtained throughout the transaction, but no lock is released until the COMMIT or
ROLLBACK command is issued. This strategy is more restrictive than two-phase locking re-
quires, but it is easier to implement.
Consider an order-entry transaction that processes data in the CUSTOMER, SALESPERSON,
and ORDER tables. To avoid concurrency problems, the order entry transaction issues locks on
CUSTOMER, SALESPERSON, and ORDER as needed; makes all database changes; and then re-
leases all locks.

Deadlock
Although locking solves one problem, it introduces another. Consider what can happen when
two users want to order two items from inventory. Suppose that User A wants to order some
paper, and if she can get the paper, she wants to order some pencils. Then suppose that User B
wants to order some pencils, and if he can get the pencils, he wants to order some paper. The
order of processing is shown in Figure 9-7.
In this figure, Users A and B are locked in a condition known as deadlock or sometimes
as the deadly embrace . Each user is waiting for a resource that the other has locked. This
problem can be solved either by preventing the deadlock from occurring or by allowing the
deadlock to occur and then breaking it.
Deadlock can be prevented in several ways. One way is to require users to issue all lock
requests at one time. In Figure 9-7, if User A had locked both the paper and the pencil records
at the beginning, deadlock would not occur. A second way to prevent deadlock is to require all
application programs to lock resources in the same order.

User A User B

Figure 9-7
Deadlock Example

1. Lock paper.
2. Take paper.
3. Lock pencils.

1. Lock pencils.
2. Take pencils.
3. Lock paper.

Order of processing at database server

1. Lock paper for user A.
2. Lock pencils for user B.
3. Process A's requests; write paper record.
4. Process B's requests; write pencil record.
5. Put A in wait state for pencils.
6. Put B in wait state for paper.

** Locked **