PostgreSQL Transactions and Concurrency Control

1. What You'll Learn

Understand the four ACID properties and how PostgreSQL implements them
Use BEGIN/COMMIT/ROLLBACK to control transactions
Use SAVEPOINT for partial rollback within a transaction
Understand the differences among PostgreSQL's three isolation levels and how to choose
Master the MVCC multi-version concurrency control mechanism
Use row locks, table locks, and Advisory Locks
Understand deadlock detection and handling
Compare pessimistic-lock vs optimistic-lock strategies
Use two-phase commit (2PC) for distributed transactions

2. The Story

Alice is in charge of an e-commerce platform's order system. During a promotion, a limited-edition item had only 1 unit left in stock, and two users placed orders almost simultaneously:

User A read the stock as 1 and prepared to deduct
User B also read the stock as 1 and prepared to deduct

Without transaction protection, both could place orders successfully, driving stock to -1 and causing oversell. Alice needs PostgreSQL's transaction isolation, MVCC, and row-lock mechanisms to ensure only one user successfully deducts stock while the other is blocked or gets an error—guaranteeing data consistency.

3. Concept: ACID and Transaction Basics

(1) The Four ACID Properties

Property	English	Meaning	PostgreSQL implementation
Atomicity	Atomicity	A transaction is all-or-nothing: fully succeeds or fully rolls back	WAL (Write-Ahead Log)
Consistency	Consistency	The database satisfies its constraints before and after a transaction	Constraints, triggers, type checks
Isolation	Isolation	Concurrent transactions don't interfere with each other	MVCC + locking
Durability	Durability	Committed data is not lost	WAL + fsync

▶ Example: ACID Atomicity—Transfer Either Fully Succeeds or Fully Rolls Back

SQL

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;

TEXT

-- SQL statement executed successfully

(2) Transaction Control Statements

Statement	Purpose	Corresponding SQL
Begin transaction	Explicitly open a transaction	`BEGIN` or `START TRANSACTION`
Commit transaction	Persist all changes	`COMMIT`
Roll back transaction	Undo all changes	`ROLLBACK`

▶ Example: Roll Back a Transaction to Restore Data

SQL

BEGIN;
DELETE FROM orders WHERE order_id = 999;
-- Oops, wrong deletion!
ROLLBACK;
-- Data is restored

TEXT

-- SQL statement executed successfully

(3) Autocommit Mode

PostgreSQL enables Autocommit by default—each SQL statement is automatically committed as its own transaction.

Mode	Behavior	psql setting
Autocommit ON	Each statement auto-commits	Default
Autocommit OFF	Manual COMMIT required	`\set AUTOCOMMIT off`

▶ Example: Disable Autocommit and Commit Manually

BASH

\set AUTOCOMMIT off
DELETE FROM orders WHERE order_status = 'cancelled';
-- Check before committing
SELECT count(*) FROM orders WHERE order_status = 'cancelled';
COMMIT;

TEXT

# command executed successfully

(4) SAVEPOINT Partial Rollback

Set a savepoint inside a transaction so you can roll back only to that savepoint rather than the whole transaction.

▶ Example: Use SAVEPOINT for Partial Rollback

SQL

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;
-- Second update was wrong, rollback to savepoint
ROLLBACK TO SAVEPOINT sp1;
-- First update still active, can commit
COMMIT;

TEXT

-- SQL statement executed successfully

