Re: deadlocks???

Vincent A. Calcaterra wrote:
>
> 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

Vince,

The only way your application can cause a deadlock is if the transaction/program is updating (locking) more than one table. Say you lock table AAA, but don't commit, because you need to update table BBB, then commit. Your buddy programmer is updating the same two tables and rows, but decides to update table BBB, then AAA, then commit. Each of you updates your first table at the same time, thus locking the rows (assuming row lock) in table AAA and BBB at the same time. Now your app is ready to update BBB, but is waiting for your buddy's app to release the lock on BBB. His app is waiting for your app to release the lock on AAA. This is a deadlock.

The way to avoid them is to have programmers update tables inside the same transaction in the same order all the time. A transaction is everything you do from one commit to the next in a program. Most Windows programs allow only one transaction per program (some apps spawn another program to get around that).

Greg Grooms
PRC
St. Louis Received on Fri Aug 30 1996 - 00:00:00 CEST