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

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

LOCK

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: }

UPDATE
SET
WHERE

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

UNLOCK PRODUCT;

Figure 9-9
Pessimistic Locking

{no need to check if update was successful}

Figure 9-9 shows the logic for the same transaction using pessimistic locking. Here a lock
is obtained on PRODUCT before any work is begun. Then values are read, the transaction is
processed, the UPDATE occurs, and PRODUCT is unlocked.
The advantage of optimistic locking is that locks are held for much less time than with
pessimistic locking because locks are obtained only after the transaction has finished. If the
transaction is complicated or if the client is slow (due to transmission delays, the client doing
other work, or the user getting a cup of coffee or shutting down without exiting the browser),
optimistic locking can dramatically improve throughput. This advantage will be especially
true if the lock granularity is large—say, the entire PRODUCT table.
The disadvantage of optimistic locking is that if there is a lot of activity on the pencil
row, the transaction may have to be repeated many times. Thus, transactions that involve a
lot of activity on a given row (purchasing a popular stock, for example) are poorly suited for
optimistic locking.
In general, the Internet is a wild and woolly place, and users are likely to take unexpected
actions, such as abandoning transactions in the middle. So, unless Internet users have been
prequalified (by enrolling in an online brokerage stock purchase plan, for example), optimistic
locking is the better choice in that environment. On intranets, however, the decision is more dif-
ficult. Optimistic locking is probably still preferred unless some characteristic of the application
causes substantial activity on particular rows or if application requirements make reprocessing
transactions particularly undesirable.

SQL Transaction Control Language and Declaring Lock Characteristics

As you can see, concurrency control is a complicated subject; determining the level, type, and
placement of the lock is difficult. Sometimes, too, the optimum locking strategy depends on
which transactions are active and what they are doing. For these and other reasons, database
application programs do not generally explicitly issue locks as shown in Figures 9-8 and 9-9.
Instead, they mark transaction boundaries using SQL Transaction Control Language (TCL) ,
and then declare the type of locking behavior they want the DBMS to use. In this way, the
DBMS can place and remove locks and even change the level and type of locks dynamically.
Figure 9-10 shows the pencil transaction with transaction boundaries marked with the
SQL TCL standard commands for controlling transactions:

The SQL BEGIN TRANSACTION statement ,

●

The SQL COMMIT TRANSACTION statement , and

●

The SQL ROLLBACK TRANSACTION statement . ●