Are transactions in PostgreSQL via `psycopg2` per-cursor or per-connection?

Question

I'm doing some work with PostgreSQL 9.3 using the psycopg2 database API.

I have the DB API set in the minimum isolation level ("autocommit" mode), and am managing my own transactions directly via SQL. Example:

cur = self.conn.cursor()
cur.execute("BEGIN;")
cur.execute("SELECT dbId, downloadPath, fileName, tags FROM {tableName} WHERE dlState=%s".format(tableName=self.tableName), (2, ))
ret = cur.fetchall()
cur.execute("COMMIT;")

Basically, Is the transaction that is started by the cur.execute("BEGIN;") limited to just that cursor, or is it for the whole connection (self.conn.cursor())?

Some of the more complex things I am doing involve multiple separate database operations, that I logically break down into functions. Since this is all in a class which has the connection as a member, it's a lot more convenient to create cursors within each function. However, I'm not sure how creating cursors within a transaction works.

Basically, if transactions are per-connection, I can just create lots of cursors on-the-fly within the transaction. If they're per-cursor, that means I have to pass the cursor around everywhere. Which is it?

The documentation does not touch on this, though the fact that you can call connection.commit() makes me fairly confident the transaction-control is per-connection.

Answer 1

Transactions are per-session, i.e. per-connection.

PostgreSQL doesn't support suspending and resuming transactions, so psycopg2 couldn't make them per-cursor unless it implicitly created new connections behind the scenes.

In practice I don't find psycopg2's cursors particularly useful. They can retain result sets if you're not using incremental fetch from the server, but I don't find them good for much else.

Why manually issue begin and commit though, rather than using the connection methods for them?

Answer 2

From the psycopg2 documentation:

In Psycopg transactions are handled by the connection class. By default, the first time a command is sent to the database (using one of the cursors created by the connection), a new transaction is created. The following database commands will be executed in the context of the same transaction – not only the commands issued by the first cursor, but the ones issued by all the cursors created by the same connection. Should any command fail, the transaction will be aborted and no further command will be executed until a call to the rollback() method.

At the same time, from version 2.4.2, there is the autocommit attribute (emphasis added):

Read/write attribute: if True, no transaction is handled by the driver and every statement sent to the backend has immediate effect; if False a new transaction is started at the first command execution: the methods commit() or rollback() must be manually invoked to terminate the transaction.

The autocommit mode is useful to execute commands requiring to be run outside a transaction, such as CREATE DATABASE or VACUUM.

The default is False (manual commit) as per DBAPI specification.