• Welcome, Guest. Please login.
 
October 25, 2021, 09:06:25 PM

News:

Welcome to the SQLitening support forums!


slSetRelNamedLocks problem also with T0 ModChars

Started by Jean-Pierre LEROY, February 02, 2016, 12:24:04 PM

Previous topic - Next topic

Jean-Pierre LEROY

Thank you Bern.

I will try to understand myself why it desn't work as expected. if I find something I will post here my findings.

Regards,
Jean-Pierre

Jean-Pierre LEROY

To Bern and Cj.

My understanding of slSetRelNamedLocks is that this function could be used:
- Without the SelStatement parameter; we can pass an empty string.
- Without the SelSetNumber; we can pass 0.

But CONTRARY to what is written in the documentation the SelModChars is not only used for the SelStatement, BUT ALSO by Sqlitening to set the lock (INSERT ...)

... so if we don't want to display the error messages, we have to pass "E0" as SelModChars.

Now I will use now this structure of code below to set a named lock (without a SelStatement) unless you find something else.

Thanks a lot for helping me on this topic.
Jean-Pierre


#Dim All
#Include "win32api.inc"
#Include "sqlitening.inc"
$Ip  ="192.168.43.248"
%Port=51234
%Threads=2

Function PBMain () As Long 
  Dim hThread(1 To %Threads) As Long, x As Long
  For x = 1 To %Threads
    Thread Create Mythread(x) To hThread(x)
    Sleep 500 'give threads time to allocate
  Next
  waitformultipleobjects %Threads,hThread(1),%TRUE,%INFINITE
  For x=1 To UBound(hThread):Thread Close hThread(x) To hThread(x):Beep:Sleep 250:Next
End Function

Thread Function MyThread(ByVal ThreadNumber As Long) As Long
  Sleep 100 * ThreadNumber 'give others time to catch up/mixup
  Local result As Long
 
  slConnect $IP,%Port
  If slGetErrorNumber Then ? "Unable to connect thread" + Str$(ThreadNumber):Exit Function
 
  slOpen "junk.db3","C"
  slexe "Create table if not exists t1(c1)"
  slexe "insert into t1 values('VALID RECORD')"
 
  result=slSetRelNamedLocks("+LOCK","T0","",0,"E0")
  If result = 0 Then
    MsgBox "Named lock Ok" + $Cr + "Thread"+Str$(ThreadNumber),,FuncName$
    slSetRelNamedLocks("-LOCK")
  Else
    ? "record already locked so ... other code to display the record."
  End If
 
End Function


Jean-Pierre LEROY

Just one more thing ... it could be useful to correct the documentation on this specific point.

Bern Ertl

February 05, 2016, 12:23:42 PM #18 Last Edit: February 05, 2016, 01:07:46 PM by Bern Ertl
It's trivial to modify Frank's fix to check for the E0 modchar in the rsModChars parameter.  In SQLitening.BAS || slSetRelNamedLocks, change:

      lNoMsg = (INSTR(rsSelModChars, "E0") > 0)

to

      lNoMsg = (INSTR(rsModChars, "E0") > 0)         'Changed to rsModChars - Bern 2/5/2016

The only issue I can see with doing this is that the function calls the same error handler (irHandleError) with a composite string of both modchar parameters from the attempts to establish the lock and from the attempts to execute the SELECT statement.  As currently structured, there doesn't appear to be an easy way to allow for a granular control of error handling for the SELECT statement separate from the named lock.

IOW, if you want to use the E1 modchar for the SELECT statement processing, and the E0 for the named locks, any error for either point ends up in the same error handler with both E0 and E1 in the modchar string.  Currently, the ErrorRut: handler is prioritizing the rsModChars parameter, so the E0 in that field would be the one processed (ie. the E1 in rsSelModChars is ignored).

Thus, I think a bit more work is required to do this "right".  I think the following is the correct solution, but I have not tested it:

'======================<[ 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.
'      E0 =  Return errors (on attempts to establish the named lock)
'   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 psActiveModChars AS STRING PTR   'Use PTR to avoid string copying - Bern 2/5/2016
   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
      psActiveModChars = VARPTR( rsModChars)
      irGetRutAddress "SQLiteSetRelNamedLocks", lhRutAddr
      lNoMsg = (INSTR(rsModChars, "E0") > 0)         'Changed to rsModChars - Bern 2/5/2016
      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
         psActiveModChars = VARPTR( rsSelModChars)
         lNoMsg = (INSTR(rsSelModChars, "E0") > 0)


         ' Check if had error
         IF tlLastError AND (ISTRUE lNoMsg) THEN GOTO ErrorRut  'tlLastError = %SQLITE3_BUSY is only possibility
         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, @psActiveModChars     'Bern 2/5/2016 originally:   rsModChars & rsSelModChars
   FUNCTION = tlLastError

