RE: Informix locking & transactions

how many rows are present in your tables?, it may be that your queries are doing a sequential scan on the table which may result in the problem that you are experiencing, you may do the following to verify 1. make sure the table is created with the row level locking 2. run the queries in dbaccess and capture the explain plan and may sure they are not doing a sequential scan on the tables 3. make sure you have "lock mode to wait <max # of seconds>" in your code 4. update the statistics for the tables 5. try using an INDEX optimizer directive in the query and see if you experience the same problem. -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]On Behalf Of BigBob Sent: Wednesday, December 29, 2004 6:08 PM To: informix-list@iiug.org Subject: Informix locking & transactions I need to lock a ROW in an Informix table and hold onto that lock until the end of a transaction. During this transaction, 2 seperate Java processes MIGHT start and want to update a table held in that transaction before it is released. Is there any way to accomplish this? Let me explain... An online application issues a "begin work" then opens a cursor with a select for update going after a single row in a table (e.g. Table A). The business requires that no one else be allowed to access 'child tables' linked to that particular row until they back-out(rollback) or complete the transaction (commit). This works well within the application. However, the problems arose in system testing when we found some "external" processes in Java that need to update a table (e.g. Table M) "in" the transaction, but not the particular table locked (e.g. Table A). Since Table M is held by the transaction, the Java processes are prevented from doing the updates. So, I need to be able to "lock" a row in Table A but not release it until the commit which could be after they access Table Z, but allow the external Java processes to update Table M. Is this possible? Is there a different design that I could use? Thanks in advance for the help! sending to informix-list