locking and processes outside the transaction....

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? Thanks!

BigBob wrote: > 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? See the concurrent discussion on 'Problems with locks and committed read' for many ideas. Your first paragraph is readily comprehensible. One question is how was the row to be locked identified - was it a simple primary key indexed lookup, or did the server have to scan for it. Also, does the table have row locking or page locking (remember, page is the default). Also, what isolation level are you running at. And how did you lock the row - by doing an update on it, or selecting it for update? You second paragraph is less comprehensible. I think that your external processes attempted to modify the same table (or possibly tables - you said 'a ROW' in the first paragraph, which inherently implies a single table), but ran into the lock. Well, isn't that what you wanted? That is, you placed the lock on the row to prevent other processes from modifying the row. How are these other processes identifying the rows that they are updating? Are they doing table scans, or are they identifying the rows by the keys? Those other processes should be unimpeded by the lock on a row in TableA when they access rows in other tables TableB or TableC or ... unless there are referential integrity constraints which influence things, or triggered actions, or ... Note that your isolation levels, locking modes and such like all interact in answering the question. We really don't have enough information to tell you a simple answer. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/