Transactions and What Two Users Can See

You know a transaction as an undo button: BEGIN, do some work, COMMIT or ROLLBACK. That is half of it. The other half matters on a real system, where somebody else is reading and writing the same tables while your transaction is open. What can they see? What can you see? What happens if you both touch the same row?

To answer that, this lesson uses two psql sessions at once, called A and B. They are two terminals connected to the same database. The examples below are shown in the order the commands were run in time, each labelled with its session, and every output is from a real run on two connections. This is PostgreSQL behaviour. The standard defines the isolation levels, and each database implements them in its own way.

Uncommitted changes are invisible

PostgreSQL's default isolation level is READ COMMITTED: each statement sees the data that was committed when the statement started. Session B opens a transaction and reads a price.

Session B

BEGIN;
SELECT name, price FROM products WHERE name = 'Desk Lamp';

BEGIN
   name    | price
-----------+-------
 Desk Lamp | 18.00
(1 row)

Session A changes the price in its own transaction and reads it back.

Session A

BEGIN;
UPDATE products SET price = 20.00 WHERE name = 'Desk Lamp';
SELECT name, price FROM products WHERE name = 'Desk Lamp';

BEGIN
UPDATE 1
   name    | price
-----------+-------
 Desk Lamp | 20.00
(1 row)

A sees its own change. B runs the same query again.

Session B

SELECT name, price FROM products WHERE name = 'Desk Lamp';

   name    | price
-----------+-------
 Desk Lamp | 18.00
(1 row)

Still 18.00. A's change is not committed, so no one else can see it. PostgreSQL never shows another session's uncommitted data, which is the "dirty read" other systems may allow. Now A commits, and B asks again inside its still-open transaction.

Session A

COMMIT;

COMMIT

Session B

SELECT name, price FROM products WHERE name = 'Desk Lamp';
COMMIT;

   name    | price
-----------+-------
 Desk Lamp | 20.00
(1 row)

COMMIT

B now sees 20.00. Inside one transaction, the same query returned two different answers, because under READ COMMITTED each statement takes a fresh look. For most work that is what you want. For a report that runs several queries and must add up, it is a problem.

One snapshot for the whole transaction

REPEATABLE READ takes one snapshot at the first query and keeps it until the end of the transaction.

Session B

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT name, price FROM products WHERE name = 'Desk Lamp';

BEGIN
   name    | price
-----------+-------
 Desk Lamp | 20.00
(1 row)

Session A (a single statement, committed straight away)

UPDATE products SET price = 22.00 WHERE name = 'Desk Lamp';

UPDATE 1

Session B

SELECT name, price FROM products WHERE name = 'Desk Lamp';
COMMIT;
SELECT name, price FROM products WHERE name = 'Desk Lamp';

   name    | price
-----------+-------
 Desk Lamp | 20.00
(1 row)

COMMIT
   name    | price
-----------+-------
 Desk Lamp | 22.00
(1 row)

During the transaction B kept seeing 20.00, even though 22.00 had been committed. After COMMIT it sees the new value. A multi-query report inside REPEATABLE READ describes one moment, so its totals agree with each other.

Readers do not wait for writers

Session A takes a row lock by updating a row and keeps its transaction open.

Session A

BEGIN;
UPDATE products SET price = 7.00 WHERE name = 'Mug';

BEGIN
UPDATE 1

Session B reads the same row. Reading is never blocked.

Session B

SELECT name, price FROM products WHERE name = 'Mug';

 name | price
------+-------
 Mug  |  6.50
(1 row)

B sees the old price, 6.50, and does not wait. A writer that touches the same row is a different story.

Two writers, one row

B tries to change the row A has locked. Normally B would wait until A finishes. To show it without hanging, B sets a lock timeout of a third of a second first.

Session B

\set VERBOSITY terse
SET lock_timeout = '300ms';

UPDATE products SET price = 8.00 WHERE name = 'Mug';

ERROR:  canceling statement due to lock timeout

B gave up because the row was still locked by A. Without the timeout it would wait, and wait, until A commits or rolls back. A transaction left open by accident (a forgotten BEGIN in a terminal, a job that crashed halfway) can hold up everyone who needs that row. A rolls back.

Session A

ROLLBACK;

ROLLBACK

Now the row is free, and B's update goes through.

Session B

UPDATE products SET price = 8.00 WHERE name = 'Mug';

UPDATE 1

When REPEATABLE READ says no

Under REPEATABLE READ, B cannot update a row that someone else changed after B's snapshot was taken. PostgreSQL stops it with an error instead of silently overwriting.

Session B

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT name, price FROM products WHERE name = 'Mug';

BEGIN
 name | price
------+-------
 Mug  |  8.00
(1 row)

Session A

UPDATE products SET price = 9.00 WHERE name = 'Mug';

UPDATE 1

Session B

UPDATE products SET price = 8.50 WHERE name = 'Mug';
SELECT name, price FROM products WHERE name = 'Mug';
ROLLBACK;

ERROR:  could not serialize access due to concurrent update
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

The error is "could not serialize access due to concurrent update". Nothing is wrong with the database. It is telling you that the answer you based your change on is no longer true. After an error, the rest of the transaction is dead until you roll back, which is why the SELECT was refused. The usual response is to retry the whole transaction. Code that uses REPEATABLE READ must be written to retry.

Common mistakes

• Reading the same table twice in one report under READ COMMITTED and expecting both reads to agree.
• Leaving a transaction open in a terminal.
• Treating "could not serialize access" as a bug and not as a signal to retry.
• Assuming other databases give the same answers at the same isolation level.
• Taking a decision on a value you read earlier in the transaction, without locking the row. There is a way to lock rows while you read them, SELECT ... FOR UPDATE, and it is a topic of its own.

If you skip this, your code will work in testing, where nobody else is connected, and show odd totals or timeouts on the first busy day. What is the longest transaction your application keeps open, and who is waiting for it?