Posting a transaction with locking conflict 1

[AndrewMozley]

A transaction (entering details of a warranty claim) results in appending records to some tables and updating a record in a third table (Customers). This last action may meet a problem if the relevant record is already locked.

In this last case the system wants to offer the user the chance to release the lock - maybe by closing some other open form – and trying again.

At present there is (in effect) this code :

BEGIN TRANSACTION
SELECT <table1>
APPEND BLANK
REPLACE <field1> WITH  <value1>, <field2> WITH value2>   . . . .
SELECT <table2>
APPEND  . . .
REPLACE   . . . .
. . .
SELECT <Customer>
LOCATE <required record>
DO WHILE .T.
   IF RLOCK()
      REPLACE <fieldname> WITH <Customer>.<Fieldname> + 1
      UNLOCK
      END TRANSACTION
     ELSE
      lAnswer = MESSAGEBOX(“Customer record locked.  Do you want to try again?”,4)
      IF lAnswer <> 6
         ROLLBACK
         MESSAGEBOX (“Transcation abandoned”)
         EXIT
         ENDIF
      ENDIF
   Wait4Me(5)
   ENDDO

Wait4Me() is a function kindly provided by csanduu on this site many years ago. It waits a few seconds and lets the user click on another form if he needs to; and for the RLOCK to work effectively SET(“REPROCESS”) <> 0

As far as I can see this code achieves the result. But if there is a better way of doing it I would be grateful for guidance.

Thanks. Andrew

 

[Mike Lewis]

Andrew,

Before even thinking about improving the code, the very first thing you MUST do is to remove the first message box (asking the user if they want to try again).

The reason is simple. When a transaction is in progress, all the data involved in the transaction is completely locked, for all users. The relevant records cannot even be read, even by users who don't need to update them. For that reason, it is essential to keep the transaction in progress for the minimum possible time. That won't happen if you display a message and wait for a user to respond. In fact, any user interaction during a transaction is to be avoided.

In this case, I would simply test for the lock before you start the transaction. If the lock fails, issue the message, and don't go ahead with the transaction until the lock is available.

Also, from a user interface viewpoint, I wouldn't ask the user if they want to try again. Many users simply won't understand the issue of record locking, and wouldn't understand why they are being asked to try again. A better approach is to say "Another user is working with this data. Please try again later." Then exit the routine entirely. (Remember, you are doing this before the transaction has started.)

Finally, I would also kill the "transaction abandoned" message. You have just told the user that they can't proceed. They don't need to be told that again.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[AndrewMozley]

Thank you Mike for your very prompt reply and for your thoughts. I take your point about the possibility of locking out other users; however the new records are not intended to be read by other users until the transaction is complete; if ROLLBACK occurs then they will not exist. So the pause while attempting RLOCK() does not cause a problem in this case. Hope that is correct.

Again, I appreciate that in general the user may not understand about record locking; but in this case the user (there are only two) may himself have caused the failure of the RLOCK() - by opening a form which is editing the customer record; so during the Wait4Me() function, he can switch focus to the editing form and close it, then complete the transaction; he will then not have to re-enter the details on the form a second time.

In the matter of the ‘Transaction abandoned’ message, you are right that it would be un-necessary if he had already been told that he could not proceed. But in this case there had been a possibility that he could proceed, so the message is there to make it clear what has happened. Maybe it is rather ‘Belt and Braces’ . . .

But again, thank you. In the more general case, where the transaction updates (rather than appends to) more than one table, I can see the advantage of getting all the RLOCK()s done first.

Andrew

 

[Olaf Doschke]

Mike already made the most important point about the duration of transactions. In a two user system and when users are used to this scheme, it may stay sufficient to act this way, but it surely doesn't scale up well.

Just have a read on this:

http://fox.wikis.com/wc.dll?Wiki~TransactionsAndBuffering

Not only, that Steven Black points out the importance of letting transactions have minimum blocking effect, you can easily combine transactions and table buffering to let Rollback get to the situation a user might want to retry to save. So these tow things are not to be seen as alternatives. What I'd rather not do anymore is RLOCK. The TableUpdates either gets the necessary automatic locks or not. The only reason for manual locks is you dp them on some header rows to indicate not only editing them is currently exclusive to the other user, but most likely also any related data on the detail levels.

Bye, Olaf.