Re: deadlocks???

Vincent A. Calcaterra <vincentc_at_erols.com> wrote in article <32234802.3746F5FE_at_erols.com>...
> I'm experiencing deadlocks from some embedded SQL that simply updates
> a row in one table. Can anyone tell me in simple english what Oracle
> means by deadlock. My guess is that one user or process is in the midst
> of a transaction that involves writting to the database, (update,
> insert, delete, etc...) and before that process or user finishes,
> another attempts to update, or otherwise "write" the same data. What
> level does Oracle lock it's tables? Is it at the row level, or is it at
> the table level? By the way, I have the virtually worthless blurb from
> Oracle concerning "Detecting and Resolving Deadlocks".
>
> Any info appreciated.
>
> thanks,
> -vince
>

A deadlock occurs when I have a lock on a resource that you want at the same time that you have a lock on a resource that I want. This is how it works in Sybase. Oracle should be similar.

I write to table 'A'. This causes an exclusive lock. You write to table 'B'. This causes an exclusive lock. I read from table 'B'. You have an exclusive lock, so I am blocked. You read from table 'A'. I have an exclusive lock. DEADLOCK! I can't release the lock on table 'A' until I read from table 'B', and you won't release your lock on table 'B' until you read from table 'A'. The DBMS recognizes the situation and one process is terminated.

Deadlocks can only occur when SQL statements are grouped together as a transaction. If you were to COMMIT after each statement, the locks would be freed and a deadlock would not occur. However, transaction management is often necessary, so this is not really an option.

It is important to note that if both processes always access database resources in the same order (table 'A', then table 'B') deadlocks cannot occur.

Hope this helps a little.

Shawn Received on Tue Aug 27 1996 - 00:00:00 CEST