>> 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?
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.