Уровень 3.0

Практика. Транзакции

Подготовительный этап

Откройте два терминала. Чтобы их можно было различать, измените приглашение командной строки. В первом терминале задайте переменную PS1 так, чтобы она заменяла \h (имя хоста) на TERM1:
```
[student@ServerName ~]$ PS1=$(echo $PS1 | sed 's/\\h/TERM1/')
```
И выполните то же самое для пользователя postgres:
```
[student@TERM1 ~]$ sudo su - postgres
```
```
[postgres@ServerName ~]$ PS1=$(echo $PS1 | sed 's/\\h/TERM1/')
```
Во втором терминале выполните то же самое, но с TERM2:
```
[student@ServerName ~]$ PS1=$(echo $PS1 | sed 's/\\h/TERM2/')
```
```
[student@TERM2 ~]$ sudo su - postgres
```
```
[postgres@ServerName ~]$ PS1=$(echo $PS1 | sed 's/\\h/TERM2/')
```
В третьей вкладке или терминале запустите оболочку от имени postgres и зайдите в сеанс psql:
```
[student@ServerName ~]$ sudo su - postgres
```
```
[postgres@ServerName ~]$ psql
```
```
psql (15.5)
Type "help" for help.
postgres=#
```
Сбросьте все настройки в postgresql.auto.conf, выполнив команду ALTER SYSTEM RESET ALL:
```
postgres=# ALTER SYSTEM RESET ALL;
```
```
ALTER SYSTEM
```
В первом сеансе student перезапустите сервер PostgreSQL:
```
[student@TERM1 ~]$ sudo systemctl restart postgresql
```
Перезапустите сессию сессию postgres в клиенте psql в третьем сеансе:
```
postgres=# \c
```
```
You are now connected to database "postgres" as user "postgres".
postgres=#
```

Удалите все БД, кроме template1, template0 и postgres:

postgres=# \l

                                                 List of databases
     Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
  -----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
   postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
   student   | student  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
   template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
             |          |          |             |             |            |                 | postgres=CTc/postgres
   template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
             |          |          |             |             |            |                 | postgres=CTc/postgres
  (4 rows)

 postgres=# DROP DATABASE student;

DROP DATABASE

Эти базы данных были удалены в качестве примера. В вашей системе набор БД может быть другой, получите его в сеансе postgres в psql метакомандой \l.

В СУБД должны быть зарегистрированы только две роли: postgres и student. Все остальные роли должны быть удалены:

postgres=# \du

                                   List of roles
Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
dbuser1   |                                                            | {}
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
student   |                                                            | {}

postgres=# DROP ROLE dbuser1;

DROP ROLE

Метакоманда \du выводит в psql список зарегистрированных в СУБД ролей.

Создайте БД student, владельцем которой будет роль student:

postgres=# CREATE DATABASE student OWNER student;

CREATE DATABASE

postgres=# \l student

                                                 List of databases
  Name   |  Owner  | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider | Access | privileges
---------+---------+----------+------------+------------+------------+-----------------+------------
 student | student | UTF8     | en_US.UTF8 | en_US.UTF8 |            | libc            |
(1 row)

Основной этап

В первом терминале зайдите в сеанс ролью student, подключившись к одноименной БД:
```
[postgres@TERM1 ~]$ psql student student
```
```
psql (15.5)
Type "help" for help.
```
```
student@student=> SELECT user;
```
```
  user
---------
 student
(1 row)
```
```
student@student=> SELECT current_catalog;
```
```
 current_catalog
-----------------
     student
(1 row)
```
Функция SQL user возвращает имя роли в сеансе, функция current_catalog — имя БД, к которой выполнено подключение.

В первом терминале создайте таблицу stab с текстовым столбцом:

student@student=> CREATE TABLE stab(msg text);

CREATE TABLE

student@student=> \d

        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+---------
 public | stab | table | student
(1 row)

student@student=> \d stab

              Table "public.stab"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
 msg    | text |           |          |

Метакоманда \d без аргументов выдает список таблиц (и других отношений). Если метакоманде \d задать имя существующей таблицы, она напечатает структуру этой таблицы.

