• Welcome, Guest. Please login.
 
September 15, 2019, 10:18:37 am

News:

Welcome to the SQLitening support forums!


slSetRelNamedLocks Problem

Started by Frank W. Kelley, August 13, 2015, 11:42:12 am

Previous topic - Next topic

Frank W. Kelley

In switching an application from purely local to client/server, I've started switching commands from slSel to slSetRelNamedLocks to prevent problems when more than one user attempts to edit the same record. I've set error handling when making the call to "E0". My understanding is that slSetRelNamedLocks should return an error condition if the requested record is locked. It does this, but only after displaying an SQLitening message that the record is locked and asking if the user wants to retry.

Is there a way to suppress the SQLitening message in order for my code to handle the locking conflict? None of the docs cover this (beyond setting the error flag to "E" or "E0"), and there's no information on these forums.

Any help is sincerely appreciated.

Bern Ertl

I'm not sure why you are calling slSetRelNamedLocks.  I'm also writing an app in the client/server model and I have not found a need for it.

My app allows users to read data from the database and edit/save changes.  Any data read from the database is current from the moment it was read.  It is possible for a concurrent user to read the same data subsequently and both of them to make edits/changes to the same data at almost the same time.  The last update will, of course, be the one that sticks.

My app also has a rather complex calculation that spans a portion of the data in the database.  When a user initiates that calculation function, the app issues a BEGIN EXCLUSIVE statement to lock down the database until the calculations can complete (changes to the required data set while the calculations are in progress would be problematic).  See here for more info on transaction types:  http://www.sqlite.org/lang_transaction.html

As long as you are trapping for errors when you read or write to the database, you should be able to handle any issues with concurrency and locked records.


Frank W. Kelley

Thanks for the response, Bern.

We were trying to avoid the "last save wins" scenario by locking the records with named locks. As it turns out, a check of the source code for the DLL revealed that the error setting is ignored when a lock conflict occurs. Adding a couple of lines of code and recompiling the DLL removes the SQLitening message and only the error code is returned, so all is good.

Jean-Pierre LEROY

Hi Franck,

It's an interesting thread as I will have to use slSetRelNamedLocks in the near future to avoid  the "last save wins" scenario.

Are-you willing to share with us the lines of codes that need to be modified.

Thanks,
Jean-Pierre

cj

August 15, 2015, 02:29:06 am #4 Last Edit: August 15, 2015, 07:34:08 pm by cj
> Are-you willing to share with us the lines of codes that need to be modified.
I would also like to be sure we are modifying the same lines.

Notes:
Network conflict displays a retry messagebox even if "E0" is specified."
"The above request was unsuccessful because the database is currently
locked.  Want to try again?"

If one occurs it is probably because allowing locking for too long and the
design should be changed or the timeout can be increased on the slOpen.

slOpen:

Frank W. Kelley

August 17, 2015, 01:37:50 pm #5 Last Edit: August 17, 2015, 01:42:34 pm by Frank W. Kelley
The issue does not involve timeout or any external setting. slSetRelNamedLocks simply ignores the rsSelModChars argument when a named lock conflict occurs.

Here is a revised version of the slSetRelNamedLocks routine (including comments) to facilitate copy/paste for those who would like to modify the DLL. Using this routine, setting rsSelModChars = "E0" correctly returns the error value to your calling routine without displaying the additional MSGBOX alert internally before doing so. Note that for simplicity, I did not test for "E" alone, so "E0" is required in this instance. New additions are in bold. (Also added the routine as a zip file.)

'======================<[ Set Rel Named Locks ]>=======================
FUNCTION slSetRelNamedLocks ALIAS "slSetRelNamedLocks" (BYREF rsLockNames AS STRING, _
                                                        OPTIONAL BYVAL rsModChars AS STRING, _
                                                        OPTIONAL BYVAL rsSelStatement AS STRING, _
                                                        OPTIONAL BYVAL rlSelSetNumber AS LONG, _
                                                        OPTIONAL BYVAL rsSelModChars AS STRING) EXPORT AS LONG
