Sorting out Autocommit
The PostgreSQL server does not have a feature called "autocomit", but the most popular database library for Python, psycopg2 has it enabled by default. I say "enabled", but to disable this behavior we have to turn something on
conn.autocommit = True
The psycopg2 documentation has a warning about the default behavior
Warning: By default, any query execution, including a simple SELECT will start a transaction
Support for Autocommit in MySQL
MySQL does have a feature called autocommit, and it can be enabled globally, or per session.
This means "start a transaction an leave it open after the first statement I run". Hence, this will begin a transaction
This is the inverse of garbage collection! I didn't start a transaction, but it is holding resources and I am responsible to clean up. Furthermore, MySQL/MariaDB never log the fact that the transaction was started
SET GLOBAL log_output = 'FILE'; SET GLOBAL general_log_file = 'my_logs.txt'; SET GLOBAL general_log = 'ON';
$ tail -f /var/lib/mysql/my_logs.txt 190313 14:30:21 3 Query select 1
While psycopg2 claims to follow the Python DBAPI specification. It's behavior is different because MySQL doesn't handle transactions in the same way. In PostgreSQL,
- Schema changes are transactional
- Errors within a transaction are fatal
- The current time is pinned to the transaction start time
None of these are true for MySQL. Instead, all schema changes applied immediately, errors do not stop the evaluation of subsequent statements, and system resources such as the current time keep changing.
Psycopg2 Resource Management
clause in Python
is usually used to obtain and release resources. If
is set to
then psycopg2 does not implement
any behavior for
The following code appears to do transaction management, but does not
with conn: with conn.cursor() as cur: cur.execute('SELECT 1;')
The Psycopg2 docs also assert that with autocommit "all the commands executed will be immediately committed and no rollback is possible."
I don't know why they say this, when transactions within a session are evaluated as expected
cur.execute("BEGIN") cur.execute("...") cur.execute("ABORT")
People will not normally start transactions and leave them open, but the default behavior of autocommit compells the programmer to clean up resources they never allocted! One way to detect this behavior si to add limits to PostgreSQL that will raise an error
SET idle_in_transaction_session_timeout = '15s';
This can be set per session or globally in
Autocommit in SQLite3
The standard SQLite library for Python also has an autocomit feature , but it tries to be samrt by only implicitly starting transactions if you execute a statement that modifies data. Consider the following
import sqlite3 con = sqlite3.connect("test.db") persons = [ ("Hugo", "Boss"), ("Calvin", "Klein") ] con.executemany("INSERT INTO person(firstname, lastname) values (?, ?)", persons) # Select using a CTE for row in con.execute("WITH p AS ( select firstname from person ) SELECT * FROM p"): print row
statement causes an implicit
and the transaction is aborted if the program ends since there is no commit.
This is not what happens! With Python2.7
WITH p AS ...
causes an implicit commit.