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

Глава 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.
Завершим и первую транзакцию:

Глава 9. Транзакции
Важно помнить, что повторный запуск может потребоваться только для транзакций,
которые вносят изменения в данные. Для транзакций, которые только читают дан- ные, повторный запуск никогда не требуется.
На первом терминале:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
Сначала посмотрим содержимое таблицы:
SELECT * FROM aircrafts_tmp;
Обратите внимание, что после уже проведенных экспериментов в таблице осталось меньше строк, чем было вначале.
aircraft_code |
model
| range
---------------+---------------------+-------
320
| Airbus A320-200
| 5700 321
| Airbus A321-200
| 5600 319
| Airbus A319-100
| 6700
SU9
| Sukhoi SuperJet-100 | 3300
CN1
| Cessna 208 Caravan | 2100
CR2
| Bombardier CRJ-200 | 1900
(6 строк)
На втором терминале проведем ряд изменений:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
Добавим одну строку и одну строку обновим:
INSERT INTO aircrafts_tmp
VALUES ( 'IL9', 'Ilyushin IL96', 9800 );
INSERT 0 1
UPDATE aircrafts_tmp
SET range = range + 100
WHERE aircraft_code = '320';
UPDATE 1 266

9.4. Уровень изоляции Repeatable Read
END;
COMMIT
Переходим на первый терминал.
SELECT *
FROM aircrafts_tmp;
На первом терминале ничего не изменилось: фантомные строки не видны, и также не видны изменения в уже существующих строках. Это объясняется тем, что снимок данных выполняется на момент начала выполнения первого запроса транзакции.
aircraft_code |
model
| range
---------------+---------------------+-------
320
| Airbus A320-200
| 5700 321
| Airbus A321-200
| 5600 319
| Airbus A319-100
| 6700
SU9
| Sukhoi SuperJet-100 | 3300
CN1
| Cessna 208 Caravan | 2100
CR2
| Bombardier CRJ-200 | 1900
(6 строк)
Завершим первую транзакцию тоже:
END;
COMMIT
А теперь посмотрим, что изменилось в таблице:
SELECT *
FROM aircrafts_tmp;
aircraft_code |
model
| range
---------------+---------------------+-------
321
| Airbus A321-200
| 5600 319
| Airbus A319-100
| 6700
SU9
| Sukhoi SuperJet-100 | 3300
CN1
| Cessna 208 Caravan | 2100
CR2
| Bombardier CRJ-200 | 1900
IL9
| Ilyushin IL96
| 9800 320
| Airbus A320-200
| 5800
(7 строк)
267

Глава 9. Транзакции
Как видим, одна строка добавлена, а значение атрибута range у самолета Airbus
A320-200 стало на 100 больше, чем было. Но до тех пор, пока мы на первом терми- нале находились в процессе выполнения первой транзакции, все эти изменения не были ей доступны, поскольку первая транзакция использовала снимок, сделанный до внесения изменений и их фиксации второй транзакцией.
Теперь покажем ошибки сериализации.
Начнем транзакцию на первом терминале:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
UPDATE aircrafts_tmp
SET range = range + 100
WHERE aircraft_code = '320';
UPDATE 1
На втором терминале попытаемся обновить ту же строку:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
UPDATE aircrafts_tmp
SET range = range + 200
WHERE aircraft_code = '320';
Команда UPDATE на втором терминале ожидает завершения первой транзакции.
Перейдя на первый терминал, завершим первую транзакцию:
END;
COMMIT
Перейдя на второй терминал, увидим сообщение об ошибке:
ОШИБКА: не удалось сериализовать доступ из-за параллельного изменения
Поскольку обновление, произведенное в первой транзакции, не было зафиксировано на момент начала выполнения первого (и, в данном частном случае, единственного)
запроса во второй транзакции, то возникает эта ошибка. Это объясняется вот чем.
При выполнении обновления строки команда UPDATE во второй транзакции видит,
268

9.5. Уровень изоляции Serializable
что строка уже изменена. На уровне изоляции Repeatable Read снимок данных созда- ется на момент начала выполнения первого запроса транзакции и в течение тран- закции уже не меняется, т. е. новая версия строки не считывается, как это делалось на уровне Read Committed. Но если выполнить обновление во второй транзакции без повторного считывания строки из таблицы, тогда будет иметь место потерянное об- новление, что недопустимо. В результате генерируется ошибка, и вторая транзакция откатывается. Мы вводим команду END на втором терминале, но PostgreSQL выпол- няет не фиксацию (COMMIT), а откат:
END;
ROLLBACK
Если выполним запрос, то увидим, что было проведено только изменение в первой транзакции:
SELECT *
FROM aircrafts_tmp
WHERE aircraft_code = '320';
aircraft_code |
model
| range
---------------+-----------------+-------
320
| Airbus A320-200 | 5900
(1 строка)
9.5. Уровень изоляции Serializable
Самый высший уровень изоляции транзакций — Serializable. Транзакции могут ра- ботать параллельно точно так же, как если бы они выполнялись последовательно одна за другой. Однако, как и при использовании уровня Repeatable Read, прило- жение должно быть готово к тому, что придется перезапускать транзакцию, кото- рая была прервана системой из-за обнаружения зависимостей чтения/записи между транзакциями. Группа транзакций может быть параллельно выполнена и успешно зафиксирована в том случае, когда результат их параллельного выполнения был бы эквивалентен результату выполнения этих транзакций при выборе одного из возмож-
ных вариантов
их упорядочения, если бы они выполнялись последовательно, одна за другой.
Для проведения эксперимента создадим специальную таблицу, в которой будет всего два столбца: один — числовой, а второй — текстовый. Назовем эту таблицу modes.
269

Глава 9. Транзакции
CREATE TABLE modes (
num integer,
mode text
);
CREATE TABLE
Добавим в таблицу две строки.
INSERT INTO modes VALUES ( 1, 'LOW' ), ( 2, 'HIGH' );
INSERT 0 2
Итак, содержимое таблицы имеет вид:
SELECT * FROM modes;
num | mode
-----+------
1 | LOW
2 | HIGH
(2 строки)
На первом терминале начнем транзакцию и обновим одну строку из тех двух строк,
которые были показаны в предыдущем запросе.
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
В команде обновления строки будем использовать предложение RETURNING. По- скольку значение поля num не изменяется, то будет видно, какая строка была обнов- лена. Это особенно пригодится во второй транзакции.
UPDATE modes
SET mode = 'HIGH'
WHERE mode = 'LOW'
RETURNING *;
num | mode
-----+------
1 | HIGH
(1 строка)
UPDATE 1
На втором терминале тоже начнем транзакцию и обновим другую строку из тех двух строк, которые были показаны выше.
270

