Determine if SQLite3 transaction is active

Question

I am running an END TRANSACTION on my database and occasionally I get error #1 that "cannot commit - no transaction is active"

Is there a way to determine if a transaction is active before trying a commit? I have been tracking my "BEGIN TRANSACTIONS" by hand but I feel there is a better way.

I am using the C API

Answer 1

In SQLite, transactions created using BEGIN TRANSACTION ... END TRANSACTION do not nest.

For nested transactions you need to use the SAVEPOINT and RELEASE commands.

See http://www.sqlite.org/lang%5Ftransaction.html

and http://www.sqlite.org/lang%5Fsavepoint.html

Answer 2

That's weird. I thought sqlite was always in a transaction, either explicitly created by you or implicitly created by sqlite:

http://www.sqlite.org/lang_transaction.html

So I suppose the error means that it's not in a transaction that you initiated ... and if that's what you need to know, it seems OK for sqlite to expect you to keep up with it. Not terribly convenient of course, but I guess that's the cost of a simple API. =/

Answer 3

You might want to check this:

http://www.sqlite.org/c3ref/get_autocommit.html

According to the page, if you are in a transaction, sqlite3_get_autocommit() will return 0.