?
Solved

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

Posted on 2011-09-27
4
Medium Priority
?
1,831 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1900 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 74

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 74

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this article, we’ll look at how to deploy ProxySQL.
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…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

801 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