Statement	Purpose
`SAVEPOINT sp_name`	Create a savepoint
`ROLLBACK TO SAVEPOINT sp_name`	Roll back to a savepoint
`RELEASE SAVEPOINT sp_name`	Release a savepoint (later rollbacks can't reference it)

4. Concept: Transaction Isolation Levels

(1) PostgreSQL's Three Isolation Levels

PostgreSQL implements only three isolation levels (READ UNCOMMITTED is mapped to READ COMMITTED):

Isolation level	Dirty read	Non-repeatable read	Phantom read	PG implementation
READ COMMITTED	Impossible	Possible	Possible	Default level; each query sees the latest committed snapshot
REPEATABLE READ	Impossible	Impossible	Possible (PG actually prevents phantoms)	Transaction sees the snapshot at its start
SERIALIZABLE	Impossible	Impossible	Impossible	Strictest; detects serialization conflicts

▶ Example: Set the Isolation Level

SQL

BEGIN ISOLATION LEVEL READ COMMITTED;
-- or
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- or
BEGIN ISOLATION LEVEL SERIALIZABLE;

TEXT

-- SQL statement executed successfully

(2) READ COMMITTED vs REPEATABLE READ in Practice

▶ Example: READ COMMITTED—Two Queries in the Same Transaction Return Different Results

SQL

-- Session 1
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE account_id = 1; -- Returns 1000

-- Session 2 (another connection)
UPDATE accounts SET balance = 800 WHERE account_id = 1;
COMMIT;

-- Back to Session 1
SELECT balance FROM accounts WHERE account_id = 1; -- Returns 800 (sees committed change)
COMMIT;

TEXT

 count 
-------
     5
(1 row)

▶ Example: REPEATABLE READ—Two Queries in the Same Transaction Return Consistent Results

SQL

-- Session 1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE account_id = 1; -- Returns 1000

-- Session 2 (another connection)
UPDATE accounts SET balance = 800 WHERE account_id = 1;
COMMIT;

-- Back to Session 1
SELECT balance FROM accounts WHERE account_id = 1; -- Still returns 1000
COMMIT;

TEXT

 count 
-------
     5
(1 row)

(3) SERIALIZABLE Isolation Level

SERIALIZABLE is the strictest level; PostgreSQL uses Serializable Snapshot Isolation (SSI) to detect serialization conflicts.

▶ Example: SERIALIZABLE Conflict Detection

SQL

-- Session 1
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE account_id = 1;

-- Session 2
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;

-- Back to Session 1
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;
-- ERROR: could not serialize access due to concurrent update

TEXT

 count 
-------
     5
(1 row)

Isolation level choice	Scenario	Recommended level
Most web apps	READ COMMITTED	Default, balances performance and consistency
Reports/audits needing a consistent snapshot	REPEATABLE READ	Same transaction reads stay consistent
Banking/financial core transactions	SERIALIZABLE	Strictest, trades performance for safety

5. Concept: MVCC Multi-Version Concurrency Control

(1) MVCC Core Principle

MVCC (Multi-Version Concurrency Control) is the core of PostgreSQL's concurrency control. Each transaction sees a snapshot of the data at a point in time; reads don't block writes, and writes don't block reads.

Each row version (tuple) contains four hidden fields:

Field	Meaning
`xmin`	The transaction ID that inserted the row
`xmax`	The transaction ID that deleted/updated the row (0 means still valid)
`xmin` visibility	A transaction whose ID < current snapshot can see this row
`xmax` visibility	A transaction whose ID >= current snapshot cannot see this deletion

▶ Example: View a Row's Version Info

SQL

SELECT xmin, xmax, * FROM products WHERE product_id = 1;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) MVCC Read/Write Concurrency Flow

sequenceDiagram
    participant R as Reader(Tx1)
    participant W as Writer(Tx2)
    participant T as Table

    R->>T: SELECT (snapshot at Tx1 start)
    T-->>R: Returns version V1 (xmin=100, xmax=0)
    W->>T: UPDATE (creates new version)
    T-->>T: V1 xmax=200, V2 xmin=200 xmax=0
    R->>T: SELECT again (same snapshot)
    T-->>R: Still returns V1 (Tx2 not committed)
    W->>T: COMMIT
    Note over T: V2 now visible to new transactions
    R->>T: SELECT again (same snapshot)
    T-->>R: Still returns V1 (REPEATABLE READ)

▶ Example: MVCC Reads Don't Block Writes

SQL

-- Session 1: Long-running read
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM orders WHERE order_date = '2025-01-01';
-- This query does NOT block writers

-- Session 2: Concurrent write (no block from Session 1)
UPDATE orders SET order_status = 'shipped' WHERE order_id = 100;
COMMIT;

TEXT

UPDATE 3

(3) The Cost of MVCC: Dead Tuples and VACUUM

An UPDATE under MVCC doesn't modify the original row—it creates a new version. The old row becomes a dead tuple and needs VACUUM cleanup.

Operation	MVCC behavior	Dead tuples
INSERT	Creates a new row	None
DELETE	Marks the old row's xmax	Produces 1 dead tuple
UPDATE	Marks the old row's xmax + creates a new row	Produces 1 dead tuple

