Transactions

A transaction groups statements so they succeed or fail as one. The examples move money between two accounts, so a half-finished change is easy to spot.

-- setup.sql
CREATE TABLE accounts (
    id      INT PRIMARY KEY,
    owner   TEXT NOT NULL,
    balance NUMERIC(10, 2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 'Ana', 100.00), (2, 'Ben', 50.00);
CREATE EXTENSION dblink;

$ psql -d sqldemo -f setup.sql
CREATE TABLE
INSERT 0 2
CREATE EXTENSION

dblink lets one script open a second database connection. Later examples use it to play a second user, so concurrency can be shown in a single, repeatable file. Creating the extension needs superuser rights, which you have on a local scratch database.

BEGIN and COMMIT

Both updates of a transfer belong in one transaction.

-- transfer.sql
BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
COMMIT;
SELECT id, owner, balance FROM accounts ORDER BY id;

$ psql -d sqldemo -f transfer.sql
BEGIN
UPDATE 1
UPDATE 1
COMMIT
 id | owner | balance 
----+-------+---------
  1 | Ana   |   70.00
  2 | Ben   |   80.00
(2 rows)

Until COMMIT, other connections still saw the old balances. At COMMIT, both changes became visible at the same instant, so no one could ever observe money that had left Ana but not reached Ben.

A Failure Aborts the Whole Transaction

This transfer credits Ben first, then tries to take 500 from Ana, who does not have it.

-- failed_transfer.sql
BEGIN;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
SELECT balance FROM accounts WHERE id = 2;
COMMIT;
SELECT id, owner, balance FROM accounts ORDER BY id;

$ psql -d sqldemo -f failed_transfer.sql
BEGIN
UPDATE 1
psql:failed_transfer.sql:4: ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (1, Ana, -430.00).
psql:failed_transfer.sql:5: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
 id | owner | balance 
----+-------+---------
  1 | Ana   |   70.00
  2 | Ben   |   80.00
(2 rows)

The CHECK constraint rejected Ana’s negative balance, and from then on PostgreSQL refused every statement in the transaction: “current transaction is aborted”. The COMMIT did not commit, psql printed ROLLBACK, and Ben’s 500 credit vanished with it. The balances are exactly as before.

Autocommit: The Same Mistake Without BEGIN

Run the same two updates without a transaction.

-- autocommit.sql
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
SELECT id, owner, balance FROM accounts ORDER BY id;
UPDATE accounts SET balance = balance - 500 WHERE id = 2;

$ psql -d sqldemo -f autocommit.sql
UPDATE 1
psql:autocommit.sql:3: ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (1, Ana, -430.00).
 id | owner | balance 
----+-------+---------
  1 | Ana   |   70.00
  2 | Ben   |  580.00
(2 rows)

UPDATE 1

Without BEGIN, each statement committed on its own. Ben’s credit was saved, Ana’s debit failed, and the bank created 500 out of nothing. The last line takes it back by hand, which is exactly the cleanup a transaction would have made unnecessary.

Savepoints

A savepoint marks a point you can roll back to without losing the whole transaction.

-- savepoint.sql
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
SAVEPOINT before_bonus;
UPDATE accounts SET balance = balance - 1000 WHERE id = 2;
ROLLBACK TO SAVEPOINT before_bonus;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;
SELECT id, owner, balance FROM accounts ORDER BY id;

$ psql -d sqldemo -f savepoint.sql
BEGIN
UPDATE 1
SAVEPOINT
psql:savepoint.sql:5: ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (2, Ben, -920.00).
ROLLBACK
UPDATE 1
COMMIT
 id | owner | balance 
----+-------+---------
  1 | Ana   |   60.00
  2 | Ben   |   90.00
(2 rows)

The failed update aborted the transaction, but ROLLBACK TO SAVEPOINT undid only the work after the savepoint and cleared the aborted state. Ana’s 10 debit survived, the transfer finished, and COMMIT saved it.

READ COMMITTED: Reads Can Change Mid-Transaction

PostgreSQL’s default isolation level lets each statement see everything committed before it started. Here the second connection commits an update between two reads.

-- read_committed.sql
SELECT dblink_connect('other', 'dbname=sqldemo');
BEGIN;  -- default isolation: READ COMMITTED
SELECT balance AS first_read FROM accounts WHERE id = 1;
SELECT dblink_exec('other', 'UPDATE accounts SET balance = balance + 1 WHERE id = 1');
SELECT balance AS second_read FROM accounts WHERE id = 1;
COMMIT;
SELECT dblink_disconnect('other');

$ psql -d sqldemo -f read_committed.sql
 dblink_connect 
----------------
 OK
(1 row)

BEGIN
 first_read 
------------
      60.00
(1 row)

 dblink_exec 
-------------
 UPDATE 1
(1 row)

 second_read 
-------------
       61.00
(1 row)

COMMIT
 dblink_disconnect 
-------------------
 OK
(1 row)

The same SELECT inside one transaction returned 60.00 and then 61.00. That is a non-repeatable read. It is harmless for many queries, but a report that reads a total twice can disagree with itself.

REPEATABLE READ: One Snapshot, and Retries

REPEATABLE READ freezes the view at the transaction’s first query.

-- repeatable_read.sql
SELECT dblink_connect('other', 'dbname=sqldemo');
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance AS first_read FROM accounts WHERE id = 1;
SELECT dblink_exec('other', 'UPDATE accounts SET balance = balance + 1 WHERE id = 1');
SELECT balance AS second_read FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 5 WHERE id = 1;
COMMIT;
SELECT balance AS after FROM accounts WHERE id = 1;
SELECT dblink_disconnect('other');

$ psql -d sqldemo -f repeatable_read.sql
 dblink_connect 
----------------
 OK
(1 row)

BEGIN
 first_read 
------------
      61.00
(1 row)

 dblink_exec 
-------------
 UPDATE 1
(1 row)

 second_read 
-------------
       61.00
(1 row)

psql:repeatable_read.sql:7: ERROR:  could not serialize access due to concurrent update
ROLLBACK
 after 
-------
 62.00
(1 row)

 dblink_disconnect 
-------------------
 OK
(1 row)

Both reads returned 61.00 even though the other connection committed 62.00 in between. The trouble came at the UPDATE: this transaction’s snapshot was out of date for that row, so PostgreSQL refused with “could not serialize access” rather than silently overwrite the other change. The transaction rolled back. Code that uses REPEATABLE READ or SERIALIZABLE must catch this error and retry the whole transaction.

Waiting on Locks

An update locks the row it changes until its transaction ends. Here the other connection holds that lock, and lock_timeout stops this session from waiting forever.

-- lock_wait.sql
SELECT dblink_connect('other', 'dbname=sqldemo');
SELECT dblink_exec('other', 'BEGIN');
SELECT dblink_exec('other', 'UPDATE accounts SET balance = balance + 1 WHERE id = 2');
SET lock_timeout = '1s';
UPDATE accounts SET balance = balance - 1 WHERE id = 2;
SELECT dblink_exec('other', 'ROLLBACK');
UPDATE accounts SET balance = balance - 1 WHERE id = 2;
SELECT dblink_disconnect('other');

$ psql -d sqldemo -f lock_wait.sql
 dblink_connect 
----------------
 OK
(1 row)

 dblink_exec 
-------------
 BEGIN
(1 row)

 dblink_exec 
-------------
 UPDATE 1
(1 row)

SET
psql:lock_wait.sql:6: ERROR:  canceling statement due to lock timeout
CONTEXT:  while updating tuple (0,9) in relation "accounts"
 dblink_exec 
-------------
 ROLLBACK
(1 row)

UPDATE 1
 dblink_disconnect 
-------------------
 OK
(1 row)

The first UPDATE waited one second for Ben’s row, then gave up with “canceling statement due to lock timeout”. Once the other connection rolled back and released the lock, the same update went straight through. Two transactions that lock rows in opposite orders can wait on each other forever; PostgreSQL detects that deadlock after about a second and cancels one of them. The cure is to lock rows in a consistent order, such as by ascending id.

Advisory Locks

An advisory lock is a lock on a number your application chooses, not on a row. It is useful for “only one worker may run this job”.

-- advisory.sql
SELECT dblink_connect('other', 'dbname=sqldemo');
SELECT pg_try_advisory_lock(42) AS me_first;
SELECT * FROM dblink('other', 'SELECT pg_try_advisory_lock(42)') AS t(other_got_it boolean);
SELECT pg_advisory_unlock(42) AS released;
SELECT * FROM dblink('other', 'SELECT pg_try_advisory_lock(42)') AS t(other_got_it boolean);
SELECT dblink_disconnect('other');

$ psql -d sqldemo -f advisory.sql
 dblink_connect 
----------------
 OK
(1 row)

 me_first 
----------
 t
(1 row)

 other_got_it 
--------------
 f
(1 row)

 released 
----------
 t
(1 row)

 other_got_it 
--------------
 t
(1 row)

 dblink_disconnect 
-------------------
 OK
(1 row)

pg_try_advisory_lock never waits: it returns t if it got the lock and f if someone else holds it. The other connection was refused until this session released lock 42. PostgreSQL attaches no meaning to the number; your code decides that 42 means, say, “the nightly billing job”.

Practice

1. Transfer 20 from Ben to Ana in one transaction and show the balances.

-- practice_transfer.sql
BEGIN;
UPDATE accounts SET balance = balance - 20 WHERE id = 2;
UPDATE accounts SET balance = balance + 20 WHERE id = 1;
COMMIT;
SELECT id, owner, balance FROM accounts ORDER BY id;

$ psql -d sqldemo -f practice_transfer.sql
BEGIN
UPDATE 1
UPDATE 1
COMMIT
 id | owner | balance 
----+-------+---------
  1 | Ana   |   82.00
  2 | Ben   |   69.00
(2 rows)

2. Start a transaction, set Ana’s balance to 0, look at it, then roll back. Confirm nothing changed.

-- practice_rollback.sql
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;
SELECT balance AS inside FROM accounts WHERE id = 1;
ROLLBACK;
SELECT balance AS after_rollback FROM accounts WHERE id = 1;

$ psql -d sqldemo -f practice_rollback.sql
BEGIN
UPDATE 1
 inside 
--------
   0.00
(1 row)

ROLLBACK
 after_rollback 
----------------
          82.00
(1 row)

3. Use a savepoint so that a failed 1,000 debit from Ana is skipped but a 5 fee taken before it is kept.

-- practice_savepoint.sql
BEGIN;
UPDATE accounts SET balance = balance - 5 WHERE id = 1;
SAVEPOINT big_debit;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
ROLLBACK TO SAVEPOINT big_debit;
COMMIT;
SELECT id, balance FROM accounts WHERE id = 1;

$ psql -d sqldemo -f practice_savepoint.sql
BEGIN
UPDATE 1
SAVEPOINT
psql:practice_savepoint.sql:5: ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
DETAIL:  Failing row contains (1, Ana, -923.00).
ROLLBACK
COMMIT
 id | balance 
----+---------
  1 |   77.00
(1 row)