Учебное пособие СанктПетербург бхвпетербург

• Название: Учебное пособие СанктПетербург бхвпетербург
• Дата: 12.02.2023
• Размер: 1.88 Mb.
• Имя файла: sql_primer.pdf
• Тип: Учебное пособие; #933464
• страница: 15 из 20

Глава 9
Транзакции
Детальное понимание механизмов выполнения транзакций придет с опытом. В этой главе мы дадим самое первое представление об этом важном и мощном инструменте, которым обладают все серьез- ные СУБД, включая PostgreSQL.
9.1. Общая информация
Транзакция — это совокупность операций над базой данных, которые вместе образу- ют логически целостную процедуру, и могут быть либо выполнены все вместе, либо не будет выполнена ни одна из них. В простейшем случае транзакция состоит из од- ной операции.
Транзакции являются одним из средств обеспечения согласованности (непротиворе- чивости) базы данных, наряду с ограничениями целостности (constraints), наклады- ваемыми на таблицы. Транзакция переводит базу данных из одного согласованного состояния в другое согласованное состояние.
В качестве примера транзакции в базе данных «Авиаперевозки» можно привести процедуру бронирования билета. Она будет включать операции INSERT, выполня- емые над таблицами «Бронирования» (bookings), «Билеты» (tickets) и «Переле- ты» (ticket_flights). В результате выполнения этой транзакции должно обеспечи- ваться следующее соотношение: значение атрибута total_amount в строке таблицы bookings должно быть равно сумме значений атрибута amount в строках таблицы ticket_flights, связанных с этой строкой таблицы bookings. Если операции дан- ной транзакции будут выполнены частично, тогда может оказаться, например, что общая сумма бронирования будет не равна сумме стоимостей перелетов, включен- ных в это бронирование. Очевидно, что это несогласованное состояние базы данных.
Транзакция может иметь два исхода: первый — изменения данных, произведенные в ходе ее выполнения, успешно зафиксированы в базе данных, а второй исход таков —
транзакция отменяется, и отменяются все изменения, выполненные в ее рамках. От- мена транзакции называется откатом (rollback).
255

Глава 9. Транзакции
Сложные информационные системы, как правило, предполагают одновременную ра- боту многих пользователей с базой данных, поэтому современные СУБД предлагают специальные механизмы для организации параллельного, т. е. одновременного, вы- полнения транзакций. Реализованы такие механизмы и в PostgreSQL.
Реализация транзакций в СУБД PostgreSQL основана на многоверсионной модели
(Multiversion Concurrency Control, MVCC). Эта модель предполагает, что каждый SQL- оператор видит так называемый снимок данных (snapshot), т. е. то согласованное состояние (версию) базы данных, которое она имела на определенный момент вре- мени. При этом параллельно исполняемые транзакции, даже вносящие изменения в базу данных, не нарушают согласованности данных этого снимка. Такой результат в PostgreSQL достигается за счет того, что когда параллельные транзакции изменяют одни и те же строки таблиц, тогда создаются отдельные версии этих строк, доступ- ные соответствующим транзакциям. Это позволяет ускорить работу с базой данных,
однако требует больше дискового пространства и оперативной памяти. И еще одно важное следствие применения MVCC — операции чтения никогда не блокируются операциями записи, а операции записи никогда не блокируются операциями чтения.
Согласно теории баз данных транзакции должны обладать следующими свойствами:
1. Атомарность (atomicity). Это свойство означает, что либо транзакция будет за- фиксирована в базе данных полностью, т. е. будут зафиксированы результаты выполнения всех ее операций, либо не будет зафиксирована ни одна операция транзакции.
2. Согласованность (consistency). Это свойство предписывает, чтобы в результате успешного выполнения транзакции база данных была переведена из одного со- гласованного состояния в другое согласованное состояние.
3. Изолированность (isolation). Во время выполнения транзакции другие транзак- ции должны оказывать по возможности минимальное влияние на нее.
4. Долговечность (durability). После успешной фиксации транзакции пользователь должен быть уверен, что данные надежно сохранены в базе данных и впослед- ствии могут быть извлечены из нее, независимо от последующих возможных сбоев в работе системы.
Для обозначения всех этих четырех свойств используется аббревиатура ACID.
При параллельном выполнении транзакций возможны следующие феномены:
1. Потерянное обновление (lost update). Когда разные транзакции одновременно изменяют одни и те же данные, то после фиксации изменений может оказаться,
256