▶ Example: View Dead Tuple Count

SQL

SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Manually Trigger VACUUM

SQL

VACUUM orders;              -- Reclaim space, doesn't block reads
VACUUM FULL orders;         -- Full table rewrite, locks table
VACUUM ANALYZE orders;      -- Reclaim + update statistics

TEXT

-- SQL statement executed successfully

VACUUM type	Lock level	Reclaims space	Speed
`VACUUM`	SHARE	Marks reusable, doesn't return disk	Fast
`VACUUM FULL`	ACCESS EXCLUSIVE	Fully returns disk	Slow, locks table
`VACUUM ANALYZE`	SHARE	Same as VACUUM + updates statistics	Fast

6. Concept: Locking

(1) Row-Level Locks

PostgreSQL automatically acquires a row lock when modifying a row; other transactions must wait.

Row lock type	How to acquire	Conflict
FOR UPDATE	`SELECT ... FOR UPDATE`	Exclusive lock, blocks other FOR UPDATE / FOR NO KEY UPDATE
FOR NO KEY UPDATE	`SELECT ... FOR NO KEY UPDATE`	Doesn't block FOR KEY SHARE
FOR SHARE	`SELECT ... FOR SHARE`	Blocks FOR UPDATE / FOR NO KEY UPDATE
FOR KEY SHARE	`SELECT ... FOR KEY SHARE`	Weakest, only blocks FOR UPDATE

▶ Example: SELECT FOR UPDATE Prevents Oversell

SQL

BEGIN;
SELECT stock FROM products WHERE product_id = 1 FOR UPDATE;
-- stock = 1, row locked
UPDATE products SET stock = stock - 1 WHERE product_id = 1;
COMMIT;
-- If another transaction tries FOR UPDATE on same row, it waits

TEXT

UPDATE 3

(2) Table-Level Locks

Lock mode	How to acquire	Conflict
ACCESS SHARE	`SELECT`	Conflicts with ACCESS EXCLUSIVE
ROW SHARE	`SELECT FOR`	Conflicts with EXCLUSIVE / ACCESS EXCLUSIVE
ROW EXCLUSIVE	`UPDATE/DELETE`	Conflicts with SHARE / EXCLUSIVE, etc.
SHARE	`LOCK TABLE ... SHARE`	Conflicts with ROW EXCLUSIVE, etc.
ACCESS EXCLUSIVE	`ALTER TABLE`	Conflicts with all locks

▶ Example: Explicit Table Lock

SQL

LOCK TABLE orders IN ACCESS EXCLUSIVE MODE;
-- Perform critical operations
COMMIT;

TEXT

-- SQL statement executed successfully

(3) Advisory Locks (PostgreSQL-Specific)

Advisory Locks are application-level locks not bound to any table row, suited for distributed coordination.

Function	Feature
`pg_try_advisory_lock(id)`	Non-blocking; returns false immediately on failure
`pg_advisory_lock(id)`	Blocks waiting until acquired
`pg_advisory_unlock(id)`	Release the lock
`pg_advisory_xact_lock(id)`	Auto-released at transaction end

▶ Example: Use an Advisory Lock to Prevent Duplicate Processing

SQL

-- Non-blocking attempt
SELECT pg_try_advisory_lock(12345);
-- Returns true: got the lock, proceed
-- Returns false: another session holds it, skip

