3 Replies Latest reply: Nov 1, 2012 1:31 AM by yoonas RSS

    How to unlock a row if i use FOR UPDATE clause

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