Locks on multi-user systems

What is the best strategy to avoid the situation, where one user is blocked by another user ? The first user will only notice that the system hangs and does not know, when things will continue, nor who the blocking user is.

I want to inform the first user, that her transaction will not continue due to a lock by the second user, and opt her to rollback.

We have tried the "select for update nowait" method (with Oracle). But this is a hell of programming task. Also to me it does not seem to be bullet proof, when many tables are touched in the same transaction.

I found a posting somewhere, where the suggestion was made to always work thru a database link (again Oracle). This will turn all transactions into distributed transactions and there is a distributed lock timeout on oracle. But there is no general lock timeout AKAIK.

Any ideas would be greatly appreciated. Received on Mon Jan 21 2002 - 14:15:03 CST