PostgreSQL Concurrency Lab

Observe MVCC, isolation levels, locks, and deadlocks in PostgreSQL.

Introduction

30 Second Summary

Think about two people trying to withdraw from the same bank account at the exact same moment. Without careful coordination, the bank could accidentally give away more money than exists.

In this project, you will build a concurrency test bench using a banking scenario in PostgreSQL. You'll open two side-by-side terminal sessions and deliberately create concurrency problems, then observe how PostgreSQL handles them and how to fix them.

What You'll Build

A hands-on lab environment where you run concurrent transactions against a shared accounts table, triggering and observing every major concurrency scenario PostgreSQL can produce.

By the end of this project, you'll have:

  • A concurrency_lab database with an accounts table seeded with test data for running experiments.
  • Hands-on experience with MVCC visibility, isolation level differences, and serialization failures.
  • A reusable lock monitoring query for diagnosing contention in production.
  • Secret Mission: Implement application-level coordination using advisory locks and observe them in pg_locks.

Are there any prerequisites?

You should have solid SQL knowledge (DDL, DML, SELECT) and a basic understanding of what a transaction is. You also need PostgreSQL installed with access to the psql command-line tool.

Before We Start

Before We Start...

Set Up the Lab Database

Every concurrency experiment needs at least two database sessions running at the same time. You cannot observe locking, blocking, or snapshot differences with just one connection.

In this step, you will create a dedicated database, populate it with a simple banking table, and open two independent psql sessions. These two sessions will serve as your test bench for the rest of the lab.

In this step, get ready to:
  • Create the concurrency_lab database.
  • Create and populate the accounts table.
  • Open two side-by-side psql sessions connected to the same database.
Create the lab database and connect

You need a clean database dedicated to these experiments. Creating a separate database keeps your concurrency tests isolated from any existing data.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or Command Prompt (Windows) and press Enter to open it.
  • Create the lab database by running this command:
psql -U postgres -c "CREATE DATABASE concurrency_lab;"

What does this command do?

The -U postgres flag connects as the postgres superuser. The -c flag runs a single SQL statement and exits.

You should see CREATE DATABASE printed in your terminal, confirming the database was created.

Seeing a connection error?

If you see a "connection refused" or authentication error, make sure your PostgreSQL server is running. On Windows, check that the PostgreSQL service is started in Services. On macOS, try brew services start postgresql if you installed via Homebrew.

If you get a password prompt, enter the password you set during PostgreSQL installation.

help me troubleshoot my psql connection

  • Connect to the new database by running:
psql -U postgres -d concurrency_lab
  • Your terminal prompt changes to concurrency_lab=#. This confirms you are connected to the lab database.

What is this prompt telling me?

The concurrency_lab=# prompt shows the database name you are connected to. The =# suffix means you are connected as a superuser. This session is now "Session A" for the rest of the lab.

Create the accounts table and seed data

The banking scenario uses a simple accounts table with an owner name and a balance. A check constraint prevents balances from going negative, which mimics real-world banking rules.

  • Create the accounts table by running this SQL in your Session A prompt:
-- Create a table to track bank account balances
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    owner TEXT NOT NULL,
    balance NUMERIC(10,2) NOT NULL CHECK (balance >= 0)
);

What does this table structure do?

  • SERIAL PRIMARY KEY auto-generates a unique ID for each row.
  • NUMERIC(10,2) stores currency with exactly two decimal places.
  • CHECK (balance >= 0) enforces that no account can have a negative balance. PostgreSQL will reject any transaction that tries to break this rule.
  • Verify the table was created. You should see CREATE TABLE in the output.

Seeing a syntax error?

