Изоляция транзакций
~~~~~~~~~~~~~~~~~~~


psql postgres -c "CREATE DATABASE dba2_isolation;" 
CREATE DATABASE

Откроем два сеанса, в которых одновременно будем запускать транзакции

      |  psql dba2_isolation
      ||     psql dba2_isolation

.......................................................................

Создадим таблицу с двумя строками

      |  => create table t (id int, value int);
      |  CREATE TABLE

      |  => insert into t values (1, 10), (2, 20);
      |  INSERT 0 2

      |  => select * from t;
      |   id | value 
      |  ----+-------
      |    1 |    10
      |    2 |    20
      |  (2 rows)
      |  

.......................................................................

Уровень изоляции: READ UNCOMMITTED
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      |  => begin;
      |  BEGIN

      |  => set transaction isolation level read uncommitted;
      |  SET

      |  => show transaction_isolation;
      |   transaction_isolation 
      |  -----------------------
      |   read uncommitted
      |  (1 row)
      |  

В чем дело? Ведь PostgreSQL не поддерживает чтение "грязных" данных

.......................................................................

Попробуем прочитать "грязные" данные

      |  => update t set value = 11 where id = 1;
      |  UPDATE 1

Во втором сеансе откроем еще одну READ UNCOMMITTED транзакцию

      ||     => begin isolation level read uncommitted;
      ||     BEGIN

      ||     => select * from t where id = 1;
      ||      id | value 
      ||     ----+-------
      ||       1 |    10
      ||     (1 row)
      ||     

Вторая транзакция не видит незафиксированных изменений.
Дело в том, что в PostgreSQL READ UNCOMMITTED = READ COMMITTED

.......................................................................

      |  => rollback;
      |  ROLLBACK

Чем плохо "грязное" чтение?
После отката 1-й транзакции оказалось бы,
что 2-я транзакция увидела данные, которых в системе не было и нет 

.......................................................................

      ||     => rollback;
      ||     ROLLBACK


Уровень изоляции: READ COMMITTED
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

READ COMMITTED используется по умолчанию

      |  => show default_transaction_isolation;
      |   default_transaction_isolation 
      |  -------------------------------
      |   read committed
      |  (1 row)
      |  

      |  => begin;
      |  BEGIN

      |  => show transaction_isolation;
      |   transaction_isolation 
      |  -----------------------
      |   read committed
      |  (1 row)
      |  

.......................................................................

Мы видели, что 2-я транзакция не видит незафиксированных изменений
Посмотрим, что будет при фиксации измений

      |  => update t set value = 11 where id = 1;
      |  UPDATE 1

      ||     => begin isolation level read committed;
      ||     BEGIN

      ||     => select * from t where id = 1;
      ||      id | value 
      ||     ----+-------
      ||       1 |    10
      ||     (1 row)
      ||     

      |  => commit;
      |  COMMIT

      ||     => select * from t where id = 1;
.......................................................................
      ||      id | value 
      ||     ----+-------
      ||       1 |    11
      ||     (1 row)
      ||     

      ||     => commit;
      ||     COMMIT

В режиме READ COMMITTED транзакции видят зафиксированные изменения
других транзакций
Всё верно?

.......................................................................

      |  => begin;
      |  BEGIN

      ||     => begin;
      ||     BEGIN

      |  => select * from t order by id;
      |   id | value 
      |  ----+-------
      |    1 |    11
      |    2 |    20
      |  (2 rows)
      |  

Запустим долгий запрос и пока он работает изменим таблицу во 2-м сеансе

.......................................................................

      |  => select pg_sleep(3), sum(value) from t;
      ||     => do $$begin perform pg_sleep(1); end;$$;
      ||     DO

      ||     => update t set value = 100 where id = 2;
      ||     UPDATE 1

      ||     => commit;
      ||     COMMIT

      ||     => select sum(value) from t;
      ||      sum 
      ||     -----
      ||      111
      ||     (1 row)
      ||     

      |   pg_sleep | sum 
      |  ----------+-----
      |            |  31
      |  (1 row)
      |  

      |  => commit;
      |  COMMIT

Если во время выполнения долгой команды другая транзакция успела
зафиксировать изменения, то они не будут видны.
2-я транзакция видит только те изменения, которые были зафиксированы
на момент старта очередного запроса к БД.
Всё верно?

.......................................................................

READ COMMITTED и lost updates

      |  => begin;
      |  BEGIN

      ||     => begin;
      ||     BEGIN

      |  => select * from t where id = 1;
      |   id | value 
      |  ----+-------
      |    1 |    11
      |  (1 row)
      |  

      |  => update t set value = value + 10 where id = 1;
      |  UPDATE 1

      ||     => update t set value = value + 10 where id = 1;

Вторая транзакция ждет завершения первой

.......................................................................

      |  => commit;
      |  COMMIT

      ||     UPDATE 1

      ||     => commit;
      ||     COMMIT

Но какой будет результат 2-й транзакции, после фиксации в 1-й?

.......................................................................

      ||     => select * from t where id = 1;
      ||      id | value 
      ||     ----+-------
      ||       1 |    31
      ||     (1 row)
      ||     

С одной стороны, команда во 2-й транзакции не должна видеть изменения,
сделанные после начала её выполнения.
Но с другой стороны, 
она не должна потерять изменения, зафиксированные другими транзакциями.
Поэтому, после снятия блокировки, она вынуждена перечитать строку, 
которую пытается обновить.

.......................................................................

Такой результат, даже без знаний об изоляции, интуитивно кажется правильным.
Но так бывает не всегда.

      |  => create table batches (id int, status text);
      |  CREATE TABLE

      |  => insert into batches values (1, 'OPEN'), (2, 'CLOSED');
      |  INSERT 0 2

      |  => select * from batches;
      |   id | status 
      |  ----+--------
      |    1 | OPEN
      |    2 | CLOSED
      |  (2 rows)
      |  

Можно ли быть уверенным, что после успешного выполнения следующего запроса
в таблице batches не останется строк со статусом CLOSED ?

delete from batches where status = 'CLOSED';

.......................................................................

      |  => begin isolation level read committed;
      |  BEGIN

      |  => update batches set status = 'CLOSED' where id = 1;
      |  UPDATE 1

      |  => update batches set status = 'OPEN' where id = 2;
      |  UPDATE 1

      ||     => delete from batches where status = 'CLOSED';

Транзакция с командой DELETE ждет завершения 1-й транзакции.
Если 1-я транзакция завершится откатом, то DELETE удалит вторую строку,
что понятно и ожидаемо.

.......................................................................

Но в случае успешного завершения 1-й транзакции, команда DELETE перечитает
строку с id=2, убедится, что её статус не равен CLOSED и ничего не удалит.
Хотя строка со статусом CLOSED присутствовала как перед выполнением DELETE,
так и присутствует после выполнения.

      |  => commit;
      |  COMMIT

      ||     DELETE 0

      ||     => select * from batches;
      ||      id | status 
      ||     ----+--------
      ||       1 | CLOSED
      ||       2 | OPEN
      ||     (2 rows)
      ||     

.......................................................................


Уровень изоляции: REPEATABLE READ
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Убедимся в отсутствии феномена "неповторяемое чтение"

      |  => begin isolation level repeatable read;
      |  BEGIN

      |  => select * from batches;
      |   id | status 
      |  ----+--------
      |    1 | CLOSED
      |    2 | OPEN
      |  (2 rows)
      |  

.......................................................................

      ||     => update batches set status = 'CLOSED' where id = 2;
      ||     UPDATE 1

      |  => select * from batches;
      |   id | status 
      |  ----+--------
      |    1 | CLOSED
      |    2 | OPEN
      |  (2 rows)
      |  

Повторное чтение измененной строки возвращает первоначальную строку.
Это происходит потому, что снимок данных делается в начале транзакции,
а не в начале команды.

.......................................................................

Но если снимок делается в начале транзакции, то фантомные записи
также не должны быть видны. Проверим

      ||     => insert into batches values (3, 'OPEN');
      ||     INSERT 0 1

      |  => select * from batches;
      |   id | status 
      |  ----+--------
      |    1 | CLOSED
      |    2 | OPEN
      |  (2 rows)
      |  

Действительно, 1-я транзакция не видит добавленной записи.
Уровень изоляции REPEATABLE READ в PostgreSQL более строгий, чем того требует 
стандарт SQL.

.......................................................................

Но нужно быть готовым к тому, если в 1-й транзакции мы захотим изменить
строку, уже обновленную другой транзакцией, то мы получим ошибку:

      |  => update batches set status = 'CLOSED' where id = 2;
      |  ERROR:  could not serialize access due to concurrent update

      |  => rollback;
      |  ROLLBACK

.......................................................................


Уровень изоляции: SERIALIZABLE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Транзакции могут положиться на то, что на их работу никто не повлияет

      |  => select * from batches;
      |   id | status 
      |  ----+--------
      |    1 | CLOSED
      |    2 | CLOSED
      |    3 | OPEN
      |  (3 rows)
      |  

      |  => begin isolation level serializable;
      |  BEGIN

      |  => update batches set status = 'OPEN' where status = 'CLOSED';
      |  UPDATE 2

Теперь для 1-й транзакции гарантировано, что записей со статусом CLOSED нет

.......................................................................

      ||     => begin isolation level serializable;
      ||     BEGIN

      ||     => update batches set status = 'CLOSED' where status = 'OPEN';
      ||     UPDATE 1

А для 2-й транзакции гарантировано, что записей со статусом OPEN нет
Но ведь это проблема!
Теперь обе транзакции не могут успешно завершиться.
Победит та, которая раньше выполнит commit

.......................................................................

      |  => commit;
      |  COMMIT

      ||     => commit;
      ||     ERROR:  could not serialize access due to read/write dependencies among transactions
      ||     DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
      ||     HINT:  The transaction might succeed if retried.

      |  => \q
      ||     => \q

psql postgres -c "DROP DATABASE dba2_isolation;" 
DROP DATABASE

Конец демонстрации.