I probably should have read the bit about concurrency on http://www.sqlite.org/whentouse.html huh :-) Although in my defense, my need for write concurrency is new. I do wonder if 2 DB's will work well.
On Fri, Jul 25, 2014 at 1:58 PM, Tom Spencer-Smith <t...@meanfox.com> wrote: > > This seems like a really major difference to MySQL. > MySQL with InnoDB allows concurrent writes to different rows in the same > table. > SQLite doesn't even allow concurrent writes to the same DB. > So MySQL has write concurrency, SQLite does not. Guess that's what I'm > learning here. Maybe I should consider MySQL before considering NoSQL. > > > On Fri, Jul 25, 2014 at 10:49 AM, Tom Spencer-Smith <t...@meanfox.com> > wrote: > >> >> Thanks guys, I have already tried WAL and it does speed things up, but I >> still need the daily processing to be done asynchronously. The computations >> are complex, involving around 16 inputs from the player table and resulting >> in 8 outputs. This is core to the game and not a simple score leaderboard. >> So my options appear to be: >> >> 1. Hope that someone addresses my other thread about async I/O not >> working for me >> 2. Create my own mechanism for asynchronous DB transactions >> 3. Deal with writes that fail due to BUSY - just handle the critical >> ones, drop the others >> 4. Experiment with using a separate DB for the leaderboard results, since >> a separate table won't work >> 5. Switch to NoSQL >> 6. Look at the feasibility of holding results in memory and incrementally >> injecting them into the player table >> 7. Some other ideas? >> >> Cheers >> >> Tom >> >> >> On Fri, Jul 25, 2014 at 7:13 AM, Simon Slavin-3 [via SQLite] < >> ml-node+s1065341n76956...@n5.nabble.com> wrote: >> >>> >>> On 24 Jul 2014, at 3:21pm, Tom <[hidden email] >>> <http://user/SendEmail.jtp?type=node&node=76956&i=0>> wrote: >>> >>> > My application presently does all the DB operations on the main >>> thread. But >>> > once a day I need it do the player leaderboards, which could be 500K >>> players >>> > or so, hence several minutes. I can't have the main thread blocking >>> for any >>> > significant length of time - a second or so at most. Hence my desire >>> to do >>> > that one long operation in a separate thread and not have other DB >>> writes >>> > block. >>> >>> Even 500K players shouldn't be taking SQLite "a few minutes" unless >>> there are no good indexes to use. Are the scores saved in your player >>> table ? Do you have indexes suitable for the commands you are executing ? >>> >>> > I don't suppose temporary tables would make any difference? I.e. use a >>> > temporary table for the LB results. Would that allow concurrent >>> writes? (to >>> > temporary table, and to normal table, from different threads). >>> >>> It still won't do what you want. Dr Hipp's suggestion of WAL mode is >>> probably your best bed. With this you can have many readers and one writer >>> all working at the same time. >>> >>> > What about using 2 databases? >>> >>> It would probably be better to try figuring out good SELECTs and indexes >>> first. >>> >>> Simon. >>> _______________________________________________ >>> sqlite-users mailing list >>> [hidden email] <http://user/SendEmail.jtp?type=node&node=76956&i=1> >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >>> ------------------------------ >>> If you reply to this email, your message will be added to the >>> discussion below: >>> >>> http://sqlite.1065341.n5.nabble.com/Does-SQLite-lock-the-entire-DB-for-writes-or-lock-by-table-tp76921p76956.html >>> To unsubscribe from Does SQLite lock the entire DB for writes, or lock >>> by table?, click here >>> <http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=76921&code=dG9tQG1lYW5mb3guY29tfDc2OTIxfDg0MjQ3NTc4OQ==> >>> . >>> NAML >>> <http://sqlite.1065341.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> >>> >> >> > -- View this message in context: http://sqlite.1065341.n5.nabble.com/Does-SQLite-lock-the-entire-DB-for-writes-or-lock-by-table-tp76921p76963.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