END FUNCTION


Edit:  I edited code to avoid potential endless loop....

Jean-Pierre LEROY

Bern,

Thank you for all your efforts on this subject.

I will try your code (later today or tomorrow) and I will keep you informed.

Regards,
Jean-Pierre

cj

February 05, 2016, 05:39:04 PM #20 Last Edit: February 05, 2016, 05:49:27 PM by cj
Doesn't require server and more control
All this code is just testing LockIt and UnLockIt  with/without server.
It could have been called CreateKey/DeleteKey

IF LockIt(sKey)=0 THEN 'Do work':UnLockIt sKey

FUNCTION LockIt(sKey AS STRING) AS LONG '0-success
  LOCAL s AS STRING
  s = "insert into LockTable values('`VALUE`')"
  REPLACE "`VALUE`" WITH sKey IN s
  FUNCTION = slexe(s,"E0")
END FUNCTION
'
SUB UnLockIt(sKey AS STRING)'no error
  LOCAL s AS STRING, result AS LONG
  s = "delete from LockTable where LockKey='`VALUE`'"
  REPLACE "`VALUE`" WITH sKey IN s
  slexe s
END SUB


#DIM ALL    'mylock.bas  2/5/16
#INCLUDE "win32api.inc"
#INCLUDE "sqlitening.inc"
%Threads = 3
$IP = "192.168.1.2"
%Port=51234
%DeleteLockTable=0
%RequireServer = 1
%MaxRetry = 40
%RetryDurationInMilliseconds=200
%SimulateWork=1 'random sleep
'
THREAD FUNCTION TEST (BYVAL ThreadNumber AS LONG) AS LONG 'mylock.bas
  LOCAL result,retry AS LONG,sKey,s AS STRING

  IF %RequireServer THEN
     slConnect $IP,%Port,"E0"
    IF slGetErrorNumber THEN ? slGetError,,"Thread" + STR$(ThreadNumber)
  END IF

  slopen "junk.db3","C"

  IF %DeleteLockTable AND ThreadNumber = 1 THEN slexe "drop table if exists LockTable"
  slexe "create table if not exists LockTable(LockKey unique)"
  DO
    sKey = "record one"
    result = LockIt(sKey)
    IF result = 0 THEN    'success
      #IF %DEF(%SimulateWork)
      IF %SimulateWork THEN SLEEP RND(100,500)  'simulate work  REMARK THIS LINE
      #ENDIF
      UnLockIt sKey       'release lock
    ELSE
      ? sKey + $CR + "Locked, will try again",,"Thread" + STR$(ThreadNumber)
      INCR retry
      IF retry = %MaxRetry THEN
        ? USING$("Retries #",Retry),,"Thread" + STR$(ThreadNumber)
        EXIT DO
      END IF
    END IF
    SLEEP %RetryDurationInMilliseconds
  LOOP UNTIL result = 0 'dont give up, no maximum tries
  slDisconnect
END FUNCTION
'
FUNCTION PBMAIN AS LONG
  DIM hThread(1 TO %Threads) AS LONG, x AS LONG
  FOR x = 1 TO %Threads
    THREAD CREATE Test(x) TO hThread(x)
    SLEEP 50
  NEXT
  waitformultipleobjects %Threads,hThread(1),%TRUE,%INFINITE
  FOR x = 1 TO %Threads
    THREAD CLOSE hThread(x) TO hThread(x)
    BEEP
    SLEEP 250
  NEXT
END FUNCTION
'
FUNCTION LockIt(sKey AS STRING) AS LONG '0-success
  LOCAL s AS STRING
  s = "insert into LockTable values('`VALUE`')"
  REPLACE "`VALUE`" WITH sKey IN s
  FUNCTION = slexe(s,"E0")
END FUNCTION
'
SUB UnLockIt(sKey AS STRING)'no error
  LOCAL s AS STRING, result AS LONG
  s = "delete from LockTable where LockKey='`VALUE`'"
  REPLACE "`VALUE`" WITH sKey IN s
  slexe s
END SUB
'
FUNCTION ShowLocks(s AS STRING) AS LONG
  s = ""
  LOCAL sArray() AS STRING, Items AS LONG
  slSelAry "select * from LockTable",sArray(),"Q9c"
  Items = UBOUND(sArray)
  IF Items > 0 THEN
    s = JOIN$(sArray(),$CR)
    FUNCTION = Items
  END IF
END FUNCTION