This discussion is archived
3 Replies Latest reply: Oct 31, 2012 11:31 PM by yoonas RSS

How to unlock a row if i use FOR UPDATE clause

970843 Newbie
Currently Being Moderated
In procedure if we use FOR UPDATE clause, it will lock particular row and allow only one client to update whereas other client can only fetch data in the same row at that time.

My question is when will it unlock the row, what should we do to unlock the row while writing procedure. Take this example here im using FOR UPDATE clause for client_count, when ll it unlock that particular row in this procedure.

create or replace PROCEDURE newprocedur(inMerid IN VARCHAR2,outCount OUT NUMBER) AS
CURSOR c1 IS
select CLIENT_COUNT from OP_TMER_CONF_PARENT where MER_ID = inMerid FOR UPDATE OF CLIENT_COUNT;
BEGIN
Open c1;
loop
fetch c1 into outCount;
exit when c1%NOTFOUND;
outCount:=outCount+1;
update OP_TMER_CONF_PARENT set CLIENT_COUNT = outCount where current of c1;
end loop;
close c1;
END;
  • 1. Re: How to unlock a row if i use FOR UPDATE clause
    yoonas Expert
    Currently Being Moderated
    Hi,

    After updating you have to use commit or rollback so that oracle will release the lock. If you don't use commit only you can see the updating you have done, use commit to make all changes permanent.

    Regards
    Yoonas

    Edited by: yoonus on Oct 31, 2012 12:57 AM
  • 2. Re: How to unlock a row if i use FOR UPDATE clause
    970843 Newbie
    Currently Being Moderated
    Here, Multiple clients will call the above procedure at same time having same MER_ID, in that case i have to lock the row only one client should update the count at tat time and others should have the right only to fetch not update, so i am using FOR UPDATE clause.Am i doing it in right way??? or how should i modify the above code?

    And if i put commit after update statement will i be able to execute another fetch against that for update cursor?

    Edited by: im_asa on Oct 31, 2012 9:09 PM
  • 3. Re: How to unlock a row if i use FOR UPDATE clause
    yoonas Expert
    Currently Being Moderated
    Hi,

    Basically you are incrementing client_count by 1 , Why you have to fetch row one by one and update? you could just finish that in a single update
    UPDATE OP_TMER_CONF_PARENT
    SET CLIENT_COUNT = CLIENT_COUNT+1
    WHERE MER_ID     = inMerid 
    This will increment client_count of all rows by one for the given mer_id;

    After updating you have to make the changes permanent so that other users will see the changes you have made.

    To lock the row before update you can use same select statement in you cursor
    SELECT CLIENT_COUNT
    FROM OP_TMER_CONF_PARENT
    WHERE MER_ID = inMerid FOR UPDATE OF CLIENT_COUNT;
    You can further modify the procedure to let other users know if the row is being updated.

    Regards
    Yoonas

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points