On 24 Jul 2014, at 8:01am, Tom <[email protected]> wrote: > 1. read various fields from table A as inputs to the leaderboard calculation > 2. in a thread, compute leaderboard and write to table B (which main thread > doesn't write to). > > In other words, the main thread only writes to table A and the background > thread only writes to table B. > I was hoping that miscellaneous concurrent writes to table A would not give > me SQLITE_BUSY, since the background thread is only writing to table B. But > I get the impression the entire DB is locked - is that correct? My code will > be simpler if I don't ever have to deal with SQLITE_BUSY.
SQLite does not do table-level locks. If anything is locked, the entire database is locked. Table-level locks would not work for SQLite because a table may have related rows in another table enforced with FOREIGN KEYs. If the application you described above is meant to be used concurrently with another application (e.g. while that process is running, another process or another computer may be updating the database) then the whole operation should be done as one transaction, e.g. with a BEGIN before step 1 and an END after step 2. This is because someone might be updating the data for table A while it's being read. You should not have to deal with SQLITE_BUSY if you set a reasonable timeout for your database connection. You can use either of the following: <http://www.sqlite.org/c3ref/busy_timeout.html> <http://www.sqlite.org/pragma.html#pragma_busy_timeout> A reasonable timeout is not half a second. A reasonable timeout is two minutes. Something as simple as a leaderboard calculation, even for a thousand teams, should not be taking a few minutes to calculate unless your language is very slow. Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
