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
Solved

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

Posted on 2011-09-27
4
1,746 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 74

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

861 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