The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Start a transactionBEGIN;View examples
Commit changesCOMMIT;View examples
Discard changesROLLBACK;View examples
Create a savepointSAVEPOINT before_optional_step;View examples
Roll back partwayROLLBACK TO SAVEPOINT before_optional_step;View examples
Release a savepointRELEASE SAVEPOINT before_optional_step;View examples
Use read committedBEGIN ISOLATION LEVEL READ COMMITTED;View examples
Use repeatable readBEGIN ISOLATION LEVEL REPEATABLE READ;View examples
Use serializableBEGIN ISOLATION LEVEL SERIALIZABLE;View examples
Declare read-only workBEGIN READ ONLY;View examples
Limit statement duration locallySET LOCAL statement_timeout = '5s';View examples
Lock selected rowsSELECT * FROM accounts WHERE id = 42 FOR UPDATE;View examples
Use a weaker update lockSELECT * FROM accounts WHERE id = 42 FOR NO KEY UPDATE;View examples
Fail instead of waitingSELECT * FROM jobs WHERE id = 7 FOR UPDATE NOWAIT;View examples
Skip locked workSELECT * FROM jobs WHERE status = 'ready' ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1;View examples
Lock a table explicitlyLOCK TABLE reports IN SHARE MODE;View examples
Lock in stable orderSELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;View examples

A transaction makes related statements one unit of success or failure and supplies a consistent concurrency model. Keep transactions short, lock rows in a consistent order, choose isolation from invariants rather than habit, and design callers to retry failures that PostgreSQL intentionally raises to preserve correctness.

Step by step

Detailed examples

01

Make related changes succeed or fail together

BEGIN starts a block, COMMIT accepts all its changes, and ROLLBACK discards them. After a statement error, PostgreSQL marks the transaction aborted until rollback or rollback to a usable savepoint. Do not leave sessions idle in transactions: snapshots and locks remain open and can impede maintenance and concurrent work.

Transfer value as one unit
BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

COMMIT;
Output
BEGIN
UPDATE 1
UPDATE 1
COMMIT
Abandon an incomplete unit
BEGIN;
UPDATE reservations SET status = 'held' WHERE id = 10;
ROLLBACK;
Output
BEGIN
UPDATE 1
ROLLBACK
Back to quick reference ↑
02

Recover from an optional sub-step

A savepoint creates a subtransaction boundary. ROLLBACK TO undoes later work and clears the error state while preserving earlier changes; RELEASE removes the marker and folds its work into the surrounding transaction. Savepoints do not commit independently, and locks acquired after a savepoint can be released when rolling back to it.

Keep the order when an optional audit step fails
BEGIN;
INSERT INTO orders (id, status) VALUES (1001, 'new');
SAVEPOINT before_audit;

-- If the optional audit insert fails:
ROLLBACK TO SAVEPOINT before_audit;
RELEASE SAVEPOINT before_audit;

COMMIT;
Output
BEGIN
INSERT 0 1
SAVEPOINT
ROLLBACK
RELEASE
COMMIT
Back to quick reference ↑
03

Choose isolation from the invariant being protected

READ COMMITTED gives each statement a fresh snapshot and is PostgreSQL's default. REPEATABLE READ keeps a stable snapshot and can raise serialization failures for conflicting writes. SERIALIZABLE additionally detects dependency patterns that could not occur in a serial schedule. Applications using the stronger levels must retry the complete transaction on SQLSTATE 40001.

Check and update under serializable isolation
BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT COUNT(*) FROM shifts WHERE doctor_id = 7 AND day = DATE '2026-08-13';
INSERT INTO shifts (doctor_id, day) VALUES (7, DATE '2026-08-13');

COMMIT;
Output
BEGIN
 count
-------
 0
INSERT 0 1
COMMIT

Note: A concurrent conflicting transaction may cause COMMIT or an earlier statement to fail with a serialization error; retry the entire unit.

Back to quick reference ↑
04

Declare intent and bound work inside the transaction

READ ONLY prevents most persistent writes and can enable safer reporting behavior, though temporary-table and sequence details have specific rules. SET LOCAL changes a parameter only for the current transaction, making it useful for statement or lock timeouts without leaking settings into a pooled session. Set the mode before substantial work begins.

Bound a read-only report
BEGIN READ ONLY;
SET LOCAL statement_timeout = '5s';

SELECT region, SUM(amount)
FROM orders
GROUP BY region;

COMMIT;
Output
BEGIN
SET
 region | sum
--------+-----
 East   | 120
 West   | 90
COMMIT
Back to quick reference ↑
05

Lock rows immediately before a dependent write

SELECT FOR UPDATE locks returned rows against conflicting updates, deletes, and row locks until transaction end. FOR NO KEY UPDATE is weaker and is automatically used by some updates that do not change key columns. Lock only rows that protect the invariant, keep the transaction short, and remember ordinary snapshot reads are not blocked by row locks.

Withdraw after locking the account
BEGIN;

SELECT balance FROM accounts WHERE id = 42 FOR UPDATE;
UPDATE accounts
SET balance = balance - 25
WHERE id = 42 AND balance >= 25;

COMMIT;
Output
BEGIN
 balance
---------
 100
UPDATE 1
COMMIT
Back to quick reference ↑
06

Choose whether to wait, fail, or skip

The default waits for a conflicting row lock. NOWAIT fails immediately so the caller can report contention or retry later. SKIP LOCKED produces an intentionally inconsistent view and is appropriate for multiple workers claiming queue entries, not for general-purpose reporting. Pair queue selection with a status update in the same short transaction.

Claim one available job
BEGIN;

WITH candidate AS (
  SELECT id
  FROM jobs
  WHERE status = 'ready'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE jobs AS j
SET status = 'running'
FROM candidate AS c
WHERE j.id = c.id
RETURNING j.id;

COMMIT;
Output
BEGIN
 id
----
 17
UPDATE 1
COMMIT
Back to quick reference ↑
07

Use explicit table locks sparingly

PostgreSQL automatically takes table locks required by each command. LOCK TABLE is for workflows needing stronger coordination than ordinary statements provide, and its mode determines which operations conflict. Acquire the strictest required mode first and as early as possible; upgrades and inconsistent acquisition order increase deadlock risk.

Stabilize a table for a coordinated report step
BEGIN;
LOCK TABLE reports IN SHARE MODE;
SELECT COUNT(*) FROM reports;
COMMIT;
Output
BEGIN
LOCK TABLE
 count
-------
 250
COMMIT
Back to quick reference ↑
08

Prevent what you can and retry what concurrency requires

Deadlocks can still occur when transactions acquire resources in different orders; PostgreSQL aborts one participant with SQLSTATE 40P01. Serializable transactions may fail with 40001 even without a deadlock. Lock resources in a stable order, keep side effects outside the retryable database unit, roll back fully, apply bounded backoff, and retry the entire transaction rather than the last statement.

Acquire account locks in stable identifier order
BEGIN;

SELECT id, balance
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

-- Apply related updates after both locks are held.
COMMIT;
Output
BEGIN
 id | balance
----+--------
 1  | 100
 2  | 150
COMMIT
Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupPostgreSQL: Transactionspostgresql.org
  2. PostgreSQL Global Development GroupPostgreSQL: Transaction Isolationpostgresql.org
  3. PostgreSQL Global Development GroupPostgreSQL: Explicit Lockingpostgresql.org
  4. PostgreSQL Global Development GroupPostgreSQL: BEGINpostgresql.org
  5. PostgreSQL Global Development GroupPostgreSQL: SELECT locking clausepostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback