Search code examples
pythonsqlpostgresqlautocommitpsycopg

What does Postgres do when BEGIN is run on a connection in autocommit mode?

I'm trying to better understand the concept of 'autocommit' when working with a Postgres (psycopg) connection. Let's say I have a fresh connection, set its isolation level to ISOLATION_LEVEL_AUTOCOMMIT, then run this SQL directly, without using the cursor begin/rollback methods (as an exercise; not saying I actually want to do this):

INSERT A
INSERT B
BEGIN
    INSERT C
    INSERT D
ROLLBACK

What happens to INSERTs C & D?

Is autocommit is purely an internal setting in psycopg that affects how it issues BEGINs? In that case, the above SQL is unafected; INSERTs A & B are committed as soon as they're done, while C & D are run in a transaction and rolled back. What isolation level is that transaction run under?

Or is autocommit a real setting on the connection itself? In that case, how does it affect the handling of BEGIN? Is it ignored, or does it override the autocommit setting to actually start a transaction? What isolation level is that transaction run under?

Or am I completely off-target?

Solution

  • Autocommit mode means that each statement implicitly begins and ends the transaction.

    In your case, if autocommit is off:

    • The client will implicitly start the transaction for the first statement
    • The BEGIN will issue a warning saying that the transaction is already started
    • The ROLLBACK will roll back all four statements

    When autocommit is on, only the c and d are rolled back.

    Note that PostgreSQL has no internal AUTOCOMMIT behavior since 8.0: all autocommit features are relied upon the clients.