9.5. Уровень изоляции Serializable
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
UPDATE modes
SET mode = 'LOW'
WHERE mode = 'HIGH'
RETURNING *;
num | mode
-----+------
2 | LOW
(1 строка)
UPDATE 1
Изменение, произведенное в первой транзакции, вторая транзакция не видит, по- скольку на уровне изоляции Serializable каждая транзакция работает с тем снимком базы данных, который был сделан непосредственно перед выполнением ее перво- го оператора. Поэтому обновляется только одна строка, та, в которой значение поля mode было равно HIGH изначально.
Обратите внимание, что обе команды UPDATE были выполнены, ни одна из них не ожидает завершения другой транзакции.
Посмотрим, что получилось в первой транзакции:
SELECT * FROM modes;
num | mode
-----+------
2 | HIGH
1 | HIGH
(2 строки)
А во второй транзакции:
SELECT * FROM modes;
num | mode
-----+------
1 | LOW
2 | LOW
(2 строки)
271

Глава 9. Транзакции
Заканчиваем эксперимент. Сначала завершим транзакцию на первом терминале:
COMMIT;
COMMIT
А потом на втором терминале:
COMMIT;
ОШИБКА: не удалось сериализовать доступ из-за зависимостей чтения/записи между транзакциями
ПОДРОБНОСТИ: Reason code: Canceled on identification as a pivot, during commit attempt.
ПОДСКАЗКА: Транзакция может завершиться успешно при следующей попытке.
Какое же изменение будет зафиксировано? То, которое сделала транзакция, первой выполнившая фиксацию изменений.
SELECT * FROM modes;
num | mode
-----+------
2 | HIGH
1 | HIGH
(2 строки)
Таким образом, параллельное выполнение двух транзакций сериализовать не уда- лось. Почему? Если обратиться к определению концепции сериализации, то нужно рассуждать так. Если бы была зафиксирована и вторая транзакция, тогда в таблице modes содержались бы такие строки:
num | mode
-----+------
1 | HIGH
2 | LOW
Но этот результат не соответствует результату выполнения транзакций ни при одном
из двух возможных вариантов их упорядочения, если бы они выполнялись последо- вательно. Следовательно, с точки зрения концепции сериализации эти транзакции невозможно сериализовать.
Покажем это, выполнив транзакции последовательно.
272

9.5. Уровень изоляции Serializable
Предварительно необходимо пересоздать таблицу modes или с помощью команды
UPDATE вернуть ее измененным строкам исходное состояние. Теперь обе транзакции можно выполнять на одном терминале. Первый вариант их упорядочения такой:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
UPDATE modes
SET mode = 'HIGH'
WHERE mode = 'LOW'
RETURNING *;
num | mode
-----+------
1 | HIGH
(1 строка)
UPDATE 1
END;
COMMIT
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
UPDATE modes
SET mode = 'LOW'
WHERE mode = 'HIGH'
RETURNING *;
num | mode
-----+------
2 | LOW
1 | LOW
(2 строки)
UPDATE 2
END;
COMMIT
273

Глава 9. Транзакции
Проверим, что получилось:
SELECT * FROM modes;
num | mode
-----+------
2 | LOW
1 | LOW
(2 строки)
Во втором варианте упорядочения поменяем транзакции местами. Конечно, предва- рительно нужно привести таблицу в исходное состояние.
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
UPDATE modes
SET mode = 'LOW'
WHERE mode = 'HIGH'
RETURNING *;
num | mode
-----+------
2 | LOW
(1 строка)
UPDATE 1
END;
COMMIT
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
UPDATE modes
SET mode = 'HIGH'
WHERE mode = 'LOW'
RETURNING *;
274

9.6. Пример использования транзакций
num | mode
-----+------
1 | HIGH
2 | HIGH
(2 строки)
UPDATE 2
END;
COMMIT
SELECT * FROM modes;
Теперь результат отличается от того, который был получен при реализации первого варианта упорядочения транзакций.
num | mode
-----+------
1 | HIGH
2 | HIGH
(2 строки)
Изменение порядка выполнения транзакций приводит к разным результатам. Одна- ко если бы при параллельном выполнении транзакций была зафиксирована и вторая из них, то полученный результат не соответствовал бы ни одному из продемонстриро- ванных возможных результатов последовательного выполнения транзакций. Таким образом, выполнить сериализацию этих транзакций невозможно. Обратите внима- ние, что вторая команда UPDATE в обоих случаях обновляет не одну строку, а две.
9.6. Пример использования транзакций
Продемонстрируем использование транзакций на примере базы данных «Авиапе- ревозки». Для этого создадим новое бронирование и оформим два билета с двумя перелетами в каждом. Выберем в качестве уровня изоляции Read Committed.
BEGIN;
BEGIN;
275

Глава 9. Транзакции
Сначала добавим запись в таблицу «Бронирования», причем назначим значение по- ля total_amount равным 0. После завершения ввода строк в таблицу «Перелеты»
мы обновим это значение: оно станет равным сумме стоимостей всех забронирован- ных перелетов. В качестве даты бронирования возьмем дату, которая была принята в качестве текущей в базе данных. Эту дату выдает функция now, созданная в схеме bookings.
INSERT INTO bookings ( book_ref, book_date, total_amount )
VALUES ( 'ABC123', bookings.now(), 0 );
INSERT 0 1
Оформим два билета на двух разных пассажиров.
INSERT INTO tickets ( ticket_no, book_ref, passenger_id, passenger_name)
VALUES ( '9991234567890', 'ABC123', '1234 123456', 'IVAN PETROV' );
INSERT 0 1
INSERT INTO tickets ( ticket_no, book_ref, passenger_id, passenger_name)
VALUES ( '9991234567891', 'ABC123', '4321 654321', 'PETR IVANOV' );
INSERT 0 1
Отправим обоих пассажиров по маршруту Москва — Красноярск и обратно.
INSERT INTO ticket_flights
( ticket_no, flight_id, fare_conditions, amount )
VALUES ( '9991234567890', 5572, 'Business', 12500 ),
( '9991234567890', 13881, 'Economy', 8500 );
INSERT 0 2
INSERT INTO ticket_flights
( ticket_no, flight_id, fare_conditions, amount )
VALUES ( '9991234567891', 5572, 'Business', 12500 ),
( '9991234567891', 13881, 'Economy', 8500 );
INSERT 0 2
Подсчитаем общую стоимость забронированных билетов и запишем ее в строку таб- лицы «Бронирования». Конечно, если такая транзакция выполняется в рамках при- кладной программы, то возможно, что подсчет общей суммы будет выполняться в этой программе. Тогда в команде UPDATE уже не потребуется выполнять подзапрос,
а будет использоваться заранее вычисленное значение. Но более надежным решени- ем было бы использование триггера для увеличения значения поля total_amount
276

