Re: row level locking and dead-lock prevention

I'm not an expert on this, but I assume the DBMS will handle deadlock for you automatically - either by avoiding it or by detecting it and aborting and restarting the transaction. The exception would be if you're using Informix SE and the CISAM library. I hope somebody will confirm or deny this assumption. - Bob (rlister@megatest.com) From article <worp.51.001736A2@knoware.nl>, by worp@knoware.nl (Bart van der Worp): > 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.