problem with record locking

Databases
1

I’m having a problem getting record locking to work.

I’m including a small code sample here:

  dim myDB as REALSQLDatabase
  dim rs as RecordSet
  dim errmsg as string
  
  myDB = new REALSQLDatabase
  myDB.DatabaseFile = GetFolderItem("MyDB.rsd")
  
  if myDB.Connect() = false then
    msgbox("unable to open db")
    return
  End if
  
  rs = myDB.SQLSelect("select * from Cases where record_id=184")
  
  if myDB.Error or rs = nil  then
    errmsg = myDB.ErrorMessage
    msgbox(errmsg)
  end if
  
  rs.Edit()
  if myDB.Error then
    msgbox("rs.edit failed")
  end if
  
  msgbox("record acquired")
  myDB.close()

I build the app, and make a copy (using the finder’s duplicate command).
The database is in the same location as the two applications.

I run app #1, and get the “record acquired” message. I leave the message
box up, which I think would leave the record in the database locked.

I then start app #2, and also get the “record acquired” message.

I’d expect app #2 would get an error telling me the rs.edit call failed,
as app #1 should have the record locked.

2

I do not believe that SQLite supports file or record locking as it is not meant to be a multi-user database

3

Hi Dave; thanks for the quick response;

Do you know if there is a way I can determine that for certain?

Thank you.

4

The SQLite FAQ page has some information on locking in #5

http://www.sqlite.org/faq.html

5

Robert, You should NEVER use record locking for user time locking, e.g. a user opening a record and locking other users out until she is finished with the record. Relational databases that implement record locking do so for transaction-time locking. That’s not to say that many front-end kits don’t try to do this. I had more than a few discussions about this Geoff more than a decade ago when I was writing database plugins. Real Studio’s / Xojo’s database API suggests that this is a good plan for the backend, when it isn’t.

The right way to do what you’re trying to do is to implement your own table that keeps track of “user record locks”, timeouts, and overrides. In my experience, the biggest usability problem with most multi-user systems is that J-E-R-K who opens a customer record and then goes off to lunch or home for the day. The built-in database locking schemes don’t deal with this well. If your app does, make it the first thing you demo. It will get you instant buy-in from your users every time.

6

I agree with Brad on this one. Having a time-out, assuming that’s appropriate for your app, is a sensible approach. However, you need to make sure two users can’t set a lock at the same time or your locking system could end up with two users locking the same record at the same time.

It would be nice to have a database server that implements this kind of thing for you.

7

We were thinking of implementing our own version of a very simple
record lock, and you’ve confirmed that our approach makes sense.

Thanks to all who responded.

rm.

8

I use Studio Stable with the SQLite database. This handles multi-users very well.

The link is here: http://www.studiostable.com

9

For what it is worth I implemented a record lock scheme in a Web App. I put a button on the screen with a timer that counted down. If you clicked the button it reset the timer and gave more time to complete the edit. Later I added some logic as you changed focus from field to field to reset the timer. Both approaches worked just fine AND the user had a visual indication of the countdown timer on the screen.

When the timer expired they were booted out of the record with a clear message of why.

The lock was simply a row in a table with a few details like who had the record locked and a timestamp. When a second user tried to access the record they got a message that “User xyz has the record locked since MM/DD/YYYY at HH:MM:SS”.

One more bit of logic cleared any stray locks when a user logged in so if they “crashed” the orphaned lock would be removed on next login.