miercuri, 20 mai 2015

ORA-00054: resource busy and acquire with NOWAIT specified

First of all, i must say this message maybe is not really scary, because you might think with some patience the problem can be overridden. Well, there is a possible scenario giving some headaches to a DBA:

alter table  tn disable constraint tnc;

Everything is fine 'till now, you have disabled a constraint in order to update some data.
After all these updates, and after you verify the data is compliant with the bussiness rules enforced by the constraint tnc:

alter table  tn enable constraint tnc;

 ORA-00054: resource busy and acquire with NOWAIT specified

Now you are in serious trouble. Working hours mean lots of data inserted in your tables, not anymore protected by that constraint, and the users can broke the bussiness rule from now.
Of course, you may say the same problem can occur during that few seconds while you ran the two DDL commands and the DML commands which modified th table tn. Agree, was not a good practice way of doing things, but this kind of manipulating data can be seen very often in practice, when a DBA or a developer doesn't care or can't wait for a maintenance window, in order to manipulate data, ignoring the possibility of any other session, locking a table.
Unfortunately, there isn't any complete solution for this problem. You can run the following code (for Oracle <10g), which tries for n minutes to achieve the lock on the table and run the DDL command:

  is_ok  BOOLEAN;
  t_l    EXCEPTION;
  start_time     DATE; -- the moment of the first cycle
  no_min number; --how many minutes the program will run, trying to
                 -- achieve the lock
  is_ok := FALSE;
  WHILE (NOT is_ok) AND (SYSDATE < start_time + no_min/24/60)
      EXECUTE IMMEDIATE ('alter table  tn enable constraint tnc');
        is_ok := TRUE;
    WHEN t_l THEN
  IF is_ok = FALSE THEN
    DBMS_OUTPUT.PUT_LINE('the constraint is still disabled!');

For Oracle versions >10g, there is a parameter, ddl_lock_timeout , doing the same job like the code from above. Any DDL command issued against a table will try for a number of seconds equal to ddl_lock_timeout.
And there is another solution, involving   the QUIESCE concept, but i don't like it and i don't recommend it to anyone.
Good luck!

Niciun comentariu:

Trimiteți un comentariu