Database Locks

Principles and uses

Control access order when operations change the same row.

Use Concurrent updates and lock waits

Two sessions want to modify one row; a conflicting writer waits until the lock holder's transaction ends.

↔ Drag to explore

Learn more

This PostgreSQL 18 example uses row locks and Read Committed. Conflicting updates or locking reads wait for transaction end. Plain SELECT uses an MVCC snapshot rather than waiting for the row lock; table locks such as DDL locks are separate. Locks provide coordination at the cost of contention.

Deadlock

Principles and uses

Waiting for each other’s locks can block both transactions.

Use Lock ordering and transaction retries

Two transactions will lock rows A and B in different orders; there is no wait cycle yet.

↔ Drag to explore

Learn more

The example acquires two row locks in opposite orders. PostgreSQL resolves a detected deadlock by aborting a transaction; the victim is not fixed. Retry the whole aborted transaction and use a consistent lock acquisition order to prevent cycles.

What Is an Index?

Principles and uses

Organize search keys to reduce data access.

Use Designing indexes for query conditions

Without an index, inspect table rows for matches. This is an eight-row teaching example.

↔ Drag to explore

SELECT * FROM items WHERE id = 14;

Learn more

The teaching model contrasts sequential scanning with B-tree index access for the same predicate. A small table or a query returning most rows may still favor a sequential scan. Indexes cost storage and maintenance on writes, and B-tree is only one index method.

EXPLAIN ANALYZE

Principles and uses

Compare a query’s estimates and measured execution.

Use Investigating slow queries

EXPLAIN shows the planned access path and estimates without executing the query for its result.

↔ Drag to explore

EXPLAIN SELECT * FROM items WHERE id = 14;

Learn more

This PostgreSQL 18 plan uses illustrative numbers, not benchmark results. Cost is an estimate, not milliseconds. ANALYZE executes the statement. Actual rows and times are per-loop averages; read loops too and do not sum inclusive parent and child times. Data, cache, and statistics affect plans.

Transactions

Principles and uses

Commit or undo related changes together.

Use Transfers, orders and related updates

Group the debit and credit into one transaction. Both accounts initially have a committed balance of 100.

↔ Drag to explore

BEGIN;

Learn more

This PostgreSQL 18 Read Committed example transfers between accounts. Intermediate changes are hidden from ordinary reads in another session until COMMIT. ROLLBACK ends failed work. Isolation level and conflicts can still require waiting, aborts, and retries.

ACID

Principles and uses

Describe transaction guarantees through four properties.

Use Understanding guarantees and their limits

ACID names four separate guarantees. Use one transfer example to distinguish them.

↔ Drag to explore

Learn more

Atomicity is all-or-nothing; consistency preserves declared constraints and business invariants; isolation governs concurrent observations; durability preserves committed results. The DB does not infer every business rule, and isolation levels differ. The durability example assumes PostgreSQL permanent tables and synchronous_commit=on.