Make sure you copied the entire statement including the closing );. In psql, a statement is only executed when it encounters a semicolon. If your prompt shows concurrency_lab(# instead of concurrency_lab=#, the command is still waiting for more input.

help me fix my CREATE TABLE statement

  • Seed the table with three test accounts by running:
-- Seed three accounts for concurrency experiments
INSERT INTO accounts (owner, balance) VALUES
    ('Alice', 1000.00),
    ('Bob', 1000.00),
    ('Charlie', 500.00);

Why these specific balances?

Alice and Bob start with identical balances (1000.00) so you can easily spot unexpected changes during transfer experiments. Charlie has a lower balance (500.00) to test the check constraint under concurrent withdrawals.

  • You should see INSERT 0 3 in the output, confirming three rows were added.
  • Confirm the data by running:
SELECT * FROM accounts;
  • You should see Alice with 1000.00, Bob with 1000.00, and Charlie with 500.00.
  • Save these commands as a reusable setup file. Open any text editor, paste the following content, and save the file as setup.sql on your Desktop:
-- Run this to create and seed the lab database.
-- Connect to postgres first: psql -U postgres

CREATE DATABASE concurrency_lab;

\c concurrency_lab

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    owner TEXT NOT NULL,
    balance NUMERIC(10,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (owner, balance) VALUES
    ('Alice', 1000.00),
    ('Bob', 1000.00),
    ('Charlie', 500.00);

What is setup.sql for?

This file lets you recreate the lab from scratch at any time by running psql -U postgres -f setup.sql. You will not need it for the rest of this lab, but it is useful if you want to reset everything later.

✔️ Awesome, I've got everything!

Great. Make sure you have saved setup.sql and that your Session A prompt still shows concurrency_lab=#.

ⓧ I'd like to double check the full code

Here is the complete setup.sql file. Make sure yours matches exactly:

-- Run this to create and seed the lab database.
-- Connect to postgres first: psql -U postgres

CREATE DATABASE concurrency_lab;

\c concurrency_lab

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    owner TEXT NOT NULL,
    balance NUMERIC(10,2) NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (owner, balance) VALUES
    ('Alice', 1000.00),
    ('Bob', 1000.00),
    ('Charlie', 500.00);
Open a second session and verify

Concurrency means multiple connections interacting with the same data at the same time. You need a second, independent psql session to simulate this.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or Command Prompt (Windows) and press Enter to open a new window.
  • Connect this second window to the same database by running:
psql -U postgres -d concurrency_lab
  • You should see the concurrency_lab=# prompt. This is now "Session B."
  • Verify both sessions can see the data. Run the following in each window:
SELECT * FROM accounts;
  • Both sessions should display the same three rows: Alice=1000.00, Bob=1000.00, Charlie=500.00.

Why two separate windows?

Each psql window is a completely independent database connection with its own transaction state. When Session A starts a transaction and modifies data, Session B will not necessarily see those changes. This is the foundation of everything you will observe in the lab.

Session B not connecting?

Make sure you typed the database name exactly as concurrency_lab (with an underscore, not a hyphen). If you see "database does not exist," go back to Session A and confirm the database was created by running \l to list all databases.

help me connect my second psql session to concurrency_lab

Your lab environment is ready. You have two independent sessions pointing at the same data. Next up, you will use these sessions to observe how MVCC and Read Committed isolation behave when two transactions overlap.

Observe MVCC and Read Committed Behavior

PostgreSQL uses MVCC (Multi-Version Concurrency Control) to let multiple transactions run at the same time without stepping on each other.

In this step, you will test two fundamental concurrency scenarios: a dirty read attempt and a non-repeatable read. You will prove that PostgreSQL physically cannot produce dirty reads, then observe how Read Committed isolation still allows non-repeatable reads.

In this step, get ready to:
  • Attempt a dirty read and confirm PostgreSQL prevents it even at READ UNCOMMITTED.
  • Trigger a non-repeatable read under Read Committed isolation.
  • Create a reusable reset script to restore your test data between experiments.
Attempt a dirty read to prove PostgreSQL prevents it

A dirty read happens when one transaction sees uncommitted changes from another. Most databases allow this at the lowest isolation level. PostgreSQL does not.

  • In Session A, begin a transaction and update Alice's balance by running:
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE owner = 'Alice';
SELECT * FROM accounts WHERE owner = 'Alice';

What does this code do?

BEGIN starts a transaction block. The UPDATE subtracts 200 from Alice's balance, and the SELECT confirms the change within this transaction.

Because you have not run COMMIT yet, this change is only visible inside Session A.

You should see Alice's balance as 800.00 in Session A's output.

  • Now switch to Session B. Explicitly request the lowest isolation level and try to read Alice's uncommitted change by running:
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM accounts WHERE owner = 'Alice';
COMMIT;

What should I see?

Session B shows Alice's balance as 1000.00, NOT 800.00. Even though you explicitly set READ UNCOMMITTED, PostgreSQL silently treats it as READ COMMITTED.

This is because MVCC makes dirty reads physically impossible in PostgreSQL. The uncommitted row version is simply never exposed to other transactions. This is a key exam fact.

Seeing Alice's balance as 800.00 in Session B?

If Session B shows 800.00, you may have accidentally run the SELECT in Session A's window instead of Session B's window. Check your window titles to confirm which is which.

help me figure out why my dirty read experiment is showing unexpected results

  • Back in Session A, commit the transaction by running:
COMMIT;

Alice's balance is now permanently 800.00 in the database.

Demonstrate a non-repeatable read under Read Committed

Under Read Committed isolation, each statement within a transaction gets a fresh snapshot of committed data. This means you can read the same row twice and get different values if another transaction commits a change in between.

  • In Session A, begin a new transaction and read Alice's balance by running:
BEGIN;
SELECT balance FROM accounts WHERE owner = 'Alice';

You should see 800.00 (the committed value from the previous experiment).

  • In Session B (no explicit transaction needed), update Alice's balance by running:
UPDATE accounts SET balance = balance + 200 WHERE owner = 'Alice';

Session B's update commits immediately (implicit single-statement transaction). Alice's balance is now 1000.00 in the database.

  • Back in Session A, read the same row again by running:
SELECT balance FROM accounts WHERE owner = 'Alice';

What should I see?

Session A now sees 1000.00. You read the same row twice in the same transaction and got different values (800.00 then 1000.00).

This is a non-repeatable read. It happens under Read Committed because each statement gets a fresh snapshot that includes changes committed since the last statement ran.

  • Commit in Session A to close the transaction:
COMMIT;

Seeing the same value both times?

Make sure you ran the UPDATE in Session B after Session A's first SELECT but before Session A's second SELECT. Timing matters here.

Also confirm you did NOT wrap Session B's UPDATE in a BEGIN block without committing. Without an explicit COMMIT, the change stays uncommitted and Session A cannot see it.

help me troubleshoot why my non-repeatable read experiment shows the same value both times

Reset the data for the next experiment

Before moving on, you need a clean slate. You will also save the reset commands as a reusable script so you can quickly restore your data between future experiments.

  • In either session, reset the balances by running:
UPDATE accounts SET balance = 1000.00 WHERE owner IN ('Alice', 'Bob');
UPDATE accounts SET balance = 500.00 WHERE owner = 'Charlie';
  • Verify in both sessions by running:
SELECT * FROM accounts;

Both sessions should show Alice=1000.00, Bob=1000.00, Charlie=500.00.

Balances not matching?

If you still see 800.00 for Alice, check that you committed all open transactions. Run COMMIT; in both sessions before running the reset.

help me reset my accounts table to the correct starting balances

You will run these reset commands several more times throughout the lab. Saving them as a file makes this faster.

  • Create a file called reset.sql in the same folder where you have setup.sql. Paste the following content:
-- Run this between experiments to reset account balances.
DELETE FROM accounts WHERE owner NOT IN ('Alice', 'Bob', 'Charlie');
UPDATE accounts SET balance = 1000.00 WHERE owner IN ('Alice', 'Bob');
UPDATE accounts SET balance = 500.00 WHERE owner = 'Charlie';

What does this script do?

  • The DELETE removes any extra rows you might insert during later experiments (like Dave or Eve).
  • The two UPDATE statements restore the original balances for Alice, Bob, and Charlie.
  • Save reset.sql.
  • Confirm the file exists by running ls in the folder where you saved it. You should see both setup.sql and reset.sql listed.

✔️ Awesome, I've got everything!

Great. Make sure you have saved reset.sql and that your accounts table shows the original balances.

ⓧ I'd like to double check the full code

-- Run this between experiments to reset account balances.
DELETE FROM accounts WHERE owner NOT IN ('Alice', 'Bob', 'Charlie');
UPDATE accounts SET balance = 1000.00 WHERE owner IN ('Alice', 'Bob');
UPDATE accounts SET balance = 500.00 WHERE owner = 'Charlie';

Your data is clean and you have a reusable reset script ready. Next up, you will escalate to Repeatable Read and Serializable isolation levels to see how PostgreSQL prevents the non-repeatable read you just observed.

Explore Repeatable Read and Serializable Isolation

In Step 2, you proved that Read Committed allows non-repeatable reads. A transaction could read the same row twice and get different values because each statement sees a fresh snapshot.

Now you will explore two stricter isolation levels that prevent this. Repeatable Read freezes the snapshot at the start of the transaction, and Serializable goes further by detecting anomalies that would violate a serial execution order.

In this step, get ready to:
  • Prove that Repeatable Read prevents non-repeatable reads and blocks lost updates.
  • Trigger a serialization failure under Serializable isolation when phantom rows appear.
  • Reset the data for the next experiment.
Prove that Repeatable Read prevents non-repeatable reads

Under Repeatable Read, PostgreSQL takes a single snapshot at the start of the transaction. Every query within that transaction sees the same data, regardless of what other sessions commit in the meantime.

  • In Session A, begin a Repeatable Read transaction and read Alice's balance:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE owner = 'Alice';

What does this do?

The BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ syntax starts a new transaction at the Repeatable Read isolation level. The snapshot is locked in at this moment.

You should see Alice's balance as 1000.00.

  • Switch to Session B and update Alice's balance:
UPDATE accounts SET balance = balance - 100 WHERE owner = 'Alice';
  • Switch back to Session A and read Alice's balance again:
SELECT balance FROM accounts WHERE owner = 'Alice';

Session A still sees 1000.00. The non-repeatable read that happened in Step 2 is now prevented because Repeatable Read uses that single frozen snapshot.

  • Still in Session A, try to update the same row that Session B already modified:
UPDATE accounts SET balance = balance + 50 WHERE owner = 'Alice';

PostgreSQL raises an error instead of applying your update.

Why does this error happen?

You will see: ERROR: could not serialize access due to concurrent update. This is how Repeatable Read prevents lost updates.

Session B already committed a change to Alice's row after Session A's snapshot was taken. If PostgreSQL allowed your update, it would silently overwrite Session B's work. Instead, it aborts your transaction and asks you to retry.

  • Roll back the failed transaction in Session A:
ROLLBACK;

Not seeing the error?

Make sure you ran the UPDATE in Session B before attempting the UPDATE in Session A. The conflict only triggers when another transaction has already committed a change to the same row.

If Session A's transaction was already ended (by a previous COMMIT or ROLLBACK), start the experiment again from the BEGIN TRANSACTION statement.

Help me troubleshoot why I'm not seeing the serialization error under Repeatable Read.

Demonstrate Serializable isolation and phantom protection

Serializable isolation is the strictest level. PostgreSQL uses predicate locks to detect when concurrent transactions would produce results impossible under any serial execution order.

  • In Session A, begin a Serializable transaction and read the total balance across all accounts:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT sum(balance) FROM accounts;
  • In Session B, begin another Serializable transaction and read the same total:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT sum(balance) FROM accounts;

Why do both sessions read the sum?

Both sessions now hold a predicate lock on the entire accounts table because they read an aggregate of all rows. This sets up the dependency that PostgreSQL needs to detect an anomaly.

  • In Session A, insert a new row and commit:
INSERT INTO accounts (owner, balance) VALUES ('Dave', 200.00);
COMMIT;
  • In Session B, also insert a new row and attempt to commit:
INSERT INTO accounts (owner, balance) VALUES ('Eve', 300.00);
COMMIT;

One of these commits will fail. The other will succeed.

What is the error you see?

The failed session raises: ERROR: could not serialize access due to read/write dependencies among transactions. PostgreSQL detected that running these two transactions concurrently produces a result that no serial ordering could produce.

Both sessions read the sum of all balances, then each inserted a "phantom" row the other didn't see. If they ran serially, the second transaction would have included the first's insert in its sum. Since they ran concurrently, that didn't happen, so PostgreSQL aborts one to protect correctness.

  • If the failed session left you in an aborted transaction state, roll it back:
ROLLBACK;

Neither session raised an error?

The serialization failure depends on PostgreSQL's predicate lock tracking detecting the read/write dependency. Make sure both sessions ran SELECT sum(balance) FROM accounts before either committed.

If both commits succeeded, retry the experiment. You can also try having both sessions UPDATE a row based on the sum result instead of inserting, which creates a more direct conflict.

Help me understand why my Serializable experiment didn't produce a serialization failure.

Clean up for the next experiment

The Serializable experiment may have inserted Dave and Eve rows. Remove them and reset all balances to the starting state.

  • Run this cleanup in either session:
DELETE FROM accounts WHERE owner IN ('Dave', 'Eve');
UPDATE accounts SET balance = 1000.00 WHERE owner IN ('Alice', 'Bob');
UPDATE accounts SET balance = 500.00 WHERE owner = 'Charlie';
  • Verify the reset by running this in both sessions:
SELECT * FROM accounts;

You should see three rows: Alice=1000.00, Bob=1000.00, Charlie=500.00.

Seeing Dave or Eve still in the table?

If one of the Serializable commits succeeded, that row exists in the table. The DELETE statement above handles both. If you still see extra rows, check that you are not inside an uncommitted transaction by running COMMIT; first, then re-run the cleanup.

Help me reset my accounts table to the original three rows.

You have now seen how Repeatable Read and Serializable protect against progressively more concurrency anomalies. Next, you will explore row-level locks and learn how to monitor lock contention with pg_locks.

Use Row-Level Locks and Monitor Contention

In the previous step, you saw how isolation levels control what a transaction sees. Now you will control what a transaction can touch. Row-level locks let you explicitly claim rows so that other sessions must wait before modifying them.

In this step, you will use SELECT FOR UPDATE and SELECT FOR SHARE to lock rows, then diagnose the resulting contention by querying pg_locks from a third session.

In this step, get ready to:
  • Lock rows with SELECT FOR UPDATE and observe another session blocking.
  • Compare exclusive locks (FOR UPDATE) with shared locks (FOR SHARE).
  • Build a reusable lock monitoring query using pg_locks.
Lock a row with FOR UPDATE and observe blocking

SELECT FOR UPDATE reads a row and simultaneously claims an exclusive lock on it. Any other transaction that tries to lock, modify, or delete that row must wait until the first transaction finishes.

  • In Session A, begin a transaction and lock Alice's row by running:
BEGIN;
SELECT * FROM accounts WHERE owner = 'Alice' FOR UPDATE;

What does this do?

This reads Alice's row and places an exclusive row-level lock on it. The lock persists until you COMMIT or ROLLBACK the transaction. Other sessions can still read Alice's row with a plain SELECT, but they cannot lock or modify it.

  • In Session B, try to lock the same row by running:
BEGIN;
SELECT * FROM accounts WHERE owner = 'Alice' FOR UPDATE;

Session B hangs. It is blocked, waiting for Session A to release the lock on Alice's row. Your cursor will not return to the prompt.

Why does Session B hang?

FOR UPDATE is an exclusive lock. Only one transaction can hold it on a given row at a time. Session B must wait until Session A commits or rolls back.

Now you will open a third psql window to inspect the lock state from the outside.

  • Open a new Command Prompt or PowerShell window (Session C) and connect to the lab database by running:
psql -U postgres -d concurrency_lab
  • In Session C, query pg_locks to see the blocked process by running:
SELECT
    l.pid,
    l.locktype,
    l.mode,
    l.granted,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE l.relation = 'accounts'::regclass
ORDER BY l.granted;

What does this query show?

This joins pg_locks with pg_stat_activity to show which process holds which lock on the accounts table.

The granted column is the key indicator. true means the lock is held. false means the process is waiting.

You should see one row with granted = true (Session A) and one with granted = false (Session B, waiting).

  • In Session A, release the lock by running:
COMMIT;

Session B immediately unblocks and acquires the lock. You will see the SELECT result appear in Session B.

  • In Session B, release the lock by running:
COMMIT;

Session B not unblocking?

Make sure you ran COMMIT (not just typed it) in Session A. Look for the cursor prompt to return in Session B. If you accidentally closed Session A, the lock is released automatically and Session B should have already unblocked.

Help me figure out why my FOR UPDATE lock is not releasing.

Compare FOR UPDATE and FOR SHARE

FOR SHARE is a shared lock. Multiple transactions can hold a shared lock on the same row simultaneously. However, no transaction can modify the row while any shared lock exists.

  • In Session A, acquire a shared lock on Bob's row by running:
BEGIN;
SELECT * FROM accounts WHERE owner = 'Bob' FOR SHARE;
  • In Session B, also acquire a shared lock on Bob's row by running:
BEGIN;
SELECT * FROM accounts WHERE owner = 'Bob' FOR SHARE;

Both succeed. Multiple readers can hold a FOR SHARE lock at the same time.

  • In Session B, try to update Bob's row by running:
UPDATE accounts SET balance = balance + 100 WHERE owner = 'Bob';

Session B blocks. The UPDATE needs an exclusive lock, which conflicts with the shared lock that Session A still holds.

  • In Session A, release the shared lock by running:
COMMIT;

Session B's UPDATE completes immediately now that the shared lock is gone.

  • In Session B, commit by running:
COMMIT;

When to use FOR UPDATE vs FOR SHARE

Use FOR UPDATE when your transaction intends to modify the row. This prevents anyone else from reading-for-update or writing until you finish.

Use FOR SHARE when you need the row to stay unchanged while you read it, but you do not plan to modify it yourself. Other readers can still acquire FOR SHARE on the same row.

  • Reset Bob's balance back to 1000.00. In either session, run:
UPDATE accounts SET balance = 1000.00 WHERE owner = 'Bob';

Seeing an error about a transaction in progress?

If you see an error mentioning an aborted transaction, run ROLLBACK; first, then retry the UPDATE. This clears any leftover transaction state.

Help me clear a stuck transaction in my psql session.

Save a reusable lock monitoring query

The simple pg_locks query you ran earlier shows who holds locks, but in production you need to quickly find which session is blocking which. This comprehensive query answers that directly.

  • In Session C, run the following lock monitoring query:
-- Query to identify blocked and blocking sessions.
SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_activity.query AS blocked_statement,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

What does this query do?

It self-joins pg_catalog.pg_locks to find pairs of locks where one is granted and the other is waiting on the same resource. It then joins pg_stat_activity to show the actual SQL statements involved.

The IS NOT DISTINCT FROM comparisons handle NULL values correctly. Standard = would fail when lock fields are NULL.

  • Save this query as lock_monitor.sql in your working directory for future use.

Since no sessions are currently blocked, this query returns zero rows right now. You will use it again in the next step when you create a deadlock.

✔️ Awesome, I've got everything!

Great. Make sure you have saved the query as lock_monitor.sql so you can reuse it.

ⓧ I'd like to double check the full code

-- Query to identify blocked and blocking sessions.
SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_activity.query AS blocked_statement,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
    ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

You now have the tools to see exactly what is happening with locks in your database. Next, you will witness what happens when two transactions get stuck waiting for each other: a deadlock.

Trigger and Prevent Deadlocks

In the previous step, you observed how row-level locks cause one session to wait for another. But what happens when two sessions are each waiting for the other?

A deadlock occurs when two transactions each hold a lock that the other needs. Neither can proceed. PostgreSQL automatically detects this situation and breaks the cycle by aborting one transaction. In this step, you will trigger a deadlock on purpose and then learn the standard prevention technique.

In this step, get ready to:
  • Trigger a deadlock by crossing row locks between two sessions.
  • Prevent deadlocks using a consistent lock-ordering strategy.
Create a deadlock scenario

A deadlock requires two sessions that lock rows in opposite order. Session A locks Alice then tries to lock Bob. Session B locks Bob then tries to lock Alice. Each session ends up waiting for the other forever.

  • In Session A, begin a transaction and lock Alice's row by running:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE owner = 'Alice';
  • In Session B, begin a transaction and lock Bob's row by running:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE owner = 'Bob';

What's happening so far?

Each session now holds an exclusive row-level lock on one row. Session A owns the lock on Alice. Session B owns the lock on Bob. No conflict yet because they locked different rows.

Now you will cross the locks. Each session will try to lock the row the other session already holds.

  • In Session A, attempt to update Bob's row by running:
UPDATE accounts SET balance = balance + 100 WHERE owner = 'Bob';

Session A now hangs. It is waiting for Session B to release the lock on Bob.

  • In Session B, attempt to update Alice's row by running:
UPDATE accounts SET balance = balance + 100 WHERE owner = 'Alice';

Within about one second, PostgreSQL detects the deadlock and aborts one of the transactions. You will see this error in one of the sessions:

ERROR:  deadlock detected

Why does it take about one second?

PostgreSQL's deadlock_timeout parameter (default 1 second) controls how long it waits before checking for deadlocks. PostgreSQL does not check continuously because deadlock detection has a performance cost.

  • In the session that received the error, run ROLLBACK to end the aborted transaction:
ROLLBACK;
  • In the other session (which unblocked and succeeded), run COMMIT to finalize its changes:
COMMIT;

What just happened?

PostgreSQL detected that Session A was waiting for Session B and Session B was waiting for Session A. It broke the cycle by aborting one transaction. The other transaction immediately unblocked and completed successfully.

This is PostgreSQL's automatic deadlock resolution. You do not need to intervene manually. The aborted transaction can simply be retried by your application.

Neither session produced an error?

Make sure you ran the second UPDATE in Session B (the one targeting Alice) while Session A was still hanging. If you accidentally committed or rolled back Session A first, there is no deadlock because the lock was already released.

help me understand why my deadlock experiment did not produce an error

Prevent deadlocks with consistent ordering

The standard prevention strategy is simple. Always acquire locks on multiple rows in a consistent order across all transactions. If every transaction locks Alice before Bob, no crossing can occur.

  • In Session A, run a transfer that locks Alice first, then Bob (alphabetical order):
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE owner = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE owner = 'Bob';
COMMIT;
  • Immediately after starting Session A, run a second transfer in Session B using the same order (Alice first, then Bob):
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE owner = 'Alice';
UPDATE accounts SET balance = balance + 50 WHERE owner = 'Bob';
COMMIT;

Why does this prevent deadlocks?

Session B may block briefly on Alice (waiting for Session A to finish). But Session B will never hold a lock that Session A needs. The lock graph can never form a cycle because both sessions request locks in the same sequence.

In production code, "consistent order" typically means sorting rows by primary key or by a natural ordering (like alphabetical) before issuing updates.

Both transactions complete successfully with no deadlock error. Session B may pause briefly while waiting for Session A, but it never blocks permanently.

Reset the data for the next experiment
  • In either session, reset the account balances by running:
UPDATE accounts SET balance = 1000.00 WHERE owner IN ('Alice', 'Bob');
UPDATE accounts SET balance = 500.00 WHERE owner = 'Charlie';
  • Verify the reset by running SELECT * FROM accounts; in both sessions.

You should see Alice=1000.00, Bob=1000.00, and Charlie=500.00 in both windows.

Secret mission

Advisory Locks

Row-level locks protect table data, but what about coordinating application logic that has nothing to do with specific rows? Advisory locks let you claim arbitrary lock keys to synchronize sessions without touching any table.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally on your PostgreSQL instance, so there are no ongoing costs.

Resources you used:

  • The concurrency_lab database on your local PostgreSQL instance.
  • SQL reference files: setup.sql, reset.sql, lock_monitor.sql, advisory_locks.sql.
  • Three open psql sessions (Session A, Session B, Session C).

Keep everything running

No action needed. Choose this if you plan to continue experimenting with concurrency scenarios or want to rerun the exercises.

  • Your concurrency_lab database stays available for future practice.
  • Your SQL files remain on disk as reference scripts you can rerun anytime.
  • Your psql sessions stay open. You can run \i reset.sql to reset balances before your next experiment.

Pause - I'll come back to this later

Close your psql sessions to free up database connections, but keep the database and files for later.

  • In each psql session (A, B, and C), type \q and press Enter to disconnect.
  • Close all three terminal windows.
  • Your database and SQL files remain intact. When you return, reconnect with psql -U postgres -d concurrency_lab and run \i reset.sql to restore balances.

Delete - I don't want to use this again

Remove the lab database and all SQL files from your machine.

  • In each psql session (A, B, and C), type \q and press Enter to disconnect.
  • Drop the lab database by running this command in a new terminal window:
psql -U postgres -c "DROP DATABASE concurrency_lab;"
  • Delete the SQL reference files. On macOS or Linux:
rm setup.sql reset.sql lock_monitor.sql advisory_locks.sql
  • On Windows (PowerShell):
Remove-Item setup.sql, reset.sql, lock_monitor.sql, advisory_locks.sql
  • Close all terminal windows.

Nice Work!

Nice Work!

Nice work! You just built a hands-on concurrency test bench in PostgreSQL and experienced every major concurrency scenario the database engine can produce.

You've learned how to:

  • Observe MVCC snapshot behavior across isolation levels, proving that PostgreSQL prevents dirty reads and demonstrating non-repeatable reads, lost updates, and serialization failures.
  • Control row-level locking with SELECT FOR UPDATE and SELECT FOR SHARE, and diagnose lock contention using pg_locks and pg_stat_activity.
  • Trigger and resolve a deadlock, then prevent it entirely with a consistent lock-ordering strategy.
  • Secret Mission: Coordinated sessions using advisory locks at both the session level and transaction level, and observed them in pg_locks.

Ready to quiz yourself?