Could not position with in a table

Im continuously facing an issue in our applications. You can find the Java Exception Errors in Application server log (attached). Please find the below sample Error: • 2017-10-12 06:58:16,054 ERROR [STDERR] (http-0.0.0.0-8180-10) Caused by: java.sql.SQLException: Could not position within a table (neura_biahmis_prod_live.inventory_batch_tbl). • 2017-10-12 06:58:16,054 ERROR [STDERR] (http-0.0.0.0-8180-10) Caused by: java.sql.SQLException: ISAM error: Lock Timeout Expired. • 2017-10-12 06:58:16,054 INFO [STDOUT] (http-0.0.0.0-8180-10) NEURA_BIACH [12 Oct 2017 06:58:16: ERROR : com.cgsl.neura.utility.Utility.update(Utility.java:164)] : org.hibernate.exception.GenericJDBCException: could not update: We are using Informix 12.10FC8 as a backend JBOSS application server. This issue is observing only on one particular table.

Hi Mukesh. You probably have uncommitted changes to that table by another session, which results in exclusive row locks. If your application has the default SET ISOLATION TO COMMITTED READ, it will abort when it tries to read those rows. When you start a new session, execute SQL such as this or set equivalent connection properties: SET LOCK MODE TO WAIT 60; -- seconds Alternatively, typically when just reading and you don't care so much: SET ISOLATION TO DIRTY READ; -- reads locked rows anyway There are quite a few solutions posted previously for SQL to list locks currently held, such as: http://members.iiug.org/forums/ids/index.cgi/read/30467 Regards, Doug Lawry

Hi Mukesh you are facing a typical lock conflict situation: one user is holding (i.e inserting, modifying or deleting) a row for a long time and he did not commit his transaction. This is expected behaviour as long as the 'holding' user does not commit his transaction. As Doug says, this is Informix default behaviour (committed read). You can try SET LOCK MODE TO WAIT n seconds, maybe not too many seconds. THis means 'your' session will keep attempting reading the row during n seconds. At the end of this period, it will get the error you had. You may want to emulate oracle's way of doing, by setting SET ISOLATION COMMITTED READ LAST COMMITTED: in that case, your session will read the last committed value for this row and probably won't catch a lock error or timeout. You can find which session is holding the lock(s) by running dbaccess sysmaster <<+ select * from syslocks where tabname "inventory_batch_tbl" and waiter != 0+ the column 'owner' is the session number of the 'culprit' of holding the lock(s) Hope this helps Eric

Thanks fro you responses friends. we check the onstat –g sql output(which I have shared the output in previous mail attachment), the Iso Lvl is DR (Dirty Read), so that we can confirm the isolation level is set to dirty read right? And also the lock mode displayed as Wait 10. Please can you confirm whether they are or not. Our onconfig parameters are : DEF_TABLE_LOCKMODE row USELASTCOMMITTED NONE LOCKS 100000 Our application team are using hibernate, so when I asked them to set the IFX_LOCK_MODE_WAIT in the connection properties or set the ‘SET LOCK MODE TO WAIT = 30’ in the connection string, but they are not getting exactly where and how to set these. Is there any possible way to set the lock mode to wait and isolation level from db end.