The onstat -k command displays the current locks on database objects held within the system. The type of locks and how long they are held is determined by the database logging mode, isolation levels and application design. The onstat -K command displays the same output as onstat -k plus one additional column: same.
IBM Informix Dynamic Server Version 14.10.FC5X3 -- On-Line -- Up 05:56:27 -- 23987004 Kbytes 2021-03-27 15:20:36 Locks address wtlist owner lklist type tblsnum rowid key#/bsiz DML table_name 45a7b028 0 85bfb008 0 S 100002 204 0 sysmaster:informix.sysdatabases 45a7b138 0 85bfb008 45a7b028 HDR+X c00422 0 0 oninit:informix.eric1 4dc31390 0 85bfb8e8 0 S 100002 204 0 sysmaster:informix.sysdatabases 4dc32c88 0 85bfb8e8 4dc31390 HDR+X c00410 0 0 oninit:informix.eric2 55de1428 0 85bff708 0 S 100002 209 0 sysmaster:informix.sysdatabases 55de16d0 0 85bfc1c8 0 S 100002 209 0 sysmaster:informix.sysdatabases 55de17e0 0 85c02368 0 HDR+S 100002 204 0 sysmaster:informix.sysdatabases 55de26c0 0 85c02c48 0 S 100002 201 0 sysmaster:informix.sysdatabases 5df94628 0 85bfcaa8 0 HDR+S 100002 209 0 sysmaster:informix.sysdatabases 5df948d0 0 85bfffe8 0 S 100002 201 0 sysmaster:informix.sysdatabases 5df94958 0 85c03528 0 S 100002 204 0 sysmaster:informix.sysdatabases 5df94a68 0 85c03528 5df94958 HDR+X c003b2 0 0 oninit:informix.eric3
|address||The in-memory address of the lock structure.||Hex|
|wtlist||The address of the first userthread waiting for this lock.||Hex||onstat -u|
|owner||The address corresponding to the userthread which holds this lock.||Hex||onstat -u|
|lklist||The address of the next lock owned by this userthread. A zero indicates the end of the list.||Hex|
|type||Describes the type of lock being held using one or more of the following coded values:
HDR Header B Byte lock IS Intent shared S Shared SR Shared lock by RR U Update UR Update lock by RR IX Intent exclusive SIX Shared, intent exclusive X Exclusive XR Exclusive lock by RR TRR Inserter's RR test
|tblsnum||The tablespace number for the item that is locked.||Hex|
|rowid||The rowid, page or 0, within the tblspace upon which the lock is held.||Hex|
|key#/bsiz||The index key number or the number of bytes locked for a VARCHAR.||Str Dec|
|active||The number of locks that are currently active within this instance.|
|total||The total number of locks configured in the instance by the LOCKS parameter.|
|hash buckets||The number of hash buckets created to track locks.|
|lock table overflows||The number of times requests for locks has exceeded the configured value (LOCKS).|
|Column Heading||Column Description|
|same||The lock address of the next lock in the linked list that is identical to this lock.|
SELECT tabname FROM systables WHERE locklevel = 'P' AND tabid >= 100;
An examination of a simple locking situation will help illustrate Informix's locking principles. In the window below, a simple transaction has been started which deletes 1 row from the customer table in the stores7 database. This should create a lock on the database as well as a lock on the row being deleted.
informix921@hobbit: dbaccess stores7 Database selected begin work >started transaction delete from custome where customer_num = 102; >1 row(s) deleted
In a second window, the onstat -k command has been executed. There are actually five locks currently being held, all by the same user (a2d51d8). The owner's information can be displayed by executing the onstat -u command.
Locks address wtlist owner lklist type tblsnum rowid key#/bsiz a999999 0 a12345 a123456 HDR+IX 100084 0 0 a999998 0 a12345 a123457 HDR+X 100084 100 0 a999910 0 a12345 a123458 HDR+X 10009a 100 1 a999911 0 a12345 a123459 HDR+S 100002 203 0 a999912 0 a12345 a123450 HDR+X 100088 100 1 5 active, 20000 total, 16384 hash buckets, 0 lock table overflows
The lock on tblsnum 100002 is the database lock. An analysis of the output from oncheck would identify row ID 203 as the stores7 database. Everyone connecting to the stores7 database will have this same entry reported from onstat -k. The two locks on tblsnum 100084 are locks for the customer table. This can be verified by executing the following select statement in the stores7 database:
SELECT tabname, tabid, HEX(partnum) FROM systables WHERE HEX(partnum) MATCHES "*100084";
tabname customer tabid 100 (expression) 0x0000000000100084 1 row(s) retrieved.
SELECT fragtype,tabid, indexname, dbspace, HEX(partn) FROM sysfragments WHERE HEX(partn) MATCHES "*100085" OR HEX(partn) MATCHES "*10009a";
To discuss how Oninit ® can assist please call on +1-913-674-0360 or alternatively just send an email specifying your requirements.