Transaction examples — every shape

18 copy-paste transaction patterns — the vast reference for builders.

Version
Latest
v0.1.0 · latest 1 min read
On this page
  1. Basics
  2. Rollbacks and savepoints
  3. Savepoints (protocol matters)
  4. Multi-statement atomicity
  5. DDL in transactions
  6. Isolation snapshots
  7. Conflict handling (app pattern)
  8. What NOT to do

Shared schema: Quickstart. Rules recap: unclosed BEGIN → Conflict; lone COMMIT no-op; COPY/UPDATE…FROM only in autocommit; first lane wins.

Basics#

sqlsource
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#

sqlsource
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 group

Savepoints (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):

sqlsource
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;
Warning

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#

sqlsource
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#

sqlsource
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#

sqlsource
-- 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)#

pythonsource
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")
        continue

What NOT to do#

sqlsource
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 close

Related: Using transactions · MVCC · Behavior contracts

Was this page helpful?