Re: locking and processes outside the transaction....

> Are you locking a row or a page? E.g. what is the lock-level for the > table, assuming you are using IDS? Row level locking is used on "table A". All others are defaulted to page-level, but not locked. Yes, using IDS. > > Locking a single row will prevent anything other than the locking > process / thread from updating it. Depending on the isolation level, > other processes may or may not be able to read it. If your external > processes are trying to update the row you have locked then of course > they should not be able to do so, but if you are having concurrency > problems e.g. they cannot update other apparently unlocked rows in the > table, then I suspect you are locking a page (2k) and hence all the rows > on the page. I don't have a problem locking the Row. It's the Java process that tries to update "table B" that is within the Transaction. I'm not concerned with "table B" but the Java processes are prevented from updating it because it's within the transaction. The Begin/end transaction stmts. are very 'far apart' as I need to get to the end of many screens before I can rollback/commit the transaction. Any advice would be appreciated. Thanks Five Cats <cats_spam@[127.0.0.1]> wrote in message news:<kZw8sVAHAvvAFw29@[127.0.0.1]>... > In message <fb509769.0406021500.81d3ca1@posting.google.com>, BigBob > <rwbanks@hotmail.com> writes > >I need to lock a ROW in an Informix DB and hold onto that lock until > >the end of a transaction. My first attempt at this was to begin work, > >lock, etc. This worked great for the application itself. > > > >However, the problems arose in system testing we found some "external" > >processes that update Informix tables "in" the transaction, but not > >locked. They were prevented from doing so and we had to backout the > >locking change. > > > >Is there any way to accomplish this? Lock a row, hold it and much > >later, release it without impacting access to other tables in the same > >transaction? > > Are you locking a row or a page? E.g. what is the lock-level for the > table, assuming you are using IDS? > > Locking a single row will prevent anything other than the locking > process / thread from updating it. Depending on the isolation level, > other processes may or may not be able to read it. If your external > processes are trying to update the row you have locked then of course > they should not be able to do so, but if you are having concurrency > problems e.g. they cannot update other apparently unlocked rows in the > table, then I suspect you are locking a page (2k) and hence all the rows > on the page. > > > >Thanks!

Looks like you'll have to rethink your program logic here. The locks will occur from the begin work through to the commit. If you have several screesn then capture the variables, and issue all the begin and all the SQL when they hit the "Confirm" button. > I don't have a problem locking the Row. It's the Java process that > tries to update "table B" that is within the Transaction. I'm not > concerned with "table B" but the Java processes are prevented from > updating it because it's within the transaction. The Begin/end > transaction stmts. are very 'far apart' as I need to get to the end of > many screens before I can rollback/commit the transaction. > > Any advice would be appreciated. > > Thanks > >