row level locking and dead-lock prevention
Posted in 1994
Topics: Transactions, Locking & Isolation
Hi, informix news readers! I have a question with respect to locking, dead-lock prevention and row (or key) level locking. I'm not a very experienced SQL programmer, so I'm not like a fish in the water regarding this subject (more like a human.......) We are designing a automatic message exchange system. Some data is to be maitained and used; this is where informix comes in. Our customer wanted us to use Informix, no choice here.... There are a limmited ammount of different possible transactions on this database. 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. Due to the limmited ammount of different queries and update actions, it seems possible to use a locking scheme in which locks for required rows are aquired in a pre-defined order. Old fashioned perhaps, but save...... 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? Thanks in advance, Bart.