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