ALLBASE/SQL Reference Manual (36216-90216)

Chapter 5 189

Concurrency Control through Locks and Isolation Levels

Examples of Obtaining and Releasing Locks

The following sections present a few scenarios that show how locks are obtained and

released within concurrent transactions.

Simple Example of Concurrency Control through Locking

The following scenario illustrates in a simple way how locks are obtained and released. It

is based on the sample DBEnvironment PartsDBE, which is fully described in Appendix C.

Try this example yourself on a system that has several terminals available in physical

proximity to one another, and observe the results:

• Four users each issue the following CONNECT statement (assume they are connecting

from a different group and account than the one containing PartsDBE):

isql=> CONNECT TO 'PartsDBE.SomeGrp.SomeAcct';

• User 1 issues the following query (transaction 1):

isql=> SELECT SALESPRICE FROM PurchDB.Parts

> WHERE PartNumber = '1123-P-01';

At this point, transaction 1 obtains a share lock on page A.

• User 2 issues the following UPDATE statement (transaction 2):

isql=> UPDATE PurchDB.Parts SET SalesPrice = 600.

> WHERE PartNumber = '1123-P-01';

Transaction 2, executing concurrently, needs an exclusive lock on page A. Transaction 2

waits.

• Users 3 and 4 each issue the following query, independently (transactions 3 and 4):

isql=> SELECT * FROM PurchDB.Parts;

Transactions 3 and 4, executing concurrently, each need a share lock on page A.

Transactions 3 and 4 wait, because of an upcoming exclusive lock request.

• User 1 issues the following statement:

isql=> COMMIT WORK;

• Transaction 1 terminates, so transaction 2 obtains its exclusive lock on page A.

Transactions 3 and 4 still wait.

• User 2 issues the following statement:

isql=> COMMIT WORK;

• Transaction 2 terminates, so transactions 3 and 4 both obtain share locks on page A.

This sequence is illustrated in Figure 5-7., Figure 5-8., and Figure 5-9..