I'm working an intermediary library in Python that turns a fair number of normal databases and database-like stores into simple key-value stores. There's a long story of why, but it does it have an actual use case outside of "because I can" (though, the symlink backend is pure, unadulterated "because I can").
For the SQLite store I was running into transaction issues here and there. At seemingly-random times data wouldn't save or I'd get an error saying a transaction hadn't been started when I tried to commit. So, I did some research.
By automatic transactions here I am not referring to SQLite's built-in behavior of wrapping data-changing operations in transactions. Rather, I am speaking of the Python module's special behavior where it actually executes BEGIN and COMMIT statements on your behalf. A transaction is automatically started (ie: a BEGIN is executed) when all of the following conditions are met:
The BEGIN statement will be concatenated with whatever value is in isolation_level. For example, if isolation_level was equal to DEFERRED, the statement executed when the above conditions are met is BEGIN DEFERRED.
- The isolation_level is not None. 
- The Connection object thinks a transaction has not already been started.
- An UPDATE, DELETE, INSERT, or REPLACE statement is being executed. 
Please remember to always call commit to save the changes. If you close the connection using close or the connection to the file is lost (maybe the program finishes unexpectedly), not committed changes will be lost.
If you want autocommit mode, then set isolation_level to None. Otherwise leave it at its default, which will result in a plain “BEGIN” statement, or set it to one of SQLite’s supported isolation levels: “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE”.
The lesson learned here comes down to these points:
- Python's sqlite3 module conforms to the broken Python PEP for database access which mandates that there's always a transaction in play and your only control over it is to commit or roll back.
- DB modules are supposed to keep track of the current transaction state internally and not offer a
- The value of
connection.isolation_levelcan be set to
Noneto turn off all this or to
EXCLUSIVEto turn it on. The text in
isolation_levelis sent with the
BEGINstatement to SQLite, so all of those carry SQLite's meaning:
DEFERRED- Get a shared lock on the first statement. Get an exclusive lock only when a DML statement is used. (Default)
IMMEDIATE- Get a write lock immediately. Others can read, but writes will be blocked (timeout applies).
EXCLUSIVE- Get an exclusive lock immediately. Others cannot even read.
- You can turn this behavior off, but then you have to fall back to 100% manual transaction management, and in many cases this can be done very wrong and cause random slowdowns or data loss (if you aren't well-versed in transactional databases).
- Due to how sqlite3 handles threaded and multi-process access, there's a timeout on getting the lock to start a DML statement, and the default is five seconds. This can lead to some interesting situations.
So, in short: abide by the PEP gods and presume a messed-up transaction environment that you can only control with
rollback() or turn it all off and wing it.
In the end, I chose to manually commit at key points in order to both ensure the data was written but also for the side effect of starting a new transaction.
That, folks, is when you know you've made a broken API. Side effects should never be the goal of a line of code, but due to the API design they must be here.