notes

postgresql_transactions_acid.md (19008B)

---

 # PostgreSQL Transactions, Stored Routines, and ACID
 
 PostgreSQL is built around **transactions**: bounded units of work that either
 commit as a whole or leave no durable effect (after rollback). Understanding
 how those transactions interact with **multi-version concurrency control
 (MVCC)**, **locking**, and the **write-ahead log (WAL)** explains both everyday
 behaviour (why two writers sometimes wait or fail) and what **ACID** means in
 practice.
 
 ---
 
 ## Transactions in PostgreSQL
 
 A transaction begins explicitly with `BEGIN` (or `START TRANSACTION`) and ends
 with `COMMIT` or `ROLLBACK`. If you do neither, a single SQL statement still runs
 inside an **implicit** transaction: PostgreSQL wraps each standalone statement
 in its own mini-transaction that commits on success.
 
 Inside one transaction, all statements see a coherent database state according to
 the chosen **isolation level** (see below). Until `COMMIT`, other sessions may or
 may not see your changes, depending on isolation and whether they started before
 or after your writes.
 
 **Savepoints** (`SAVEPOINT`, `ROLLBACK TO SAVEPOINT`) let you partially undo
 work without aborting the whole outer transaction—useful for complex workflows
 with selective retry logic.
 
 ---
 
 ## “Stored procedures” vs functions in PostgreSQL
 
 PostgreSQL’s primary server-side programming mechanism is the **`FUNCTION`**
 (PL/pgSQL, SQL, or other languages). A function **always runs in the caller’s
 transaction**; it cannot `COMMIT` or `ROLLBACK` the surrounding transaction from
 inside the function body.
 
 Since **PostgreSQL 11**, **`PROCEDURE`** exists for routines that **may** issue
 transaction control: a procedure can end its own transaction with `COMMIT` or
 `ROLLBACK` and continue (multi-transaction batching inside one call). That is
 the feature people often mean by “stored procedure” when comparing to other
 databases. For most application code, plain SQL inside a client-managed
 transaction plus optional **functions** is still the common pattern.
 
 Whether logic lives in the app or in the server, the same **MVCC and locking
 rules** apply: routines do not bypass concurrency controls.
 
 ---
 
 ## How PostgreSQL handles many writers (conflicts)
 
 PostgreSQL uses **MVCC** instead of readers blocking writers with a single
 global lock on rows. Each row version carries metadata about which transactions
 created and invalidated it (`xmin` / `xmax` system columns). A query reads **row
 versions visible to its snapshot**—typically versions committed before the
 snapshot’s cutoff—so normal **SELECT** does not take row-level locks that block
 concurrent **UPDATE** on other rows.
 
 When **two transactions try to change the same row**, MVCC does not allow both
 to succeed blindly:
 
 1. **Row-level locks** — `UPDATE`, `DELETE`, and `SELECT … FOR UPDATE` acquire
    locks on the target rows. A second transaction that needs the same row may
    **block** until the first commits or rolls back, then either proceeds or
    detects the row was changed (depending on operation and isolation).
 
 2. **Serialization failures** — Under **`REPEATABLE READ`** or **`SERIALIZABLE`**,
    PostgreSQL can abort a transaction with **`SQLSTATE 40001`**
    (`serialization_failure`) when the guarantee of that isolation level would be
    violated—e.g. a concurrent write pattern that could produce anomalies if both
    committed. The application is expected to **retry** the whole transaction.
 
 3. **Deadlocks** — When transactions wait on each other in a cycle, PostgreSQL
    **detects** the deadlock and **aborts one transaction** (`deadlock_detected`)
    so the others can proceed.
 
 So “multiple users writing the same data at the same time” is handled by a mix
 of **waiting**, **abort with retry**, and **ordering**—not by silently losing
 updates. Which outcome you get depends on SQL patterns (locking reads vs blind
 updates) and isolation level.
 
 The subsections below quote **actual C code** from the upstream PostgreSQL tree
 so you can see how these behaviours are implemented. Excerpts are taken from
 commit [`43fc1dc7527`](https://github.com/postgres/postgres/tree/43fc1dc7527c4213c4d48a96bf98738df0acb71a)
 (PostgreSQL is licensed under [the PostgreSQL Licence](https://www.postgresql.org/about/licence/)).
 
 ### Row-level blocking: `heap_lock_tuple`
 
 When a tuple is already being modified or deleted, `heap_lock_tuple` inspects
 visibility with **`HeapTupleSatisfiesUpdate`**, unlocks the buffer briefly, and
 may **sleep** until competing lockers finish—first acquiring a **heavyweight
 tuple lock** (`LockTuple` via `heap_acquire_tuplock`), then waiting on the
 transaction or MultiXact holding `xmax`:
 
 ```c
 l3:
 	result = HeapTupleSatisfiesUpdate(tuple, cid, *buffer);
 
 	if (result == TM_Invisible)
 	{
 		/*
 		 * This is possible, but only when locking a tuple for ON CONFLICT DO
 		 * SELECT/UPDATE.  We return this value here rather than throwing an
 		 * error in order to give that case the opportunity to throw a more
 		 * specific error.
 		 */
 		result = TM_Invisible;
 		goto out_locked;
 	}
 	else if (result == TM_BeingModified ||
 			 result == TM_Updated ||
 			 result == TM_Deleted)
 	{
 		TransactionId xwait;
 		uint16		infomask;
 		uint16		infomask2;
 		bool		require_sleep;
 		ItemPointerData t_ctid;
 
 		/* must copy state data before unlocking buffer */
 		xwait = HeapTupleHeaderGetRawXmax(tuple->t_data);
 		infomask = tuple->t_data->t_infomask;
 		infomask2 = tuple->t_data->t_infomask2;
 		ItemPointerCopy(&tuple->t_data->t_ctid, &t_ctid);
 
 		LockBuffer(*buffer, BUFFER_LOCK_UNLOCK);
 ```
 
 (The branch continues with multixact checks, tuple-lock acquisition, and sleep
 logic; only the opening is shown here.)
 
 The fragment above is the start of the branch taken when the tuple is already
 being modified, updated, or deleted; the remainder of `heap_lock_tuple` decides
 whether sleeping is required and how to wait.
 
 Later, when `require_sleep` is true, the same function acquires the tuple lock
 and waits on the rival transaction id:
 
 ```c
 		if (!skip_tuple_lock &&
 			!heap_acquire_tuplock(relation, tid, mode, wait_policy,
 								  &have_tuple_lock))
 		{
 			/*
 			 * This can only happen if wait_policy is Skip and the lock
 			 * couldn't be obtained.
 			 */
 			result = TM_WouldBlock;
 			/* recovery code expects to have buffer lock held */
 			LockBuffer(*buffer, BUFFER_LOCK_EXCLUSIVE);
 			goto failed;
 		}
 ```
 
 ```c
 				switch (wait_policy)
 				{
 					case LockWaitBlock:
 						XactLockTableWait(xwait, relation, &tuple->t_self,
 										  XLTW_Lock);
 						break;
 					case LockWaitSkip:
 						if (!ConditionalXactLockTableWait(xwait, false))
 						{
 							result = TM_WouldBlock;
 							/* recovery code expects to have buffer lock held */
 							LockBuffer(*buffer, BUFFER_LOCK_EXCLUSIVE);
 							goto failed;
 						}
 						break;
 					case LockWaitError:
 						if (!ConditionalXactLockTableWait(xwait, log_lock_failures))
 							ereport(ERROR,
 									(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
 									 errmsg("could not obtain lock on row in relation \"%s\"",
 											RelationGetRelationName(relation))));
 						break;
 				}
 ```
 
 (Source: [`src/backend/access/heap/heapam.c`](https://github.com/postgres/postgres/blob/43fc1dc7527c4213c4d48a96bf98738df0acb71a/src/backend/access/heap/heapam.c),
 function `heap_lock_tuple`; this `switch` sits in the branch that waits on a
 single transaction id after copying `xwait` from `xmax`.)
 
 The **heavyweight** tuple lock is wired to `LockTuple` through a small macro
 layer that maps `LockTupleMode` to the lock manager’s `LOCKMODE`:
 
 ```c
 #define LockTupleTuplock(rel, tup, mode) \
 	LockTuple((rel), (tup), tupleLockExtraInfo[mode].hwlock)
 #define UnlockTupleTuplock(rel, tup, mode) \
 	UnlockTuple((rel), (tup), tupleLockExtraInfo[mode].hwlock)
 #define ConditionalLockTupleTuplock(rel, tup, mode, log) \
 	ConditionalLockTuple((rel), (tup), tupleLockExtraInfo[mode].hwlock, (log))
 ```
 
 `heap_acquire_tuplock` is the helper that invokes it when blocking is allowed:
 
 ```c
 static bool
 heap_acquire_tuplock(Relation relation, const ItemPointerData *tid, LockTupleMode mode,
 					 LockWaitPolicy wait_policy, bool *have_tuple_lock)
 {
 	if (*have_tuple_lock)
 		return true;
 
 	switch (wait_policy)
 	{
 		case LockWaitBlock:
 			LockTupleTuplock(relation, tid, mode);
 			break;
 
 		case LockWaitSkip:
 			if (!ConditionalLockTupleTuplock(relation, tid, mode, false))
 				return false;
 			break;
 
 		case LockWaitError:
 			if (!ConditionalLockTupleTuplock(relation, tid, mode, log_lock_failures))
 				ereport(ERROR,
 						(errcode(ERRCODE_LOCK_NOT_AVAILABLE),
 						 errmsg("could not obtain lock on row in relation \"%s\"",
 								RelationGetRelationName(relation))));
 			break;
 	}
 	*have_tuple_lock = true;
 
 	return true;
 }
 ```
 
 (Source: same file, macros near the top of `heapam.c` and `heap_acquire_tuplock`
 below `heap_lock_tuple`.)
 
 ### Deadlock detection: `CheckDeadLock` and `DeadLockCheck`
 
 While a backend sleeps on a lock, a **deadlock timeout** can fire; the waiter
 then locks all lock-hash partitions and runs **`DeadLockCheck`**. If the graph
 contains an unresolvable cycle (`DS_HARD_DEADLOCK`), the waiter is removed from
 the queue so **`ProcSleep`** surfaces an error and the transaction aborts. The
 full handler is **`CheckDeadLock`** in `proc.c`:
 
 ```c
 /*
  * CheckDeadLock
  *
  * We only get to this routine, if DEADLOCK_TIMEOUT fired while waiting for a
  * lock to be released by some other process.  Check if there's a deadlock; if
  * not, just return.  If we have a real deadlock, remove ourselves from the
  * lock's wait queue.
  */
 static DeadLockState
 CheckDeadLock(void)
 {
 	int			i;
 	DeadLockState result;
 
 	/*
 	 * Acquire exclusive lock on the entire shared lock data structures. Must
 	 * grab LWLocks in partition-number order to avoid LWLock deadlock.
 	 *
 	 * Note that the deadlock check interrupt had better not be enabled
 	 * anywhere that this process itself holds lock partition locks, else this
 	 * will wait forever.  Also note that LWLockAcquire creates a critical
 	 * section, so that this routine cannot be interrupted by cancel/die
 	 * interrupts.
 	 */
 	for (i = 0; i < NUM_LOCK_PARTITIONS; i++)
 		LWLockAcquire(LockHashPartitionLockByIndex(i), LW_EXCLUSIVE);
 
 	/*
 	 * Check to see if we've been awoken by anyone in the interim.
 	 *
 	 * If we have, we can return and resume our transaction -- happy day.
 	 * Before we are awoken the process releasing the lock grants it to us so
 	 * we know that we don't have to wait anymore.
 	 *
 	 * We check by looking to see if we've been unlinked from the wait queue.
 	 * This is safe because we hold the lock partition lock.
 	 */
 	if (dlist_node_is_detached(&MyProc->waitLink))
 	{
 		result = DS_NO_DEADLOCK;
 		goto check_done;
 	}
 
 #ifdef LOCK_DEBUG
 	if (Debug_deadlocks)
 		DumpAllLocks();
 #endif
 
 	/* Run the deadlock check */
 	result = DeadLockCheck(MyProc);
 
 	if (result == DS_HARD_DEADLOCK)
 	{
 		/*
 		 * Oops.  We have a deadlock.
 		 *
 		 * Get this process out of wait state. (Note: we could do this more
 		 * efficiently by relying on lockAwaited, but use this coding to
 		 * preserve the flexibility to kill some other transaction than the
 		 * one detecting the deadlock.)
 		 *
 		 * RemoveFromWaitQueue sets MyProc->waitStatus to
 		 * PROC_WAIT_STATUS_ERROR, so ProcSleep will report an error after we
 		 * return.
 		 */
 		Assert(MyProc->waitLock != NULL);
 		RemoveFromWaitQueue(MyProc, LockTagHashCode(&(MyProc->waitLock->tag)));
 
 		/*
 		 * We're done here.  Transaction abort caused by the error that
 		 * ProcSleep will raise will cause any other locks we hold to be
 		 * released, thus allowing other processes to wake up; we don't need
 		 * to do that here.  NOTE: an exception is that releasing locks we
 		 * hold doesn't consider the possibility of waiters that were blocked
 		 * behind us on the lock we just failed to get, and might now be
 		 * wakable because we're not in front of them anymore.  However,
 		 * RemoveFromWaitQueue took care of waking up any such processes.
 		 */
 	}
 
 	/*
 	 * And release locks.  We do this in reverse order for two reasons: (1)
 	 * Anyone else who needs more than one of the locks will be trying to lock
 	 * them in increasing order; we don't want to release the other process
 	 * until it can get all the locks it needs. (2) This avoids O(N^2)
 	 * behavior inside LWLockRelease.
 	 */
 check_done:
 	for (i = NUM_LOCK_PARTITIONS; --i >= 0;)
 		LWLockRelease(LockHashPartitionLockByIndex(i));
 
 	return result;
 }
 ```
 
 `DeadLockCheck` in `deadlock.c` implements the search for cycles and possible
 wait-queue reordering; its header comment states the `DS_HARD_DEADLOCK`
 contract, and the body begins by resetting workspace used by the detector:
 
 ```c
 /*
  * DeadLockCheck -- Checks for deadlocks for a given process
  *
  * This code looks for deadlocks involving the given process.  If any
  * are found, it tries to rearrange lock wait queues to resolve the
  * deadlock.  If resolution is impossible, return DS_HARD_DEADLOCK ---
  * the caller is then expected to abort the given proc's transaction.
  *
  * Caller must already have locked all partitions of the lock tables.
  *
  * On failure, deadlock details are recorded in deadlockDetails[] for
  * subsequent printing by DeadLockReport().  That activity is separate
  * because we don't want to do it while holding all those LWLocks.
  */
 DeadLockState
 DeadLockCheck(PGPROC *proc)
 {
 	/* Initialize to "no constraints" */
 	nCurConstraints = 0;
 	nPossibleConstraints = 0;
 	nWaitOrders = 0;
 
 	/* Initialize to not blocked by an autovacuum worker */
 	blocking_autovacuum_proc = NULL;
 
 	/* Search for deadlocks and possible fixes */
 	if (DeadLockCheckRecurse(proc))
 ```
 
 (Sources: [`src/backend/storage/lmgr/proc.c`](https://github.com/postgres/postgres/blob/43fc1dc7527c4213c4d48a96bf98738df0acb71a/src/backend/storage/lmgr/proc.c)
 and [`src/backend/storage/lmgr/deadlock.c`](https://github.com/postgres/postgres/blob/43fc1dc7527c4213c4d48a96bf98738df0acb71a/src/backend/storage/lmgr/deadlock.c). The `EDGE` / waits-for graph structs at the top of `deadlock.c` are also worth reading alongside [`src/backend/storage/lmgr/README`](https://github.com/postgres/postgres/blob/43fc1dc7527c4213c4d48a96bf98738df0acb71a/src/backend/storage/lmgr/README).)
 
 ### Serializable isolation: `PreCommit_CheckForSerializationFailure`
 
 Under **SSI**, commit-time validation walks **read/write conflict** lists; if a
 **dangerous structure** appears (pivot still uncommitted), PostgreSQL raises
 **`ERRCODE_T_R_SERIALIZATION_FAILURE`** (`SQLSTATE 40001`), matching the
 user-visible `serialization_failure`:
 
 ```c
 void
 PreCommit_CheckForSerializationFailure(void)
 {
 	dlist_iter	near_iter;
 
 	if (MySerializableXact == InvalidSerializableXact)
 		return;
 
 	Assert(IsolationIsSerializable());
 
 	LWLockAcquire(SerializableXactHashLock, LW_EXCLUSIVE);
 
 	/*
 	 * Check if someone else has already decided that we need to die.  Since
 	 * we set our own DOOMED flag when partially releasing, ignore in that
 	 * case.
 	 */
 	if (SxactIsDoomed(MySerializableXact) &&
 		!SxactIsPartiallyReleased(MySerializableXact))
 	{
 		LWLockRelease(SerializableXactHashLock);
 		ereport(ERROR,
 				(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
 				 errmsg("could not serialize access due to read/write dependencies among transactions"),
 				 errdetail_internal("Reason code: Canceled on identification as a pivot, during commit attempt."),
 				 errhint("The transaction might succeed if retried.")));
 	}
 ```
 
 (Source: [`src/backend/storage/lmgr/predicate.c`](https://github.com/postgres/postgres/blob/43fc1dc7527c4213c4d48a96bf98738df0acb71a/src/backend/storage/lmgr/predicate.c).)
 
 ---
 
 ## Isolation levels (what “consistent view” means)
 
 Default isolation is **`READ COMMITTED`**: each statement sees rows committed
 before that **statement** began. That prevents dirty reads but allows **non-repeatable
 reads** and **phantoms** across statements in the same transaction.
 
 **`REPEATABLE READ`** uses a **snapshot** taken at the **first query** in the
 transaction; subsequent reads see the same world. Concurrent commits that would
 change what that snapshot means for writes can trigger **`serialization_failure`**.
 
 **`SERIALIZABLE`** uses **Serializable Snapshot Isolation (SSI)**—tracking
 dependencies between concurrent transactions so that any outcome that could not
 have occurred if transactions ran one-after-another causes an abort. It is the
 strongest guarantee PostgreSQL offers but may force **retries** under contention.
 
 ---
 
 ## ACID “under the hood”
 
 The acronym maps to mechanisms roughly as follows:
 
 ### Atomicity
 
 All changes in a transaction are recorded as **WAL records** before commit is
 allowed (see durability). If the session crashes mid-transaction, recovery
 replays only **committed** work; incomplete transactions are rolled back during
 recovery. Within the server, rollback uses transaction status and MVCC rules so
 uncommitted row versions are invisible after abort.
 
 ### Consistency
 
 PostgreSQL enforces **declared rules**: primary keys, foreign keys, `CHECK`,
 `NOT NULL`, triggers, etc. **Consistency** in the broader business sense (e.g.
 “account totals match ledger”) remains the application’s responsibility;
 transactions bundle changes so those rules can be checked together before commit.
 
 ### Isolation
 
 Implemented through **MVCC snapshots**, **row locks**, **predicate locks /
 SSI bookkeeping** (for serializable), and occasional **blocking**. Isolation is
 not “free”: stronger levels trade convenience for **abort/retry** under
 concurrency.
 
 ### Durability
 
 Committed transactions are made **durable** via the **write-ahead log**: redo
 information is written to WAL and, depending on **`synchronous_commit`** and
 replication settings, flushed to disk (and replicas) before the client is told
 `COMMIT` succeeded. That way a crash after commit can **replay** the log and
 restore committed data.
 
 ---
 
 ## Practical takeaways
 
 - Use explicit transactions when several statements must succeed or fail
   together.
 - Expect **`serialization_failure`** under **`SERIALIZABLE`** or **`REPEATABLE
   READ`** when writes overlap; implement **retry** with backoff.
 - Use **`SELECT … FOR UPDATE`** (or equivalent) when you read a row and then
   update it based on that read, to serialize competing writers on that row.
 - Distinguish **`FUNCTION`** (single outer transaction) from **`PROCEDURE`**
   (optional internal commits) when porting “stored procedure” designs from other
   DBMSs.
 
 ---
 
 ## References
 
 - [PostgreSQL server sources (GitHub)](https://github.com/postgres/postgres)
 - [PostgreSQL Manual — Transaction Isolation](https://www.postgresql.org/docs/current/transaction-iso.html)
 - [PostgreSQL Manual — Routine vacuuming / MVCC](https://www.postgresql.org/docs/current/routine-vacuuming.html)
 - [PostgreSQL Manual — Write-Ahead Logging (WAL)](https://www.postgresql.org/docs/current/wal.html)
 - [PostgreSQL Manual — `CREATE PROCEDURE`](https://www.postgresql.org/docs/current/sql-createprocedure.html)