This content has been marked as final. Show 3 replies
Hi,1 person found this helpful
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.
Edited by: yoonus on Oct 31, 2012 12:57 AM
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
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
This will increment client_count of all rows by one for the given mer_id;
UPDATE OP_TMER_CONF_PARENT SET CLIENT_COUNT = CLIENT_COUNT+1 WHERE MER_ID = inMerid
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
You can further modify the procedure to let other users know if the row is being updated.
SELECT CLIENT_COUNT FROM OP_TMER_CONF_PARENT WHERE MER_ID = inMerid FOR UPDATE OF CLIENT_COUNT;