Cursor locking the table

Question

    declare 
     CURSOR C1 
      IS select tgt.exp_date ,(src.eff_date - 1/(24*60*60))eff_date 
         from mira_rate tgt,mira_rate_dummy src
       where src.tc_code = tgt.tc_code and src.carrier_code = tgt.carrier_code and tgt.exp_date is null for update of tgt.exp_date;
       v_a date;
       v_b date;
        i number:=0;
      begin 
      open c1;
      loop
        fetch c1 into v_a, v_b;
        exit when c1%notfound;

        update mira_rate 
        set exp_date =v_b where current of c1;
        i:=i+1;
        end loop;

        dbms_output.put_line(i||' rows updated');

      close c1;
   commit;
      end;

After i excecute the query it is locking the table says

ORA-00054: resource busy and acquire with NOWAIT specified

Also pls tell me how to remove the lock i tried killing the sesssion it is not happening.still it says the same

Affter removing the lock. Pls clear me this requirement

select tgt.exp_date ,(src.eff_date - 1/(24*60*60))eff_date 
             from mira_rate tgt,mira_rate_dummy src
           where src.tc_code = tgt.tc_code and src.carrier_code = tgt.carrier_code and tgt.exp_date is null;

it ill return rows I need to goto the mira_rate table need to update exp_date=eff_date.

Please suggest me how to do i m using Oracle 9i so merge without not matched is working

