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
Проверим, что получилось:
SELECT * FROM modes;
num | mode
-----+------
2 | LOW
1 | LOW
(2 строки)
Во втором варианте упорядочения поменяем транзакции местами. Конечно, предва- рительно нужно привести таблицу в исходное состояние.
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN
213
UPDATE modesSET mode = 'LOW'WHERE mode = 'HIGH'RETURNING *;num | mode
-----+------
2 | LOW
(1 строка)
UPDATE 1
END;COMMIT
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN
UPDATE modesSET mode = 'HIGH'WHERE mode = 'LOW'RETURNING *;num | mode
-----+------
1 | HIGH
2 | HIGH
(2 строки)
UPDATE 2
END;COMMIT
SELECT * FROM modes;Теперь
результат отличается от того, который был получен при реализации первого варианта упорядочения транзакций.
num | mode
-----+------
1 | HIGH
2 | HIGH
(2 строки)
Изменение порядка выполнения транзакций приводит к разным результатам. Одна- ко если бы при параллельном выполнении транзакций была зафиксирована и вторая из них, то полученный результат не соответствовал бы
ни одному из продемонстриро- ванных возможных результатов последовательного выполнения транзакций. Таким образом, выполнить сериализацию этих транзакций невозможно. Обратите внима- ние, что вторая команда UPDATE в обоих случаях обновляет не одну строку, а две.
214
9.5 Пример использования транзакций
Продемонстрируем использование транзакций на примере базы данных «Авиапере- возки». Для этого создадим новое бронирование и оформим два билета с двумя пе- релетами в каждом. Выберем в качестве уровня изоляции READ COMMITTED.
BEGIN;
BEGIN;
Сначала добавим запись в таблицу «Бронирования», причем, значение поля 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 );
215
INSERT 0 2
Подсчитаем общую стоимость забронированных билетов и запишем ее в строку таб- лицы «Бронирования». Конечно, если такая транзакция выполняется в рамках при- кладной программы, то возможно, что подсчет общей суммы будет выполняться в этой программе. Тогда в команде UPDATE уже не потребуется выполнять подзапрос,
а будет использоваться заранее вычисленное значение. Но более надежным решени- ем было бы использование триггера для увеличения значения поля total_amount при каждом добавлении строки в таблицу 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;
В начале главы говорилось о свойствах транзакций. Их удобно прокомментировать на примере этой транзакции, в которой участвуют три таблицы. Атомарность го- ворит о том, что либо транзакция выполняется и фиксируется полностью, либо не фиксируется ни одна из ее операций. Поэтому в случае отказа сервера баз данных в процессе выполнения транзакции и последующего восстановления состояния базы данных те операции, которые уже были выполнены, будут отменены. Таким образом,
база данных будет приведена к тому согласованному состоянию, в котором она на- ходилась до начала транзакции. При выборе соответствующего уровня изоляции эта транзакция сможет выполняться, не подвергаясь помехам со стороны других парал- лельных транзакций. После успешной фиксации всех выполненных изменений в базе данных пользователь может быть уверен, что они станут долговечными и сохранятся даже в случае сбоя в работе сервера.
216
9.6 БлокировкиКроме поддержки уровней изоляции транзакций, 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;На первом терминале обновите одну строку, а затем завершите транзакцию:
UPDATE aircrafts_tmpSET range = 5800WHERE 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 строки)
Завершите и вторую транзакцию.
217
Аналогичным образом можно организовать блокировки на уровне таблиц. Также на первом терминале организуйте транзакцию с уровнем изоляции READ COMMITTED
и выполните команду блокировки всей таблицы в самом строгом режиме, в котором другим транзакциям доступ к этой таблице запрещен полностью:
LOCK TABLE aircrafts_tmp IN ACCESS EXCLUSIVE MODE;LOCK TABLE
На втором терминале выполните совершенно «безобидную» команду:
SELECT * FROM aircrafts_tmp WHERE model '^Air';Вы увидите, что выполнение команды SELECT на втором терминале будет задержано.
Прервите транзакцию на первом терминале командой ROLLBACK. Вы увидите, что на втором терминале команда будет успешно выполнена.
Более подробно ознакомиться с различными видами блокировок уровня строки и уровня таблицы можно с помощью документации (раздел 13.3 «Явные блокиров- ки»).
Контрольные вопросы и задания1. По умолчанию каждая SQL-команда, выполняемая в среде psql, образует отдель- ную транзакцию с уровнем изоляции READ COMMITTED. Поэтому в тех экс- периментах, когда одна из транзакций состоит только из единственной SQL- команды, можно не выполнять команды BEGIN и END. Конечно, если каждая из параллельных транзакций состоит из единственной SQL-команды, то хотя бы для одной из транзакций придется все же выполнить и команду BEGIN, иначе эксперимент не получится.
В тексте главы были приведены примеры транзакций, в которых рассматрива- лись команды SELECT ... FOR UPDATE и LOCK TABLE. Попробуйте повторить эти эксперименты с учетом описанного поведения PostgreSQL.
2. Транзакции, работающие на уровне изоляции READ COMMITTED, видят только свои собственные обновления и обновления,
зафиксированные параллельными транзакциями. При этом нужно учитывать, что иногда могут возникать ситуа- ции, которые на первый взгляд кажутся парадоксальными, но на самом деле все происходит в строгом соответствии с этим принципом.
Давайте воспользуемся таблицей «Самолеты» (aircrafts) или ее копией. Пред- положим, что мы решили удалить из таблицы те модели, дальность полета ко- торых менее 2000 км. В таблице представлена одна такая модель — Cessna 208
Caravan, имеющая дальность полета 1200 км. Для выполнения удаления мы ор- ганизовали транзакцию. Однако параллельная транзакция, которая, причем,
началась раньше,
успела обновить таблицу таким образом, что дальность по- лета самолета Cessna 208 Caravan стала составлять 2100 км, а вот для самолета
Bombardier CRJ-200 она, напротив, уменьшилась до 1900 км. Таким образом, в результате выполнения операций обновления в таблице по-прежнему присут- ствует строка, удовлетворяющая первоначальному условию, т. е. значение ат- рибута range у которой меньше 2000.
218
Наша задача: проверить, будет ли в результате выполнения двух транзакций удалена какая-либо строка из таблицы.
На первом терминале начнем транзакцию, при этом уровень изоляции READ
COMMITTED в команде указывать не будем, т. к. он принят по умолчанию:
BEGIN;
BEGIN
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.
Давайте зафиксируем изменения, выполненные первой транзакцией. На пер- вом терминале сделаем так:
COMMIT;
COMMIT
219
Тогда на втором терминале мы получим такой результат от команды 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;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, в которой значение какого-то атрибута изменяется с применением одного из действий арифметики. Напри- мер:
220
UPDATE aircrafts_tmp SET range = range + 200WHERE 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';Очевидно, что сохранится только одно из значений атрибута 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;221
BEGIN
UPDATE aircrafts_tmpSET range = 6100WHERE 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, выполняющая блокировку на уровне отдельных строк. Организуйте две параллельные тран- закции с уровнем изоляции READ COMMITTED и выполните с ними ряд экспе- риментов. В первой транзакции заблокируйте некоторое множество строк, от- бираемых с помощью условия WHERE. А во второй транзакции
изменяйте усло- вие выборки таким образом, чтобы выбираемое множество строк:
– являлось подмножеством множества строк, выбираемых в первой транзак- ции;
– являлось надмножеством множества строк, выбираемых в первой транзак- ции;
– пересекалось с множеством строк, выбираемых в первой транзакции;
– не пересекалось с множеством строк, выбираемых в первой транзакции.
222
Наблюдайте за поведением команд выборки в каждой транзакции. Попробуйте обобщить ваши наблюдения.
6. Самостоятельно ознакомьтесь с предложением FOR SHARE команды SELECT и выполните необходимые эксперименты. Используйте документацию: раздел
13.3.2 «Блокировки на уровне строк» и описание команды SELECT.
7. В тексте главы для иллюстрации изучаемых концепций мы создавали только две параллельные транзакции. Попробуйте воспроизвести представленные экспе- рименты, создав три или даже четыре параллельные транзакции.
8.* В тексте главы была рассмотрена транзакция для выполнения бронирования билетов. Для нее был выбран уровень изоляции READ COMMITTED.
Как вы думаете, если одновременно будут производиться несколько операций бронирования, то, может быть, имеет смысл «ужесточить» уровень изоляции до
SERIALIZABLE? Или нет необходимости это делать? Обдумайте и вариант с ис- пользованием явных блокировок. Обоснуйте ваш ответ.
9.* В разделе документации 13.2.3 «Уровень изоляции Serializable» сказано, что ес- ли поиск в таблице осуществляется последовательно, без использования индек- са, тогда на всю таблицу накладывается так называемая предикатная блокиров- ка. Такой подход приводит к увеличению числа сбоев сериализации. В качестве контрмеры можно попытаться использовать индексы. Конечно, если таблица совсем небольшая, то может и не получиться заставить PostgreSQL использовать поиск по индексу. Тем не менее, давайте выполним следующий эксперимент.
Для его
проведения создадим специальную таблицу, в которой будет всего два столбца: один — числовой, а второй — текстовый. Значения во втором столб- це будут иметь вид: LOW1, LOW2, ..., HIGH1, HIGH2, ... Назовем эту таблицу —
modes. Добавим в нее такое число строк, которое сделает очень вероятным ис- пользование индекса при выполнении операций обновления строк и, соответ- ственно, отсутствие предикатной блокировки всей таблицы. О том, как узнать,
используется ли индекс при выполнении тех или иных операций, написано в главе 10.
Скачать 0.9 Mb.