• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2057
  • Last Modified:

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

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
toooki
Asked:
toooki
  • 3
1 Solution
 
sdstuberCommented:
>>> 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
 
sdstuberCommented:
>>> 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
 
sdstuberCommented:
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
 
toookiAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now