asked on

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

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

>>> 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?

Sean Stuber

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.

toooki

ASKER

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