Re: Deadlock and ORA-0600 ocurred yesterday

Reply in-line

On 03/24/2018 10:21 PM, Sayan Malakshinov wrote:
> Mladen,
>
> Update statement doesn't support "order by" and I almost never seen
> anyone write sorted update statements like
> update (select * from t where b=:b order by rowid) set d=:d
> So you can easily get deadlock with simple concurrent updates like
> "update t set d=:d where b=:b" and "update t set d=:d where c=:c"

How would you get a deadlock on locking a single row in a single table? You need at least two resources, one to have locked and another to wait for. All deadlocks are closed graphs of lock waits, that's how deadlocks are detected. You can get an endless wait, if someone forgets to end the transaction, but you can't get a deadlock. In other words, it is not possible to get a deadlock with a simple update statement. When I said "lock the resources in the same order", that means that all transactions should first update table A and then update table B. If there is a transactions that updates table B before updating table A, you suddenly have a strong possibility for deadlocks.

>
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--
http://www.freelists.org/webpage/oracle-l