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! > > > In individual programs that will do anything to a data base, including external Java programs, set isolation to committed read; set lock mode to wait; Seems like that ought to do it.

And ensure that the tables being access have row level locking. Thomas Ronayne wrote: > 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! > > > > > > > In individual programs that will do anything to a data base, including > external Java programs, > > set isolation to committed read; > set lock mode to wait;> > Seems like that ought to do it.

Thanks! Didn't think about the lock mode wait option. Would using the Wait allow the Java process to wait until the online application releases the lock (completes/aborts the transaction) ? Thanks again,

And ensure that the tables being access have row level locking. Thomas Ronayne wrote: > 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! > > > > > > > In individual programs that will do anything to a data base, including > external Java programs, > > set isolation to committed read; > set lock mode to wait;> > Seems like that ought to do it.

Thanks...they do have Row level. Appreciate the input!