This discussion is archived
10 Replies Latest reply: Jan 22, 2013 8:52 AM by user346369 RSS

explicit locking

MostafaAbolaynain Newbie
Currently Being Moderated
Hi All

I have a button on a form, when this button is clicked it should read a value from the form and sum it to the total value in a table , here are the steps for When-button-pressed trigger :

1- Read the total issued quantity form the master table and lock the row :
select ISSUED_QTY INTO V_LOCK_QTY
from stopen
where STOCK_CODE=:ISSLINES.STOCK_CODE
for update NOWAIT;


2- Add the value in the form to issued_qty colum in that table:


UPDATE STOPEN
SET ISSUED_QTY=NVL(ISSUED_QTY,0)+NVL(:ISSLINES.QTY,0)
where STOCK_CODE=:ISSLINES.STOCK_CODE;


I used the for update clause to lock the row that was read in step 1 and will be updated in step 2 to make sure that no other user can update that row untill the current user update it and commit (or rollback).

My question is :
1- Is this type of locking is suitable to this situation ?
2- How can I Handle the exception if that row was locked by another user ? by a message with 2 options (wait or exit the trigger).
3- How can I lock the row for a limited time ,I tried to write : for update WAIT 5, but forms cosidered it as syntax error.


Thanks in advance
Mostafa
  • 1. Re: explicit locking
    user346369 Expert
    Currently Being Moderated
    I have written explicit lock pl/sql within Forms to get a value from a table, update it, and then commit. But it isn't the easiest or most elegant thing to do in Forms.

    If you query the row you want to update into a base-table block, Forms will handle the locking and error messaging for you. Forms will lock the record the instant you change a column value in the block, either via PL/SQL or when the user presses the first keystroke. The automatic lock occurs at that point, and Forms performs the Select for update at that point. Forms ALSO compares the values fetched when you first populated the block, with the values when it issued the lock, and issues a message that the record has been changed by another user. If the row is locked by another process, Forms issues that message asking whether you want to retry. If both the lock and the data comparison succeed, the record stays locked until the user commits or clears with rollback.

    If you really need to do explicit locking rather than using the default locking described above, then in order for your form NOT to block other users from updating that same record in the same table, you should ONLY do the lock-update-commit JUST BEFORE you issue a commit_form, but within the commit_form processing. I've done that by calling the update process from the Key-Commit trigger, just before issuing the Commit_form; That way, the locked record doesn't lie out there locked, blocking other users while perhaps the one individual has taken a phone call and forgotten to commit the form.

    Here's how I've accomplished explicit locking. I do not use the Forms method of prompting the user whether to retry -- that would require even more fussing around. Instead, I retry 5 times in 5 seconds, and then fail.

    <pre>Procedure lock_and_update is
    wait_begin_time date;
    Begin
    Loop
    Begin
    Select .... for update nowait;
    Exit; -- exit loop if lock is successful
    Exception when others then
    If SQLCODE &lt;&gt; -54 then Raise;
    End if;
    End;
    -- Continue here if row was locked (ORA-00054);
    If wait_begin_time is null then
    wait_begin_time := Sysdate;
    Elsif (Sysdate - wait_begin_time) * 86400 >= 4.999 then
    --Fails after 5 seconds of trying to lock
    Message(' Table has been locked by another user. Try the commit again');
    Raise Form_trigger_failure;
    End if;
    -- call stored procedure to sleep 1 second
    Sleeper(1); --Sleeper uses DBMS_Lock.Sleep, requires privileges to call that.
    End Loop;
    -- Update your table here
    ...
    -- Be sure to commit immediately, either here,
    -- or by whatever process calls this procedure.
    End;</pre>

    Answering your questions:
    +1- Is this type of locking is suitable to this situation ?+
    It is ok as long as you commit right away.
    +2- How can I Handle the exception if that row was locked by another user ? by a message with 2 options (wait or exit the trigger).+
    I used a loop with a 1-second delay, and failing after 5 seconds. I suppose you could instead of waiting and retrying, you could issue an alert within the loop asking the user whether to retry or exit the commit.
    +3- How can I lock the row for a limited time?+
    You can't. Oracle doesn't provide this capability. Just do the lock and update immediately before a commit. Then, either the row will be updated and committed, or it the lock will fail so there is no lock in effect.
  • 2. Re: explicit locking
    MostafaAbolaynain Newbie
    Currently Being Moderated
    Hi Steve


    Thank you for continous help

    I can't query the row which I want to update into a base-table block, beacause it will lock that row as long as the user is updating that row in the form. he may be sitting and going lunch and leave that row locked, which will block other users till he user finishes from updating and commiting.

    I prefere the second methos (explicit locking), because it will be done during the commit process (So it will take a fraction of a second).

    I will try your procedure and tell you.

    Thank you Steve.
    Mostafa
  • 3. Re: explicit locking
    Andreas Weiden Guru
    Currently Being Moderated
    Is it not possible to completely leave out the select and do a direct update on the table?
  • 4. Re: explicit locking
    user346369 Expert
    Currently Being Moderated
    Andreas' question and idea is good. Why not just update the value in the table with a single update. What could that hurt? Only thing is (see the next paragraph)...

    No matter how you update the table, whether a direct update statement, or the explicit lock and update, or doing it via Forms automatic processing, you should NOT update that value until the user presses a button or function key that commences the commit process. At that point, you should make sure ALL edits have been performed successfully, and then, just before you do the Commit_Form command, update the table -- direct update SQL, or lock and update, or set the value in the base-table block. Then immediately commit. The row would then only be locked for an instant, and many users could update the value and nobody would block the other.

    There should be no way for the user or the form, to stop and ask for more input from the user after the update. Anything like that opens up the possibility of the row being locked and other users being blocked or stalled.

    On the other hand, for rows that are seldom used, or on data where only one user at a time is working on specific rows, then it is ok for Forms to lock rows while the user completes the work. But on heavily used and updated rows, what I wrote above applies.
  • 5. Re: explicit locking
    MostafaAbolaynain Newbie
    Currently Being Moderated
    Hi Andreas

    Your qusetion may leads me to a very nice solution.

    If I could cancel the select statment , there will not be a time gap bwtween the select and update statment. This time is the critical time that may be other users could updatae the selected ISSUED_QTY.

    I will check if I can completely leave out the select statment and do a direct update on the table.

    Thank you Andreas for your very simple and genious idea.

    Mostafa Abolaynain
  • 6. Re: explicit locking
    MostafaAbolaynain Newbie
    Currently Being Moderated
    I changed my code so as to cancel the select statment and done a direct update on the table.


    This is a successfull workaround to escape from locking problems. I'm afraid I need locking in one day.


    Deep thanks to Andreas and Steve for your help.
  • 7. Re: explicit locking
    user346369 Expert
    Currently Being Moderated
    Glad you're happy.

    This issue threw a monkey wrench into our client's system just today. Started getting emails from a user saying they had never seen such a message before. Looking into the form, I found the error -- the form was doing a similar thing to the count being kept above: keeping a count of remaining seats available in a course enrollment. The user was trying to "manually" enroll a student, since the student's online (web) attempt had failed.

    Turns out BOTH were failing at the same point -- my locked record code was kicking back an error message and rolling back. It repeatedly tried to lock the record for 5 seconds, and then gave up after the 6th failure and reported an error.

    After a number of emails, their DBA found the culprit: A THIRD application running had 17 rows in that table locked. Apparently they have some system that does not do as I do: Update the record at the last possible point, and commit immediately. Possibly an error occurred after the update (or lock), and left locks hanging.

    Now a word of warning... If you use the direct SQL update without_ first attempting a lock, then rolling back and reporting an error on failure, your direct update will hang if your system encounters what ours did. Your user or process will be stalled, unable to proceed. And if it has stalled after updating other rows in other tables, THOSE tables will also have locks. It could quickly balloon into a huge stalled mess!

    So I prefer my method.
  • 8. Re: explicit locking
    user346369 Expert
    Currently Being Moderated
    For the record, my looping code above was written in Oracle 7 or 8, and used in Forms 6i, which did not quite keep up with the latest version of SQl.

    In Oracle 9 and above, the "for update" clause has a "wait n" clause, which works the same as the pl/sql loop, so it is much easier to write the code:

    <pre>Procedure lock_and_update is begin
    Begin
    Select .... for update wait 5;
    Exception when others then
    If SQLCODE = -54 then
    Message(' Table has been locked by another user. Try the commit again');
    Raise Form_trigger_failure;
    Else
    Raise;
    End if;
    End;
    -- Update your table here
    ...
    -- Be sure to commit immediately, either here,
    -- or by whatever process calls this procedure.
    End;</pre>
  • 9. Re: explicit locking
    MostafaAbolaynain Newbie
    Currently Being Moderated
    I agree with you that explicit locking is better and safer, as I told you in the first post I tried to use the "for update" clause with "wait n"

    but forms 10g cosidered it as syntax error.
  • 10. Re: explicit locking
    user346369 Expert
    Currently Being Moderated
    Mostafa Abolaynain wrote:
    I agree with you that explicit locking is better and more safe, as a told you in the first post I tried to use the "for update" clause with "wait n"

    but forms 10g cosidered it as syntax error.
    I missed the significance of the "wait 5" in your first post. Sorry. The Oracle Forms developer has always been a step or two behind the Oracle database level with new features. Not sure why it needs to inspect and validate SQL calls within the Developer -- seems like it should just throw the code at the database and let the database validate. But that is what it does -- PRE-validate your SQL.

    So to get around that issue, you could have written a stored procedure (or a procedure in a package) that you called from your form, and within the procedure you could have issued the Select ... for update wait 5. Often times, it is better to put SQL used by a form in a database package anyway. I have written forms where ALL SQL is in a package dedicated to (paired with) the form. That way, if the database changes, or tables accessed by the procedures change, the database will automatically recompile the package. That is a nice feature that Oracle cannot accomplish with a form.

Legend

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