It is because you have selected 'FOR UPDATE'. Are you sure you need that ? — Hugh Jones, Jan 25 '13 at 12:17
How to do that without that?? can u give me the query by removing that — Mani, Jan 25 '13 at 12:19
have you tried the code with commit? (which is now included after your edit.) — Florin Ghita, Jan 25 '13 at 12:20
Also tell me how to remove the lock pls..I killed the session it is not happening. — Mani, Jan 25 '13 at 12:25
I doubt the commit is relevant, plus, I would only VERY rarely do transactions in PL/SQL. The problem is that the cursor is locking the record you are trying to update. Take out the 'FOR UPDATE'. — Hugh Jones, Jan 25 '13 at 12:25
this code does not lock mira_rate. the table is locked by something else. — Florin Ghita, Jan 25 '13 at 12:26
oops - yes you are right - my bad. Nevertheless, try taking it out. (I am thinking there is a trigger somewhere ...) — Hugh Jones, Jan 25 '13 at 12:28
@HughJones: How to remove the lock first . I killed the session but still it says the same error:( — Mani, Jan 25 '13 at 12:30
You probably have to wait - it depends on whether you have admin rights ... — Hugh Jones, Jan 25 '13 at 12:33
@Satheesh you (or your dba if you have no privs) can see the sessions that are holding locks with `select * from dba_dml_locks where name in ('MIRA_RATE', 'MIRA_RATE_DUMMY');`. some other session (not your session that is getting the error) is holding row/table locks on the tables. — DazzaL, Jan 25 '13 at 12:35
@DazzaL: In case if it is locked there is no other way to remove the lock? — Mani, Jan 25 '13 at 12:41
Somebody help me in removing the lock. i tried it is not hapening — Mani, Jan 25 '13 at 12:50
@Satheesh to remove the lock: Find the session that holds the lock. (I gave you some links as comments on my answer. However you can google: Oracle find locks). Kill that session OR commit or rollback in that session, and the lock will be released. — Florin Ghita, Jan 25 '13 at 13:05
Pls help me in creating the query which i Mentioned in the 2nd aprt of the question — Mani, Jan 25 '13 at 13:45
@FlorinGhita: I m using Oracle 9i I cannot give without not matched statement in MERGE — Mani, Jan 25 '13 at 14:06

Florin Ghita · Answer 1 · 2013-01-25T13:47:36.803

3

At first sight, there is no commit in the code.

The code with commit wil be ok. Commit will release the locks(Oracle cursor examples/expl)

But better you would:

MERGE INTO mira_rate tgt
USING mira_rate_dummy src
ON (src.tc_code = tgt.tc_code and src.carrier_code = tgt.carrier_code)
WHEN MATCHED THEN UPDATE
SET exp_date= src.eff_date - 1/(24*60*60) --or just src.eff_date
WHERE tgt.exp_date is null;

This is what you want to do as far as I understand.

As a rule: What you can do in SQL, do in SQL, not PL/SQL.

edited Jan 25 '13 at 13:47

answered Jan 25 '13 at 12:18

Florin Ghita

17,525
6
57
76

I tried commit ..still it is locked how to remove the lock..I killed the session still it is locked. – Mani Jan 25 '13 at 12:24
[http://www.praetoriate.com/oracle_tips_find_locks.htm](http://www.praetoriate.com/oracle_tips_find_locks.htm) , [http://psoug.org/reference/locks.html](http://psoug.org/reference/locks.html) – Florin Ghita Jan 25 '13 at 12:33
I l try but can you tell me how to remove the lock which i already happnd. i tried killing the session but still same – Mani Jan 25 '13 at 12:33
Yes - be patient - go get a cup of coffee and wait for the lock to release. – Hugh Jones Jan 25 '13 at 12:35
@HughJones: How much does it take? I m waiting for 15 mins – Mani Jan 25 '13 at 12:38
+1 - the comment about commit is not correct, but a single sql statement is the way forward. (Let Oracle decide what to lock and what not ) – Hugh Jones Jan 25 '13 at 12:42
@HughJones:It is not removing the lock . – Mani Jan 25 '13 at 12:48
I reckon by the time you have modified your stored procedure to implement Florin's suggestion of a single update statement (with no for update cursor) it will have returned to you. – Hugh Jones Jan 25 '13 at 12:50
@HughJones Commit or Rollbak realease the locks. Read here [http://docs.oracle.com/cd/E14072_01/appdev.112/e10472/static.htm#CHDGEHBF](http://docs.oracle.com/cd/E14072_01/appdev.112/e10472/static.htm#CHDGEHBF) – Florin Ghita Jan 25 '13 at 12:53
Perhaps have a skim read of this article http://stackoverflow.com/questions/24168/why-are-relational-set-based-queries-better-than-cursors – Hugh Jones Jan 25 '13 at 12:56
@Florin - yes but Satheesh needs to simplify. Perhaps 'Not correct' was too strong, 'Not the way I would do it' is more accurate. – Hugh Jones Jan 25 '13 at 13:02
@HughJones Ok, I have believed that you say that commit won't help to release the lock. His problem is the lock. The better solution is a bonus from me :) – Florin Ghita Jan 25 '13 at 13:08
Now My problem or query is select tgt.exp_date ,(src.eff_date - 1/(24*60*60))eff_date from mira_rate tgt,mira_rate_dummy src where src.tc_code = tgt.tc_code and src.carrier_code = tgt.carrier_code and tgt.exp_date is null Select the row it needs to update in mira_rate. I need to update this row exp_date with eff_date – Mani Jan 25 '13 at 13:20
I ve given the requirement in the question in 2nd part claeraly.Pls check there – Mani Jan 25 '13 at 13:41
@FlorinGhita:I m using Oracle 9i Without not matched I am not able to give – Mani Jan 25 '13 at 13:54
@HughJones:this is returning more than one row SELECT SRC.EFF_DATE - 1/86400 FROM MIRA_RATE_DUMMY SRC WHERE SRC.TC_CODE = TGT.TC_CODE AND SRC.CARRIER_CODE = TGT.CARRIER_CODE – Mani Jan 25 '13 at 14:18
You can try the responses from [here](http://stackoverflow.com/questions/7266880/using-merge-in-9i) – Florin Ghita Jan 25 '13 at 14:19
But if you are on 9i, your code is also ok, because you can't use MERGE. As a note your question is tagged Oracle10g – Florin Ghita Jan 25 '13 at 14:26

Hugh Jones · Answer 2 · 2013-01-25T14:15:54.840

1

Take out the 'FOR UPDATE'.

You need to be very clear in your mind why you need it and in my experience you generally don't.

Between us I think we are saying this should be your approach

begin
  UPDATE mira_rate 
  SET exp_date= src.eff_date - 1/(24*60*60)
  WHERE exp_date is null;

  DBMS_OUTPUT.PUT_LINE
    (TO_CHAR(SQL%ROWCOUNT) || ' Rows Updated);

end;

No need for locks and no need for cursors.

Hope that helps.

Edit - still not entirely sure what your requirement is but the following sql may be what you are looking for.

UPDATE MIRA_RATE TGT
SET EXP_DATE = 
  (
    SELECT SRC.EFF_DATE - 1/86400 
    FROM MIRA_RATE_DUMMY SRC
    WHERE 
      SRC.TC_CODE = TGT.TC_CODE AND
      SRC.CARRIER_CODE = TGT.CARRIER_CODE
  )
WHERE
  TGT.EXP_DATE IS NULL;

edited Jan 25 '13 at 14:15

answered Jan 25 '13 at 12:27

Hugh Jones

2,706
19
30

Yes i l try but it is already locked.How to remove the lock first . I killed the session but still it says the same error – Mani Jan 25 '13 at 12:31
+1 for answer and @Satheesh : http://www.erpgreat.com/oracle-database/removing-table-lock.htm – Najzero Jan 25 '13 at 13:16
Yes I removed the lock.But can you tell me how to change the above query according to my requiremnt – Mani Jan 25 '13 at 13:22
@HughJones :In your answer pls join tables and put – Mani Jan 25 '13 at 13:33
@HughJones: SELECT SRC.EFF_DATE - 1/86400 FROM MIRA_RATE_DUMMY SRC WHERE SRC.TC_CODE = TGT.TC_CODE AND SRC.CARRIER_CODE = TGT.CARRIER_CODE Is returning more than one row – Mani Jan 25 '13 at 14:17
Then you need to post some table definitions - without it we cannot determine what unique or primary keys are available. – Hugh Jones Jan 25 '13 at 14:21
@Satheesh - I don't wish to sound unkind, but you need some basic tuition (and experience) in Sql. You have my sympathy because we all started somewhere. 'Returning More Than Row' means that the second query in brackets finds more than one record for each tc_code, carrier_code. You could add 'AND ROWNUM = 1' to the inner where clause to get the thing to run, but I cannot advise you on the MEANING of your results. – Hugh Jones Jan 25 '13 at 14:28
I understand sorry for that – Mani Jan 25 '13 at 14:29

score 0 · Answer 3 · answered Mar 01 '17 at 09:45

@Satheesh, Updatable select will work only for primary key columns. See if the select fetches the primary key and also useins it in where clause. Else the update will throw error. There is something to check for cannot modify a column which maps to a non key-preserved table You can have join but the update needs primary key to update in the base table.

Cursor locking the table

3 Answers3