HP 3000 Manuals

Row Level Locking: Technical Addendum for ALLBASE/SQL Release F

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
       (transaction 3 and transaction 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 Transaction
       2's 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 2-7 , Figure 2-8 , and
Figure 2-9 .


          Figure 2-7.  Lock Requests 1:  Waiting for Exclusive Lock 


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


          Figure 2-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 group and account than the one
       containing PartsDBE):

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

   *   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 group and acount than the one
       containing PartsDBE):

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

   *   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 group and account than the one
       containing PartsDBE):

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

   *   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 group and account than the one
       containing PartsDBE):

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

   *   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.

MPE/iX 5.0 Documentation