Re: Online Row Level Locking
On 11 Feb 1994, Rory Martin wrote: > Here are the skimpy details as I have currently have them. > Running Online (Version, not sure yet, details to follow) > on an NCR 3550 Unix SVR4. > > The system consists of Plexus (NCR DMS) style clients. > > In addition, there are a number of server processes also running. > > The problem seems to be errors relating to the fact that Online locks > adjacent rows in a table when a row is locked. Does anyone have any > suggestions or comments about the best way for the applications to work > around this? There is considerable activity occurring on the system, and > the potential for lockouts happening is high. Any thoughts would be > appreciated. Additional details as soon as I get them (The system is in > Cincinnati and I am in Wash.DC). Thanks. > > Rory Martin > dmartin@access.digex.net > "No matter where you go, there you are." > > > > This is termed "The Twin Problem" by Informix and it caused us a bit of worry as well! Quoting a passage from one of the many Informix manuals (we don't know which one because the page was faxed to us by Informix support) : "The Twin problem is a subtle error that can arise during an uncommon sequence of operations in databases with logging. Suppose that two processes, Px and Py, are updating a table that has a unique index. Px deletes a row; then Py inserts a new row with the same key as the deleted one. Later, Px decides to rollback it's transaction. But now it cannot do so, because that would mean replacing the row it deleted, and the key to that row is now in use. Clearly, Py should not have been allowed to insert it's row until Px completed it's transaction. But when only row locks are available, Py's insertion cannot be prevented. Oncy Px deletes the row, the locks vanish; a row that is not there cannot be locked! The INFORMIX-TURBO solution uses key locks. In databases with logging, whenever a row is to be deleted from a table with an index, an exclusive lock is placed on the next higher key in the index. (It cannot be placed on the deleted row, since the key is deleted with the row.) Whenever a row is to be inserted, INFORMIX-TURBO tests the next higher key in the index to see if it is locked. If so, it returns a lock failure and does not complete the insertion. This ensures that the key of a deleted row cannot be pre-empted until the deleting transaction commits its work." Obviously, the documentation is a bit old, but the same holds true of Informix OnLine (Version 5.00.UC2). We also failed to find any of this documented in the usual manuals supplied with OnLine, which is why we had to escalate this to Informix Support. It's hard to comment on a general solution for your problems without more information. We have managed a work-around to our problems involving twin locks by inserting dummy rows! By the way, does anybody know if OnLine 6.00 behaves differently? Cheers! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Mark Lansley <mark@dsl.com> | "These opinions are most Druid Systems Ltd, UK | probably those of my parents" +44 932 571212 (Vce) 571552 (Fax) | #include <disclaimer.h> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~