Search code examples
pythontransactionssqlitecommit

Python/SQLite3: cannot commit - no transaction is active

I'm trying to code a book indexer using Python (traditional, 2.7) and SQLite (3).

The code boils down to this sequence of SQL statements:

'select count(*) from tag_dict' ()
/* [(30,)] */
'select count(*) from file_meta' ()
/* [(63613,)] */
'begin transaction' ()
'select id from archive where name=?' ('158326-158457.zip',)
/* [(20,)] */
'select id from file where name=? and archive=?' ('158328.fb2', 20)
/* [(122707,)] */
'delete from file_meta where file=?' (122707,)
'commit transaction' ()
# error: cannot commit - no transaction is active

The isolation level is 'DEFERRED' ('EXCLUSIVE' is no better).

I've attempted to use connection.commit() instead of cursor.execute('commit') - nothing useful happened.

  • Sure, I've searched stackoverflow and the Net, but the answers found are irrelevant.
  • Autocommit mode is unacceptable for performance reason.
  • I use the only database file at a time.
  • My code runs in single thread.
  • All the SQL execution is being done via single function that ensures that I have no more than only one cursor open at a time.

So, what's wrong with transaction here?

If I use connection.commit() (note: there is no connection.begin method!), then I merely lose my data.

Sure, I've double/triple/d checked file permissions on the database file and its directory.

Solution

  • Well, as it often happens I found the solution just a minutes after posing the question.

    The solution was found here and consists of the only idea:

    Never use BEGIN/COMMIT in non-autocommit mode in Python application - use db.commit() and db.rollback() only!

    It sounds odd, but it works.