On Thu, Jan 21, 2016 at 4:25 AM, Daniel Polski <daniel at agelektronik.se> wrote:
> Den 2016-01-21 kl. 11:30, skrev Simon Slavin: > >> On 21 Jan 2016, at 9:44am, Daniel Polski <daniel at agelektronik.se> wrote: >> >>> The Webserver/PHP can process up to 16 requests simultanuously and will >>> share one database connection among all instances. >>> The process for each request is: >>> >>> * Use PHP's PDO extension to open a persistent (shared among all >>> instances) connection to the database >>> -> In the background PHP will only open the database once and then use >>> this connection forever >>> >> You have a persistent PHP process on the server ? How are you limiting >> this to 16 simultaneous connections ? I'm not saying any of your procedure >> is wrong, I'm just curious about how it is done under (presumably) Apache. >> > > PHP is running in fastcgi mode, in combination with a lightttp server. > This lighttp server always starts 16 instances of the PHP daemon - once a > request arrives it passes a request to one of the PHP processes. I don't know what this means in real life, but what it sounds like is you believe you're opening a single database connection then forking that into the 16 children. SQLite is in-process, so that doesn't work like it might for something like MySQL - you can't have a connection "shared among all instances" if the instances are in separate processes, you'd need some sort of SQLite server proxy to share in that way. So I think you actually do have multiple connections to the underlying SQLite database file, and those multiple connections can cause locking issues between themselves. From what I can recall the most common reason I've seen causing SQLITE_BUSY is when someone leaves dangling prepared statements around. If the prepared statement is stepped to SQLITE_DONE, it should be fine, but if the most recent sqlite3_step() returned SQLITE_ROW and the client code decided to early exit, then it needs to call sqlite3_reset() or sqlite3_finalize(). Hmm. That sounded ambiguous, and I often find it's a subtle issue to explain. Specifically, what I mean is that if you have /tmp/db.db something like: CREATE TABLE t (c INTEGER); INSERT INTO "t" VALUES(1); INSERT INTO "t" VALUES(2); Then if you write code something like: #include <assert.h> #include <stdio.h> #include <sqlite3.h> int main(void) { const char* db_name = "/tmp/db.db"; sqlite3* db; sqlite3_stmt* s; int i; assert(SQLITE_OK == sqlite3_open(db_name, &db)); assert(SQLITE_OK == sqlite3_exec(db, "BEGIN", NULL, NULL, NULL)); assert(SQLITE_OK == sqlite3_exec(db, "INSERT INTO t VALUES (3)", NULL, NULL, NULL)); assert(SQLITE_OK == sqlite3_prepare(db, "SELECT c FROM t", -1, &s, NULL)); assert(SQLITE_ROW == sqlite3_step(s)); i = sqlite3_column_int(s, 0); /* sqlite3_reset(s); */ assert(SQLITE_OK == sqlite3_exec(db, "COMMIT", NULL, NULL, NULL)); /* Sleep forever. */ printf("Successful, going to sleep.\n"); while (1) { sqlite3_sleep(1000 * 1000); } return 0; } Then if you compile this and run it in one window, you'll get "Successful, going to sleep.", then if you run it in another window, you'll get: Assertion failed: (SQLITE_OK == sqlite3_exec(db, "COMMIT", NULL, NULL, NULL)), function main, file /tmp/ex.c, line 19. If you remove the comments around sqlite3_reset() and try again, the second run will also get to the "Successful..." output. Or it could be sqlite3_finalize(), though often this comes up because you have a prepared-statement cache, which is why I phrased it as sqlite3_reset() in the example. THIS MAY NOT BE WHAT YOU ARE SEEING! But this kind of issue is the first place I'd look. -scott