On this page
Shared schema: Quickstart. Rules recap: unclosed BEGIN → Conflict; lone COMMIT no-op; COPY/UPDATE…FROM only in autocommit; first lane wins.
Basics#
BEGIN; SELECT * FROM users WHERE id = 1; COMMIT; -- read-only txn
BEGIN; INSERT INTO users VALUES (100, 'tmp'); COMMIT; -- single insert
BEGIN; INSERT INTO users VALUES (101,'a'),(102,'b'),(103,'c'); COMMIT; -- multi-row
BEGIN; UPDATE orders SET total = total + 1 WHERE id = 101; COMMIT;
BEGIN; DELETE FROM orders WHERE id = 999; COMMIT; -- zero-row commit ok
BEGIN; INSERT INTO users VALUES (1,'dup') ON CONFLICT (id) DO NOTHING; COMMIT;
BEGIN; INSERT INTO users VALUES (1,'ADA') ON CONFLICT (id) DO UPDATE SET name='ADA'; COMMIT;Rollbacks and savepoints#
BEGIN; INSERT INTO users VALUES (200,'x'); ROLLBACK; -- discard
SELECT * FROM users WHERE id = 200; -- zero rows
COMMIT; -- lone COMMIT: no-op group, not an error
ROLLBACK; -- lone ROLLBACK: no-op groupSavepoints (protocol matters)#
Over ordinary client connections (psql, drivers sending plain statements), savepoints are true partial rollbacks (verified: statements after SAVEPOINT sp1 rewind on ROLLBACK TO SAVEPOINT sp1, earlier statements survive):
BEGIN;
INSERT INTO users VALUES (201, 'keep', '{}');
SAVEPOINT sp1;
INSERT INTO users VALUES (202, 'drop', '{}');
ROLLBACK TO SAVEPOINT sp1; -- rewinds to sp1: 202 gone, 201 kept
COMMIT;At the SQL-parser level, bare SAVEPOINT name is not a statement and ROLLBACK [TO [SAVEPOINT] name] parses to a full rollback — partial rollback is served by the connection layer, not the parser. Drivers that prepare these statements instead of sending them plainly may see rejection or full-rollback behavior. Structure transactions so they can be retried whole (see the e-commerce order transaction).
Multi-statement atomicity#
BEGIN;
-- transfer funds pattern
UPDATE accounts SET bal = bal - 100 WHERE id = 1;
UPDATE accounts SET bal = bal + 100 WHERE id = 2;
INSERT INTO ledger VALUES (1, 1, 2, 100, now());
COMMIT;
BEGIN; -- order + lines
INSERT INTO orders VALUES (500, 1, 99.98, now());
INSERT INTO order_lines VALUES (500, 1), (500, 2);
COMMIT;DDL in transactions#
BEGIN; CREATE TABLE t2 (id BIGINT PRIMARY KEY, v TEXT); INSERT INTO t2 VALUES (1,'a'); COMMIT;
BEGIN; CREATE VIEW v_active AS SELECT * FROM users WHERE id > 0; COMMIT;Isolation snapshots#
-- session A: BEGIN; SELECT count(*) FROM orders; (watermark W)
-- session B: INSERT INTO orders ...; COMMIT; (commit_ts W+1)
-- session A: SELECT count(*) FROM orders; (same snapshot: same count)
-- session A: COMMIT; SELECT count(*) FROM orders; (new snapshot: sees B)Conflict handling (app pattern)#
for attempt in range(3):
try:
cur.execute("BEGIN")
cur.execute("UPDATE accounts SET bal = bal - 100 WHERE id = 1")
cur.execute("COMMIT")
break
except ConflictError:
cur.execute("ROLLBACK")
continueWhat NOT to do#
BEGIN; COPY orders FROM STDIN; COMMIT; -- Unsupported in-txn: run COPY in autocommit
BEGIN; UPDATE users AS u SET name='x' FROM orders o WHERE o.user_id=u.id; COMMIT;
-- Unsupported: autocommit only
BEGIN; INSERT INTO users VALUES (1,'a'); -- missing COMMIT: Conflict on closeRelated: Using transactions · MVCC · Behavior contracts