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
