Python's sqlite3 package tries to take care of the tricky business of properly executing BEGIN and COMMIT statements. The rules of when and how this automatic transactioning is done has confounded a number of developers, many of whom have jumped ship and chosen to use the aspw package or similar.

For one reason or another I needed to use the standard libary's package for a project, so I jumped into CPython's internals to take a look at exactly how it behaves (I was specifically looking at Python 2.7's most recent code). The results are mostly ordinary with some occasional gotchas.

## Rules for Automatic Transactions

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:

1. The isolation_level is not None. ^{[^1]}
2. The Connection object thinks a transaction has not already been started.
3. An UPDATE, DELETE, INSERT, or REPLACE statement is being executed. ^{[^2]}

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.

A transaction is automatically ended (ie: a COMMIT is executed) when all of the following conditions are met:

1. The isolation_level is not None. ^{[^1]}
2. The Connection object thinks a transaction has already been started.
3. Any statement that is not an UPDATE, DELETE, INSERT, REPLACE, or SELECT is being executed. ^{[^2]}

The sqlite3.Connection.commit() function will also send a COMMIT statement as long as the Connection object thinks a transaction has already been started.

## How the Connection Object Detects Transactions

When the module checks to see if a transaction is in progress, it inspects the Connection's inTransaction data member (which is not available from Python). This is fine and well if you're only using automatic transactions, but the module can get confused if you ever execute a BEGIN statement yourself: If the version of SQLite CPython was linked with is older than 3.2.2, ^{[^3]} and you've started a transaction manually by executing a BEGIN statement yourself, the inTransaction data member may have an incorrect value.

As a rule of thumb, I would advise never executing a BEGIN statement manually unless you never use the sqlite3.Connection.commit() function and you've set the isolation_level to None.