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
