Re: ORA-00054: resource busy and acquire with NOWAIT specified
- From: Jared Still <jkstill@xxxxxxxxx>
- To: dd.yakkali@xxxxxxxxx
- Date: Tue, 27 Oct 2009 14:22:08 -0700
Here are the locks on 10.2.0.4
With 0 rows deleted:
Oracle Database
Lock Mode Mode OS OS
SID Usernam WATR BLKR Object COMMAND Type
Lock Description Held Requested Program Process
------ ------- ----- ----- ------------------------- --------------- ----
---------------- --------------- ---------- ------------------ -------
143 JS00129 JS001292.TL2 BACKGROUND TM
DML enqueue lock Row-X (SX) None sqlplus@orcl. 26720
With 1 row deleted:
Oracle Database
Lock Mode Mode OS OS
SID Usernam WATR BLKR Object COMMAND Type
Lock Description Held Requested Program Process
------ ------- ----- ----- ------------------------- --------------- ----
---------------- --------------- ---------- ------------------ -------
143 JS00129 . BACKGROUND TX
Transaction enqu Exclusive None sqlplus@orcl. 26720
143 JS00129 JS001292.TL2 BACKGROUND TM
DML enqueue lock Row-X (SX) None sqlplus@orcl. 26720
2 rows selected.
Oracle is taking a DML lock regardless of whether any rows were actually
deleted.
A transaction has been started, and must be committed or rolled back before
the
other session can truncate the table.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
On Tue, Oct 27, 2009 at 2:09 PM, dd yakkali <dd.yakkali@xxxxxxxxx> wrote:
> Hello gurus,
>
> I got this error and I want to confirm my concepts are right.
>
> Session 1
>
> delete from tab1 where col1=20;
> 0 rows deleted
>
> session2
> truncate table tab1;
> I get "ORA-00054: resource busy and acquire with NOWAIT specified"
>
>
> When session1 deleted 0 records, it should not be placing any locks on
> the table correct? I do not understand why I got the error. Am I
> missing some basic concept?
>
> When I did a rollback on the session1 then session2 was able to
> truncate the table.
>
>
> Thanks
> Deen
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
