Re: Informix locking & transactions

BigBob wrote: > 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! > Two ways --- 1) issue a dummy update on the row (i.e. set col1 = col1 where xxxx) 2) use repeatable read isolation.