-- Transaction-level advisory lock (auto-release on COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(12345);
-- Do work...
COMMIT; -- Lock auto-released

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Advisory Lock for Single-Instance Jobs

SQL

CREATE FUNCTION run_daily_report() RETURNS void AS $$
BEGIN
  IF pg_try_advisory_lock(99999) THEN
    -- Only one session can run this at a time
    INSERT INTO report_log (report_date, status)
    VALUES (CURRENT_DATE, 'running');
    PERFORM pg_sleep(5); -- Simulate work
    UPDATE report_log SET status = 'done' WHERE report_date = CURRENT_DATE;
    PERFORM pg_advisory_unlock(99999);
  ELSE
    RAISE NOTICE 'Report already running in another session';
  END IF;
END;
$$ LANGUAGE plpgsql;

TEXT

INSERT 0 1

7. Concept: Deadlock Detection and Handling

(1) How Deadlocks Arise

Two transactions wait for each other's held locks, forming a circular dependency.

flowchart LR
    A[Tx1: Lock Row A] -->|Wait for Row B| B[Tx2: Lock Row B]
    B -->|Wait for Row A| A
    A -->|Deadlock!| C[PG auto-detects in 1s]

▶ Example: Deadlock Scenario

SQL

-- Session 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; -- Lock row 1

-- Session 2
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 2; -- Lock row 2

-- Session 1 (now waits for row 2)
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- Session 2 (deadlock!)
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;
-- ERROR: deadlock detected

TEXT

-- SQL statement executed successfully

(2) Deadlock Detection and Prevention

Strategy	Description
Auto-detect	PostgreSQL checks once per second by default (`deadlock_timeout`)
Auto-rollback	On detecting a deadlock, automatically rolls back one transaction
Fixed lock order	Always acquire locks in the same order, avoiding circular waits
Short transactions	Reduce the time a transaction holds locks

▶ Example: Prevent Deadlocks with a Fixed Lock Order

SQL

-- Always lock rows in account_id order
BEGIN;
SELECT * FROM accounts WHERE account_id IN (1, 2) ORDER BY account_id FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

TEXT

 count 
-------
     5
(1 row)

8. Concept: Pessimistic Lock vs Optimistic Lock

(1) Comparing the Two Concurrency Strategies

Dimension	Pessimistic lock	Optimistic lock
Idea	Lock first, then modify; block on conflict	Modify first, then check; retry on conflict
Implementation	`SELECT FOR UPDATE`	version column + `WHERE version = old`
Conflict frequency	Suited to high-conflict scenarios	Suited to low-conflict scenarios
Performance	Lock-wait overhead	Retry overhead
Deadlock risk	Yes	No

▶ Example: Pessimistic Lock for Stock Deduction

SQL

BEGIN;
SELECT stock FROM products WHERE product_id = 1 FOR UPDATE;
-- stock = 1, row locked
IF stock > 0 THEN
  UPDATE products SET stock = stock - 1 WHERE product_id = 1;
END IF;
COMMIT;

TEXT

UPDATE 3

▶ Example: Optimistic Lock for Stock Deduction

SQL

-- Add version column
ALTER TABLE products ADD COLUMN version INT DEFAULT 1;

-- Optimistic update
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE product_id = 1 AND version = 5;
-- If affected rows = 0, someone else modified it, need to retry

TEXT

-- SQL statement executed successfully

▶ Example: Optimistic Lock Application-Layer Retry Logic

SQL

CREATE FUNCTION deduct_stock(p_id INT, p_qty INT) RETURNS BOOLEAN AS $$
DECLARE
  v_version INT;
  v_stock INT;
  v_updated INT;
BEGIN
  LOOP
    SELECT stock, version INTO v_stock, v_version
    FROM products WHERE product_id = p_id;

    IF v_stock < p_qty THEN RETURN FALSE; END IF;

    UPDATE products
    SET stock = stock - p_qty, version = version + 1
    WHERE product_id = p_id AND version = v_version;

    GET DIAGNOSTICS v_updated = ROW_COUNT;
    IF v_updated > 0 THEN RETURN TRUE; END IF;
    -- Conflict, retry
  END LOOP;
END;
$$ LANGUAGE plpgsql;

TEXT

 count 
-------
     5
(1 row)

9. Concept: Two-Phase Commit (2PC)

(1) 2PC for Distributed Transactions

When a transaction spans multiple databases or external resources, two-phase commit is needed to guarantee atomicity.

Phase	Operation	Description
PREPARE	`PREPARE TRANSACTION 'tx_id'`	Transaction enters prepared state, written to WAL
COMMIT	`COMMIT PREPARED 'tx_id'`	Phase 2 confirms commit
ROLLBACK	`ROLLBACK PREPARED 'tx_id'`	Phase 2 confirms rollback

▶ Example: Two-Phase Commit Flow

SQL

-- Phase 1: Prepare
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
PREPARE TRANSACTION 'transfer_out';

-- (Coordinator confirms all participants are prepared)

-- Phase 2: Commit
COMMIT PREPARED 'transfer_out';

TEXT

-- SQL statement executed successfully

▶ Example: View Prepared Transactions

SQL

SELECT * FROM pg_prepared_xacts;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

Parameter	Default	Description
`max_prepared_transactions`	0	Must be > 0 to use 2PC
`deadlock_timeout`	1s	Deadlock detection interval
`idle_in_transaction_session_timeout`	0	Transaction idle timeout (milliseconds)

10. In Practice: Complete Inventory-Deduction Transaction for E-Commerce

Alice needs a complete inventory-deduction transaction that handles concurrent orders, prevents oversell, and logs the order.

SQL

-- Step 1: Create tables
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name TEXT NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  price NUMERIC(10,2) NOT NULL,
  version INT NOT NULL DEFAULT 1
);

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  total_price NUMERIC(10,2) NOT NULL,
  order_status TEXT DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT now()
);

