RE: Informix locking & transactions

Bob- What you may be missing is when you declare your cursor, be sure to include "FOR UPDATE" in your SELECT statement. That way the engine will lock all rows of your cursor as it reads them, rather than waiting until you issue the UPDATE statement. Or, if you're only dealing with one row, you could do something like this: BEGIN WORK SELECT * INTO foo_bar.* FROM my_table WHERE whatever = whatever_else UPDATE my_table SET * = foo_bar.* ...What ever your process is here... UPDATE my_table SET * = foo_bar.* IF ( SQLCA.SQLERRD[3] = 1 AND SQLCA.SQLCODE = 0 ) THEN COMMIT WORK ELSE ROLLBACK WORK END IF This would force the engine to lock your row and hold it until you issue COMMIT WORK. --EEM > -----Original Message----- > From: Obnoxio The Chav [mailto:obnoxio@serendipita.com] > Sent: Wednesday, December 29, 2004 11:43 PM > To: BigBob > Cc: informix-list@iiug.org > Subject: Re: Informix locking & transactions > > > BigBob said: > > 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... > > Try again. :o) > > I really can't understand the problem -- are you using LOCK MODE (ROW) on > all these tables? What ISOLATION LEVEL are you using? > > > 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! > > > > > -- > > Bye now, > Obnoxio > > "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" > - Coluche > > "I'm trying to see things your way, but I can't get my head up my ass" > - JCH > > "Ogni uomo mi guarda come se fossi una testa di cazzo" > - Marco > > I went to the airport to check in and they asked what I did because I > looked like a terrorist. I said I was a comedian. They said, "Say > something funny then." I told them I had just graduated from flying > school. > > -- Ahmed Ahmed > sending to informix-list sending to informix-list

EEM...thanks for the input. I do have the 'for update' in my select cursor along with commits and/or rollbacks. The problem is that because it's held in a transaction and the fact I need to hold onto the lock for a long time (I know this isn't good, but the business requires it), there are many other tables that are read during that transaction before I can commit. I don't lock/care about the interum tables, but since they are in the transaction, it prevents the Java processes from updating ANY table within the transaction. Again, this encasulates many tables, but I'm only locking / worried about the first table in the 'chain'. Any advice?