Dear Users
I've spent hours reading various web-based documents, examined lots of
code snippets, written some code of my own, but I still patently do
not understand SQLite locks.
My misunderstanding is probably best illustrated with a concrete
example written in C.
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
int main()
{
sqlite3 *db;
int error;
sqlite3_stmt *res;
int count;
char *sqlite_query;
error = sqlite3_open_v2("simple.db3", &db, SQLITE_OPEN_READWRITE, 0);
if(error)
{
fprintf(stderr, "Can't open database: %s\n",
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
sqlite3_busy_timeout(db, 10000);
sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
error = sqlite3_prepare_v2(db, "select count from table1", 100, &res,
NULL);
if (error != SQLITE_OK)
{
printf("SQLITE_OK is not OK - %d\n", error);
exit(1);
}
while (sqlite3_step(res) == SQLITE_ROW)
{
count = sqlite3_column_int(res, 0);
printf("%u", count);
}
sqlite3_finalize(res);
count++;
sqlite_query = sqlite3_mprintf("update table1 set count = %d",
count);
error = sqlite3_exec(db, sqlite_query, NULL, NULL, NULL);
if(error != SQLITE_OK)
{
puts(" - error updating count");
}
else
{
puts(" - updated OK");
}
sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);
sqlite3_free(sqlite_query);
sqlite3_close(db);
return 0;
}
When I run this serially, I get:
$ ./demo
214 - updated OK
$ ./demo
215 - updated OK
$ ./demo
216 - updated OK
When I run it in parallel, using this script:
#!/bin/sh
count=0
limit=4
while [ ${count} -lt ${limit} ]
do
count=`expr $count + 1`
./demo 2>&1 &
done
I get:
$ ./multi.sh
219 - updated OK
219 - error updating count
220 - updated OK
220 - error updating count
I thought that this line...
sqlite3_busy_timeout(db,
10000);
...would give me a 10 second window where SQLite would gracefully,
invisibly take care of locking for me, as per this documentation:
"This routine sets a busy handler that sleeps for a specified amount
of time when a table is locked. The handler will sleep multiple times
until at least "ms" milliseconds of sleeping have accumulated. After
at least "ms" milliseconds of sleeping, the handler returns 0 which
causes sqlite3_step() to return SQLITE_BUSY or SQLITE_IOERR_BLOCKED."
But things go wrong long before 10 seconds have elapsed, and I get the
error messages shown.
Now, clearly, SQLite is capable of handling locks, and the fault lies
with me. Maybe I've simply missed a nuance somewhere, or maybe my
understanding is fundamentally wrong. I just don't know. I would turn
cartwheels if somebody could show me what I'm doing wrong in my code,
and spoon-feed me the canonical way of doing it the right way. I am
keen to use SQLite, but this locking impass is currently a sticking
point for me.
Many thanks
Peter
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
