Register forum user name Search FAQ

Gammon Forum

Notice: Any messages purporting to come from this site telling you that your password has expired, or that you need to verify your details, confirm your email, resolve issues, making threats, or asking for money, are spam. We do not email users with any such messages. If you have lost your password you can obtain a new one by using the password reset link.

Due to spam on this forum, all posts now need moderator approval.

 Entire forum ➜ MUSHclient ➜ General ➜ Making sqlite db access thread-safe

Making sqlite db access thread-safe

It is now over 60 days since the last post. This thread is closed.     Refresh page


Posted by Fiendish   USA  (2,534 posts)  Bio   Global Moderator
Date Sun 30 Oct 2011 12:53 AM (UTC)

Amended on Mon 31 Oct 2011 06:53 PM (UTC) by Fiendish

Message
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

https://github.com/fiendish/aardwolfclientpackage
Top

Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #1 on Sun 30 Oct 2011 01:26 AM (UTC)
Message
Can you show your busy handler?

- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top

Posted by Fiendish   USA  (2,534 posts)  Bio   Global Moderator
Date Reply #2 on Sun 30 Oct 2011 01:34 AM (UTC)

Amended on Sun 30 Oct 2011 01:40 AM (UTC) by Fiendish

Message
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. :\

https://github.com/fiendish/aardwolfclientpackage
Top

Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #3 on Sun 30 Oct 2011 02:26 AM (UTC)
Message
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

www.gammon.com.au, www.mushclient.com
Top

Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #4 on Sun 30 Oct 2011 02:45 AM (UTC)
Message
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.

- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top

Posted by Twisol   USA  (2,257 posts)  Bio
Date Reply #5 on Sun 30 Oct 2011 03:13 AM (UTC)
Message
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.

'Soludra' on Achaea

Blog: http://jonathan.com/
GitHub: http://github.com/Twisol
Top

Posted by Fiendish   USA  (2,534 posts)  Bio   Global Moderator
Date Reply #6 on Sun 30 Oct 2011 03:19 AM (UTC)
Message
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*

https://github.com/fiendish/aardwolfclientpackage
Top

Posted by Nick Gammon   Australia  (23,133 posts)  Bio   Forum Administrator
Date Reply #7 on Sun 30 Oct 2011 05:57 AM (UTC)
Message
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.

- Nick Gammon

www.gammon.com.au, www.mushclient.com
Top

The dates and times for posts above are shown in Universal Co-ordinated Time (UTC).

To show them in your local time you can join the forum, and then set the 'time correction' field in your profile to the number of hours difference between your location and UTC time.


24,173 views.

It is now over 60 days since the last post. This thread is closed.     Refresh page

Go to topic:           Search the forum


[Go to top] top

Information and images on this site are licensed under the Creative Commons Attribution 3.0 Australia License unless stated otherwise.