9.6. Пример использования транзакций
при каждом добавлении строки в таблицу ticket_flights, но в этом учебном по- собии они не рассматриваются.
UPDATE bookings
SET total_amount =
( SELECT sum( amount )
FROM ticket_flights
WHERE ticket_no IN
( SELECT ticket_no
FROM tickets
WHERE book_ref = 'ABC123'
)
)
WHERE book_ref = 'ABC123';
UPDATE 1
Проверим, что получилось.
SELECT *
FROM bookings
WHERE book_ref = 'ABC123';
book_ref |
book_date
| total_amount
----------+------------------------+--------------
ABC123
| 2016-10-13 22:00:00+08 |
42000.00
(1 строка)
COMMIT;
COMMIT;
В начале главы говорилось о свойствах транзакций. Их удобно прокомментировать на примере этой транзакции, в которой участвуют три таблицы. Атомарность го- ворит о том, что либо транзакция выполняется и фиксируется полностью, либо не фиксируется ни одна из ее операций. Поэтому в случае отказа сервера баз данных в процессе выполнения транзакции и последующего восстановления состояния базы данных те операции, которые уже были выполнены, будут отменены. Таким образом,
база данных будет приведена к тому согласованному состоянию, в котором она на- ходилась до начала транзакции. При выборе соответствующего уровня изоляции эта транзакция сможет выполняться, не подвергаясь помехам со стороны других парал- лельных транзакций. После успешной фиксации всех выполненных изменений в базе данных пользователь может быть уверен, что они станут долговечными и сохранятся даже в случае сбоя в работе сервера.
277

Глава 9. Транзакции
9.7. Блокировки
Кроме поддержки уровней изоляции транзакций, PostgreSQL позволяет также созда- вать явные блокировки данных как на уровне отдельных строк, так и на уровне це- лых таблиц. Блокировки могут быть востребованы при проектировании транзакций с уровнем изоляции, как правило, Read Committed, когда требуется более детальное управление параллельным выполнением транзакций. PostgreSQL предлагает много различных видов блокировок, но мы ограничимся рассмотрением только двух из них.
Команда SELECT имеет предложение FOR UPDATE, которое позволяет заблокировать отдельные строки таблицы с целью их последующего обновления. Если одна транзак- ция заблокировала строки с помощью этой команды, тогда параллельные транзакции не смогут заблокировать эти же строки до тех пор, пока первая транзакция не завер- шится, и тем самым блокировка не будет снята.
Проведем эксперимент, как и прежде, с использованием двух терминалов. Мы не бу- дем приводить все вспомогательные команды создания и завершения транзакций,
а ограничимся только командами, выполняющими полезную работу.
Итак, на первом терминале организуйте транзакцию с уровнем изоляции Read
Committed и выполните следующую команду:
SELECT *
FROM aircrafts_tmp
WHERE model '^Air'
FOR UPDATE;
aircraft_code |
model
| range
---------------+-----------------+-------
320
| Airbus A320-200 | 5700 321
| Airbus A321-200 | 5600 319
| Airbus A319-100 | 6700
(3 строки)
На втором терминале организуйте аналогичную транзакцию и выполните точно та- кую же команду. Вы увидите, что ее выполнение будет приостановлено.
SELECT *
FROM aircrafts_tmp
WHERE model '^Air'
FOR UPDATE;
278

9.7. Блокировки
На первом терминале обновите одну строку, а затем завершите транзакцию:
UPDATE aircrafts_tmp
SET range = 5800
WHERE aircraft_code = '320';
UPDATE 1
Перейдя на второй терминал, вы увидите, что там была, наконец, выполнена выбор- ка, которая показала уже измененные данные:
aircraft_code |
model
| range
---------------+-----------------+-------
320
| Airbus A320-200 | 5800 321
| Airbus A321-200 | 5600 319
| Airbus A319-100 | 6700
(3 строки)
Завершите и вторую транзакцию.
Аналогичным образом можно организовать блокировки на уровне таблиц. Также на первом терминале организуйте транзакцию с уровнем изоляции Read Committed и выполните команду блокировки всей таблицы в самом строгом режиме, в котором другим транзакциям доступ к этой таблице запрещен полностью:
LOCK TABLE aircrafts_tmp
IN ACCESS EXCLUSIVE MODE;
LOCK TABLE
На втором терминале выполните совершенно «безобидную» команду:
SELECT *
FROM aircrafts_tmp
WHERE model '^Air';
Вы увидите, что выполнение команды SELECT на втором терминале будет задержа- но. Прервите транзакцию на первом терминале командой ROLLBACK. Вы увидите, что на втором терминале команда будет успешно выполнена.
Более подробно ознакомиться с различными видами блокировок уровня строки и уровня таблицы можно с помощью документации (раздел 13.3 «Явные блокировки»).
279

Глава 9. Транзакции
Контрольные вопросы и задания
1. По умолчанию каждая SQL-команда, выполняемая в среде psql, образует от- дельную транзакцию с уровнем изоляции Read Committed. Поэтому в тех экс- периментах, когда одна из транзакций состоит только из единственной SQL- команды, можно не выполнять команды BEGIN и END. Конечно, если каждая из параллельных транзакций состоит из единственной SQL-команды, то хотя бы для одной из транзакций придется все же выполнить и команду BEGIN, иначе эксперимент не получится.
В тексте главы были приведены примеры транзакций, в которых рассматрива- лись команды SELECT ... FOR UPDATE и LOCK TABLE. Попробуйте повторить эти эксперименты с учетом описанного поведения PostgreSQL.
2. Транзакции, работающие на уровне изоляции Read Committed, видят только свои собственные обновления и обновления, зафиксированные параллельными транзакциями. При этом нужно учитывать, что иногда могут возникать ситу- ации, которые на первый взгляд кажутся парадоксальными, но на самом деле все происходит в строгом соответствии с этим принципом.
Воспользуемся таблицей «Самолеты» (aircrafts) или ее копией. Предполо- жим, что мы решили удалить из таблицы те модели, дальность полета которых менее 2 000 км. В таблице представлена одна такая модель — Cessna 208 Caravan,
имеющая дальность полета 1 200 км. Для выполнения удаления мы организова- ли транзакцию. Однако параллельная транзакция, которая, причем, началась раньше, успела обновить таблицу таким образом, что дальность полета самоле- та Cessna 208 Caravan стала составлять 2 100 км, а вот для самолета Bombardier
CRJ-200 она, напротив, уменьшилась до 1 900 км. Таким образом, в результате выполнения операций обновления в таблице по-прежнему присутствует стро- ка, удовлетворяющая первоначальному условию, т. е. значение атрибута range у которой меньше 2000.
Наша задача: проверить, будет ли в результате выполнения двух транзакций удалена какая-либо строка из таблицы.
На первом терминале начнем транзакцию, при этом уровень изоляции Read
Committed в команде указывать не будем, т. к. он принят по умолчанию:
BEGIN;
BEGIN
280

