Transcript

The transaction isolation level determines what effect concurrent transactions can have on one another. Postgres implements three transaction isolation levels: read committed, repeatable read, and serializable.

We will use a simple ledger table with a running total as an example domain.

create table entry(
  id serial primary key,
  amount integer not null,
  running_total integer
);

The default isolation level, read committed, guarantees that uncommitted changes are not visible.

-- conn 1
begin;

insert into entry(amount) values(10) returning *;

Typically, the running total calculation would be handled in an after insert trigger, but to make the example easier to follow we will do it inline. returning * returns the inserted row so we can grab the id for the update.

update entry
set running_total=(
  select sum(amount) from entry
)
where id=1
returning *;

Now before we commit, let's look at the entry table from another connection.

-- conn 2
select * from entry;

Our changes aren't visible until we commit.

-- conn 1
commit;

-- conn 2
select * from entry;

But what if two of these happened concurrently?

-- conn 1
begin;

insert into entry(amount) values(10) returning *;

update entry
set running_total=(
  select sum(amount) from entry
)
where id=2
returning *;

-- conn 2
begin;

insert into entry(amount) values(10) returning *;

update entry
set running_total=(
  select sum(amount) from entry
)
where id=3
returning *;

-- conn 1
commit;

-- conn 2
commit;

select * from entry;

Even though these statements were run in a transaction, the running totals are incorrect. This is because neither transaction could see the other's newly inserted row.

To solve this issue we will use the strongest isolation level, serializable. This level guarantees that concurrent transactions could have been executed in serial.

Let's reset the table and try again.

truncate entry restart identity;

To change isolation levels we use the set transaction statement at the beginning of the transaction.

-- conn 1
begin;
set transaction isolation level serializable;

insert into entry(amount) values(10) returning *;

update entry
set running_total=(
  select sum(amount) from entry
)
where id=1
returning *;

We can also set the isolation level as part of the begin statement.

-- conn 2
begin isolation level serializable;

insert into entry(amount) values(10) returning *;

update entry
set running_total=(
  select sum(amount) from entry
)
where id=2
returning *;

commit;

The first committed transaction succeeds. But watch what happens when we commit the other transaction.

-- conn 1
commit;

Postgres detects that there is no way these two transactions could have executed serially and rejects the commit.

Applications that use the serializable isolation level must be ready to handle serialization errors on commit.

Higher isolation levels can have a performance cost, but they prevent data anomalies.

More information, including about the repeatable read isolation level, a middle ground between the default and serializable isolation levels can be found in the Postgres documentation.

Thanks for listening.