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.
||The in-memory address of the lock structure.
||The address of the first userthread waiting for this lock.
||The address corresponding to the userthread which holds this lock.
||The address of the next lock owned by this userthread. A zero indicates the end of the list.
||Describes the type of lock being held using one or more of the following coded values:
B Byte lock
IS Intent shared
SR Shared lock by RR
UR Update lock by RR
IX Intent exclusive
SIX Shared, intent exclusive
XR Exclusive lock by RR
TRR Inserter's RR test
||The tablespace number for the item that is locked.
||The rowid, page or 0, within the tblspace upon which the lock is held.
||The index key number or the number of bytes locked for a VARCHAR.
||The number of locks that are currently active within this instance.
||The total number of locks configured in the instance by the LOCKS parameter.
||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
||The lock address of the next lock in the linked list that is identical to this lock.
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
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:
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.
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
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.
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)
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:
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)
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.