[Home] [Downloads] [Search] [Help/forum]

Gammon Software Solutions forum

See www.mushclient.com/spam for dealing with forum spam. Please read the MUSHclient FAQ!

[Folder]  Entire forum
-> [Folder]  MUSHclient
. -> [Folder]  General
. . -> [Subject]  Making sqlite db access thread-safe
Home  |  Users  |  Search  |  FAQ
Username:
Register forum user name
Password:
Forgotten password?
(New message)
Subject: Making sqlite db access thread-safe
Name:
Your forum user name.
Register forum user name
Password:
Your forum password.
Forgotten password?
Message:
Message to be posted (in English, please)
Maximum of 6000 characters. Text only please, no HTML.
Forum codes:
Check this if your message uses 'forum codes' or templates (auto-detected for new posts).
Forum codes Templates

Save this message ...

Subject review (reverse sequence)

Posted by Nick Gammon   Australia  (18,772 posts)  [Biography] bio   Forum Administrator
Date Sun 30 Oct 2011 05:57 AM (UTC)  [ quote  ]
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
[Go to top] top
Posted by Fiendish   USA  (849 posts)  [Biography] bio   Global Moderator
Date Sun 30 Oct 2011 03:19 AM (UTC)  [ quote  ]
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*
http://aardwolfclientpackage.googlecode.com/
[Go to top] top
Posted by Twisol   USA  (2,229 posts)  [Biography] bio
Date Sun 30 Oct 2011 03:13 AM (UTC)  [ quote  ]
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
[Go to top] top
Posted by Nick Gammon   Australia  (18,772 posts)  [Biography] bio   Forum Administrator
Date Sun 30 Oct 2011 02:45 AM (UTC)  [ quote  ]
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
[Go to top] top
Posted by Nick Gammon   Australia  (18,772 posts)  [Biography] bio   Forum Administrator
Date Sun 30 Oct 2011 02:26 AM (UTC)  [ quote  ]
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
[Go to top] top
Posted by Fiendish   USA  (849 posts)  [Biography] bio   Global Moderator
Date Sun 30 Oct 2011 01:34 AM (UTC)  [ quote  ]

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. :\
http://aardwolfclientpackage.googlecode.com/
[Go to top] top
Posted by Nick Gammon   Australia  (18,772 posts)  [Biography] bio   Forum Administrator
Date Sun 30 Oct 2011 01:26 AM (UTC)  [ quote  ]
Message
Can you show your busy handler?
- Nick Gammon

www.gammon.com.au, www.mushclient.com
[Go to top] top
Posted by Fiendish   USA  (849 posts)  [Biography] bio   Global Moderator
Date Sun 30 Oct 2011 12:53 AM (UTC)  [ quote  ]

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
http://aardwolfclientpackage.googlecode.com/
[Go to top] 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.

1,521 views.

[Reply to this subject]  Reply to this subject   [New subject]  Start a new subject   [Refresh] Refresh page

Go to topic:           Search the forum

[Go to top] top

[Home]

Written by Nick Gammon - 5K

Comments to: Gammon Software support
[RH click to get RSS URL] Forum RSS feed ( http://www.gammon.com.au/rss/forum.xml )

[Best viewed with any browser - 2K]    [Internet Contents Rating Association (ICRA) - 2K]    [Web site powered by FutureQuest.Net]