Начните транзакцию в первом терминале:
```
student@student=> BEGIN;
```
```
BEGIN
```
Обратите внимание на изменение приглашения командной строки psql. Там появился символ звездочки (*), который и говорит о том, что в сеансе запущена транзакция.
Получите номер текущей транзакции в первом терминале:
```
student@student=*> SELECT pg_current_xact_id();
```
```
pg_current_xact_id
--------------------
                885
(1 row)
```
Функция pg_current_xact_id() возвращает номер XID транзакции, если он был назначен. Если он не был назначен, назначает.
Вставьте в таблицу произвольную строку:
```
student@student=*> INSERT INTO stab VALUES ('Вставка');
```
```
INSERT 0 1
```
```
student@student=*> SELECT * FROM stab;
```
```
   msg
---------
 Вставка
(1 row)
```
Изменения данных, производимые командами DML, в транзакции видны сразу.
Проверьте, видны ли изменения в таблице stab вне транзакции, обратившись к таблице с запросом на чтение во втором сеансе:
```
[postgres@TERM2 ~]$ psql student student
```
```
psql (15.5)
Type "help" for help.
```
```
student@student=> SELECT * FROM stab;
```
```
 msg
-----
(0 rows)
```
Обратите внимание, активная пишущая транзакция не блокирует операции чтения, но система MVCC предоставляет командам и транзакциям разные представления данных — снимки. Во втором сеансе снимок данных таблицы stab не содержит строки, так как транзакция в первом сеансе еще не зафиксирована.
Зафиксируйте транзакцию в первом сеансе:
```
student@student=> COMMIT;
```
```
COMMIT
```
Проверьте содержимое таблицы в обоих сеансах.

В первом:
```
student@student=> SELECT * FROM stab;
```
```
   msg
---------
 Вставка
(1 row)
```
Во втором:
```
student@student=> SELECT * FROM stab;
```
```
   msg
---------
 Вставка
(1 row)
```
После фиксации транзакции внесенные ею изменения в данных видны всем командам и транзакциям.

Откат при ошибке в командах транзакции

Начните транзакцию в первом сеансе, но сделайте в какой-либо команде транзакции ошибку:
```
student@student=> BEGIN;
```
```
BEGIN
```
```
student@student=*> амбигоус_цомманд;
```
```
ERROR:  syntax error at or near "амбигоус_цомманд"
LINE 1: амбигоус_цомманд;
        ^
```
```
student@student=!> SELECT pg_current_xact_id();
```
```
ERROR: current transaction is aborted, commands ignored until end of transaction block
```
Попробуйте зафиксировать транзакцию:
```
student@student=!> COMMIT;
```
```
ROLLBACK
```
В режиме по умолчанию, если в какой-либо команде транзакции произошла ошибка, транзакция отменяется и при попытке ее фиксации происходит откат.
Получите текущее значение встроенной переменной psql ON_ERROR_ROLLBACK:
```
student@student=> \echo :ON_ERROR_ROLLBACK
```
```
off
```
Установите значение этой переменной в on:
```
student@student=> \set ON_ERROR_ROLLBACK on
```
```
student@student=> \echo :ON_ERROR_ROLLBACK
```
```
on
```
Снова начните транзакцию и допустите ошибку. Проверьте отличия:
```
student@student=> BEGIN;
```
```
BEGIN
```
```
student@student=*> амбигоус_цомманд;
```
```
ERROR: syntax error at or near "амбигоус_цомманд" LINE 1: амбигоус_цомманд;
^
```
Теперь, несмотря на ошибку, транзакция не перешла в состояние ошибки (приглашение осталось =*>).
```
student@student=*> SELECT pg_current_xact_id();
```
```
 pg_current_xact_id
--------------------
             886
(1 row)
```
```
student@student=*> COMMIT;
```
```
COMMIT
```
При ON_ERROR_ROLLBACK = on перед каждой командой транзакции неявно устанавливает точку сохранения SAVEPOINT, и при возникновении ошибки происходит не откат всей транзакции, а откат к предыдущей успешной команде транзакции.

Но у этого подхода есть существенный минус – значительно возрастает нагрузка на сервер из-за обилия точек отката. Поэтому такой настройкой не следует злоупотреблять.

Сбросьте ON_ERROR_ROLLBACK:

student@student=> \unset ON_ERROR_ROLLBACK

student@student=> \echo :ON_ERROR_ROLLBACK

off

Версии строк

Посмотрим на механизм версионирования.

Начните транзакцию в первом терминале и получите ее номер:

student@student=> BEGIN;

BEGIN

