Re: Transactions & locking

On 17 Sep 1997 18:56:10 GMT, Michael Hoffman <mrh@panix2.panix.com> wrote: >Hello all, > > A relatively simple, yet undocumented, question: > >After "Begin Work", all rows touched will be locked. However, does this >locking happen to all affected rows at the start, or as they are affected? > >The reason for asking comes from the desire to use "Set Lock Mode to Wait". >We have MANY different input systems that access their related tables in >different orders. Obviously this can lead to a dealocking problem if two >processes are waiting for the other release a needed table. >Example: Prog1 updates Tables A, B, and C. > Prog2 updates Tables C, A, and B. > Both Progs run "Set Lock Mode to Wait" right before "Begin Work" and >then proceed with their updates. When Prog 2 gets to its second step, Prog1 >already has a lock on the row it needs (this can happen easily with this >system!), so it begins to wait. Prog1, meanwhile, continues on to update >table B, but when it tries to update table C, it finds that Prog2 has locked >it. So Prog1 begins to wait! DEADLOCK!!! > >If the "Begin Work" locks all associated rows at once, then the chances of >both programs colliding like this is much smaller (although not non-existent). >But that would mean Informix is running the transaction's logic twice (once >to find the rows to lock, the second to actually process the info). This >seems too inefficient for even Informix to allow. > >Any help is appreciated! > >Michael Hoffman >mrh@panix.com > > Page 6-122 of the "Managing & Optimizing Informix Online Dynamic Server Databases" Training manual covers deadlock detection. I can't vouch for the effectiveness of the algorithm (I haven't installed ODS here yet), but it seemed reasonable. Jeff Kalchik ------------------------------- Views expressed are my own, not 3COM or U.S. Robotics. -------------------------------