INSERT INTO products VALUES
  (1, 'Limited Edition Watch', 1, 299.99, 1),
  (2, 'Wireless Headphones', 50, 89.99, 1);

-- Step 2: Pessimistic lock approach for hot items
CREATE FUNCTION place_order(
  p_customer_id INT, p_product_id INT, p_qty INT
) RETURNS INT AS $$
DECLARE
  v_stock INT;
  v_order_id INT;
BEGIN
  BEGIN
    -- Lock the product row
    SELECT stock INTO v_stock
    FROM products
    WHERE product_id = p_product_id
    FOR UPDATE;

    IF v_stock < p_qty THEN
      RAISE EXCEPTION 'Insufficient stock: % < %', v_stock, p_qty;
    END IF;

    -- Deduct stock
    UPDATE products
    SET stock = stock - p_qty
    WHERE product_id = p_product_id;

    -- Create order
    INSERT INTO orders (customer_id, product_id, quantity, total_price)
    VALUES (p_customer_id, p_product_id, p_qty,
            p_qty * (SELECT price FROM products WHERE product_id = p_product_id))
    RETURNING order_id INTO v_order_id;

    RETURN v_order_id;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE '%', SQLERRM;
      RETURN -1;
  END;
END;
$$ LANGUAGE plpgsql;

-- Step 3: Test concurrent orders
SELECT place_order(101, 1, 1); -- Success, order created
SELECT place_order(102, 1, 1); -- Fails, stock insufficient

-- Step 4: Check results
SELECT product_id, stock FROM products WHERE product_id = 1;
SELECT order_id, customer_id, order_status FROM orders;

❓ FAQ

Q Why doesn't PostgreSQL have a READ UNCOMMITTED isolation level?

A PostgreSQL maps READ UNCOMMITTED to READ COMMITTED, because under the MVCC architecture it's impossible to read uncommitted data (it always reads a snapshot), so dirty reads can't happen in PG.

Q Can REPEATABLE READ prevent phantom reads?

A PostgreSQL's REPEATABLE READ actually can prevent phantom reads, going beyond what the SQL standard requires of that level. In the standard only SERIALIZABLE prevents phantoms, but PG's MVCC snapshot mechanism also blocks phantoms under REPEATABLE READ.

Q Can SAVEPOINTs be nested?

A Yes. SAVEPOINTs support nesting; an inner ROLLBACK TO SAVEPOINT only rolls back to that savepoint, not affecting outer ones. RELEASE SAVEPOINT releases the named savepoint and all savepoints created after it.

Q What's the difference between SELECT FOR UPDATE and a direct UPDATE?

A SELECT FOR UPDATE locks the row first and then decides whether to modify—ideal for "read then write" compound operations; a direct UPDATE does it all in one step. The advantage of SELECT FOR UPDATE is that you can make a business judgment before modifying.

Q When should I use VACUUM FULL?

A Only when a table is heavily bloated and routine VACUUM can't reclaim space. VACUUM FULL needs an ACCESS EXCLUSIVE lock, during which the table is completely unavailable. Routine maintenance should rely on autovacuum.

Q What's the difference between an Advisory Lock and a regular lock?

A An Advisory Lock is application-defined, not bound to a table or row, and isn't automatically released at transaction end (unless you use the xact version). Regular locks are managed automatically by PostgreSQL and bound to specific database objects.

Q When is 2PC used?