student@student=*> SELECT pg_current_xact_id();

  pg_current_xact_id
 --------------------
                887
(1 row)

Выполните запрос, выводящий поля xmin и xmax из заголовка строки:
```
student@student=*> SELECT xmin, xmax, * FROM stab;
```
```
 xmin | xmax | msg
------+------+---------
  885 |    0 | Вставка
(1 row)
```
Поле xmin — номер транзакции, создавшей эту версию строки. Строка в примере была создана транзакцией 885. Поле xmax — номер транзакции, удалившей версию строки. Пока эту версию строки никакая транзакция не удаляла.
Во втором терминале также запустите транзакцию и выполните такой же запрос:
```
student@student=> BEGIN;
```
```
BEGIN
```
```
student@student=*> SELECT xmin, xmax, * FROM stab;
```
```
 xmin | xmax | msg
------+------+---------
  885 |    0 | Вставка
(1 row)
```
Пока результат ничем не отличается от транзакции в первом сеансе.
В первом сеансе измените строку командой UPDATE:
```
student@student=*> UPDATE stab SET msg = 'Обновление';
```
```
UPDATE 1
```
```
student@student=*> SELECT xmin, xmax, * FROM stab;
```
```
 xmin | xmax | msg
------+------+------------
  887 |    0 | Обновление
(1 row)
```
Теперь xmin = 887 – номер текущей транзакции, создавшей новую версию строки. Старая версия стала невидимой внутри этой транзакции.
В снимке данных второй транзакции до сих пор должно быть видно старую версию строки, ведь фиксации первой транзакции не было:
```
student@student=*> SELECT xmin, xmax, * FROM stab;
```
```
 xmin | xmax |   msg
------+------+---------
  885 |  887 | Вставка
(1 row)
```
Что произошло? Во втором сеансе видно старую версию строки, созданную транзакцией 885, которая была ранее зафиксирована. Поле xmax теперь равно 887 – номеру транзакции, которая удалила эту версию (через UPDATE). Новая версия пока не видна, так как транзакция 887 не зафиксирована.

Команда UPDATE эквивалентна действию DELETE, удаляющей версию строки (помечая xmax номером транзакции), плюс INSERT, добавляющей новую версию строки (помечая xmin номером этой же транзакции).
В первом терминале зафиксируйте транзакцию:
```
student@student=*> COMMIT;
```
```
 COMMIT
```
Во втором терминале в активной транзакции проверьте изменения:
```
student@student=*> SELECT xmin, xmax, * FROM stab;
```
```
 xmin | xmax | msg
------+------+------------
  887 |    0 | Обновление
(1 row)
```
Во втором сеансе транзакция работает на уровне изоляции Read Committed. На этом уровне изоляции снимки данных строятся для каждой команды транзакции отдельно. После фиксации транзакции в первом сеансе новый снимок во втором сеансе включил зафиксированные изменения.

Откатите транзакцию во втором терминале:

student@student=*> ROLLBACK;

ROLLBACK

student@student=> SELECT xmin, xmax, * FROM stab;

 xmin | xmax | msg
------+------+------------
  887 |    0 | Обновление
(1 row)

Уровни изоляции

Проверьте, какой уровень изоляции установлен по умолчанию:

student@student=> \dconfig def*iso*

        List of configuration parameters
           Parameter           |     Value
-------------------------------+----------------
 default_transaction_isolation | read committed
(1 row)

В первом сеансе запустите транзакцию на уровне изоляции Repeatable Read:
```
student@student=> BEGIN ISOLATION LEVEL REPEATABLE READ;
```
```
BEGIN
```
```
student@student=*> SELECT * FROM stab;
```
```
    msg
------------
 Обновление
(1 row)
```
Снимок данных строится при вызове первой команды в транзакции. В данном случае SELECT. Так как уровень изоляции установлен Repeatable Read, снимок, построенный для первой команды транзакции, будет оставаться неизменным до ее завершения.
Во втором терминале выполните команду INSERT и прочитайте таблицу:
```
student@student=> INSERT INTO stab VALUES ('Еще вставка');
```
```
INSERT 0 1
```
```
student@student=> SELECT * FROM stab;
```
```
    msg
-------------
 Обновление
 Еще вставка
(2 rows)
```
В psql по умолчанию работает режим автофиксации:
```
student@student=> \echo :AUTOCOMMIT
```
```
on
```
Поэтому выполнение команд DML, изменяющих данные, автоматически фиксируется после каждой выполненной команды.
Вернитесь в первый сеанс и повторите запрос:
```
student@student=*> SELECT * FROM stab;
```
```
    msg
------------
 Обновление
(1 row)
```
Изменения, внесенные INSERT, зафиксированы, но их не видно в снимке данных транзакции в первом сеансе, так как снимок был получен до выполнения INSERT.
Зафиксируйте транзакцию:
```
student@student=*> COMMIT;
```
```
COMMIT
```
```
student@student=> SELECT * FROM stab;
```
```
    msg
-------------
 Обновление
 Еще вставка
(2 rows)
```
Снимок, сделанный во время работы транзакции, больше не существует, поэтому данные стали видны.