9.1. Общая информация
что одна транзакция перезаписала данные, обновленные и зафиксированные другой транзакцией.
2. «Грязное» чтение (dirty read). Транзакция читает данные, измененные парал- лельной транзакцией, которая еще не завершилась. Если эта параллельная транзакция в итоге будет отменена, тогда окажется, что первая транзакция про- читала данные, которых нет в системе.
3. Неповторяющееся чтение (non-repeatable read). При повторном чтении тех же самых данных в рамках одной транзакции оказывается, что другая транзакция успела изменить и зафиксировать эти данные. В результате тот же самый запрос выдает другой результат.
4. Фантомное чтение (phantom read). Транзакция повторно выбирает множество строк в соответствии с одним и тем же критерием. В интервале времени меж- ду выполнением этих выборок другая транзакция добавляет новые строки и успешно фиксирует изменения. В результате при выполнении повторной вы- борки в первой транзакции может быть получено другое множество строк.
5. Аномалия сериализации (serialization anomaly). Результат успешной фиксации группы транзакций, выполняющихся параллельно, не совпадает с результатом ни одного из возможных вариантов упорядочения этих транзакций, если бы они выполнялись последовательно.
Перечисленные феномены, а также ситуации, в которых они имеют место, будут рас- смотрены подробно и проиллюстрированы примерами.
Поясним кратко, в чем состоит смысл концепции сериализации. Для двух транзак- ций, скажем, A и B, возможны только два варианта упорядочения при их последова- тельном выполнении: сначала A, затем B или сначала B, затем A. Причем результаты реализации двух вариантов могут в общем случае не совпадать. Например, при вы- полнении двух банковских операций — внесения некоторой суммы денег на какой-то счет и начисления процентов по этому счету — важен порядок выполнения операций.
Если первой операцией будет увеличение суммы на счете, а второй — начисление процентов, тогда итоговая сумма будет больше, чем при противоположном порядке выполнения этих операций. Если описанные операции выполняются в рамках двух различных транзакций, то оказываются возможными различные итоговые результа- ты, зависящие от порядка их выполнения.
Сериализация двух транзакций при их параллельном выполнении означает, что полу- ченный результат будет соответствовать одному из двух возможных вариантов упоря- дочения транзакций при их последовательном выполнении. При этом нельзя сказать точно, какой из вариантов будет реализован.
257

Глава 9. Транзакции
Если распространить эти рассуждения на случай, когда параллельно выполняется бо- лее двух транзакций, тогда результат их параллельного выполнения также должен быть таким, каким он был бы в случае выбора некоторого варианта упорядочения транзакций, если бы они выполнялись последовательно, одна за другой. Конечно,
чем больше транзакций, тем больше вариантов их упорядочения. Концепция сериа- лизации не предписывает выбора какого-то определенного варианта. Речь идет лишь об одном из них.
В том случае, если СУБД не сможет гарантировать успешную сериализацию группы параллельных транзакций, тогда некоторые из них могут быть завершены с ошибкой.
Эти транзакции придется выполнить повторно.
Для конкретизации степени независимости параллельных транзакций вводится по- нятие уровня изоляции транзакций. Каждый уровень характеризуется перечнем тех феноменов, которые на данном уровне не допускаются.
Всего в стандарте SQL предусмотрено четыре уровня. Каждый более высокий уровень включает в себя все возможности предыдущего.
1. Read Uncommitted. Это самый низкий уровень изоляции. Согласно стандарту
SQL на этом уровне допускается чтение «грязных» (незафиксированных) дан- ных. Однако в PostgreSQL требования, предъявляемые к этому уровню, более строгие, чем в стандарте: чтение «грязных» данных на этом уровне не допуска- ется.
2. Read Committed. Не допускается чтение «грязных» (незафиксированных) дан- ных. Таким образом, в PostgreSQL уровень Read Uncommitted совпадает с уров- нем Read Committed. Транзакция может видеть только те незафиксированные изменения данных, которые произведены в ходе выполнения ее самой.
3. Repeatable Read. Не допускается чтение «грязных» (незафиксированных) дан- ных и неповторяющееся чтение. В PostgreSQL на этом уровне не допускается также фантомное чтение. Таким образом, реализация этого уровня является бо- лее строгой, чем того требует стандарт SQL. Это не противоречит стандарту.
4. Serializable. Не допускается ни один из феноменов, перечисленных выше, в том числе и аномалии сериализации.
Конкретный уровень изоляции обеспечивает сама СУБД с помощью своих внутрен- них механизмов. Его достаточно указать в команде при старте транзакции. Одна- ко программист может дополнительно использовать некоторые операторы и прие- мы программирования, например, устанавливать блокировки на уровне отдельных строк или всей таблицы. Это будет показано в конце главы.
258