A 2PC is mainly used for distributed transactions across databases or systems, requiring a coordinator to commit uniformly. A single-database transaction uses plain BEGIN/COMMIT; 2PC has extra overhead and requires configuring max_prepared_transactions.

📖 Summary

ACID is the four guarantees of a transaction, implemented by PostgreSQL via WAL, MVCC, constraints, and locks
BEGIN/COMMIT/ROLLBACK control transaction boundaries; SAVEPOINT enables partial rollback
PostgreSQL implements three isolation levels: READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE
MVCC lets reads and writes not block each other; each transaction sees a data snapshot; updates create new versions
Dead tuples are cleaned up by VACUUM/autovacuum; high-update scenarios need attention to cleanup strategy
Row lock SELECT FOR UPDATE prevents concurrent modification conflicts; Advisory Lock is for application-level coordination
Deadlocks are auto-detected by PostgreSQL (1 second) and one transaction is rolled back
Pessimistic locks suit high-conflict scenarios; optimistic locks suit low-conflict scenarios
Two-phase commit (2PC) guarantees atomicity for distributed transactions

📝 Exercises

⭐ Write a transaction that transfers 200 USD from account 1 to account 2 in the accounts table, and rolls back if account 1 has insufficient balance.
⭐⭐ Use SAVEPOINT to write a transaction: first insert an order record, then try to update stock; if stock is insufficient, roll back to the savepoint but keep the order (mark its status as 'failed'), then commit the transaction.
⭐⭐⭐ Implement an optimistic-lock version of stock deduction: use a version column, automatically retry on concurrent conflict up to 3 times, and return FALSE if all 3 fail. Also log each retry to a retry_log table.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Transactions and Concurrency Control

1. What You'll Learn

2. The Story

3. Concept: ACID and Transaction Basics

(1) The Four ACID Properties

▶ Example: ACID Atomicity—Transfer Either Fully Succeeds or Fully Rolls Back

(2) Transaction Control Statements

▶ Example: Roll Back a Transaction to Restore Data

(3) Autocommit Mode

▶ Example: Disable Autocommit and Commit Manually

(4) SAVEPOINT Partial Rollback

▶ Example: Use SAVEPOINT for Partial Rollback

4. Concept: Transaction Isolation Levels

(1) PostgreSQL's Three Isolation Levels

▶ Example: Set the Isolation Level

(2) READ COMMITTED vs REPEATABLE READ in Practice

▶ Example: READ COMMITTED—Two Queries in the Same Transaction Return Different Results

▶ Example: REPEATABLE READ—Two Queries in the Same Transaction Return Consistent Results

(3) SERIALIZABLE Isolation Level

▶ Example: SERIALIZABLE Conflict Detection

5. Concept: MVCC Multi-Version Concurrency Control

(1) MVCC Core Principle

▶ Example: View a Row's Version Info

(2) MVCC Read/Write Concurrency Flow

▶ Example: MVCC Reads Don't Block Writes

(3) The Cost of MVCC: Dead Tuples and VACUUM

▶ Example: View Dead Tuple Count

▶ Example: Manually Trigger VACUUM

6. Concept: Locking

(1) Row-Level Locks

▶ Example: SELECT FOR UPDATE Prevents Oversell

(2) Table-Level Locks

▶ Example: Explicit Table Lock

(3) Advisory Locks (PostgreSQL-Specific)

▶ Example: Use an Advisory Lock to Prevent Duplicate Processing

▶ Example: Advisory Lock for Single-Instance Jobs

7. Concept: Deadlock Detection and Handling

(1) How Deadlocks Arise

▶ Example: Deadlock Scenario

(2) Deadlock Detection and Prevention

▶ Example: Prevent Deadlocks with a Fixed Lock Order

8. Concept: Pessimistic Lock vs Optimistic Lock

(1) Comparing the Two Concurrency Strategies

▶ Example: Pessimistic Lock for Stock Deduction

▶ Example: Optimistic Lock for Stock Deduction

▶ Example: Optimistic Lock Application-Layer Retry Logic

9. Concept: Two-Phase Commit (2PC)

(1) 2PC for Distributed Transactions

▶ Example: Two-Phase Commit Flow

▶ Example: View Prepared Transactions

10. In Practice: Complete Inventory-Deduction Transaction for E-Commerce

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好