SHOW default_transaction_isolation;
default_transaction_isolation
------------------------------- read committed
(1 строка)
9.1 Уровень изоляции READ UNCOMMITTED
Давайте начнем рассмотрение с уровня изоляции READ UNCOMMITTED. Проверим,
видит ли транзакция те изменения данных, которые были произведены в другой транзакции, но еще не были зафиксированы, т. е. «грязные» данные.
Для проведения экспериментов воспользуемся таблицей «Самолеты» (aircrafts). Но можно создать копию этой таблицы, чтобы при удалении строк из нее не удалялись строки из таблицы «Места» (seats), связанные со строками из таблицы aircrafts по внешнему ключу.
CREATE TABLE aircrafts_tmp AS SELECT * FROM aircrafts;
SELECT 9
Для организации выполнения параллельных транзакций с использованием утилиты psql будем запускать ее на двух терминалах.
Итак, для изучения уровня изоляции READ UNCOMMITTED проделаем следующие эксперименты.
На первом терминале выполним следующие команды:
BEGIN;
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET
SHOW transaction_isolation;
transaction_isolation
----------------------- read uncommitted
(1 строка)
UPDATE aircrafts_tmp
SET range = range + 100
WHERE aircraft_code = 'SU9';
202
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;
SELECT * FROM aircrafts_tmp WHERE aircraft_code = 'SU9';
aircraft_code |
model
| range
---------------+---------------------+-------
SU9
| Sukhoi SuperJet-100 | 3000
(1 строка)
Таким образом, вторая транзакция не видит изменение значения атрибута range,
произведенное в первой — незафиксированной — транзакции. Это объясняется тем,
что в PostgreSQL реализация уровня изоляции READ UNCOMMITTED более строгая,
чем того требует стандарт языка SQL. Фактически этот уровень тождественен уров- ню изоляции READ COMMITTED. Поэтому будем считать эксперимент, проведен- ный для уровня изоляции READ UNCOMMITTED, выполненным и для уровня READ
COMMITTED.
Давайте не будем фиксировать произведенное изменение в базе данных, а восполь- зуемся командой ROLLBACK для отмены транзакции, т. е. для ее отката.
На первом терминале:
ROLLBACK;
ROLLBACK
На втором терминале сделаем так же:
ROLLBACK;
ROLLBACK
203
9.2 Уровень изоляции 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 строка)
UPDATE aircrafts_tmpSET range = range + 100WHERE 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;BEGIN
204
UPDATE aircrafts_tmpSET range = range + 200WHERE aircraft_code = 'SU9';И вот мы видим, что команда UPDATE во второй транзакции не завершилась, а пере- шла в состояние ожидания. Это ожидание продлится до тех пор, пока не завершится первая транзакция. Дело в том, что команда UPDATE в первой транзакции заблоки- ровала строку в таблице airctafts_tmp, и эта блокировка будет снята только при завер- шении транзакции либо с фиксацией изменений с помощью команды COMMIT, либо с отменой изменений по команде ROLLBACK.
Давайте завершим первую транзакцию с фиксацией изменений:
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
205
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 | 3700
(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 | 3700
(6 строк)
Сразу завершим вторую транзакцию:
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
CN1
| Cessna 208 Caravan | 1200
CR2
| Bombardier CRJ-200 | 2700
SU9
| Sukhoi SuperJet-100 | 3700
(6 строк)
206
Видим, что теперь получен другой результат, т. к. вторая транзакция завершилась в момент времени между двумя запросами. Таким образом, налицо эффект неповто- ряющегося чтения данных, который является допустимым на уровне изоляции READ
COMMITTED.
Завершим и первую транзакцию:
END;COMMIT
9.3 Уровень изоляции REPEATABLE READТретий уровень изоляции — REPEATABLE READ. Само его название говорит о том,
что он не допускает наличия феномена неповторяющегося чтения данных. А в
PostgreSQL на этом уровне не допускается и чтение фантомных строк.
Приложения, использующие этот уровень изоляции должны быть готовы к тому, что придется выполнять транзакции повторно. Это объясняется тем, что транзакция, ис- пользующая этот уровень изоляции, создает снимок данных не перед выполнением каждого запроса, а только однократно, перед выполнением
первого запроса транзак- ции. Поэтому транзакции с этим уровнем изоляции не могут изменять строки, ко- торые были изменены другими завершившимися транзакциями уже после создания снимка. Вследствие этого PostgreSQL не
позволит зафиксировать транзакцию, кото- рая попытается изменить уже измененную строку.
Важно помнить, что повторный запуск может потребоваться только для транзакций,
которые вносят изменения в данные. Для транзакций, которые только читают дан- ные, повторный запуск никогда не требуется.
На первом терминале:
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 | 3700
CN1
| Cessna 208 Caravan | 2100
CR2
| Bombardier CRJ-200 | 1900
(6 строк)
207
На втором терминале проведем ряд изменений:
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
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 | 3700
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 | 3700
CN1
| Cessna 208 Caravan | 2100
CR2
| Bombardier CRJ-200 | 1900
IL9
| Ilyushin IL96
| 9800 208
320
| Airbus A320-200
| 5800
(7 строк)
Как видим, одна строка добавлена, а значение атрибута range у самолета Airbus A320-
200 стало на 100 больше, чем было. Но до тех пор, пока мы на первом терминале на- ходились в процессе выполнения первой транзакции, все эти изменения не были ей доступны, поскольку первая транзакция использовала снимок, сделанный до внесе- ния изменений и их фиксации второй транзакцией.
Теперь покажем ошибки сериализации.
Начнем транзакцию на первом терминале:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;BEGIN
UPDATE aircrafts_tmpSET range = range + 100WHERE aircraft_code = '320';UPDATE 1
На втором терминале попытаемся обновить ту же строку:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;BEGIN
UPDATE aircrafts_tmpSET range = range + 200WHERE aircraft_code = '320';Команда UPDATE на втором терминале ожидает завершения первой транзакции.
Перейдя на первый терминал, завершим первую транзакцию:
END;COMMIT
Перейдя на второй терминал, увидим сообщение об ошибке:
ОШИБКА: не удалось сериализовать доступ из-за параллельного изменения
Поскольку обновление,
произведенное в первой транзакции, не было зафиксировано на момент начала выполнения первого (и, в данном частном случае, единственного)
запроса во второй транзакции, то возникает эта ошибка. Это объясняется вот чем.
При выполнении обновления строки команда UPDATE во второй транзакции видит,
что строка уже изменена. На уровне изоляции REPEATABLE READ снимок данных со- здается на момент начала выполнения первого запроса транзакции и в течение тран- закции уже не меняется, т. е. новая версия строки не считывается, как это делалось на уровне READ COMMITTED. Но если выполнить обновление во второй транзакции без повторного считывания строки из таблицы, тогда будет иметь место потерянное об- новление, что недопустимо. В результате генерируется ошибка, и вторая транзакция откатывается. Мы вводим команду END на втором терминале, но PostgreSQL выпол- няет не фиксацию (COMMIT), а откат:
209
END;ROLLBACK
Если выполним запрос, то увидим, что было проведено только изменение в первой транзакции:
SELECT * FROM aircrafts_tmp WHERE aircraft_code = '320';aircraft_code |
model
| range
---------------+-----------------+-------
320
| Airbus A320-200 | 5900
(1 строка)
9.4 Уровень изоляции SERIALIZABLEСамый высший уровень изоляции транзакций — SERIALIZABLE. Транзакции могут работать параллельно точно так же, как если бы они выполнялись последовательно одна за другой. Однако, как и при использовании уровня REPEATABLE READ, при- ложение должно быть готово к тому, что
придется перезапускать транзакцию, кото- рая была прервана системой из-за обнаружения зависимостей чтения/записи между транзакциями. Группа транзакций может быть параллельно выполнена и успешно зафиксирована в том случае, когда результат их параллельного выполнения был бы эквивалентен результату выполнения этих транзакций при выборе
одного из возмож-ных вариантових упорядочения, если бы они выполнялись последовательно, одна за другой.
Для проведения эксперимента создадим специальную таблицу, в которой будет все- го два столбца: один — числовой, а второй — текстовый. Назовем эту таблицу —
modes.
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 строки)
На первом терминале начнем транзакцию и обновим одну строку из тех двух строк,
которые были показаны в предыдущем запросе.
210
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN
В команде обновления строки будем использовать предложение RETURNING. По- скольку значение поля num не изменяется, то будет видно, какая строка была обнов- лена. Это особенно пригодится во второй транзакции.
UPDATE modesSET mode = 'HIGH'WHERE mode = 'LOW'RETURNING *;num | mode
-----+------
1 | HIGH
(1 строка)
UPDATE 1
На втором терминале тоже начнем транзакцию и обновим другую строку из тех двух строк, которые были показаны выше.
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN
UPDATE modesSET mode = 'LOW'WHERE mode = 'HIGH'RETURNING *;Изменение, произведенное в первой транзакции, вторая транзакция не видит, по- скольку на уровне изоляции SERIALIZABLE каждая транзакция работает с тем сним- ком базы данных, которых был сделан в ее начале, т. е. непосредственно перед выпол- нением ее первого оператора.
Поэтому обновляется только одна строка, та, в которой значение поля mode было равно «HIGH» изначально.
num | mode
-----+------
2 | LOW
(1 строка)
UPDATE 1
Обратите внимание, что обе команды UPDATE были выполнены, ни одна из них не ожидает завершения другой транзакции.
Посмотрим, что получилось в первой транзакции:
SELECT * FROM modes;num | mode
-----+------
2 | HIGH
1 | HIGH
(2 строки)
211
А во второй транзакции:
SELECT * FROM modes;
num | mode
-----+------
1 | LOW
2 | LOW
(2 строки)
Заканчиваем эксперимент. Сначала завершим транзакцию на первом терминале:
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
Но этот результат не соответствует результату выполнения транзакций ни при одном
из двух возможных вариантов их упорядочения, если бы они выполнялись последо- вательно. Следовательно, с точки зрения концепции сериализации, эти транзакции невозможно сериализовать. Покажем это, выполнив транзакции последовательно.
Предварительно необходимо пересоздать таблицу modes или с помощью команды
UPDATE вернуть ее измененным строкам исходное состояние.
Теперь обе транзакции можно выполнять на одном терминале. Первый вариант их упорядочения такой:
212
Скачать 0.9 Mb.