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
top