Re: How to anticipate (Oracle) database locks.

On Sun, 18 May 2003 20:10:15 +0000, Jan Brunia wrote:

> Hi,
>
> We have the problem of encountering database locks in our
> Java program. De piece of code most probably causing
> the lock is shown below.
>
> Has anybody some advise concerning how to avoid the locks
> and when they happen how to handle them and remove them
> runtime?

First, locks are not removed, they're usually released. Second, in the database theory, locks are acquired by transactions. When transaction ends, all of its locks are released. Locking is not evil, it is necessary to prevent concurrent access to the same data which would cause data corruption. There are strategies how to deal with lock waits and deadlocks. First strategy is called "queue" or "TPM". In short, nobody touches the database but a background process to which all transactions are submitted. In other words, transactions are "sent" or "described" to the applying process. That was the logic behind CICS, IMS, Tuxedo, Encina and alike. The problem with this approach arises when the monitor process is unable to catch up with the sheer transaction volume. IBM approach was the same one as from that famous Taco Bell/Godzilla commercial: "I think I need a bigger box".
Second approach for avoiding deadlocks is to develop a resource hierarchy. and always lock in the same order. To explain this, you need to understand the definition of a deadlock? So, what is a deadlock? Deadlock is a circular wait. In essence, transaction T1 is holding resource A and waiting for the resource B, while transaction T2 is holding resource B and waiting for A. This situation is possible if and only if transactions T1 and T2 can lock their resources in the different order. If both transactions are required to obtain a lock on A in order to request the lock on B, deadlock is impossible. For a more thorough discussion of the deadlock and locking strategies, please see Andrew Tanenbaum's "Modern Operating Systems", one of the best university textbooks of all times. Your problem is not specific to Oracle, it's a general design problem.

-- 
Mladen Gogala
Software is like sex, it is better when it is free.
Linus Torvalds