Re: Transactions & locking

Michael Hoffman 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!!! Right. Informix locks each row (or page if the table has page mode locking) when it is touched. The only solution is that since the engine will detect the deadlock it will force the deadlocked transactions to rollback, when you get a lock timeout or deadlock timeout error code (check the Error Messages manual for these codes, I used to know them but I no longer have that kind of an application to deal with) sleep for a random number of seconds (important on a multiprocessor), or usleep a random number of milliseconds, and then retry the transaction. Another solution is to create a semaphore table that every application has to get access to first before it can update any of the other tables. It can just contain a single record for each set of tables and any application that wants to update that set of tables MUST first select the semaphore record for the set FOR UPDATE before anything else. If there are overlapping sets of tables [ie (a,b,c) (a,b,d) (b,c,d) (d,e,f) etc] then there must be a semaphore record for each table and an application must be able to get the semaphore for each table without waiting; if it cannot get any one of the semaphores it must ROLLBACK, sleep or spin, and try again. Art S. Kagel