And in addition to Dan's words: if you change "BEGIN" to "BEGIN IMMEDIATE" your code will work as you expect it to.
Pavel On Thu, May 6, 2010 at 12:41 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > > On May 5, 2010, at 8:32 PM, myomancer wrote: > >> 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. > > 4th paragraph of this page (The presence of a busy...) describes the > condition your program is hitting: > > http://www.sqlite.org/c3ref/busy_handler.html > > > > >> >> 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 > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
