If a user remains within a transaction for a considerable period and locks critical areas of the database this could indicate
Command: 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 -konstat -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
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.
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;