Re: row level locking and dead-lock prevention
> 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 = whateverwhile 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
----------------------------------------------------------------------