Контрольные вопросы и задания
SELECT *
FROM aircrafts_tmp
WHERE range < 2000;
aircraft_code |
model
| range
---------------+--------------------+-------
CN1
| Cessna 208 Caravan | 1200
(1 строка)
UPDATE aircrafts_tmp
SET range = 2100
WHERE aircraft_code = 'CN1';
UPDATE 1
UPDATE aircrafts_tmp
SET range = 1900
WHERE aircraft_code = 'CR2';
UPDATE 1
На втором терминале начнем вторую транзакцию, которая и будет пытаться удалить строки, у которых значение атрибута range меньше 2000.
BEGIN;
BEGIN
SELECT *
FROM aircrafts_tmp
WHERE range < 2000;
aircraft_code |
model
| range
---------------+--------------------+-------
CN1
| Cessna 208 Caravan | 1200
(1 строка)
DELETE FROM aircrafts_tmp WHERE range < 2000;
Введя команду DELETE, мы видим, что она не завершается, а ожидает, когда со строки, подлежащей удалению, будет снята блокировка. Блокировка, установ- ленная командой UPDATE в первой транзакции, снимается только при завер- шении транзакции, а завершение может иметь два исхода: фиксацию измене- ний с помощью команды COMMIT (или END) или отмену изменений с помощью команды ROLLBACK.
281

Глава 9. Транзакции
Давайте зафиксируем изменения, выполненные первой транзакцией. На пер- вом терминале сделаем так:
COMMIT;
COMMIT
Тогда на втором терминале мы получим такой результат от команды DELETE:
DELETE 0
Чем объясняется такой результат? Он кажется нелогичным: ведь команда
SELECT, выполненная в этой же второй транзакции, показывала наличие стро- ки, удовлетворяющей условию удаления.
Объяснение таково: поскольку вторая транзакция пока еще не видит измене- ний, произведенных в первой транзакции, то команда DELETE выбирает для удаления строку, описывающую модель Cessna 208 Caravan, однако эта строка была заблокирована в первой транзакции командой UPDATE. Эта команда из- менила значение атрибута range в этой строке.
При завершении первой транзакции блокировка с этой строки снимается (со второй строки — тоже), и команда DELETE во второй транзакции получает воз- можность заблокировать эту строку. При этом команда DELETE данную строку
перечитывает
и вновь вычисляет условие WHERE применительно к ней. Однако теперь условие WHERE для данной строки уже не выполняется, следовательно,
эту строку удалять нельзя. Конечно, в таблице есть теперь другая строка, для самолета Bombardier CRJ-200, удовлетворяющая условию удаления, однако по- вторный поиск строк, удовлетворяющих условию WHERE в команде DELETE, не производится.
В результате не удаляется ни одна строка. Таким образом, к сожалению, имеет место нарушение согласованности, которое можно объяснить деталями реали- зации СУБД.
Завершим вторую транзакцию:
END;
COMMIT
Вот что получилось в результате:
SELECT * FROM aircrafts_tmp;
282

Контрольные вопросы и задания
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 | 2100
CR2
| Bombardier CRJ-200 | 1900
(9 строк)
Задание.
Модифицируйте сценарий выполнения транзакций: в первой тран- закции вместо фиксации изменений выполните их отмену с помощью команды
ROLLBACK и посмотрите, будет ли удалена строка и какая конкретно.
3.* Когда говорят о таком феномене, как потерянное обновление, то зачастую в ка- честве примера приводится операция UPDATE, в которой значение какого-то атрибута изменяется с применением одного из действий арифметики. Напри- мер:
UPDATE aircrafts_tmp
SET range = range + 200
WHERE aircraft_code = 'CR2';
При выполнении двух и более подобных обновлений в рамках параллельных транзакций, использующих, например, уровень изоляции Read Committed, бу- дут учтены все такие изменения (что и было показано в тексте главы). Очевид- но, что потерянного обновления не происходит.
Предположим, что в одной транзакции будет просто присваиваться новое зна- чение, например, так:
UPDATE aircrafts_tmp
SET range = 2100
WHERE aircraft_code = 'CR2';
А в параллельной транзакции будет выполняться аналогичная команда:
UPDATE aircrafts_tmp
SET range = 2500
WHERE aircraft_code = 'CR2';
283

Глава 9. Транзакции
Очевидно, что сохранится только одно из значений атрибута range. Можно ли говорить, что в такой ситуации имеет место потерянное обновление? Если оно имеет место, то что можно предпринять для его недопущения? Обоснуйте ваш ответ.
Для получения дополнительной информации можно обратиться к фундамен- тальному труду К. Дж. Дейта, а также к полному руководству по SQL Дж. Гроффа,
П. Вайнберга и Э. Оппеля. Библиографические описания этих книг приведены в списке рекомендуемой литературы.
4. На уровне изоляции транзакций Read Committed имеет место такой феномен,
как чтение фантомных строк. Такие строки могут появляться в выборке как в ре- зультате добавления новых строк параллельной транзакцией, так и вследствие изменения ею значений атрибутов, участвующих в формировании условия вы- борки. Рассмотрим пример, иллюстрирующий вторую из указанных причин.
На первом терминале организуем транзакцию. Она будет иметь уровень изоля- ции Read Committed:
BEGIN;
BEGIN
SELECT *
FROM aircrafts_tmp
WHERE range > 6000;
aircraft_code |
model
| range
---------------+-----------------+-------
773
| Boeing 777-300 | 11100 763
| Boeing 767-300 | 7900 319
| Airbus A319-100 | 6700
(3 строки)
На втором терминале организуем транзакцию и обновим одну из строк табли- цы таким образом, чтобы эта строка стала удовлетворять условию отбора строк,
заданному в первой транзакции.
BEGIN;
BEGIN
284

