| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Script to overcome ORA-54 during DDL
Thank you Jonathan - you have prompted me to find out why.
session 1
create table dmk1(a number); insert into dmk1 values(1); create table dmk2(a number); insert into dmk2 values(1); commit; session 2 update dmk1 set a=2; --so now we hold a lock
back to session 1
update dmk2 set a=2; alter table dmk1 modify a not null; ORA-00054: resource busy and acquire with NOWAIT specified rollback; select * from dmk2; A ---------- 2 --ouch! a DDL that fails with ORA-54 has already issued an implicit commit
So the DDL commits, releasing the exclusive lock, and then tries to acquire the lock again in nowait mode, presumably queuing up behind any else who requested a lock between my requesting and subsequently acquiring and releasing the exclusive lock?
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jonathan Lewis
Sent: 26 June 2004 08:19
To: oracle-l_at_freelists.org
Subject: Re: Script to overcome ORA-54 during DDL
The drawback to locking the table before doing the DDL is that any other user wanting to do DML will be queueing behind your lock request. So in busy systems this is likely to increase the probability that someone will block you in the moments between the implicit commit and the start of the DDL.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st
I've used something like this in the past, except I also exclusively lock the table to which I am trying to apply the DDL. The idea being that I wait to get the exclusive lock, and having got the exclusive lock the DDL can acquire the lock in nowait mode. Its not perfect, but I find I need less attempts to get the DDL to execute.
 |
 |