Re: [sqlite] Understanding TRANSACTION's

[Sam Carleton]

Please ignore this post, I just followed up with another post with a
lot more info, I am still having the problem, though.

Sam

On Sun, Mar 7, 2010 at 10:27 AM, Sam Carleton
 wrote:
> My use of SQLite is within a Apache module and Axis2/C web services.  At a
> minimum, every request invokes a unique call to sqlite3_open_v2, most of the
> time there are multiple calls to sqlite3_open_v2, one for each specific
> task.  An example would be the module part, which sends configuration to the
> web servers calls sqlite3_open_v2 in readonly to set things up, then it
> closes the connection.  Later the web service will open up the database in
> either read/write or read, depending on what it is doing.
> So far all the inserts/updates have not needed to be wrapped in a
> transaction.  I have finally run into a case where a set of updates do need
> to be wrapped in a tranaction, and things are not working correctly.  Here
> is what I am doing:
> 1: sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
> 2: Calling a function that prepare and execute: UPDATE Customer SET
> IsInSlideShow = 0 WHERE CustomerId <> ?1
> 3: Calling same method that prepare and execute: UPDATE Customer SET
> IsInSlideShow = 1 WHERE CustomerId = ?1
> 4a: if all is successful: sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL,
> NULL);
> 4b: if something fails: sqlite3_exec(db, "ROLLBACK TRANSACTION", NULL, NULL,
> NULL);
> This seems to work fine.  The problem comes in the next web service call
> that tries to update the database.  The call to sqlite3_step() always
> returns SQLITE_BUSY when called right after the above web service, but runs
> fine when called before the above web service.  I am guessing that I am
> doing something wrong in the above transaction.  Any thoughts?
> Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Understanding TRANSACTION's

[Sam Carleton]

My use of SQLite is within a Apache module and Axis2/C web services.  At a
minimum, every request invokes a unique call to sqlite3_open_v2, most of the
time there are multiple calls to sqlite3_open_v2, one for each specific
task.  An example would be the module part, which sends configuration to the
web servers calls sqlite3_open_v2 in readonly to set things up, then it
closes the connection.  Later the web service will open up the database in
either read/write or read, depending on what it is doing.

So far all the inserts/updates have not needed to be wrapped in a
transaction.  I have finally run into a case where a set of updates do need
to be wrapped in a tranaction, and things are not working correctly.  Here
is what I am doing:

1: sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL);
2: Calling a function that prepare and execute: UPDATE Customer SET
IsInSlideShow = 0 WHERE CustomerId <> ?1
3: Calling same method that prepare and execute: UPDATE Customer SET
IsInSlideShow = 1 WHERE CustomerId = ?1

4a: if all is successful: sqlite3_exec(db, "COMMIT TRANSACTION", NULL, NULL,
NULL);
4b: if something fails: sqlite3_exec(db, "ROLLBACK TRANSACTION", NULL, NULL,
NULL);

This seems to work fine.  The problem comes in the next web service call
that tries to update the database.  The call to sqlite3_step() always
returns SQLITE_BUSY when called right after the above web service, but runs
fine when called before the above web service.  I am guessing that I am
doing something wrong in the above transaction.  Any thoughts?

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users