Контрольные вопросы и задания
UPDATE aircrafts_tmp
SET range = 6100
WHERE aircraft_code = '320';
UPDATE 1
Сразу завершим вторую транзакцию, чтобы первая транзакция увидела эти из- менения.
END;
COMMIT
На первом терминале повторим ту же самую выборку:
SELECT *
FROM aircrafts_tmp
WHERE range > 6000;
aircraft_code |
model
| range
---------------+-----------------+-------
773
| Boeing 777-300 | 11100 763
| Boeing 767-300 | 7900 319
| Airbus A319-100 | 6700 320
| Airbus A320-200 | 6100
(4 строки)
Транзакция еще не завершилась, но она уже увидела новую строку, обновлен- ную зафиксированной параллельной транзакцией. Теперь эта строка стала соот- ветствовать условию выборки. Таким образом, не изменяя критерий выборки,
мы получили другое множество строк.
Завершим теперь и первую транзакцию:
END;
COMMIT
Задание.
Модифицируйте этот эксперимент: вместо операции UPDATE исполь- зуйте операцию INSERT.
5. В тексте главы была рассмотрена команда SELECT ... FOR UPDATE, выполня- ющая блокировку на уровне отдельных строк. Организуйте две параллельные
285

Глава 9. Транзакции
транзакции с уровнем изоляции Read Committed и выполните с ними ряд экспе- риментов. В первой транзакции заблокируйте некоторое множество строк, от- бираемых с помощью условия WHERE. А во второй транзакции изменяйте усло- вие выборки таким образом, чтобы выбираемое множество строк:
– являлось подмножеством множества строк, выбираемых в первой транзакции;
– являлось надмножеством множества строк, выбираемых в первой транзакции;
– пересекалось с множеством строк, выбираемых в первой транзакции;
– не пересекалось с множеством строк, выбираемых в первой транзакции.
Наблюдайте за поведением команд выборки в каждой транзакции. Попробуйте обобщить ваши наблюдения.
6. Самостоятельно ознакомьтесь с предложением FOR SHARE команды SELECT и выполните необходимые эксперименты. Используйте документацию: раздел
13.3.2 «Блокировки на уровне строк» и описание команды SELECT.
7. В тексте главы для иллюстрации изучаемых концепций мы создавали только две параллельные транзакции. Попробуйте воспроизвести представленные экспе- рименты, создав три или даже четыре параллельные транзакции.
8.* В тексте главы была рассмотрена транзакция для выполнения бронирования билетов. Для нее был выбран уровень изоляции Read Committed.
Как вы думаете, если одновременно будут производиться несколько операций бронирования, то, может быть, имеет смысл «ужесточить» уровень изоляции до
Serializable? Или нет необходимости это делать? Обдумайте и вариант с исполь- зованием явных блокировок. Обоснуйте ваш ответ.
9.* В разделе документации 13.2.3 «Уровень изоляции Serializable» сказано, что ес- ли поиск в таблице осуществляется последовательно, без использования индек- са, тогда на всю таблицу накладывается так называемая предикатная блокиров- ка. Такой подход приводит к увеличению числа сбоев сериализации. В качестве контрмеры можно попытаться использовать индексы. Конечно, если таблица совсем небольшая, то может и не получиться заставить PostgreSQL использовать поиск по индексу. Тем не менее давайте выполним следующий эксперимент.
Для его проведения создадим специальную таблицу, в которой будет всего два столбца: один — числовой, а второй — текстовый. Значения во втором столбце будут иметь вид: LOW1, LOW2, ..., HIGH1, HIGH2, ... Назовем эту таблицу modes.
286

Контрольные вопросы и задания
Добавим в нее такое число строк, которое сделает очень вероятным использо- вание индекса при выполнении операций обновления строк и, соответственно,
отсутствие предикатной блокировки всей таблицы. О том, как узнать, исполь- зуется ли индекс при выполнении тех или иных операций, написано в главе 10.
CREATE TABLE modes AS
SELECT num::integer, 'LOW' || num::text AS mode
FROM generate_series( 1, 100000 ) AS gen_ser( num )
UNION ALL
SELECT num::integer, 'HIGH' || ( num - 100000 )::text AS mode
FROM generate_series( 100001, 200000 ) AS gen_ser( num );
SELECT 200000
Проиндексируем таблицу по числовому столбцу.
CREATE INDEX modes_ind
ON modes ( num );
CREATE INDEX
Из всего множества строк нас будут интересовать только две:
SELECT *
FROM modes
WHERE mode IN ( 'LOW1', 'HIGH1' );
num
| mode
--------+-------
1 | LOW1 100001 | HIGH1
(2 строки)
На первом терминале начнем транзакцию и обновим одну строку из тех двух строк, которые были показаны в предыдущем запросе.
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
UPDATE modes
SET mode = 'HIGH1'
WHERE num = 1;
UPDATE 1 287

Глава 9. Транзакции
На втором терминале тоже начнем транзакцию и обновим другую строку из тех двух строк, которые были показаны выше.
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
UPDATE modes
SET mode = 'LOW1'
WHERE num = 100001;
UPDATE 1
Обратите внимание, что обе команды UPDATE были выполнены, ни одна из них не ожидает завершения другой транзакции.
Попробуем завершить транзакции. Сначала — на первом терминале:
COMMIT;
COMMIT
А потом на втором терминале:
COMMIT;
COMMIT
Посмотрим, что получилось:
SELECT *
FROM modes
WHERE mode IN ( 'LOW1', 'HIGH1' );
num
| mode
--------+-------
1 | HIGH1 100001 | LOW1
(2 строки)
Теперь система смогла сериализовать параллельные транзакции и зафиксиро- вать их обе. Как вы думаете, почему это удалось? Обосновывая ваш ответ, при- мите во внимание тот результат, который был бы получен при последователь- ном выполнении транзакций.
288

