Solved

Oracle error ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Posted on 2011-09-27
4
1,719 Views
Last Modified: 2012-05-12
In my Oracle (11gR2) database procedure (package) the code attached:

I have attached the exact code. I call the package procedure t1:

begin
  P1.t1;
end;

I have a few questions:
1. If sql3 fails (truncate table fails) sql2 still gets committed -- I tested.  I want that not to happen.
2. If sql3 fails (truncate table fails) , sql2 still gets committed even if I comment out sql1 in the package procedure (and recompile).
3. The line PRAGMA AUTONOMOUS_TRANSACTION; in the update_load_log procedure is supposed to only commit anything executed by this  update_load_log procedure itself by a calling procedure -- but I do not see it is doing so.
4. If sql3 fails (truncate table fails) , sql4 does not work (as expected and as I wanted).

How can I rollback (commit not gets done) sql2 if sql3 fails? And sql1 always work?

Do I need to put any exception? Thanks.



CREATE OR REPLACE Package P1 IS

  PROCEDURE update_load_log(p_id           IN NUMBER,
                            p_load_status  IN VARCHAR2,
                            p_load_message IN VARCHAR2);
  PROCEDURE t1;
END;
/
CREATE OR REPLACE PACKAGE BODY P1 IS
  PROCEDURE update_load_log(p_id           IN NUMBER,
                            p_load_status  IN VARCHAR2,
                            p_load_message IN VARCHAR2) IS
                            PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    UPDATE load_log
       SET load_message = load_message || ', ' || p_load_message,
           load_status  = p_load_status,
           load_date    = SYSDATE
     WHERE id = p_id;
    COMMIT;
  END update_load_log;
  
   PROCEDURE t1 IS
  BEGIN

update_load_log (1001, 'test1002','test1002'); //sql1
 
insert into test2 (f1) values (sysdate); //sql2

EXECUTE IMMEDIATE 'TRUNCATE TABLE test1'; //sql3

insert into test2 (f1) values (sysdate); //sql4

COMMIT; //sql5
end;
end;

Open in new window

0
Comment
Question by:toooki
  • 3
4 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 475 total points
ID: 36712210
>>> 1. If sql3 fails (truncate table fails) sql2 still gets committed -- I tested.  I want that not to happen.

that is expected behavior

 All DDL statements implicitly create 2 commits.  Once prior to execution and once after.

TRUNCATE TABLE is DDL,  so it commits, then attempts the truncate, then commits again if it succeeded.  if it fails, it doesn't matter, the first commit already completed.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36712219
>>> 3. The line PRAGMA AUTONOMOUS_TRANSACTION; in the update_load_log procedure is supposed to only commit anything executed by this  update_load_log procedure itself by a calling procedure -- but I do not see it is doing so.

so, what do you see it doing? error message?  wrong results? no results? hang? keyboard catches fire?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36712222
also,  on the truncate,  if you want a table purge to be transactional,  you'll have to use delete.

delete is DML,  truncate is DDL.
0
 

Author Comment

by:toooki
ID: 36712517
Thanks a lot.

DDL statements implicitly create 2 commits -- ok understand now. I considered Delete instead of truncate too. Decided to go with truncate.

But . I attached the almost same code, here

-- If sql4 fails (insert into test3), I assumed because of the AUTONOMOUS_TRANSACTION; clause in the update_load_log procedure, sql2 does not get committed (but sql1 is committed). But actually sql2 gets committed either way (sql4 succeeds or fails) -- I want sql2 to commit only if sql4 succeeds. I commented out the truncate table statement (sql3) so that is not part of the procedure now.

--How could say sql8 (update Tab1 statement) and sql10 (update Tab2 statement) could run as a single transaction (both pass or both fail) if sql9 (truncate table Tab3 statement) is run between sql8 and sql10 (these sql8, sql9, sql10 are not in code there). I understand that slq9 is going to COMMIT to sql8 statement as you mentioned.

Thanks.

CREATE OR REPLACE Package P1 IS

  PROCEDURE update_load_log(p_id           IN NUMBER,
                            p_load_status  IN VARCHAR2,
                            p_load_message IN VARCHAR2);
  PROCEDURE t1;
END;
/
CREATE OR REPLACE PACKAGE BODY P1 IS
  PROCEDURE update_load_log(p_id           IN NUMBER,
                            p_load_status  IN VARCHAR2,
                            p_load_message IN VARCHAR2) IS
                            PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    UPDATE load_log
       SET load_message = load_message || ', ' || p_load_message,
           load_status  = p_load_status,
           load_date    = SYSDATE
     WHERE id = p_id;
    COMMIT;
  END update_load_log;
  
   PROCEDURE t1 IS
  BEGIN
 
insert into test2 (f1) values (sysdate); //sql2

update_load_log (1001, 'test1002','test1002'); //sql1

--EXECUTE IMMEDIATE 'TRUNCATE TABLE test1'; //sql3

insert into test3 (f1) values (sysdate); //sql4

COMMIT; //sql5
end;
end;

Open in new window

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Fastest way to replace data in Oracle 5 50
DBF to ... Converter 5 32
Sql Join Problem 2 22
PL SQL Search Across Columns 4 18
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now