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

By The WAy Even if all the applications do not lock resources in the same order, dead-
lock will be prevented for those that do. Sometimes this policy is imple-
mented with an organizational programming standard such as “Whenever processing
rows from tables in a parent-child relationship, lock the parent row before the child
rows.” This policy will at least reduce the likelihood of deadlock and thus save the
DBMS from having to recover from some deadlocked transactions.

Almost every DBMS has algorithms for breaking deadlock, when it does occur. First,
the DBMS must detect that it has occurred. Then the typical solution is to cancel one of the
transactions and remove its changes from the database. You will see variants of this with SQL
Server, Oracle Database, and MySQL in the next three chapters.

Optimistic Versus Pessimistic Locking
Locks can be invoked in two basic styles. With optimistic locking , the assumption is made
that no conflict will occur. Data are read, the transaction is processed, updates are issued, and
then a check is made to see if conflict occurred. If not, the transaction is finished. If conflict
did occur, the transaction is repeated until it processes with no conflict. With pessimistic
locking , the assumption is made that conflict will occur. Locks are issued, the transaction is
processed, and then the locks are freed.
Figures 9-8 and 9-9 show examples of each style for a transaction that is reducing the
quantity of the pencil row in PRODUCT by 5. Figure 9-8 shows optimistic locking. First, the
data are read and the current value of Quantity of pencils is saved in the variable OldQuantity.
The transaction is then processed, and assuming that all is OK, a lock is obtained on
PRODUCT. (In fact, the lock might be only for the pencil row or it might be at a larger level of
granularity, but the principle is the same.) After obtaining the lock, an SQL statement is issued
to update the pencil row with a WHERE condition that the current value of Quantity equals
OldQuantity. If no other transaction has changed the Quantity of the pencil row, then this
UPDATE will be successful. If another transaction has changed the Quantity of the pencil row,
the UPDATE will fail. In either case, the lock is released. If the transaction failed, the process is
repeated until the transaction finishes with no conflict.

/* *** EXAMPLE CODE - DO NOT RUN *** */

Figure 9-8
Optimistic Locking

SELECT
FROM
WHERE

PRODUCT.Name, PRODUCT.Quantity
PRODUCT
PRODUCT.Name = Pencil ;

Set NewQuantity = PRODUCT.Quantity - 5;

{process transaction - take exception action if NewQuantity < 0, etc.

Assuming all is OK: }

LOCK PRODUCT;

UPDATE
SET
WHERE

PRODUCT
PRODUCT.Quantity = NewQuantity
PRODUCT.Name = Pencil
PRODUCT.Quantity = OldQuantity; AND

UNLOCK PRODUCT;

{check to see if update was successful;
if not, repeat transaction}