slSetRelNamedLocks behavior

[Jean-Pierre LEROY]

Dear all,

I started using slSetRelNamedLocks to avoid the "last save wins" scenario by locking the records with named locks.

When the function returns 0 I open a form allowing the user to UPDATE the record.
When the result of the function is <> 0, the record is locked by another user and I open a form allowing the user to VIEW only the record (no updates are allowed).

I discovered that with the same Tcp file number, slSetRelNamedLocks always 0 with the same lock name.

Example for the same user (i.e. same tcp file number) calling twice the function:

lSetRelNamedLocks("+Cust1395")   => Return 0  normal for the first call.
lSetRelNamedLocks("+Cust1395")   => Return 0  seems strange for the second call ?

What do you think ?
Is-there any workaround ?

Any help will be appreciated.

Regards,
Jean-Pierre

[cj]

I have not used them, but some commands are ignored if already executed.
I need to study this command, hope I'm not off-base here.

#INCLUDE "win32api.inc"
#INCLUDE "sqlitening.inc"
$IP = "192.168.1.2"
%Port = 51234
%SLEEP = 1000
'
FUNCTION PBMAIN () AS LONG  'locks.bas
  LOCAL hThread AS LONG

  slConnect $IP,%Port 'connect
  slConnect $IP,%Port 'again, ignored

  slopen "sample.db3","C" 'open
  slOpen "sample.db3","C" 'again, ignored

  slSetRelNamedLocks "+L" 'create named lock
  slSetRelNamedLocks "+L" 'again, ignored

  THREAD CREATE Mythread(0) TO hThread
  SLEEP %SLEEP            'make thread wait before release
 
  slSetRelNamedLocks "-L" 'release named lock
  slSetRelnamedLocks "-L" 'again, ignored

  waitforsingleobject hThread,%INFINITE
  THREAD CLOSE hThread TO hThread
END FUNCTION
'
THREAD FUNCTION MyThread(BYVAL x AS LONG) AS LONG
  slConnect $IP,%Port
  slopen "sample.db3","C"
  DO
    BEEP
    SLEEP 250
  LOOP WHILE LEN(slGetStatus(1))
END FUNCTION

[Bern Ertl]

Check the code for SetRelNamedLocks() in the SQLiteningServer.BAS file:

' Process each set lock name
   for llDo = 1 to parsecount(lsLockNames, $VT)
      lsLockName = trim$(parse$(lsLockNames, $VT, llDo))
      if left$(lsLockName, 1) = "+" then
         lsA = "Insert into tblNamedLocks Values(" & format$(rlTcpFileNumber) & ",'" & mid$(lsLockName, 2) & "', 0," & format$(llSetTime) & ")"
         llA = sqlite3_exec(ghSystemTempDB, strptr(lsA), 0, 0, 0)
         if llA then
            if llA <> %SQLite_Dups then SQLiteError

            ' Can't set lock so insert waiter
            lsA = "Insert into tblNamedLocks Values(" & format$(rlTcpFileNumber) & ",'" & mid$(lsLockName, 2) & "'," & format$(rlTcpFileNumber) & "," & format$(llSetTime) & ")"
            llA = sqlite3_exec(ghSystemTempDB, strptr(lsA), 0, 0, 0)
            if llA then
               if llA <> %SQLite_Dups then SQLiteError
               ' If we got here it means they tried to set same value more than once
               ' in same connection, so we just ignore it.
            else
               ' We have a real waiter
               lbHaveWaiter = %True
            end if
         end if

It appears to be designed behavior to ignore duplicate lock attempts from the same connection.

[Jean-Pierre LEROY]

Thank you all for looking at this issue.

I would like the function slSetRelNamedLocks to return a value <> 0 if the locking failed REGARDLESS of the tcp connection ?

What do you think ? do you agree with me ?

I don't understand very well the code made by Fred.

Q1. Why did he tried twice to insert a value into the table tblNamedLocks ?
Q2. Which modifications need to be done to return an error code if we try to lock a named lock even if it is with the with the same connection.

Thanks.
Jean-Pierre

[Bern Ertl]

It looks to me like, if you really wanted to change the behavior, you could add the following line where the bolded comments exist in the code fragment I posted earlier:

FUNCTION = %SQLitening_LockTimeout

But I see now that it was coded the way it was to ensure that the function returns no error in this case because when your code calls this function, it is trying to establish a named lock (or control) over the database.  In the case that you already own the named lock, you have control, so returning an all clear lets your app proceed as expected.

[Jean-Pierre LEROY]

Hi Bern,

A few months ago, you gave me the solution to this issue; I made the change that you mentioned and it works perfectly. Thank a lot.

I think this change should be in implemented in the standard distribution of SQLitening.

This change is very useful with applications with multiple non-modal Windows, where the same end user could (by error) try to edit twice the same record.

Thanks,
Jean-Pierre