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

Thanks for the post. I'll try and answer. > 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? The row was locked using an update cursor accessing the index / primary key. I have row-level locking on the table. Isolation level is defaulted. > > 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 ... I don't have any problems with the locking as I have it so long as I only test within the application (MF Cobol/Informix). However, the problem arises during System Testing where as in a production environment, there are 2 Java processes that are initiated and when done, they return to the MF Cobol application to update a DIFFERENT table than I have locked, but is within the Transaction. This causes a failure for the Java process which I need to resolve. Hope that helps. Any advice would be appreciated. Bob Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<lfzvc.21422$be.7574@newsread2.news.pas.earthlink.net>... > 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.