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 directory than the one containing PartsDBE):
isql=> CONNECT TO '../sampledb/PartsDBE';
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 Title not available, Title not available, and Title not available.

Figure 5-7 Lock Requests 1: Waiting for Exclusive Lock

Figure 5-8 Lock Requests 2: Waiting for Share Locks

Figure 5-9 Lock Requests 3: Share Locks Granted

Sample Transactions Using Isolation Levels

The following sections show typical situations in which different isolation levels affect the behavior of your transactions when using the sample DBEnvironment PartsDBE.

Example of Repeatable Read

The following scenario illustrates the operation of the RR isolation level:

Two users each issue the following CONNECT statement (assume they are connecting from a different directory than the one containing PartsDBE):
isql=> CONNECT TO '../sampledb/PartsDBE';
User 1 then issues a query (transaction 1) as follows:
isql=> SELECT * FROM PurchDB.Vendors;
This implicitly issues a BEGIN WORK statement at the RR isolation level, and obtains a share lock (S) on the Vendors table, because the scan is a sequential one, reading the entire table. User 1 sees the query result in the ISQL browser, and exits the browser, but does not issue a COMMIT WORK statement.
User 2 then issues the following statement (which starts transaction 2 at the RR isolation level):
isql=> UPDATE PurchDB.Vendors > SET ContactName = 'Harry Jones' > WHERE VendorNumber = 9001;
Transaction 2 now must wait for an IX lock on the Vendors table because an IX lock is not compatible with the S lock already held by transaction 1. Transaction 2 also must obtain an X lock on the page containing data for vendor 9001.
User 1 now issues the following statement:
isql=> COMMIT WORK;
Transaction 2 can now complete the update, because transaction 1 no longer holds the S lock on the Vendors table. This makes it possible for transaction 2 to obtain the IX lock on the Vendors table and the X lock on the page containing data for 9001.

Example of Cursor Stability

The following scenario illustrates the operation of the CS isolation level:

Two users each issue the following CONNECT statement (assume they are connecting from a different directory than the one containing PartsDBE):
isql=> CONNECT TO '../sampledb/PartsDBE';
User 1 then sets the CS isolation level for transaction 1 and issues the following query:
isql=> BEGIN WORK CS; isql=> SELECT * FROM PurchDB.Vendors;
User 1 sees the query result in the ISQL browser, but does not exit the browser.
User 2 then issues the following statement (this statement implicitly starts transaction 2 at the RR isolation level):
isql=> UPDATE PurchDB.Vendors > SET ContactName = 'Harry Jones' > WHERE VendorNumber = 9001;
Transaction 2 now waits for an exclusive lock on a page in the Vendors table, because transaction 1 still has a cursor positioned on that page.
User 1 now exits from the ISQL browser, but does not issue a COMMIT WORK statement.
Transaction 2 can now complete the update, because transaction 1's cursor is no longer positioned on the page that transaction 2 wishes to update.
Transaction 1 now attempts to issue the same query again, using a REDO statement:
isql=> REDO; SELECT * FROM PurchDB.Vendors;
Now transaction 1 waits, because transaction 2 has obtained an exclusive lock on the table.
Transaction 2 issues the following statement:
isql=> COMMIT WORK;
The query result for transaction 1 now appears in the ISQL browser again, this time with the changed row in the query result.

Example of Read Committed

The following scenario illustrates the operation of the RC isolation level in concurrent transactions in the sample DBEnvironment PartsDBE. Most of the details are the same as for the CS example just presented:

Two users each issue the following CONNECT statement (assume they are connecting from a different directory than the one containing PartsDBE):
isql=> CONNECT TO '../sampledb/PartsDBE';
User 1 then sets the RC isolation level for transaction 1 and issues the following query:
isql=> BEGIN WORK RC; isql=> SELECT * FROM PurchDB.Vendors;
User 1 sees the query result in the ISQL browser, but does not exit the browser.
User 2 then issues the following statement (this statement implicitly starts transaction 2 at the RR isolation level):
isql=> UPDATE PurchDB.Vendors > SET ContactName = 'Harry Jones' > WHERE VendorNumber = 9001;
Transaction 2 is able to perform the update, because the locks on pages that were obtained by transaction 1's cursor were released as soon as the data was placed in transaction 1's tuple buffer. Notice the difference between RC and CS.

Example of Read Uncommitted

The following scenario illustrates the operation of the RU isolation level:

Two users each issue the following CONNECT statement (assume they are connecting from a different directory than the one containing PartsDBE):
isql=> CONNECT TO '../sampledb/PartsDBE';

User 1 issues the following update:

   isql=> UPDATE PurchDB.Vendors SET ContactName = 'Rogers, Joan'  
   > WHERE VendorNumber = 9005;

User 2 then sets the RU isolation level for transaction 2 and issues a query:
isql=> BEGIN WORK RU; isql=> SELECT * FROM PurchDB.Vendors WHERE VendorNumber = 9005;
User 2 sees the desired row in the ISQL browser, where the contact name for vendor 9005 is Rogers, Joan, even though user 1 has not issued a COMMIT WORK statement. In other words, user 2 has read uncommitted data.