====== Locking Problems ====== ===== User stuck in transaction ===== 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 [[http://www.oninit.com/onstat/index.php?page=./onstatidx.php&id=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. ===== Database is locked exclusively by another applications ===== Command: onstat -k [[http://www.oninit.com/onstat/index.php?page=./onstatidx.php&id=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 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 [[howto#who is holding a lock|can be traced]] ===== A table is locked by a user ===== Command: onstat -k [[http://www.oninit.com/onstat/index.php?page=./onstatidx.php&id=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 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 [[howto#which table is locked|identified]] as can the user [[howto#who is holding a lock|holding the lock]]. ===== At what level is the lock being held ===== * 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. ===== Set lock mode to wait being ignored ===== 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 ===== Count() locking problems ===== 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;