9.2. Уровень изоляции Read Uncommitted
По умолчанию PostgreSQL использует уровень изоляции Read Committed.
SHOW default_transaction_isolation;
default_transaction_isolation
------------------------------- read committed
(1 строка)
9.2. Уровень изоляции Read Uncommitted
Давайте начнем рассмотрение с уровня изоляции Read Uncommitted. Проверим, ви- дит ли транзакция те изменения данных, которые были произведены в другой тран- закции, но еще не были зафиксированы, т. е. «грязные» данные.
Для проведения экспериментов воспользуемся таблицей «Самолеты» (aircrafts).
Но можно создать копию этой таблицы, чтобы при удалении строк из нее не удаля- лись строки из таблицы «Места» (seats), связанные по внешнему ключу со строками из таблицы aircrafts.
CREATE TABLE aircrafts_tmp
AS SELECT * FROM aircrafts;
SELECT 9
Для организации выполнения параллельных транзакций с использованием утилиты psql будем запускать ее на двух терминалах.
Итак, для изучения уровня изоляции Read Uncommitted проделаем следующие экспе- рименты.
На первом терминале выполним следующие команды:
BEGIN;_BEGINSET_TRANSACTION_ISOLATION_LEVEL_READ_UNCOMMITTED;_SET259__Глава_9._Транзакции_SHOW_transaction_isolation;'>BEGIN;
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET
259

Глава 9. Транзакции
SHOW transaction_isolation;
transaction_isolation
----------------------- read uncommitted
(1 строка)
UPDATE aircrafts_tmp
SET range = range + 100
WHERE aircraft_code = 'SU9';
UPDATE 1
SELECT *
FROM aircrafts_tmp
WHERE aircraft_code = 'SU9';
aircraft_code |
model
| range
---------------+---------------------+-------
SU9
| Sukhoi SuperJet-100 | 3100
(1 строка)
Начнем транзакцию на втором терминале (все, что происходит на втором терминале,
показано на сером фоне):
BEGIN;
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET
SELECT *
FROM aircrafts_tmp
WHERE aircraft_code = 'SU9';
aircraft_code |
model
| range
---------------+---------------------+-------
SU9
| Sukhoi SuperJet-100 | 3000
(1 строка)
Таким образом, вторая транзакция не видит изменение значения атрибута range,
произведенное в первой — незафиксированной — транзакции. Это объясняется тем,
что в PostgreSQL реализация уровня изоляции Read Uncommitted более строгая, чем
260

9.3. Уровень изоляции Read Committed
того требует стандарт языка SQL. Фактически этот уровень тождественен уровню изоляции Read Committed. Поэтому будем считать эксперимент, проведенный для уровня изоляции Read Uncommitted, выполненным и для уровня Read Committed.
Давайте не будем фиксировать произведенное изменение в базе данных, а восполь- зуемся командой ROLLBACK для отмены транзакции, т. е. для ее отката.
На первом терминале:
ROLLBACK;
ROLLBACK
На втором терминале сделаем так же:
ROLLBACK;
ROLLBACK
9.3. Уровень изоляции Read Committed
Теперь обратимся к уровню изоляции Read Committed. Именно этот уровень уста- новлен в PostgreSQL по умолчанию. Мы уже показали, что на этом уровне изоляции не допускается чтение незафиксированных данных. А сейчас покажем, что на этом уровне изоляции также гарантируется отсутствие потерянных обновлений, но воз- можно неповторяющееся чтение данных.
Опять будем работать на двух терминалах. В первой транзакции увеличим значение атрибута range для самолета Sukhoi SuperJet-100 на 100 км, а во второй транзакции —
на 200 км. Проверим, какое из этих двух изменений будет записано в базу данных.
На первом терминале выполним следующие команды:
BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN
SHOW transaction_isolation;
transaction_isolation
----------------------- read committed
(1 строка)
261