Блокировки

В первом сеансе запустите транзакцию и измените UPDATE строку «Обновление», переведя ее в верхний регистр:

student@student=> BEGIN;

BEGIN

student@student=*> UPDATE stab SET msg = upper(msg) WHERE msg ~ '^Об';

UPDATE 1

student@student=*> SELECT * FROM stab;

    msg
-------------
 Еще вставка
 ОБНОВЛЕНИЕ
(2 rows)

Во втором сеансе проверьте, блокируется ли чтение таблицы:
```
student@student=> SELECT * FROM stab;
```
```
    msg
-------------
 Обновление
 Еще вставка
(2 rows)
```
Чтение не блокируется изменением строк.

Во втором сеансе выполните очистку командой VACUUM:

student@student=> VACUUM VERBOSE stab;

INFO:  vacuuming "student.public.stab"
INFO:  finished vacuuming "student.public.stab": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total)
tuples: 1 removed, 2 remain, 0 are dead but not yet removable, oldest xmin: 800
removable cutoff: 800, which was 1 XIDs old when operation ended
new relfrozenxid: 798, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 36.722 MB/s, avg write rate: 36.722 MB/s
buffer usage: 17 hits, 4 misses, 4 dirtied
WAL usage: 5 records, 4 full page images, 26909 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
INFO:  vacuuming "student.pg_toast.pg_toast_16391"
INFO:  finished vacuuming "student.pg_toast.pg_toast_16391": index scans: 0
pages: 0 removed, 0 remain, 0 scanned (100.00% of total)
tuples: 0 removed, 0 remain, 0 are dead but not yet removable, oldest xmin: 800
removable cutoff: 800, which was 1 XIDs old when operation ended
new relfrozenxid: 800, which is 5 XIDs ahead of previous value
frozen: 0 pages from table (100.00% of total) had 0 tuples frozen
index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
avg read rate: 35.034 MB/s, avg write rate: 70.067 MB/s
buffer usage: 24 hits, 1 misses, 2 dirtied
WAL usage: 3 records, 2 full page images, 14340 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

Команда очистки не мешает параллельному выполнению запросов.

Во втором терминале выполните команду UPDATE строки «Еще вставка»:
```
student@student=> UPDATE stab SET msg = upper(msg) WHERE msg ~ '^Ещ';
```
```
UPDATE 1
```
```
student@student=> SELECT * FROM stab;
```
```
    msg
-------------
 Обновление
 ЕЩЕ ВСТАВКА
(2 rows)
```
Так как транзакция в первом сеансе изменила строку «Обновление» и она еще не зафиксирована, то эта строка заблокирована. Но все остальные строки — нет.
Попробуйте во втором сеансе обновить строку, которая в данный момент заблокирована транзакцией в первом сеансе:
```
student@student=> UPDATE stab SET msg = upper(msg) WHERE msg ~ '^Об';
```
Команда «зависла» и не выполняется. На самом деле она ожидает освобождения блокировки, удерживаемой транзакцией в первом сеансе.
Зафиксируйте транзакцию в первом сеансе. При этом во втором сеансе команда UPDATE выполнит свою работу.

В первом терминале:
```
student@student=*> COMMIT;
```
```
COMMIT
```
Во втором терминале:
```
UPDATE 0
```

Очистка

В первом терминале запустите транзакцию, а в ней выполните команду SELECT:

student@student=> BEGIN;

BEGIN

student@student=*> SELECT * FROM stab;

    msg
-------------
 ОБНОВЛЕНИЕ
 ЕЩЕ ВСТАВКА
(2 rows)

