Re: row level locking and dead-lock prevention

>>From article <worp.51.001736A2@knoware.nl>, by worp@knoware.nl (Bart van der Worp): >> I have a question with respect to locking, dead-lock prevention and row >> (or key) level locking. [...] We are designing a automatic message >> exchange system. Some data is to be maintained and used [...] Our >> customer wanted us to use Informix [...] >> There are [few] different [...] transaction [types]. Transactions are >> very short, no human interaction. Different transactions access tables >> in different sequences as the logic of these transactions demand. I'm >> afraid that if nothing is done to prevent, dead-lock is possible to >> occur. Due to the required real-time behaviour, dead lock repair >> (rollback and retry) is out of the question. >> [..] it seems possible to use a locking scheme in which locks for >> required rows are acquired in a pre-defined order. [...] >> This could be done on table level (LOCK TABLE), but I don't see how this >> can be achieved using row level locking, since this seems only possible >> to take place implicitly during execution of the query logic. >> Because of the short transaction time, table level locking might produce >> sufficient throuhgput, but I'm not (yet) sure about this. >> Are my fears about deadlock correct? >> Is there a way, or work around, to lock on row level explicitly? If you are worried about locking problems in the scenario you describe, I cannot think of a much better way of ensuring that you run into locking problems than applying table locks. That guarantees conflicts! I assume that your engine will be OnLine, and that the database will have a transaction log on it. If not, think again. Your schema will be careful to ensure that most (probably all) tables use row-level locking rather than page level locking. This ensures maximum concurrency with minimum lock contention. When doing INSERT operations, there are minimal locking problems. When doing DELETE operations, other transactions should not be trying to access the deleted rows. So only UPDATE operations should cause any problems. Your code will be written to use cursors with the FOR UPDATE clause. These are usable only inside a transaction. You will automatically lock the rows as you fetch them with this cursor, and the row will be updated using WHERE CURRENT OF. You'll be designing your code to identify exactly the rows that need to be updated, not scanning large numbers of rows in the application to see what might need updating. The remaining issue is: how often will two different transactions need to update the same row? The answer had better be "not often", otherwise you have an inherently non-concurrent, non-real-time design. Set the lock mode to wait with a timeout. Since all your transactions are short, your timeout can be short too. If there is a single table where there is contention, then try to ensure that it is processed last in each transaction, so that the rows are locked for the minimum possible time. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>