Глава 9. Транзакции
UPDATE aircrafts_tmp
SET range = range + 100
WHERE aircraft_code = 'SU9';
UPDATE 1
SELECT *
FROM aircrafts_tmp
WHERE aircraft_code = 'SU9';
aircraft_code |
model
| range
---------------+---------------------+-------
SU9
| Sukhoi SuperJet-100 | 3100
(1 строка)
Мы видим, что в первой транзакции значение атрибута range было успешно изме- нено, хотя пока и не зафиксировано. Но транзакция видит изменения, выполненные в ней самой.
Обратите внимание, что вместо использования команды SET TRANSACTION мы про- сто включили указание уровня изоляции непосредственно в команду BEGIN. Эти два подхода равносильны. Конечно, когда речь идет об использовании уровня изоляции
Read Committed, принимаемого по умолчанию, можно вообще ограничиться только командой BEGIN без дополнительных ключевых слов.
На втором терминале так и сделаем. Во второй транзакции попытаемся обновить эту же строку таблицы airctafts_tmp, но для того, чтобы впоследствии разобраться,
какое из изменений прошло успешно и было зафиксировано, добавим к значению атрибута range не 100, а 200.
BEGIN;_BEGIN263__Глава_9._Транзакции_SELECT_*_FROM_aircrafts_tmp;'>BEGIN;
BEGIN
UPDATE aircrafts_tmp
SET range = range + 200
WHERE aircraft_code = 'SU9';
И вот мы видим, что команда UPDATE во второй транзакции не завершилась, а пере- шла в состояние ожидания. Это ожидание продлится до тех пор, пока не завершится первая транзакция. Дело в том, что команда UPDATE в первой транзакции заблоки- ровала строку в таблице airctafts_tmp, и эта блокировка будет снята только при завершении транзакции либо с фиксацией изменений с помощью команды COMMIT,
либо с отменой изменений по команде ROLLBACK.
262

9.3. Уровень изоляции Read Committed
Давайте завершим первую транзакцию с фиксацией изменений:
COMMIT;
COMMIT
Перейдя на второй терминал, мы увидим, что команда UPDATE завершилась:
UPDATE 1
Теперь на втором терминале, не завершая транзакцию, посмотрим, что стало с нашей строкой в таблице aircrafts_tmp:
SELECT *
FROM aircrafts_tmp
WHERE aircraft_code = 'SU9';
aircraft_code |
model
| range
---------------+---------------------+-------
SU9
| Sukhoi SuperJet-100 | 3300
(1 строка)
Как видно, были произведены оба изменения. Команда UPDATE во второй транзак- ции, получив возможность заблокировать строку после завершения первой транзак- ции и снятия ею блокировки с этой строки, перечитывает строку таблицы и потому обновляет строку, уже обновленную в только что зафиксированной транзакции. Та- ким образом, эффекта потерянных обновлений не возникает.
Завершим транзакцию на втором терминале, но вместо команды COMMIT воспользу- емся эквивалентной командой END, которая является расширением PostgreSQL:
END;
COMMIT
Если вы самостоятельно проведете только что выполненный эксперимент, выбрав уровень изоляции Read Uncommitted, то увидите, что и на этом — самом низком —
уровне изоляции эффекта потерянных обновлений также не возникает.
Для иллюстрации эффекта неповторяющегося чтения данных проведем совсем про- стой эксперимент также на двух терминалах. На первом терминале:
BEGIN;
BEGIN
263

Глава 9. Транзакции
SELECT * FROM aircrafts_tmp;
aircraft_code |
model
| range
---------------+---------------------+-------
773
| Boeing 777-300
| 11100 763
| Boeing 767-300
| 7900 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
SU9
| Sukhoi SuperJet-100 | 3300
(9 строк)
На втором терминале:
BEGIN;
BEGIN
DELETE FROM aircrafts_tmp
WHERE model

'^Boe';
DELETE 3
SELECT * FROM aircrafts_tmp;
aircraft_code |
model
| range
---------------+---------------------+-------
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
SU9
| Sukhoi SuperJet-100 | 3300
(6 строк)
Сразу завершим вторую транзакцию:
END;
COMMIT
264

9.4. Уровень изоляции Repeatable Read
Повторим выборку в первой транзакции:
SELECT * FROM aircrafts_tmp;
aircraft_code |
model
| range
---------------+---------------------+-------
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
SU9
| Sukhoi SuperJet-100 | 3300
(6 строк)
Видим, что теперь получен другой результат, т. к. вторая транзакция завершилась в момент времени между двумя запросами. Таким образом, налицо эффект неповто- ряющегося чтения данных, который является допустимым на уровне изоляции Read
Committed.
Завершим и первую транзакцию: