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
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
Are my fears about deadlock correct?
Is there a way, or work around, to lock on row level explicitly?
Thanks in advance,