Транзакции в Postgresql
Общие понятия
Транзакция - это несколько операций над БД, которые образуют логически цельную процедуру и могут быть выполнены вместе, либо не будет выполнена ни одна из операций. В случае если не будет выполнена хотя бы одна операция в составе транзакции, будет откат(rollback) всей транзакции в исходное состояние.
Транзакция - это средство согласованности и непротиворечивости, гарантия того, что все запланировнные операции будут выполнены или не выполнены вовсе
Транзакция СУБД основана на многоверсионной модели MVCC.
Версии - так называемые снимки данных (snapshots) согласованное состояние (версия) базы данных, на определенный момент времени. Параллельно выполняемые транзакции не нарушают согласованности данных этих снимков.
Согласованность параллельных транзакций достигается за счет того, что когда параллельные транзакции изменяют одни и те же строки таблиц, тогда создаются отдельные версии этих строк, доступные соответствующим транзакциям.
Однако такой подход требует больше дискового пространства и оперативной памяти.
При этом операции чтения не блокируются операциями записи, в свою очередь операции записи не блокируются операциями чтения.
Свойства транзакции ACID
Атомарность(atomicity) - означает, что будут выполнены все операции в составе транзакции, но если хоть одна операция не будет выполнена, тогда произойдет откат всех операций текущей транзакции в исходное состояние(снимок).
Согласованность (consistency) - означает, что после успешного выполнения транзакции, согласованное состояние будет переведено в другое согласованное состояние.
Изолированность (isolation) - во время выполнения транзакции, другие транзакции не должны оказывать вляния на нее.
Долговечность (durability) - гарантия что после выполнения транзакции, данные надежно сохранены и при необходимости могут быть извлечены, независомо от сбоев в работе.
При параллельном выполнении транзакции возможны следующие ситуации:
1. Потерянное обновление ( lost update ), происходит, когда разные транзакции обновляют одни и те же данные, одна за другой.
2. Грязное чтение ( dirty read ), когда транзакция читает данные в тот момент, когда другая транзакция не завершила изменение данных. При отмене транзакции, пользователь будеть читать данные которых нет.
3. Не повторяющееся чтение ( non-repeatable read ), когда при повторном чтении в рамках одной транзакции, другая транзакция уже изменила эти данные и при повторном запросе на чтение, результат будет другой.
4. Фантомное чтение (phantom read), когда в промежутке между двумя транзакциями чтения происходит запрос на изменение.
5. Аномальная сериализация ( serialization anomaly ). Результат группы фиксаций выполняющихся параллельно, не совпадает, как если бы они выполнялись в нужной последовательности, иначе говоря, сачала должна выполняться фиксация A , затем B.
По умолчанию (без BEGIN) PostgreSQL выполняет транзакции в режиме «autocommit» (автофиксация), каждый оператор выполняется в своей транзакции, которая неявно фиксируется в конце оператора
В блоке транзакции операторы выполняются быстрее, так как для запуска и фиксации транзакции производится масса операций, которые нагружают процессор и диск.
START TRANSACTION делает то же, что и BEGIN
Статус транзакций ( clog )
Для работы многоверсионности серверу надо понимать в каком статусе находится та или иная транзакция ( активна или завершена )
Для статуса транзакции выделяется два бита и хранятся в специальных служебных файлах, в каталоге PG_DATA/pg_xact
Если прерванная транзакция успела создать новые версии строк, эти версии не уничтожаются и другие транзакции видят, что транзакция создавшая или удалившая версии строк, на самом деле прервана, поэтому не станут принимать ее изменения во внимание
Уровни изоляции
1. Read Uncommitted - по стандарт на этом уровне допускается чтение грязных незафиксированных данных, однако в Postgresql требование более жесткие, и чтение не зафиксированных данных на этом уровне не допускается.
2. Read Committed - на этом уровне не допускается чтение грязных незафиксированных данных. Транзакция чтения может читать только те незафиксированные данные, которые были сделаны во время ее выполнения
3. Repeatable Read - не допускается чтение грязных (незафиксированных данных), не повторяющееся чтение, фантомное чтение
4. Serializable - не допускается сериализация и не один из вышеперечисленных требований ( соблюдаются все уровни )
Уровень изоляции обеспечивается СУБД. Требуемый уровень можно установить в начале транзакции.
По умолчанию используется уровень изолиции: Read Committed
Посмотреть текущий уровень изоляции:
~# sudo -u postgres psql -c "SHOW default_transaction_isolation;" -x megabase
-[ RECORD 1 ]-----------------+---------------
default_transaction_isolation | read committed
Read Uncommitted
Для наглядного примера уровня изоляции Read Uncommitted, проверим видит ли одна транзакция незафиксированные изменения второй транзакцией
Для теста создать таблицу:
demo=# CREATE TABLE aircrafts_tmp AS SELECT * FROM aircrafts;
SELECT 9
Запустить два терминала
На первом терминале выполнить команды:
demo=# BEGIN;
BEGIN
demo=# SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET
demo=# SHOW transaction_isolation;
transaction_isolation
-----------------------
read uncommitted
(1 row)
demo=# UPDATE aircrafts_tmp
demo-# SET range = range + 100
demo-# WHERE aircraft_code = 'SU9';
UPDATE 1
demo=# SELECT * FROM aircrafts_tmp WHERE aircraft_code = 'SU9';
aircraft_code | model | range
---------------+---------------------+-------
SU9 | Сухой Суперджет-100 | 3100
(1 row)
Не завершая первую, начнем выполнение транзакции на втором терминале:
demo=# BEGIN;
BEGIN
demo=# SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SET
demo=# SELECT * FROM aircrafts_tmp WHERE aircraft_code = 'SU9';
aircraft_code | model | range
---------------+---------------------+-------
SU9 | Сухой Суперджет-100 | 3000
(1 row)
По результату запроса SELECT видно, что значение range осталось прежним, так как транзакция в первом запросе еще не завершена
Для отката транзакций в исходное состояние используется команду ROLLBACK
demo=# BEGIN;
BEGIN
demo=# SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET
demo=# SHOW transaction_isolation;
transaction_isolation
-----------------------
read uncommitted
(1 row)
demo=# ROLLBACK;
ROLLBACK
demo=# SHOW transaction_isolation;
transaction_isolation
-----------------------
read committed
(1 row)
Для фиксации транзакции используется команда END
demo=# END;
COMMIT
Read Commited
Установлен в Postgresql по умолчанию
На этом уровне запрещено чтение незафиксированных данных, гарантируется отсутствие потерянных обновлений, возможно неповторяющееся чтение данных.
Для экспримента будут использоваться все таже тестовая таблица и два терминала
В первом терминале увеличивается значение range на 100 км, во втором терминале на 200 км
demo=# BEGIN ISOLATION LEVEL READ COMMITTED ;
BEGIN
demo=# SHOW transaction_isolation ;
transaction_isolation
-----------------------
read committed
(1 row)
demo=# UPDATE aircrafts_tmp SET range = range + 100 WHERE aircraft_code = 'SU9'
UPDATE 1
demo=# SELECT * FROM aircrafts_tmp WHERE aircraft_code = 'SU9';
aircraft_code | model | range
---------------+---------------------+-------
SU9 | Сухой Суперджет-100 | 3100
(1 row)
В коде выше, уровень изоляции указывается без использования SET, начиная с команды BEGIN
Транзакция не фиксируется, переходим во второй терминал
demo=# BEGIN;
BEGIN
demo=# UPDATE aircrafts_tmp SET range = range + 200 WHERE aircraft_code = 'SU9';
После запуска команды, команда не завершит UPDATE изменение, ожидая, когда будет зафиксирована транзакция в первом терминале выполняющая операцию над тем же атрибутом, той же таблицы.
Команда будет находиться в режиме ожидания команды ROLLBACK или COMMIT в первом терминале
Завершим выполнение транзакции в терминале 1 командой COMMIT после выполнения которой будет видно, что операция UPDATE завершит свое выполнение во втором терминале, в котором так же завершим транзакцию эквивалентной командой END
Неповторяющееся чтение
В первом терминале:
demo=# BEGIN ;
BEGIN
demo=# SELECT * FROM bookings.aircrafts_tmp ;
aircraft_code | model | range
---------------+---------------------+-------
773 | Boeing 777-300 | 11100
763 | Boeing 767-300 | 7900
SU9 | Sukhoi SuperJet-100 | 3000
320 | Airbus A320-200 | 5700
321 | Airbus A321-200 | 5600
319 | Airbus A319-100 | 6700
733 | Boeing 737-300 | 4200
CN1 | Cessna 208 Caravan | 1200
CR2 | Bombardier CRJ-200 | 2700
(9 rows)
Во втором терминале и завершить транзакцию:
demo=# BEGIN ;
BEGIN
demo=# DELETE FROM bookings.aircrafts_tmp WHERE model ~ '^Boe';
DELETE 3
demo=# SELECT * FROM bookings.aircrafts_tmp ;
aircraft_code | model | range
---------------+---------------------+-------
SU9 | Sukhoi SuperJet-100 | 3000
320 | Airbus A320-200 | 5700
321 | Airbus A321-200 | 5600
319 | Airbus A319-100 | 6700
CN1 | Cessna 208 Caravan | 1200
CR2 | Bombardier CRJ-200 | 2700
(6 rows)
demo=# END ;
COMMIT
В первом терминале чтение покажет изменение во второй транзакции:
demo=# SELECT * FROM bookings.aircrafts_tmp ;
aircraft_code | model | range
---------------+---------------------+-------
SU9 | Sukhoi SuperJet-100 | 3000
320 | Airbus A320-200 | 5700
321 | Airbus A321-200 | 5600
319 | Airbus A319-100 | 6700
CN1 | Cessna 208 Caravan | 1200
CR2 | Bombardier CRJ-200 | 2700
(6 rows)
Таким образом вторая транзакция завершилась в момент времени между двумя запросами первой транзакции, тем самым доказывая, что неповторяющееся чтения соблюдается
Repeatable Read
Repeatable Read - неповторяющееся чтение данных, также на этом уровне не допускается повторение фантомных строк
Приложения использующие данные уровень изоляции должны быть готовы выполнить транзакцию повторно
На этом уровне транзакция делает снимок данных только перед выполнением первого запроса текущей транзакции, поэтому такая транзакции не может изменить строки, которые были изменены другими транзакциями, которые уже завершились после создания снимка.
Другими словами на этом уровне изоляции Postgresql не позволит завершить транзакцию, которая пытается изменить уже измененные строки.
На этом уровне, повторный запуск транзакции нужен если необходимо изменить уже измененные строки, для чтения эти требования не соблюдаются.
В первом термнале:
demo=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
BEGIN
demo=# SELECT * FROM bookings.aircrafts_tmp ;
aircraft_code | model | range
---------------+---------------------+-------
SU9 | Sukhoi SuperJet-100 | 3000
320 | Airbus A320-200 | 5700
321 | Airbus A321-200 | 5600
319 | Airbus A319-100 | 6700
CN1 | Cessna 208 Caravan | 1200
CR2 | Bombardier CRJ-200 | 2700
(6 rows)
Во втором терминале:
COMMIT
demo=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
BEGIN
demo=# INSERT INTO bookings.aircrafts_tmp VALUES ( 'IL9', 'Ilyushin IL96', 9800 );
INSERT 0 1
demo=# UPDATE bookings.aircrafts_tmp SET range = range + 100
demo-# WHERE bookings.aircarts_code = '320' ;
UPDATE 0 1
demo=# END;
Если повторить тот же запрос чтения, изменений после выполнения второй транзакции не видно
demo=# SELECT * FROM bookings.aircrafts_tmp ;
aircraft_code | model | range
---------------+---------------------+-------
SU9 | Sukhoi SuperJet-100 | 3000
320 | Airbus A320-200 | 5700
321 | Airbus A321-200 | 5600
319 | Airbus A319-100 | 6700
CN1 | Cessna 208 Caravan | 1200
CR2 | Bombardier CRJ-200 | 2700
(6 rows)
Данные останутся актуальны только на момент выполнения первого и единственного снимка текущей ( в первом терминале ) транзакции
Поэтому завершим транзакцию в первом терминале и повторим запрос, после чего увидим изменения
Ошибки сериализации
На первом терминале:
demo=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
BEGIN
demo=# UPDATE bookings.aircrafts_tmp
SET range = range + 100
WHERE aircraft_code = '320';
UPDATE 1
То же самое попытаемся сделать во втором терминале:
demo=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
BEGIN
demo=# UPDATE bookings.aircrafts_tmp SET range = range + 100 WHERE aircraft_code = '320';
Но команда не завершится, так как будет ждать завершения изменения той же строки в транзакции первого терминала
Завершим транзакцию в первом терминале и посмотрим на ошибку во втром терминале
demo=# UPDATE bookings.aircrafts_tmp SET range = range + 100 WHERE aircraft_code = '320';
ERROR: could not serialize access due to concurrent update
Выполнение команды завершится ошибкой, так как транзакция во втором терминале видит, что строка уже была изменена, но на уровне изоляции REPEATABLE READ снимок данных создается только на момент выполнения первого запроса транзакции и новая версия строки не считывается и если уже изменилась в другой транзакции, не сможет измениться в текущей транзакции
SAVEPOINT
SAVEPOINT — определяет точку сохранения в текущей транзакции
Точка сохранения — это отметка внутри транзакции, которая позволяет откатить все команды, выполненные после неё
Для отката к установленной точке сохранения предназначена команда ROLLBACK TO SAVEPOINT
Чтобы уничтожить точку сохранения, сохраняя изменения, произведённые после того, как она была установлена, применяется команда RELEASE SAVEPOINT
testdb=# \d test_table
Table "public.test_table"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
testdb=#
testdb=#
testdb=# BEGIN ;
BEGIN
testdb=*#
testdb=*# INSERT INTO test_table VALUES ( 2, 'test1' );
INSERT 0 1
testdb=*#
testdb=*# SAVEPOINT my_test;
SAVEPOINT
testdb=*#
testdb=*# INSERT INTO test_table VALUES ( 3, 'test2' );
INSERT 0 1
testdb=*#
testdb=*# ROLLBACK TO SAVEPOINT my_test;
ROLLBACK
testdb=*#
testdb=*# SELECT * FROM test_table ;
id | name
----+-------
1 | test
2 | test1
(2 rows)
testdb=*#
testdb=*# INSERT INTO test_table VALUES ( 4, 'test3' );
INSERT 0 1
testdb=*#
testdb=*# COMMIT ;
COMMIT
testdb=#
testdb=# SELECT * FROM test_table ;
id | name
----+-------
1 | test
2 | test1
4 | test3
(3 rows)
testdb=#
Управление транзакциями
По умолчанию psql работает в режиме автофиксации
При отключении режима автофиксации, необходимо самостоятельно вводить команды BEGIN и COMMIT
testdb=# \set AUTOCOMMIT off
testdb=# \echo :AUTOCOMMIT
off
testdb=# \set AUTOCOMMIT on
testdb=# \echo :AUTOCOMMIT
on
Источники
Здесь пока нет комментариев