Re: Deadlock and ORA-0600 ocurred yesterday
- From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
- To: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
- Date: Sun, 25 Mar 2018 00:22:08 -0400
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
Other related posts: