ALLBASE/SQL Reference Manual (36216-90216)

192 Chapter5
Concurrency Control through Locks and Isolation Levels
Examples of Obtaining and Releasing Locks
> 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.
4. User 1 now issues the following statement:
isql=> COMMIT WORK;
5. 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:
1. 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';
2. 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.
3. 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.
4. User 1 now exits from the ISQL browser, but does
not issue a COMMIT WORK statement.
5. 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.
6. 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.
7. 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.