![]() |
Getting Locking Information
|
|
Displaying Current Locking InformationLocking information is provided by querying the LockTable and TransactionTable VTIs. Typically, you will derive the locking information you need by writing queries that join these two virtual tables on the XID column. LockTableEach row of the COM.cloudscape.vti.LockTable virtual table corresponds to a lock and its associated objects. LockTable is a built-in class alias, so you can invoke LockTable without using the class name. For example: SELECT * FROM NEW LockTable() AS LT NOTE: Update locks can only be acquired on update cursors (those declared with the FOR UPDATE clause). TransactionTableEach row of the COM.cloudscape.vti.TransactionTable VTI (aliased as TransactionTable) corresponds to a transaction and its associated objects. The columns of TransactionTable are: Example Using the Lock Manager VTIsThe following example illustrates a use of the lock diagnostics VTIs. Statements that create locks on the HotelAvailability and FlightAvailability tables are run, and then a query of LockTable and TransactionTable shows the status of the locks.
SELECT Locks.xid, Locks.Type, TableName, Mode, Lockname, State XID |TYPE |TABLENAME |MODE|LOCKNAME |STATE ------------------------------------------------------------------------------- 995 |ROW |HOTELAVAILABILITY |X |(1,12) |GRANT 1000 |ROW |HOTELAVAILABILITY |S |(2,1) |GRANT 995 |ROW |HOTELAVAILABILITY |X |(1,13) |GRANT 995 |ROW |HOTELAVAILABILITY |X |(1,14) |GRANT 995 |ROW |HOTELAVAILABILITY |X |(1,15) |GRANT 1000 |ROW |FLIGHTAVAILABILITY |X |(1,19) |GRANT 995 |TABLE|HOTELAVAILABILITY |IX |Tablelock |GRANT 1000 |TABLE|HOTELAVAILABILITY |IX |Tablelock |GRANT 1000 |ROW |FLIGHTAVAILABILITY |X |(1,18) |GRANT 1000 |ROW |FLIGHTAVAILABILITY |X |(1,17) |GRANT 1000 |ROW |FLIGHTAVAILABILITY |X |(1,16) |GRANT 995 |ROW |HOTELAVAILABILITY |X |(1,6) |GRANT 1000 |ROW |HOTELAVAILABILITY |X |(1,6) |WAIT 995 |ROW |HOTELAVAILABILITY |X |(1,7) |GRANT 1000 |ROW |FLIGHTAVAILABILITY |X |(1,11) |GRANT 1000 |ROW |FLIGHTAVAILABILITY |X |(1,10) |GRANT 1000 |ROW |FLIGHTAVAILABILITY |X |(1,9) |GRANT 1000 |ROW |FLIGHTAVAILABILITY |X |(1,8) |GRANT 1000 |ROW |FLIGHTAVAILABILITY |X |(1,15) |GRANT 1000 |ROW |FLIGHTAVAILABILITY |X |(1,14) |GRANT 1000 |ROW |FLIGHTAVAILABILITY |X |(1,13) |GRANT 1000 |ROW |FLIGHTAVAILABILITY |X |(1,12) |GRANT 995 |ROW |HOTELAVAILABILITY |X |(1,16) |GRANT 995 |ROW |HOTELAVAILABILITY |X |(1,17) |GRANT 1000 |TABLE|FLIGHTAVAILABILITY |IX |Tablelock |GRANT 995 |TABLE|FLIGHTAVAILABILITY |IX |Tablelock |GRANT 995 |ROW |HOTELAVAILABILITY |X |(1,18) |GRANT 995 |ROW |HOTELAVAILABILITY |X |(1,19) |GRANT 995 |ROW |FLIGHTAVAILABILITY |S |(2,1) |GRANT 1000 |ROW |FLIGHTAVAILABILITY |X |(1,7) |GRANT 995 |ROW |HOTELAVAILABILITY |X |(1,20) |GRANT 1000 |ROW |FLIGHTAVAILABILITY |X |(1,6) |GRANT 995 |ROW |FLIGHTAVAILABILITY |X |(1,6) |WAIT 995 |ROW |HOTELAVAILABILITY |X |(1,8) |GRANT 995 |ROW |HOTELAVAILABILITY |X |(1,9) |GRANT 995 |ROW |HOTELAVAILABILITY |X |(1,10) |GRANT 995 |ROW |HOTELAVAILABILITY |X |(1,11) |GRANT Note in this example that transactions 995 and 1000 are competing for the same locks. For the row lock on (1,6) in HotelAvailability, Transaction 995 has the lock and transaction 1000 is waiting. For the rowlock on (1,6) in FlightAvailability, transaction 1000 has the lock and Transaction 995 is waiting. (These two transactions are deadlocked). At this point, you may want to see the text of the SQL statements associated with each of these transactions. You could query the TransactionTable like this:
SELECT SQL_TEXT FROM NEW TransactionTable() AS Trans NOTE: See the sample program JBMSTours.AdminHelper for this example of forcing a deadlock and working with the lock diagnostics VTIs. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |