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	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

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.

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:

  SELECT tabname 
  FROM systables 
  WHERE  locklevel = 'P'
  AND tabid >= 100;

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

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";

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.

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";

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.