'   Will set or release named lock(s).  This can be used to
'   implement application locks or to simulate record locks.  Names
'   are locked on a server-wide basis.  If a name has been locked by
'   one client, the server blocks any request by another client for
'   a lock with the same name.  This allows clients that agree on a
'   given lock name to use the name to perform cooperative named
'   locking.  But be aware that it also allows a client that is not
'   among the set of cooperating clients to lock a name and thus
'   prevent any of the cooperating clients from locking that name.
'   One way to reduce the likelihood of this is to use lock names
'   that are database-specific or application-specific.  Named locks
'   are only used in remote mode, ignored when running in local mode.
'   Will also optionally do a Sel command but only if the lock request
'   was successful.
'   LockNames:
'      This is a vertical tab ($VT) delimited list of names to set or release.
'      Be sure these names uniquely identify the lock you want to set.  If, for
'      example, you know the RowID of a record you want to lock you would need
'      to include some type of table ID within the name. The first character of
'      each name must be one of the following:
'         + (plus)  = Set lock
'         - (minus) = Release lock.
'      All named locks owned by a connection are automatically released when
'      the connection is no longer active.
'   ModChars:
'      Tn = Where n is the number of milliseconds to wait for a named
'           lock to be set before returning as unsuccessful.  If omitted
'           or is zero then will return immediately if a named lock can
'           not be set because it is owned by another connection.
'      R =  Release all named locks owned by this connection before setting
'           or releasing any named locks in LockNames.
'   SelStatement:
'      If the SelStatement is not empty and locking was successful then
'      will do the Sel command.  The Statement should be Select or Pragma
'      or any other that returns rows (Insert, Update, and Delete do not
'      return rows).
'   SelSetNumber:
'      SelSetNumber can be omitted or be any value from 0 to 32767. If omitted
'      then will use zero.  If SelStatement is empty then this is ignored.
'      SelSetNumber is used as an array index, no gaps is best, which will result
'      in a smaller array.  You can have as many unique sets open/active at same
'      time as your memory will allow.
'   SelModChars:
'      This value is ignored it SelStatement is empty.
'      C  = Will first do a slCloseSet(rlSetNumber). This will prevent
'           error 14 (%SQLitening_InvalidSetNumber) but should be used
'           with caution. Omiting set number or passing set number of
'           zero will do the same thing.
'      D  = Allow duplicate column names. Not recommended if using slFN or slFNX.
'           because you will always get the first value returned.  SQLite does
'           not normally return qualified column names.  SQLite will return C1
'           twice if you Select T1.C1, T2.C1. So the solution is to alias one of
'           them with the As clause as follows Select T1.C1, T2.C1 as C1Again.
'           There is a Pragma called "full_column_names" which forces SQLite to
'           return qualified names, but does not seem to work if you Select *.
'           Read up on it and use if you like.  I like using an alias because it
'           is less code and more clear.
'      Em = Return errors. This will override the global return errors flag.
'           m is the optional message display modifier and can be:
'              0 = No message is displayed.  This is the default.
'              1 = Display a warning message with OK button.  Error is
'                  returned when OK pressed.
'              2 = Display a question message with OK and Cancel buttons.
'                  If they press OK, error is returned.  If they press
'                  Cancel, will exit process.
'      e  = Do not return errors, display message and exit process. This
'           will override the global return errors flag.
'      Bn = Do a Begin Transaction before doing the Sel command. The type
'           of Begin is controlled by the value of n as follows:
'              0 = Deferred. This is the default if n is omitted.
'              1 = Immediate.
'              2 = Exclusive.
'           This allows for database locking and selecting in one trip to the server.
'                                 *** CAUTION ***
'                 If the Begin or the Select returns Busy then will restart with
'                 the Begin.  Use Begin Immediate to prevent this or set ProcessMods
'                 to %gbfDoNotRetryIfBusy.
'      R =  Release all named locks owned by this connection after doing
'           the Sel.
'   ReturnCode:
'      Zero if locking was successful. If locking failed then depending on
'      ModChars and the global return errors flag, will either display error
'      and exit or will return %SQLitening_LockTimeout.  If locking failed then
'      no locks are set nor are any released and no Sel is run. If locking was
'      successful and there is an optional SelStatement then will return zero if
'      processed OK. Else, depending on ModChars and the global return errors flag,
'      will either display error and exit or will return the error number.
'   Examples:  (No error checking shown)
'      slSetRelNamedLocks("+Cust1395" & $VT & +"Cust40928")  --  Will set two named locks.
'      slSetRelNamedLocks("+Cust1395" & $VT & "-Cust40928")  --  Will set one named locks and release another.
'      slSetRelNamedLocks("-Cust1395")  --  Will release one named locks.
'      slSetRelNamedLocks("+Cust1395", "T15000R", "Select * from Cust where Num=1395")
'          Will first release all locks for this connection then set one named lock,
'          waiting up to 15 seconds. If lock was set OK then will select the customer.
'      slSetRelNamedLocks("", "R")   --  Will release all locks for this connection.
'      slSetRelNamedLocks("")   --  Will do nothing.

   LOCAL lhRutAddr AS DWORD
   LOCAL lsColumnNames AS STRING
   LOCAL lNoMsg AS LONG

   ' Must handle differently if running remote
   IF tuFlags.AreRunningRemote THEN

      ' Do pre select stuff if there is a slSel commnad
      IF LEN(rsSelStatement) THEN
         ' Close set if requested
         IF INSTR(rsSelModChars, "C") OR ISFALSE rlSelSetNumber THEN slCloseSet(rlSelSetNumber)
         ' Audit it
         IF ISFALSE irPreSelect(rsSelStatement, rlSelSetNumber) THEN ErrorRut
      END IF

      ' Call remote rut
      irGetRutAddress "SQLiteSetRelNamedLocks", lhRutAddr
      lNoMsg = (INSTR(rsSelModChars, "E0") > 0)
      DO
         CALL DWORD lhRutAddr USING UsingSetRelNamedLocks(thDab, rsLockNames, rsModChars, rsSelStatement, rlSelSetNumber, rsSelModChars, lsColumnNames) TO tlLastError
         IF ISFALSE tlLastError OR tlLastError = %SQLITE3_BUSY THEN EXIT DO
         IF tlLastError <> %SQLitening_LockTimeout OR ISTRUE tuDbFlags.Bits.DoNotRetryIfBusy OR ISTRUE(lNoMsg) THEN ErrorRut
         IF MSGBOX(LEFT$("Named Locks " & rsLockNames & ", " & rsSelStatement, 255) & STRING$(2, $CR) & _
              "The above request was unsuccessful because the named lock(s) is currently locked.  Want to try again?", %MB_ICONQUESTION + %MB_YESNO, $Title) = %IDNO THEN ErrorRut
      LOOP

      ' Do post processing for the Sel stuff
      IF LEN(rsSelStatement) THEN

         ' Check if had error
         IF tlLastError AND ISFALSE irCheckIfBusy("Named Locks " & rsSelStatement) THEN ErrorRut
         DO WHILE tlLastError
            CALL DWORD thCallSel USING UsingSel(thDab, rsSelStatement, rlSelSetNumber, rsSelModChars, lsColumnNames) TO tlLastError
            IF tlLastError AND ISFALSE irCheckIfBusy("Named Locks " & rsLockNames & " " & rsSelStatement) THEN GOTO ErrorRut
         LOOP WHILE tlLastError = %SQLITE3_BUSY

         ' Mark the set as active
         thaSets(rlSelSetNumber) = 1

          ' Do post select stuff
         IF LEN(rsSelStatement) THEN IF ISFALSE irPostSelect(lsColumnNames, rlSelSetNumber, rsSelModChars) THEN ErrorRut

      END IF

   ELSE

      ' Call slSel if they passed a statement
      IF LEN(rsSelStatement) THEN FUNCTION = slSel(rsSelStatement, rlSelSetNumber, rsSelModChars)

   END IF

   ' Exit OK
   EXIT FUNCTION

   ErrorRut:
   irHandleError "Named Locks " & rsLockNames & ",  " & rsSelStatement, rsModChars & rsSelModChars
   FUNCTION = tlLastError

END FUNCTION


Bern Ertl

I took a closer look at Frank's code after reading Jean-Pierre's thread.

While it's logically equivalent in this case, I believe that Frank's suggestion is slightly malformed.

IF tlLastError <> %SQLitening_LockTimeout OR ISTRUE tuDbFlags.Bits.DoNotRetryIfBusy OR ISTRUE(lNoMsg) THEN ErrorRut

should probably be:

IF tlLastError <> %SQLitening_LockTimeout OR (ISTRUE tuDbFlags.Bits.DoNotRetryIfBusy) OR (ISTRUE lNoMsg) THEN ErrorRut

Again, this doesn't make a difference for the IF statement as written, but if anyone were to modify the statement further, the previous/original version could be problematic.