Home » SQL & PL/SQL » SQL & PL/SQL » resource busy and acquire with NOWAIT specified or timeout expired (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
resource busy and acquire with NOWAIT specified or timeout expired [message #673245] Tue, 13 November 2018 05:39 Go to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

I have the LIST Partitioned table.We are performing some DML Operation into one partition and
exactly at the same time doing some DDL Operations on the another Partition .

But some times in UAT Environments it leading to that
resource busy and acquire with NOWAIT specified or timeout expired Error.

When we perform the DML Operations on partitioned table on one specific partition,
will it acquire the LOCK on the total table?
Can't we perform any DDL operation on another Partition ?

But I am unable to replicate the same in Dev Environment

Please help me to understand

Thanks
SaiPradyumn
Re: resource busy and acquire with NOWAIT specified or timeout expired [message #673247 is a reply to message #673245] Tue, 13 November 2018 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Simple test:

-- Session 1:
SQL> drop table t;

Table dropped.

SQL> create table t (val int)
  2  partition  by list (val)
  3  (
  4   partition t_1 values (1),
  5   partition t_default values (default)
  6  )
  7  /

Table created.

SQL> insert into t values(1);

1 row created.

SQL> -- no commit

-- Session 2:
SQL> alter table t split partition t_default values (2) into (partition t_2, partition t_default);

Table altered.
So at least some DDL are permitted but not those that impact the whole table:
SQL> alter table t modify (val not null);
alter table t modify (val not null)
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Locks held by session 1 are:
SQL> @lock

Sid        Status   User            OS Pid     LK Mod W Object
---------- -------- --------------- ---------- -- --- - ------------------------------------------------------------
145,9      ACTIVE   MICHEL          5008       TX X     _SYSSMU5_863307317$ tx: 0x0005.0013.0000DCA9
                                               TM RX    MICHEL.T
                                               TM RX    MICHEL.T.T_1
An exclusive lock on a rollback segment (this is the transaction marker) and 2 row exclusive locks on the definition of T and T.T_1 so you can work on other partition(s) than T_1 but some of the operations that can be done may require a lock on the global table definition too.

Note that locks change with Oracle version, I used the one you posted for this test.

[Updated on: Tue, 13 November 2018 10:31]

Report message to a moderator

Re: resource busy and acquire with NOWAIT specified or timeout expired [message #673249 is a reply to message #673247] Tue, 13 November 2018 07:17 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks for detailed exception.Finally we can perform DML&DDL operation parallel on two different partitions of the same table which are not impacting the total table .

Thanks
SaiPradyumn

Re: resource busy and acquire with NOWAIT specified or timeout expired [message #673251 is a reply to message #673249] Tue, 13 November 2018 07:20 Go to previous messageGo to next message
BlackSwan
Messages: 26564
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Tue, 13 November 2018 05:17
Thanks for detailed exception.Finally we can perform DML&DDL operation parallel on two different partitions of the same table which are not impacting the total table .

Thanks
SaiPradyumn

How much elapsed time is reduced by do DML in parallel?
Re: resource busy and acquire with NOWAIT specified or timeout expired [message #673254 is a reply to message #673251] Tue, 13 November 2018 08:27 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
we are not looking on the Elapsed time. As per the business requirement we need to run the different jobs
on the the tow different partitions of the same table at the same time.
Re: resource busy and acquire with NOWAIT specified or timeout expired [message #673255 is a reply to message #673254] Tue, 13 November 2018 08:36 Go to previous messageGo to next message
BlackSwan
Messages: 26564
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Tue, 13 November 2018 06:27
we are not looking on the Elapsed time. As per the business requirement we need to run the different jobs
on the the tow different partitions of the same table at the same time.

Explain why it is a business requirement that
"we need to run the different jobs on the the tow different partitions of the same table at the same time."

Why does end user know or care if underlying table is partitioned or not?
Re: resource busy and acquire with NOWAIT specified or timeout expired [message #673256 is a reply to message #673249] Tue, 13 November 2018 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
saipradyumn wrote on Tue, 13 November 2018 14:17
Thanks for detailed exception.Finally we can perform DML&DDL operation parallel on two different partitions of the same table which are not impacting the total table .
...
So what was the original problem to do it?
This may help others to know it.

Re: resource busy and acquire with NOWAIT specified or timeout expired [message #673263 is a reply to message #673256] Tue, 13 November 2018 23:15 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks Michel for your follow up.

Here original problem is when we are running two jobs which are impacting the different partition with DDL and DML statements,still I am getting Resource busy Error at the time DDL statement execution.

We have insertions(DML) into one partition and truncation of another partition (DDL) on the same table and at the same time.

I am trying to find our are there any other statements which are executing on the same table at the same time

Thanks
SaiPradyumn

Re: resource busy and acquire with NOWAIT specified or timeout expired [message #673265 is a reply to message #673263] Wed, 14 November 2018 00:53 Go to previous messageGo to next message
mg.hossain.it@gmail.com
Messages: 4
Registered: November 2018
Junior Member
I think the problem will be no more if your other sessions COMMIT their work.

Please pardon me if I am wrong.
Re: resource busy and acquire with NOWAIT specified or timeout expired [message #673268 is a reply to message #673265] Wed, 14 November 2018 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

mg.hossain.it@gmail.com wrote on Wed, 14 November 2018 07:53
I think the problem will be no more if your other sessions COMMIT their work.
Of course but the problem is that the DDL are raised when the DML are executed at any moment so before or after the commit.

Re: resource busy and acquire with NOWAIT specified or timeout expired [message #673269 is a reply to message #673263] Wed, 14 November 2018 01:09 Go to previous message
Michel Cadot
Messages: 66469
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
We have insertions(DML) into one partition and truncation of another partition (DDL) on the same table and at the same time.
There is no problem to do this (unless the DML impacts the truncated partition like with an UPDATE that moves the updated row into the truncated partition).

Previous Topic: Relationship between two weak entities
Next Topic: Calling Java function from pl/sql
Goto Forum:
  


Current Time: Wed Jul 17 19:43:56 CDT 2019