Контрольные вопросы и задания
10.* В тексте главы был рассмотрен пример транзакции над таблицами базы дан- ных «Авиаперевозки». Давайте теперь создадим две параллельные транзакции и выполним их с уровнем изоляции Serializable. Отправим также двоих пасса- жиров теми же самыми рейсами, что и ранее, но операции распределим между двумя транзакциями. Отличие заключается в том, что в начале транзакции бу- дут выполняться выборки из таблицы ticket_flights. Для упрощения ситу- ации не будем предварительно проверять наличие свободных мест, т. к. сейчас для нас важно не это. Итак, первая транзакция:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
SELECT *
FROM ticket_flights
WHERE flight_id = 13881;
ticket_no
| flight_id | fare_conditions | amount
---------------+-----------+-----------------+----------
0005433848165 |
13881 | Business
| 99800.00 0005433848007 |
13881 | Economy
| 33300.00
(82 строки)
INSERT INTO bookings ( book_ref, book_date, total_amount )
VALUES ( 'ABC123', bookings.now(), 0 );
INSERT 0 1
INSERT INTO tickets
( ticket_no, book_ref, passenger_id, passenger_name )
VALUES ( '9991234567890', 'ABC123', '1234 123456', 'IVAN PETROV' );
INSERT 0 1
INSERT INTO ticket_flights
( ticket_no, flight_id, fare_conditions, amount )
VALUES ( '9991234567890', 13881, 'Business', 12500 );
INSERT 0 1
UPDATE bookings
SET total_amount = 12500
WHERE book_ref = 'ABC123';
UPDATE 1 289

Глава 9. Транзакции
COMMIT;
COMMIT
Вторая транзакция:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
SELECT *
FROM ticket_flights
WHERE flight_id = 5572;
ticket_no
| flight_id | fare_conditions | amount
---------------+-----------+-----------------+----------
0005433847924 |
5572 | Business
| 99800.00 0005433847890 |
5572 | Economy
| 33300.00
(100 строк)
INSERT INTO bookings ( book_ref, book_date, total_amount )
VALUES ( 'ABC456', bookings.now(), 0 );
INSERT 0 1
INSERT INTO tickets
( ticket_no, book_ref, passenger_id, passenger_name )
VALUES ( '9991234567891', 'ABC456', '4321 654321', 'PETR IVANOV' );
INSERT 0 1
INSERT INTO ticket_flights
( ticket_no, flight_id, fare_conditions, amount )
VALUES ( '9991234567891', 5572, 'Business', 12500 );
INSERT 0 1
UPDATE bookings
SET total_amount = 12500
WHERE book_ref = 'ABC456';
UPDATE 1 290

Контрольные вопросы и задания
COMMIT;
ОШИБКА: не удалось сериализовать доступ из-за зависимостей чтения/записи между транзакциями
ПОДРОБНОСТИ: Reason code: Canceled on identification as a pivot,
during commit attempt.
ПОДСКАЗКА: Транзакция может завершиться успешно при следующей попытке.
Задание 1.
Попытайтесь объяснить, почему транзакции не удалось сериа- лизовать. Что можно сделать, чтобы удалось зафиксировать обе транзакции?
Одно из возможных решений — понизить уровень изоляции. Другим ре- шением может быть создание индекса по столбцу flight_id для таблицы ticket_flights. Почему создание индекса может помочь? Обратитесь за разъяснениями к разделу документации 13.2.3 «Уровень изоляции Serializable».
Задание 2.
В первой транзакции условие в команде SELECT такое: ... WHERE
flight_id = 13881. В команде вставки в таблицу ticket_flights значение поля flight_id также равно 13881. Во второй транзакции в этих же командах используется значение 5572. Поменяйте местами значения в командах SELECT
и повторите эксперименты, выполнив транзакции параллельно с уровнем изо- ляции Serializable. Почему сейчас наличие индекса не помогает зафиксировать обе транзакции? Вспомните, что аномалия сериализации — это ситуация, когда параллельное выполнение транзакций приводит к результату, невозможному ни при каком из вариантов упорядочения этих же транзакций при их последо- вательном выполнении.
291

Глава 10. Повышение производительности
индексе нужного ключа производится обращение к соответствующей странице таб- лицы и извлечение искомой строки по ее идентификатору. При этом нужно учиты- вать, что хотя записи в индексе упорядочены, но обращения к страницам таблицы происходят хаотически, поскольку строки в таблицах не упорядочены. В таком случае при низкой селективности выборки, т. е. когда из таблицы отбирается значительное число строк, использование индексного поиска может не только не давать ускорения работы, но даже и снижать производительность.
Просмотр исключительно на основе индекса
(index only scan), как следует из на- звания метода, не должен, казалось бы, требовать обращения к строкам таблицы,
поскольку все данные, которые нужно получить с помощью запроса, в этом случае присутствуют в индексе. Однако в индексе нет информации о видимости строк тран- закциям — нельзя быть уверенным, что данные, полученные из индекса, видны те- кущей транзакции.
Поэтому сначала выполняется обращение к карте видимости (visibility map), которая существует для каждой таблицы. В ней одним битом отмечены страницы, на кото- рых содержатся только те версии строк, которые видны всем без исключения тран- закциям. Если полученная из индекса версия строки находится на такой странице,
значит, эта строка видна текущей транзакции и обращаться к самой таблице не тре- буется. Поскольку размер карты видимости очень мал, то в результате сокращается объем операций ввода/вывода. Если же строка находится на странице, не отмеченной в карте видимости, тогда происходит обращение и к таблице; в результате никакого выигрыша по быстродействию в сравнении с обычным индексным поиском не до- стигается. Просмотр исключительно на основе индекса особенно эффективен, когда выбираемые данные изменяются редко. Он может применяться, когда в предложе- нии SELECT указаны только имена столбцов, по которым создан индекс.
Просмотр на основе битовой карты
(bitmap scan) является модификацией про- смотра на основе индекса. Данный метод позволяет оптимизировать индексный по- иск за счет того, что сначала производится поиск в индексе для всех искомых строк и формирование так называемой битовой карты, в которой указывается, в каких страницах таблицы эти строки содержатся. После того как битовая карта сформиро- вана, выполняется извлечение строк из страниц таблицы, но при этом обращение к каждой странице производится только один раз.
Другим важным понятием является способ соединения наборов строк (join). Набор строк может быть получен из таблицы с помощью одного их методов доступа, опи- санных выше. Набор строк может быть получен не только из одной таблицы, а может быть результатом соединения других наборов. Важно различать способ соединения таблиц (JOIN) и способ соединения наборов строк. Первое понятие относится к языку
294

10.1. Основные понятия
SQL и является высокоуровневым, логическим, оно не касается вопросов реализа- ции. А второе относится именно к реализации, это — механизм непосредственного выполнения соединения наборов строк. Принципиально важным является то, что за один раз соединяются только два набора строк.
Существует три способа соединения: вложенный цикл (nested loop), хеширование
(hash join) и слияние (merge join). Они имеют свои особенности, которые PostgreSQL
учитывает при выполнении конкретных запросов.
Суть способа «вложенный цикл» в том, что перебираются строки из «внешнего» на- бора и для каждой из них выполняется поиск соответствующих строк во «внутрен- нем» наборе. Если соответствующие строки найдены, то выполняется их соединение со строкой из «внешнего» набора. При этом способы выбора строк из обоих наборов могут быть различными. Метод поддерживает соединения как на основе равенства значений атрибутов (эквисоединения), так и любые другие виды условий. Поскольку он не требует подготовительных действий, то способен быстро приступить к непо- средственной выдаче результата. Метод эффективен для небольших выборок.
При соединении хешированием строки одного набора помещаются в хеш-таблицу,
содержащуюся в памяти, а строки из второго набора перебираются, и для каждой из них проверяется наличие соответствующих строк в хеш-таблице. Ключом хеш- таблицы является тот столбец, по которому выполняется соединение наборов строк.
Как правило, число строк в том наборе, на основе которого строится хеш-таблица,
меньше, чем во втором наборе. Это позволяет уменьшить ее размер и ускорить про- цесс обращения к ней. Данный метод работает только при выполнении эквисоедине- ний, поскольку для хеш-таблицы имеет смысл только проверка на равенство прове- ряемого значения одному из ее ключей. Метод эффективен для больших выборок.
Соединение методом слияния
производится аналогично сортировке слиянием.
В этом случае оба набора строк должны быть предварительно отсортированы по тем столбцам, по которым производится соединение. Затем параллельно читаются стро- ки из обоих наборов и сравниваются значения столбцов, по которым производится соединение. При совпадении значений формируется результирующая строка. Этот процесс продолжается до исчерпания строк в обоих наборах. Этот метод, как и ме- тод соединения хешированием, работает только при выполнении эквисоединений.
Он пригоден для работы с большими наборами строк.
295

Глава 10. Повышение производительности
10.2. Методы просмотра таблиц
Теперь мы можем перейти к рассмотрению планов выполнения запросов.
Прежде чем приступить к непосредственному выполнению запроса, PostgreSQL фор- мирует план его выполнения. Чтобы достичь хорошей производительности, этот план должен учитывать свойства данных. Планированием занимается специальная под- система — планировщик (planner). Просмотреть план выполнения любого запроса можно с помощью команды EXPLAIN. Для детального понимания планов выполне- ния сложных запросов требуется опыт. Мы изложим лишь основные приемы работы с этой командой.
Структура плана запроса представляет собой дерево, состоящее из так называемых
узлов плана
(plan nodes). Узлы на нижних уровнях дерева отвечают за просмотр и вы- дачу строк таблиц, которые осуществляются с помощью методов доступа, описанных выше. Если конкретный запрос требует выполнения операций агрегирования, соеди- нения таблиц, сортировки, то над узлами выборки строк будут располагаться допол- нительные узлы дерева плана. Например, для соединения наборов строк будут ис- пользоваться способы, которые мы только что рассмотрели. Для каждого узла дерева плана команда EXPLAIN выводит по одной строке, при этом выводятся также оцен- ки стоимости выполнения операций на каждом узле, которые делает планировщик.
В случае необходимости для конкретных узлов могут выводиться дополнительные строки. Самая первая строка плана содержит общую оценку стоимости выполнения данного запроса.
Запустите утилиту psql и введите простой запрос:
EXPLAIN SELECT *
FROM aircrafts;
В ответ получим план выполнения запроса:
QUERY PLAN
---------------------------------------------------------
Seq Scan on aircrafts (cost=0.00..1.09 rows=9 width=52)
(1 строка)
Поскольку в этом запросе нет предложения WHERE, он должен просмотреть все строки таблицы, поэтому планировщик выбирает последовательный просмотр (sequential scan). В скобках приведены важные параметры плана.
296

10.2. Методы просмотра таблиц
Первое число означает оценку ресурсов, требуемых для того, чтобы приступить к вы- воду данных. В нашем примере эта оценка равна нулю, поскольку никакие дополни- тельные операции с выбранными строками не предполагаются, и PostgreSQL может сразу же выводить прочитанные строки.
Второе число — это оценка общей стоимости выполнения запроса. Формируя эту оценку, планировщик исходит из предположения, что данный узел плана запроса вы- полняется до конца, т. е. извлекаются все имеющиеся строки таблицы. Однако в ряде случаев на практике это может оказаться и не так, если узел-родитель прекратит свою работу досрочно, например, в случае использования в запросе SELECT предложения
LIMIT, которое ограничивает выборку записей из таблицы конкретным их числом.
Обе оценки стоимости выполнения выражаются в неких условных единицах, которые вычисляются на основе ряда параметров сервера баз данных. При этом не важно,
в каких конкретно единицах производится измерение стоимости: важны соотноше- ния стоимостей. Для каждого запроса планировщик формирует несколько планов.
При сравнении различных вариантов плана, как правило, для выполнения выбира- ется тот, который имеет наименьшую общую стоимость выполнения запроса. Однако при работе с курсорами этот принцип можно изменить с помощью специального па- раметра планировщика cursor_tuple_fraction (курсоры в учебном пособии не рассматриваются).
Далее в выводе идет общее число строк, которые должны быть извлечены (возвра- щены) на данном узле плана, также при условии выполнения этого узла до полного завершения. В нашем примере число строк равно 9. Это число является оценкой, ко- торую планировщик получает на основе статистики, накапливаемой в специальных системных таблицах.
Последним параметром узла плана идет оценка среднего размера строк, которые вы- водятся на данном узле плана запроса. В нашем примере размер (ширина) строки данных оценивается в 52 байта.
В том случае, когда нас не интересуют численные оценки, можно воспользоваться параметром COSTS OFF:
EXPLAIN ( COSTS OFF ) SELECT *
FROM aircrafts;
QUERY PLAN
-----------------------
Seq Scan on aircrafts
(1 строка)
297

Глава 10. Повышение производительности
Сформируем запрос с предложением WHERE:
EXPLAIN SELECT *
FROM aircrafts
WHERE model 'Air';
QUERY PLAN
---------------------------------------------------------
Seq Scan on aircrafts (cost=0.00..1.11 rows=1 width=52)
Filter: (model 'Air'::text)
(2 строки)
Поскольку наложено дополнительное условие на строки, выбираемые из таблицы,
то ниже узла плана, отвечающего за их последовательную выборку, добавляется еще один узел, описывающий критерий отбора строк.
Filter: (model 'Air'::text)
Поскольку наложено условие отбора строк, то оценка их числа изменилась с 9 на 1.
В данном случае планировщик неточно оценил число выбираемых строк — фактиче- ски их будет три.
Обратите внимание, что по своей форме вывод команды EXPLAIN также является вы- боркой, поэтому в конце выборки, как обычно, выводится информация о числе строк в ней, т. е. в дереве плана. Это не число строк, которые будут выбраны из таблицы.
В данном случае это
(2 строки)
Теперь усложним запрос, добавив в него сортировку данных:
EXPLAIN SELECT *
FROM aircrafts
ORDER BY aircraft_code;
QUERY PLAN
-------------------------------------------------------------
Sort (cost=1.23..1.26 rows=9 width=52)
Sort Key: aircraft_code
-> Seq Scan on aircrafts (cost=0.00..1.09 rows=9 width=52)
(3 строки)
298

