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.

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

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.

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.