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
