Transaction Isolation Levels
Transaction isolation levels in PostgreSQL. This episode is brought to you by Hashrocket, expert consultants in PostgreSQL - learn more at https://hashrocket.com and www.pgcasts.com
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.
```sql
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.
```sql
-- 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.
```sql
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.
```sql
-- conn 2
select * from entry;
```
Our changes aren't visible until we commit.
```sql
-- conn 1
commit;
```
```sql
-- conn 2
select * from entry;
```
But what if two of these happened concurrently?
```sql
-- conn 1
begin;
insert into entry(amount) values(10) returning *;
update entry
set running_total=(
select sum(amount) from entry
)
where id=2
returning *;
```
```sql
-- conn 2
begin;
insert into entry(amount) values(10) returning *;
update entry
set running_total=(
select sum(amount) from entry
)
where id=3
returning *;
```
```sql
-- conn 1
commit;
```
```sql
-- conn 2
commit;
```
```sql
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.
```sql
truncate entry restart identity;
```
To change isolation levels we use the `set transaction` statement at the
beginning of the transaction.
```sql
-- 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.
```sql
-- 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.
```sql
-- 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.
Watch video Transaction Isolation Levels online without registration, duration hours minute second in high quality. This video was added by user PG Casts by Hashrocket 13 September 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 10,146 once and liked it 118 people.