Locked table problem

Hi, I have a table with the following fields that serves as a counter: tabfcont char(8) not null , contfcont integer, campofcont varchar(20), primary key (tabfcont) constraint "informix".pk_grlfcont The problem is that there are transactions that fail to execute and as this table must record all operations performed successfully, all other connections are blocked. Thanks for any help or suggestions. SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------ #Informix

Does the lock last long due to the failed transactions despite using the index properly? Is the reason the lock is held for a long time due to the large transaction size or is there an external factor? ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

Thanks for reply, yes, the blocking is usually due to external failures. SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

The solution is the same as the post the other day about too many connections. Shorten the system keep alive so the OS kills the network connection to the dead applications. Art

Thanks for reply Art, I had already thought about this solution, but won't that make the process unreliable? SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

How so? In what way unreliable? You indicated that apps are dyingd leaving locked rows. First, that indicates that the application may be locking rows too soon, probably at fetch time rather than at the time data is modified. That's the first problem and can only be fixed by rewriting the apps to properly implement optimistic locking/transaction protocol. Second, if multiple applications are being affected by one transaction's locks on a single table that may mean that a) the table has its lock mode set to page rather than row, or b) the query is not using an index to access the data and so multiple rows are being examined and the current isolation level is locking all visited rows. To resolve a) change the lock mode of the table. If b) use a different isolation level that only locks specific rows on update (committed read). Third, if the apps are exiting without rolling back open transactions and the engine is not recognizing that the client app is gone, then the problem is the one I mentioned before, namely that the OS keepalive is set to the default (two hours on most systems) and needs to be shortened to something reasonable like 30 seconds. That way the OS will shutdown the network connection to the database soon after the app dies.causing the engine to rollback the open transaction and releasing the locks. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Thanks for reply Art, I am using one simple script to read locks over syslocks and syssessions: select trim(s.hostname)||":"||s.sid||":" ||decode (trim(l.type), "B", "'B-Byte_lock'", "IS","IS-Intent_shared_lock", "S","S-Shared_lock", "XS","XS-Shared_key_value_held_by_a_repeatable_reader", "U","U-Update_lock", "IX",'IX-Intent_exclusive', "SIX","SIX-Shared_intent_exclusive_lock", "X","X-Exclusive_lock", "XR","XR-Exclusive_key_value_held_by_a_repeatable_reader")||":"||trim(l.dbsname)||":"||trim(l.tabname)||":"||hex(l.rowidlk) from sysmaster:syslocks l, sysmaster:syssessions s where s.sid = l.owner and dbsname <> 'sysmaster' order by l.type; there is any other way/script to detect whats is locked and who is waiting? SP ------------------------------ Sergio Peres AIRC Coimbra ------------------------------

Sergio: There is a good post from Doug showing the information of the session owning or waiting on the lock. https://www.oninitgroup.com/listing-informix-locks [informix@db2 ids1410fc3]$ echo "select * from v_locks" | dbaccess testdb Database selected. session process connected host user database table lock number duration 198 29039 0:11:21 db2 skjeong stores_demo catalog RBYT 14 0:11:14 198 29039 0:11:21 db2 skjeong stores_demo catalog RX 148 0:11:14 2 row(s) retrieved. Database closed. [informix@db2 ids1410fc3]$ echo "select * from v_waiters" | dbaccess testdb Database selected. session process connected host user database locker 216 7489 0:06:24 db2 informix stores_demo 198 1 row(s) retrieved. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------