Locking Problems

If a user remains within a transaction for a considerable period and locks critical areas of the database this could indicate

• Poor application design
• Incorrect 'lock mode' selected.

Command: onstat -u

onstat -u

INFORMIX-OnLine Version 10.00.UD2 -- On-Line -- Up 17:09:46 -- 195680 Kbytes 
Userthreads 
address   flags  sessid    user tty  wait   tout locks nreads nwrites
40024010 ---P--D    0   informix -        0  0    0     264      843 
40024444 ---P--F    0   informix -        0  0    0       0        0
40024878 ---P--F    0   informix -        0  0    0       0        0 
40024cac ---P--F    0   informix -        0  0    0       0        0 
400261b0 ---P--F    0   informix -        0  0    0       0        0 
400265e4 ---P--B    9   informix -        0  0    0       0       75 
40026a18 ---P--D    0   informix -        0  0    0       0        0 
40026e4c L--P--- 2245       eric - 401bfa6c -1    1     383      454
40027280 L--P--- 2506       eric - 401bfa6c -1    1     227       92
400276b4 L--P--- 2241       eric - 401bfa6c -1    3     228      209
40027ae8 L--P--- 2480       eric - 401bfa6c -1    1     219      335
40027f1c L--P--- 2535       eric - 401bfa6c -1    1      57       22
.
.
4003273c L--P--- 2427       eric - 401bfa6c -1    1     630      875 
40032b70 L--P--- 2380       eric - 401bfa6c -1    1      77       92
40032fa4 Y--P--- 2412       eric - 404a33fc  0    1     321      577
400333d8 Y--P--- 2496       eric -        0  0   47     335      319
4003380c Y--P--- 2511       eric - 80419330  0    1     102      132
.
.
40033c40 L--P--- 2436       eric - 401bfa6c -1    1      28       18
40035144 Y--P--- 2526       eric - 404d366c  0    1       9       14
58 active, 128 total, 71 maximum concurrent

In the above example session 2496 is holding 47 locks and blocking most of the other sessions.

Command: onstat -k

INFORMIX-OnLine Version 10.00.UD2 -- On-Line -- Up 03:08:49 -- 147744 Kbytes

Locks
address wtlist   owner lklist   type   tblsnum rowid key#/bsiz 
30258128 0    400276b4        0 HDR+X  100002    208     0     <DB Lock>
302583e8 0    40027280        0 HDR+S  100002    20d     0 
30258414 0    40028350 30258548 HDR+X  a0004f  4e50f     0
3025851c 0    40028350        0 S      100002    20d     0 
30258548 0    40028350 3025851c HDR+IX a0004f      0     0 

5 active, 50000 total, 32768 hash buckets

If an application requests and gains an exclusive lock on the database then no other user will be able to gain access until the database is closed by the application. The user holding the lock can be traced

Command: onstat -k

onstat -k

INFORMIX-OnLine Version 10.00.UD2 -- On-Line -- Up 03:08:49 -- 147744 Kbytes

Locks
address  wtlist   owner   lklist   type    tblsnum rowid key#/bsiz 
30258128      0 400276b4        0  HDR+X   100002    208     0
302583e8      0 40027280        0  HDR+S   100002    20d     0 
30258414      0 40028350 30258548  HDR+X   a0004f  4e50f     0
3025851c      0 40028350        0  S       100002    20d     0 
30258548      0 40028350 3025851c  HDR+IX  a0004f      0     0 <Table Lock>

5 active, 50000 total, 32768 hash buckets 

While data is being updated the affected rows are locked, either at a row or page level. The affected table can be identified as can the user holding the lock.

• If the rowid equals zero, the lock is a table lock.
• If the rowid ends in two zeros, the lock is a page lock.
• If the rowid is six digits or less and does not end in zero, the lock is probably a row lock.
• If the rowid is more than six digits, the lock is probably an index key value lock.
• Refer to the reference manuals for a full explanation.

In some releases of 7.x servers the command set lock mode to wait is not executed correctly. This can be overcome by using the lock wait time to a definite value i.e. set lock mode to wait 30

Beginning with Version 5.04, if you do select count(*) on an OnLine table without a where clause within a transaction, the table will be locked in shared mode until the transaction is committed or rolled back. For example:

Begin work; 
Select count(*) from customer; 

The shared lock on table customer won't be released until the end of the transaction. (Commit or Rollback). If you do not want to lock the entire table while doing select count(*) on a table within a transaction, you can use a dummy where clause. For example:

Begin work; 
Select count(*) from customer where 1=1;