Managing Transactions

1. Starting Transaction

BEGIN;                              -- or START TRANSACTION
-- ... statements ...
COMMIT;

COMMIT;
COMMIT AND CHAIN;     -- commit and immediately open new TX (same iso/access)

ROLLBACK;
ROLLBACK TO SAVEPOINT sp1;
ROLLBACK AND CHAIN;

Client	Default
psql	Autocommit ON
JDBC	Autocommit ON (set false for batches)
psycopg2/3	Autocommit OFF (must commit)

\set AUTOCOMMIT off            -- psql
-- subsequent statements run in implicit transaction until COMMIT

BEGIN;
SAVEPOINT before_risky;
INSERT INTO ... ;
ROLLBACK TO SAVEPOINT before_risky;     -- undo just this part
COMMIT;

ROLLBACK TO SAVEPOINT sp1;
-- TX still open; can re-attempt or COMMIT

RELEASE SAVEPOINT sp1;

Action	Effect
RELEASE	Discard SP (cannot roll back to it later)
ROLLBACK TO	Undo subsequent work, keep SP

COMMIT AND CHAIN;
-- equivalent to COMMIT; BEGIN; with same isolation/access mode

ROLLBACK AND CHAIN;

Note: CHAIN preserves transaction characteristics (READ ONLY, SERIALIZABLE) for the next transaction.