onstat -k/-K Locks / Locks + Same
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
Detail Definitions
| Heading | Description | Format | See Also |
|---|---|---|---|
| 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. If the address is in parenthesis it indicates a two phase commit transaction branch that do not have associated threads | 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 </div> |
Str | |
| 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 |
Summary Definitions
| Label | Description |
|---|---|
| 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). |
onstat -K additional output
| Column Heading | Column Description |
|---|---|
| same | The lock address of the next lock in the linked list that is identical to this lock. |
Notes
A rowid of 0 corresponds to a table level lock.
A rowid ending in 0x00, as in 500, represents a page level lock.
A rowid ending in anything other than 0x00 is a row level lock.
Tablespace 0x100002 always corresponds to the database tblspace. Everyone connecting to a database in the system will have a shared (or exclusive, if chosen) lock in tablespace 0x100002.
If the key#/bsiz field contains a value preceded by 'K-', it is an attached key index lock. Detached index keys must be looked up in the sysfragments table.
The HDR in the type field refers to the buffer header. The first userthread to grab the lock will grab the buffer header along with the lock. This is represented by HDR+ preceding the type of lock (e.g., HDR+X).
The onstat -k command output should be redirected to a paging utility such as 'more' or 'pg', especially on a system with a large number of locks.
A rowid ending in 0x00, as in 500, represents a page level lock.
A rowid ending in anything other than 0x00 is a row level lock.
Tablespace 0x100002 always corresponds to the database tblspace. Everyone connecting to a database in the system will have a shared (or exclusive, if chosen) lock in tablespace 0x100002.
If the key#/bsiz field contains a value preceded by 'K-', it is an attached key index lock. Detached index keys must be looked up in the sysfragments table.
The HDR in the type field refers to the buffer header. The first userthread to grab the lock will grab the buffer header along with the lock. This is represented by HDR+ preceding the type of lock (e.g., HDR+X).
The onstat -k command output should be redirected to a paging utility such as 'more' or 'pg', especially on a system with a large number of locks.
Monitoring and Tuning
Altering the value of the LOCKS parameter in the system configuration file will affect the number of
locks maintained by the instance. Since locks are allocated in blocks of memory and one block
can monitor many locks, you may not necessarily see an increase in memory size when changing the value
of LOCKS.
If a resource appears to be hung, the onstat -k command may reveal the address of the userthread holding the lock on the resource. To decipher the owner of the lock, take the address of the userthread holding the lock (owner) and perform an onstat -u (normally with a egrep). The owner address is the same as the userthread address in onstat -u.
On highly active systems, lock contention may become a problem. Lock contention can be kept to a minimum by several means. One way is through isolation levels. By setting the isolation level of the session to the appropriate level, unnecessary locks can be avoided. A second method that can be used, with regard to OLTP systems, is row level locks rather than page level locks. Although this requires the system to handle more locks, it prevents a userthread from locking an entire page just to modify a single row. A simple method to determine which tables are using page level locks and which are using row level locks is to execute the following query:
When a session connects to a database, Informix Dynamic Server places a shared lock on the database. This prevents another session from acquiring an exclusive lock on the same database. A lock will also be placed at the table level for tables being queried or modified. For queries, a shared lock (S) is acquired on the table. For modifications, a shared, intent-exclusive lock (SIX) is acquired. The purpose of the lock at the table level is to improve performance in determining whether any locks are held at lower levels (i.e., page or row). It is faster to check one table lock to determine if a modification is being made to a table, than to perform a search through possibly thousands of shared locks to find the one exclusive lock that might exist at the row level.
If a resource appears to be hung, the onstat -k command may reveal the address of the userthread holding the lock on the resource. To decipher the owner of the lock, take the address of the userthread holding the lock (owner) and perform an onstat -u (normally with a egrep). The owner address is the same as the userthread address in onstat -u.
On highly active systems, lock contention may become a problem. Lock contention can be kept to a minimum by several means. One way is through isolation levels. By setting the isolation level of the session to the appropriate level, unnecessary locks can be avoided. A second method that can be used, with regard to OLTP systems, is row level locks rather than page level locks. Although this requires the system to handle more locks, it prevents a userthread from locking an entire page just to modify a single row. A simple method to determine which tables are using page level locks and which are using row level locks is to execute the following query:
SELECT tabname FROM systables WHERE locklevel = 'P' AND tabid >= 100; </div>A locklevel of 'P' represents a page level lock; 'R' represents a row level lock. A lock level of 'B' may exist for system tables. This is left over from earlier versions of the engine and stands for Both. One myth to dispel is Informix Dynamic Server and lock escalation. Informix Dynamic Server does not escalate its locks. In other words, if you currently have a session holding a large percentage of locks against a table at the row level, Informix Dynamic Server does not upgrade the lock to a table level lock. If you have an application that is going to be modifying a significant portion of a table (as in a batch update), it would be prudent to grab a table level lock with the LOCK TABLE statement. This will reduce the number of locks required and also improve the performance of the operation.
When a session connects to a database, Informix Dynamic Server places a shared lock on the database. This prevents another session from acquiring an exclusive lock on the same database. A lock will also be placed at the table level for tables being queried or modified. For queries, a shared lock (S) is acquired on the table. For modifications, a shared, intent-exclusive lock (SIX) is acquired. The purpose of the lock at the table level is to improve performance in determining whether any locks are held at lower levels (i.e., page or row). It is faster to check one table lock to determine if a modification is being made to a table, than to perform a search through possibly thousands of shared locks to find the one exclusive lock that might exist at the row level.
Example
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 </div>
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 </div>
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"; </div>The MATCHES clause makes the job of identifying the HEX value for the partnum a little easier. This SQL produces the following output:
tabname customer tabid 100 (expression) 0x0000000000100084 1 row(s) retrieved. </div>The rowid of 0 represents a table level lock. The type of lock is intent exclusive (HDR+IX), which is used for lock performance only. The rowid of 100 is the actual page in the customer table containing the record for customer number 102. The fact that the rowid ends in zero indicates that the customer table is using page level locking. There are now two tablespace numbers remaining: 10009a and 100085. Executing the SELECT statement above for each tablespace number will reveal that neither exists in systables. In addition, the DELETE statement should affect only one table. It is also necessary to check the sysfragments table. Fragmented tables are entered into systables with a partnum of 0. By executing the following SQL statement it is possible to determine what fragments are being locked:
SELECT fragtype,tabid, indexname, dbspace, HEX(partn) FROM sysfragments WHERE HEX(partn) MATCHES "*100085" OR HEX(partn) MATCHES "*10009a"; </div>This SQL produces the following output: The fragtype of I indicates an index fragment. In this case there are two indexes: " 100_1" which is a constraint and "zip_ix" which is a performance index. Both indexes are detached and located in the dbspace rootdbs.