Making sqlite db access thread-safe

Posted by Fiendish on Sun 30 Oct 2011 12:53 AM — 8 posts, 32,275 views.

Fiendish USA Global Moderator Sun 30 Oct 2011 12:53 AM #0

I have a scenario where multiple instances of MUSHclient may want to use the same sqlite database at the same time. Consider the gmcp mapper being used simultaneously in parallel worlds, main and test, for example.

Now the documentation for SQLite says that it is thread safe by default with certain caveats. Fine. So I followed some advice and tried calling db:busy_handler(myHandler) after opening the connection. Where myHandler prints "BUSY!" (so I know it got called) and then returns true (to continuously retry).

But given the dbcheck function in the mapper

function dbcheck (code)
   if code ~= sqlite3.OK and    -- no error
      code ~= sqlite3.ROW and   -- completed OK with another row of data
      code ~= sqlite3.DONE then -- completed OK, no more rows
      local err = db:errmsg ()  -- the rollback will change the error message
      db:exec ("ROLLBACK")      -- rollback any transaction to unlock the database
      error (err, 2)            -- show error in caller's context
   end -- if
end -- dbcheck

I can rather easily and consistently contrive examples where a call of

dbcheck(db:exec(my_query))

in one or the other of my simultaneous sessions gets the following error

Quote:
Run-time error
Plugin: Aardwolf_GMCP_Mapper (called from world: Aardwolf)
Function/Sub: OnPluginEnable called by Plugin Aardwolf_GMCP_Mapper
Reason: Executing plugin Aardwolf_GMCP_Mapper sub OnPluginEnable
[string "Plugin"]:2035: database is locked
stack traceback:
[C]: in function 'error'
[string "Plugin"]:117: in function 'dbcheck'
[string "Plugin"]:2035: in function <[string "Plugin"]:2027>
Error context in script:

With the OTHER session showing "BUSY!" one or maybe two times.

So, fine. Maybe I messed...something...up. So I try db:busy_timeout(100) instead of busy_handler. Still no luck.

I don't quite understand why the busy handler isn't getting used all the time, because it SHOULDN'T be deadlocking (the only reason I can find for sqlite to ignore the busyhandler (I wish it didn't have that inconsistent behavior, but whatever)), but maybe it is for reasons I'm not seeing.

Anyway, manually looping on busy does appear to work. Though this may not be an exactly optimal method...

function dbCheckExecute(query)
wait.make (function()
   local code = db:exec(query)
   while code == 5 do
      wait.time(.1)
      code = db:exec(query)
   end
   dbcheck(code)
end)
end

Nick Gammon Australia Forum Administrator Sun 30 Oct 2011 01:26 AM #1

Can you show your busy handler?

Fiendish USA Global Moderator Sun 30 Oct 2011 01:34 AM #2

To make it as simple as possible, I used

function myHandler(udata, retries)
   print("BUSY!")
   return true
end

I suppose maybe I should have included some sort of delay, but I wasn't concerned about resources for this test. Also, I did try the timeout and that didn't help. :\

Nick Gammon Australia Forum Administrator Sun 30 Oct 2011 02:26 AM #3

See:

http://www.sqlite.org/c3ref/busy_handler.html

Quote:

The presence of a busy handler does not guarantee that it will be invoked when there is lock contention. If SQLite determines that invoking the busy handler could result in a deadlock, it will go ahead and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of invoking the busy handler.

...

Quote:

If both processes invoke the busy handlers, neither will make any progress. Therefore, SQLite returns SQLITE_BUSY for the first process, hoping that this will induce the first process to release its read lock and allow the second process to proceed.

You may have to design around this.

Nick Gammon Australia Forum Administrator Sun 30 Oct 2011 02:45 AM #4

Is this a single copy of MUSHclient running? If so, I don't see why you would have any problems as any script would (should) run to completion before yielding to let another world's scripts run.

If you are using multiple copies of MUSHclient, perhaps consider not doing that?

If that's not an option, your test/delay method should be a reasonable work-around.

Twisol USA Sun 30 Oct 2011 03:13 AM #5

Fiendish said:
multiple instances of MUSHclient may want to use the same sqlite database at the same time. Consider the gmcp mapper being used simultaneously in parallel worlds, main and test, for example.

Now, I was under the impression that multiple worlds run within the same instance of MUSHclient, so maybe he's doing some other voodoo. But, there you go.

Fiendish USA Global Moderator Sun 30 Oct 2011 03:19 AM #6

Nick Gammon said:
If you are using multiple copies of MUSHclient, perhaps consider not doing that?

Robustness in the face of adversity!

Quote:
If that's not an option, your test/delay method should be a reasonable work-around.

Yeah, it works pretty well. Is the mechanism ok, you think? I have no idea what kind of wait delay is reasonable. Maybe .1s is too much, but it's the smallest that wait.time allows. *shrug*

Nick Gammon Australia Forum Administrator Sun 30 Oct 2011 05:57 AM #7

I think 0.1 second sounds OK. I don't quite know why both clients are updating at the same time. Are you simultaneously playing on both?

If you had both worlds open in the same copy of the client I think this problem would go away.