Re: [sqlite] transaction best practices, post-committal headaches
On 6/21/07, James Dennett <[EMAIL PROTECTED]> wrote: > -Original Message- > From: Andrew Finkenstadt [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 21, 2007 2:56 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] transaction best practices, post-committal headaches > > In my sqlite framework I have the concept of a transaction, which uses a > pair of begin transaction (immediate, exclusive, normal) / end transaction > or rollback transaction statements that execute based on C++ object > construction and stack unwinding destruction. The transaction, if it's > marked as .failed() by some sqlite::execution object, will issue the > rollback upon destruction, otherwise it'll issue the "commit" operation of > "end transaction" followed by the execution of some dummy statement. > > It's that dummy statement that I find somewhat annoying, as begin > transaction and end transaction are documented as merely setting an > autocommit flag and it's the execution of some FUTURE statement that > actually causes the commit. What documentation do you see that makes you think that's the case? COMMIT does the commit *and* as a side-effect of ending the transaction restores auto-commit mode, so far as I know. There should be no need to execute a dummy statement. That seems to work in all of my tests. -- James Perhaps I was misreading what this says: "The COMMIT command does not actually perform a commit until all pending SQL commands finish. Thus if two or more SELECT statements are in the middle of processing and a COMMIT is executed, the commit will not actually occur until all SELECT statements finish." So this sounds like as long as all of my statements have been _reset or _finalized by the time the "commit transaction;" statement is executed, then it takes effect immediately?Alternatively, if the commit has been executed while a statement is still pending, will the commit be executed *when* the _reset or _finalize has been called on the sqlite_stmt object? I admit that when I was on my learning curve for SQLITE that I definitely had lifespan issues, and that may have led me to the errant conclusion about when a transaction would actually commit to disk. --andy
RE: [sqlite] transaction best practices, post-committal headaches
> -Original Message- > From: Andrew Finkenstadt [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 21, 2007 2:56 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] transaction best practices, post-committal headaches > > In my sqlite framework I have the concept of a transaction, which uses a > pair of begin transaction (immediate, exclusive, normal) / end transaction > or rollback transaction statements that execute based on C++ object > construction and stack unwinding destruction. The transaction, if it's > marked as .failed() by some sqlite::execution object, will issue the > rollback upon destruction, otherwise it'll issue the "commit" operation of > "end transaction" followed by the execution of some dummy statement. > > It's that dummy statement that I find somewhat annoying, as begin > transaction and end transaction are documented as merely setting an > autocommit flag and it's the execution of some FUTURE statement that > actually causes the commit. What documentation do you see that makes you think that's the case? COMMIT does the commit *and* as a side-effect of ending the transaction restores auto-commit mode, so far as I know. There should be no need to execute a dummy statement. That seems to work in all of my tests. -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] transaction best practices, post-committal headaches
In my sqlite framework I have the concept of a transaction, which uses a
pair of begin transaction (immediate, exclusive, normal) / end transaction
or rollback transaction statements that execute based on C++ object
construction and stack unwinding destruction. The transaction, if it's
marked as .failed() by some sqlite::execution object, will issue the
rollback upon destruction, otherwise it'll issue the "commit" operation of
"end transaction" followed by the execution of some dummy statement.
It's that dummy statement that I find somewhat annoying, as begin
transaction and end transaction are documented as merely setting an
autocommit flag and it's the execution of some FUTURE statement that
actually causes the commit. In the framework I use I do not need to worry
about other sqlite select statements still being in execution, so I
worry-not about the warnings on this page:
http://www.sqlite.org/lang_transaction.html . This is approximately what
executes:
void impl::begin_transaction()
{
int nrows = m_db.get_connection().execDML("begin immediate transaction;");
++nrows; // don't care what its value is
}
void impl::commit_work()
{
int nrows = m_db.get_connection().execDML("commit transaction;"); // turn
auto-commit back on
nrows = m_db.get_connection().execQueryScalar("select count(0) from
dual;"); // and do it. silly SQLITE3
++nrows; // don't care what its value is
}
Is there a recommendation on what statement to execute after committing the
transaction?
--andy