10.2. Методы просмотра таблиц
Дополнительный узел обозначен на плане символами «->».
Хотя по столбцу aircraft_code создан индекс (для поддержки первичного ключа),
планировщик предпочел не использовать этот индекс, а прибегнуть к последователь- ному сканированию (Seq Scan) таблицы, о чем говорит нам нижний узел плана.
На верхнем узле выполняется сортировка выбранных строк. Поскольку для выполне- ния сортировки требуется время, отличное от нуля, то этот факт и отражен в первой числовой оценке — 1,23. Это оценка времени, которое потребуется для того, чтобы приступить к выводу отсортированных строк. Но времени непосредственно на саму сортировку потребуется меньше: ведь в оценку 1,23 входит и оценка стоимости по- лучения выборки — 1,09.
Когда таблица очень маленькая, то обращение к индексу не даст выигрыша в скоро- сти, а лишь добавит к операциям чтения страниц, в которых хранятся строки таблиц,
еще и операции чтения страниц с записями индекса.
Обратимся к таблице «Бронирования» для иллюстрации сканирования по индексу.
EXPLAIN SELECT *
FROM bookings
ORDER BY book_ref;
QUERY PLAN
----------------------------------------------------------------
Index Scan using bookings_pkey on bookings (cost=0.42..8511.24
rows=262788 width=21)
(1 строка)
Поскольку выводимые строки плана в утилите psql могут быть очень длинными, мы будем вносить небольшие изменения в форматирование вывода при переносе плана в текст пособия.
Обратите внимание, что первая оценка стоимости в плане — не нулевая. Это объяс- няется тем, что, хотя индекс уже упорядочен, и дополнительная сортировка не требу- ется, но для того, чтобы найти в индексе первую строку в соответствии с требуемым порядком, тоже нужно некоторое время.
Если к сортировке добавить еще и условие отбора строк, то это отразится в дополни- тельной строке верхнего (и единственного) узла плана.
EXPLAIN SELECT *
FROM bookings
WHERE book_ref > '0000FF' AND book_ref < '000FFF'
ORDER BY book_ref;
299

Глава 10. Повышение производительности
QUERY PLAN
---------------------------------------------------------------------
Index Scan using bookings_pkey on bookings (cost=0.42..9.50 rows=54
width=21)
Index Cond: ((book_ref > '0000FF'::bpchar) AND (book_ref <
'000FFF'::bpchar))
(2 строки)
Обратите внимание, что поскольку столбец, по которому производится отбор строк,
является индексируемым, то их отбор реализуется не через Filter, а через Index
Cond.
Теперь проиллюстрируем метод сканирования на основе битовой карты на при- мере таблицы «Места».
EXPLAIN SELECT *
FROM seats
WHERE aircraft_code = 'SU9';
QUERY PLAN
------------------------------------------------------------------------
Bitmap Heap Scan on seats (cost=5.03..14.24 rows=97 width=15)
Recheck Cond: (aircraft_code = 'SU9'::bpchar)
-> Bitmap Index Scan on seats_pkey (cost=0.00..5.00 rows=97 width=0)
Index Cond: (aircraft_code = 'SU9'::bpchar)
(4 строки)
В этом плане в нижнем узле строится битовая карта, а в верхнем узле с помощью этой карты сканируются страницы таблицы seats. Здесь также для отбора строк в со- ответствии с предложением WHERE используется индекс — Index Cond. Обратите внимание, что значение параметра width при создании битовой карты равно нулю,
поскольку сами строки на этом этапе еще не выбираются.
Если нам будет нужно выбрать только номера бронирований в каком-то диапазоне,
то обращения к таблице не потребуется: достаточно сканирования исключительно
по индексу
EXPLAIN SELECT book_ref
FROM bookings
WHERE book_ref < '000FFF'
ORDER BY book_ref;
300

10.2. Методы просмотра таблиц
QUERY PLAN
-------------------------------------------------------------------------
Index Only Scan using bookings_pkey on bookings (cost=0.42..9.42 rows=57
width=7)
Index Cond: (book_ref < '000FFF'::bpchar)
(2 строки)
В этом плане только один узел — Index Only Scan. Здесь также первая оценка стои- мости не нулевая, т. к. отыскание в индексе наименьшего значения требует некото- рого времени.
Посмотрим, как отражаются в планах выполнения запросов различные агрегатные
функции
. Начнем с простого подсчета строк.
EXPLAIN SELECT count( * )
FROM seats
WHERE aircraft_code = 'SU9';
QUERY PLAN
--------------------------------------------------------------------
Aggregate (cost=14.48..14.49 rows=1 width=8)
-> Bitmap Heap Scan on seats (cost=5.03..14.24 rows=97 width=0)
Recheck Cond: (aircraft_code = 'SU9'::bpchar)
-> Bitmap Index Scan on seats_pkey (cost=0.00..5.00 rows=97
width=0)
Index Cond: (aircraft_code = 'SU9'::bpchar)
(5 строк)
В верхнем узле плана выполняется агрегирование — Aggregate. А в нижних узлах подготавливаются строки с помощью сканирования на основе формирования бито- вой карты.
Но возникает вопрос: зачем вообще выполняется обращение к страницам таблицы
(Bitmap Heap Scan), если никакие значения атрибутов не выбираются, а подсчиты- вается лишь число этих строк? Казалось бы, достаточно использования только ин- декса. Но это нужно для того, чтобы проверить видимость версий строк: ведь разные транзакции могут видеть разные версии строк, поэтому при подсчете их числа нуж- но учитывать, какой транзакции они видны. Обратите еще внимание на тот факт, что собственно стадия агрегирования «ст´oит» не очень дорого. Ее можно приблизительно оценить как 0,24 (отняв от оценки 14,48 в узле Aggregate оценку 14,24 в узле Bitmap
Heap Scan).
301

Глава 10. Повышение производительности
А в этом примере агрегирование связано уже с вычислениями на основе значений конкретного столбца, а не просто с подсчетом строк.