Во втором терминале выполните команду VACUUM FULL для таблицы:
```
student@student=> VACUUM FULL stab;
```
Команда не завершается. VACUUM FULL в отличие от обычного VACUUM требует эксклюзивной блокировки таблицы, так как она перестраивает таблицу заново. Запрос чтения в первом сеансе удерживает блокировку, которая препятствует выполнению VACUUM FULL.
Завершите транзакцию и проверьте, отработает ли VACUUM FULL.

В первом терминале:
```
student@student=*> END;
```
```
COMMIT
```
Во втором терминале:
```
VACUUM
```
После освобождения блокировки, наложенной командой SELECT, команда очистки VACUUM FULL отработала и вернула командную строку.

На этом лабораторную работу можно считать завершенной.

Самопроверка

Вопрос 1

Открыто два сеанса psql. Первый сеанс обслуживается процессом с pid=13646, второй — процессом с pid=13917. У каждого из сеансов в начале приглашения указывается pid обслуживающего процесса. В указанных сеансах последовательно выполняются следующие команды:

13646 test_db=# BEGIN;
BEGIN

13646 test_db=*# CREATE TABLE test_table AS SELECT 1 id;
SELECT 1

13646 test_db=*# UPDATE test_table SET id=2;
UPDATE 1

13917 test_db=# SELECT id FROM test_table;

Каким будет результат выполнения последнего запроса?

Одна строка с id = 1Одна строка с id = 2Ошибка выполнения запроса

Вопрос 2

Имеется таблица с данными test_table:

test_db=# \d test_table
Таблица "public.test_table"
Столбец |   Тип   | Правило сортировки | Допустимость NULL | По умолчанию
---------+---------+--------------------+-------------------+--------------
id      | integer |                    |                   |

test_db=# SELECT * FROM test_table;
id
----
1
(1 строка)

Открыто два сеанса psql. Первый сеанс обслуживается процессом с pid=13646, второй — процессом с pid=13917.

У каждого из сеансов в начале приглашения указывается pid обслуживающего процесса.

В указанных сеансах последовательно выполняются следующие команды:

13646 test_db=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

13646 test_db=*# SELECT id FROM test_table;
id
----
1
(1 строка)

13917 test_db=# BEGIN;
BEGIN

13917 test_db=*# UPDATE test_table SET id=2;
UPDATE 1

13917 test_db=*# COMMIT;
COMMIT

13646 test_db=*# SELECT id FROM test_table;

Какое значение id будет получено в результате выполнения последнего запроса?

Вопрос 3

13646 test_db=# BEGIN;
BEGIN

13646 test_db=*#update test_table set id=3;
UPDATE 1

13646 test_db=*# SELECT pg_current_xact_id();
pg_current_xact_id
--------------------
795
(1 строка)

13917 test_db=# BEGIN;
BEGIN

13917 test_db=# SELECT pg_current_xact_id();
pg_current_xact_id
--------------------
796
(1 строка)

13917 test_db=# SELECT id, xmin, xmax FROM test_table;
id | xmin | xmax
----+------+------
2 |  794 |  795
(1 строка)

Транзакция с каким номером заблокировала единственную строку в таблице test_table?

794795796Строка не заблокирована

Вопрос 4

Имеется таблица с данными test_table:

test_db=# \d test_table
Таблица "public.test_table"
Столбец |   Тип   | Правило сортировки | Допустимость NULL | По умолчанию
---------+---------+--------------------+-------------------+--------------
id      | integer |                    |                   |

test_db=# SELECT * FROM test_table;
id
----
1
(1 строка)

Открыто два сеанса psql. Первый сеанс обслуживается процессом с pid=13646, второй — процессом с pid=13917.

У каждого из сеансов в начале приглашения указывается pid обслуживающего процесса.

В указанных сеансах последовательно выполняются следующие команды:

13646 test_db=# BEGIN;
BEGIN

13917 test_db=# BEGIN;
BEGIN

13917 test_db=*# DROP TABLE test_table;
DROP TABLE

13646 test_db=*# SELECT id FROM test_table;

Что произойдет после вызова последней команды?

Выдана одна строка с id = 1Приостановка выполнения запросаВыдана ошибка выполнения запроса

Практика. Транзакции​

Подготовительный этап​

Основной этап​

Откат при ошибке в командах транзакции​

Версии строк​

Уровни изоляции​

Блокировки​

Очистка​

Самопроверка​