In this section, you will be introduced to an important topic called transaction isolation. In my long career as a PostgreSQL database consultant, I have seen countless scenarios in which transaction isolation has caused enormous problems. Sometimes, people even desperately replaced their hardware to get around issues that were definitely not related to hardware.
So, what is transaction isolation? The idea behind transaction isolation is to give users a tool to control what they see inside a transaction. A report might have different requirements than a simple OLTP application, but let's not get lost in plan theory. Instead, let's look at a real example with some data:
test=# CREATE TABLE t_test (id int); CREATE TABLE test=# INSERT INTO t_test VALUES (4), (5); INSERT 0 2
We've got two rows. Now let's see what happens if two transactions battle for the data at the same time:
|
User 1: |
User 2: |
|---|---|
|
BEGIN;
| |
|
SELECT sum(id) FROM t_test; sum... |