row level locking and dead-lock prevention

row level locking and dead-lock prevention

Post by Bart van der Wo » Fri, 14 Oct 1994 13:12:38



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.

 
 
 

row level locking and dead-lock prevention

Post by Jim Gord » Sat, 15 Oct 1994 02:58:08


Quote:> 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......

> Because of the short transaction time, table level locking might
> produce sufficient throuhgput, but I'm not (yet) sure
> about this.

> Bart.

Table level locking is fine but performance may prove too slow
especially in a close to real-time environment.

Consider this:

Create a table that will be used to control dead-locks.  Create a row
(with a unique key an a timestamp) for every single dead-lock
condition that may exist.

At the start of each transaction that can get into a deadlock
condition do:-

[syntax arbitrary]
update lock_table set timestamp = current
       where unique_key = whatever
while status locked do
   wait ? second
   update lock_table set timestamp = current
          where unique_key = whatever
end while

Once the deadlock row is updated and therfore locked for this
transaction you know that you can complete your transaction.  Every
other transaction, that could cause a deadlock with you, now has to
wait for this lock to release before grabbing any rows that your
transaction wants.

It isn't necessary to use a lock table if you design your transactions
to always grab the same row first.  This can sometimes be more
difficult to maintain though.

Cheers - Jim
My opinions are my own.  They may vary with time but they remain MINE!
----------------------------------------------------------------------
Name: Jim Gordon         Company: DHL Systems Inc, Burlingame, CA, USA
----------------------------------------------------------------------

 
 
 

row level locking and dead-lock prevention

Post by Robert List » Wed, 19 Oct 1994 02:56:44


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.



Quote:> 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.