I can't argue that SQLite drop SHARED lock, because it can damage data. For example schema can be different after transaction on db1.
So all this means that If I am using two separate access to one database with transaction, busy_timeout will always dont work for this situation. I see only one solution - using exclusive transaction. Am i right? -- Alexander Batyrshin aka bash Biomechanical Artificial Sabotage Humanoid On Mon, Apr 6, 2009 at 3:04 PM, Dan <[email protected]> wrote: > > On Apr 6, 2009, at 5:02 AM, Alexander Batyrshin wrote: > >> Hello all, >> I have found that busy_timeout doesn work in this case: >> >> ------- >> %< >> -------------------------------------------------------------------------------- >> #include <stdio.h> >> #include <sqlite3.h> >> >> >> int check_error (int rc, char *zErrMsg) >> { >> if( rc!=SQLITE_OK ){ >> fprintf(stderr, "SQL error: %s\n", zErrMsg); >> sqlite3_free(zErrMsg); >> } >> } >> >> int main(int argc, char **argv){ >> sqlite3 *db, *db2; >> char *zErrMsg = 0; >> int rc; >> >> rc = sqlite3_open("test.db", &db); >> rc = sqlite3_open("test.db", &db2); >> >> printf("db1 start trans\n"); >> rc = sqlite3_exec(db, "BEGIN TRANSACTION" , NULL, NULL, &zErrMsg); >> check_error(rc, zErrMsg); >> >> printf("db1 insert\n"); >> rc = sqlite3_exec(db, "INSERT INTO Blah VALUES ( 1, 'Test1' )" , >> NULL, NULL, &zErrMsg); >> check_error(rc, zErrMsg); >> >> >> sqlite3_busy_timeout(db2, 30000); >> >> printf("db2 start trans\n"); >> rc = sqlite3_exec(db2, "BEGIN TRANSACTION" , NULL, NULL, &zErrMsg); >> check_error(rc, zErrMsg); >> >> /* SQLITE should wait for 3 second before returning error, but it >> doesn't */ >> printf("db2 insert\n"); >> rc = sqlite3_exec(db2, "INSERT INTO Blah VALUES ( 1, 'Test1' )" , >> NULL, NULL, &zErrMsg); >> check_error(rc, zErrMsg); >> >> sqlite3_close(db); >> return 0; >> } >> >> ------- >> %< >> -------------------------------------------------------------------------------- >> >> Most interesting thing that If you try to INSERT in db2 WITHOUT >> transaction busy_timeout() will work correctly. > > Or if you execute any SELECT statement using db2 before the > BEGIN TRANSACTION statement. > > sqlite3_exec() is implemented using the standard prepare_v2()/ > step()/finalize() APIs. While prepare()ing the INSERT statement executed > by db2, SQLite obtains a SHARED (read) lock on the database file in > order > to read the database schema. Because there is a transaction open, > the SHARED lock is not released when sqlite3_prepare() returns. At > this point db2 has a SHARED lock and db1 has RESERVED. When > sqlite3_step() > is called, db2 attempts to upgrade to a RESERVED lock. You then have > the situation described in the 4th paragraph of the documentation here: > > http://www.sqlite.org/c3ref/busy_handler.html > > You could argue that SQLite should drop the SHARED lock on the database > when the call to sqlite3_prepare() on db2 returns in this case. > > Dan. > > > >> >> -- >> Alexander Batyrshin aka bash >> Biomechanical Artificial Sabotage Humanoid >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
