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.
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 '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 account 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.