Informix locking & transactions
Posted in 2004
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!