Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 44: взаимодействие конкурирующих транзакций
Фантомное чтение в Oracle может быть исследовано выполнением в двух отдельных сессиях следующих блоков кода:
Oracle і Решение 6.2.2.a (код для исследования аномалии фантомного чтения)
1 |
-- Транзакция A: |
|
-- Транзакция B: |
|
2 |
ALTER SESSION SET |
|
ALTER SESSION SET |
|
|
||||
3 |
ISOLATION LEVEL = {УРОВЕНЬ}; |
|
ISOLATION LEVEL = {УРОВЕНЬ}; |
|
4 |
SET TRANSACTION {РЕЖИМ}; |
|
SET TRANSACTION {РЕЖИМ}; |
|
5 |
SELECT 'Tr A: ' || |
|
SELECT 'Tr B: ' || |
|
6 |
GET IDS AND ISOLATION LEVEL |
|
GET IDS AND ISOLATION LEVEL |
|
7 |
FROM DUAL; |
|
FROM DUAL |
|
8 |
SELECT 'Tr A START: ' || |
|
SELECT 'Tr B START: ' || |
|
9 |
Г FROM DUAL; |
|
|
Г FROM DUAL ; |
|
|
|
|
|
10 |
|
|
SELECT 'Tr B COUNT-1: ' |
|
11 |
|
|
GET CT FROM DUAL; |
|
12 |
EXEC DBMS LOCK.SLEEP 5 ; |
|
SELECT COUNT(*) |
|
13 |
|
|
FROM |
"subscriptions" |
14 |
|
|
WHERE |
"sb id" > 500; |
15 |
SELECT 'Tr A INSERT: ' |
|
|
|
16 |
GET CT FROM DUAL; |
|
|
|
17 |
INSERT INTO "subscriptions" |
|
|
|
18 |
"sb id" |
|
|
|
19 |
"sb subscriber", |
|
|
|
20 |
"sb book" |
|
|
|
21 |
"sb start" |
|
|
|
22 |
"sb finish", |
|
|
|
23 |
"sb is active") |
|
|
|
24 |
VALUES 1000, |
|
|
|
25 |
1 |
|
EXEC DBMS LOCK.SLEEP110); |
|
26 |
1 |
|
|
|
27 |
TO DATE('2025-01-12', |
|
|
|
|
|
|
|
|
28 |
'YYYY-MM-DD'), |
|
|
|
29 |
TO DATE('2026-01-12', |
|
|
|
30 |
'YYYY-MM-DD'), |
|
|
|
31 |
'N'); |
|
|
|
|
|
|
|
|
32 |
|
|
SELECT 'Tr B COUNT-2: ' |
|
33 |
|
|
GET CT FROM DUAL; |
|
34 |
EXEC DBMS LOCK.SLEEP 10); |
|
SELECT COUNT(*) |
|
35 |
|
|
FROM |
"subscriptions" |
36 |
|
|
WHERE |
"sb id" > 5001 |
37 |
SELECT 'Tr A ROLLBACK: ' |
|
|
|
38 |
GET CT FROM DUAL; |
|
EXEC DBMS LOCK.SLEEP115); |
|
39 |
ROLLBACK; |
|
|
|
|
|
|
|
|
40 |
|
|
SELECT 'Tr B COUNT-3: ' |
|
41 |
|
|
GET CT FROM DUAL; |
|
42 |
|
|
SELECT COUNT(*) |
|
43 |
|
|
FROM |
"subscriptions" |
44 |
|
|
WHERE |
"sb id" > 5001 |
45 |
|
|
SELECT 'Tr B COMMIT: ' || |
|
46 |
|
|
GET CT FROM DUAL; |
|
47 |
|
|
COMMIT; |
|
Перед выполнением представленных выше блоков кода необходимо отключить триггер, обеспечивающий автоинкрементацию первичного ключа в таблице subscriptions (ALTER TRIGGER "TRG_subscriptions_sb_id" DISABLE), а
после проведения эксперимента — снова включить этот триггер (ALTER TRIGGER "TRG_subscriptions_sb_id" ENABLE).
Добавлять эти команды непосредственно перед и после INSERT в транзакции A нельзя, т.к. ALTER TRIGGER приводит к автоматическому подтверждению предыдущей транзакции и запуску новой.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 490/545
Пример 44: взаимодействие конкурирующих транзакций
Итоговые результаты взаимодействия транзакций таковы.
|
|
|
Уровень изолированности транзакции B |
|||
|
|
|
READ COMMITTED |
SERIALIZABLE |
||
|
|
|
READ ONLY |
READ WRITE |
READ ONLY |
READ WRITE |
|
|
READ ONLY |
INSERT в |
INSERT в |
INSERT в |
INSERT в |
A |
|
транзакции A |
транзакции A |
транзакции A |
транзакции A |
|
|
|
|||||
транзакции |
READ |
|
запрещён (R/O) |
запрещён (R/O) |
запрещён (R/O) |
запрещён (R/O) |
|
|
|||||
|
|
|
|
|
|
|
|
|
Транзакция B не |
Транзакция B не |
Транзакция B не |
Транзакция B не |
|
|
COMMITTED |
|
||||
|
|
получает |
получает |
получает |
получает |
|
|
|
|
||||
изолированности |
|
READ WRITE |
доступа к |
доступа к |
доступа к |
доступа к |
|
|
запрещён (R/O) |
запрещён (R/O) |
запрещён (R/O) |
запрещён (R/O) |
|
|
|
|
«фантомной |
«фантомной |
«фантомной |
«фантомной |
|
|
|
записи» |
записи» |
записи» |
записи» |
|
|
READ ONLY |
INSERT в |
INSERT в |
INSERT в |
INSERT в |
|
|
транзакции A |
транзакции A |
транзакции A |
транзакции A |
|
|
|
|
||||
Уровень |
|
|
|
|
|
|
|
READ WRITE |
доступа к |
доступа к |
доступа к |
доступа к |
|
|
SERIALIZABLE |
|
Транзакция B не |
Транзакция B не |
Транзакция B не |
Транзакция B не |
|
|
|
получает |
получает |
получает |
получает |
|
|
|
«фантомной |
«фантомной |
«фантомной |
«фантомной |
|
|
|
записи» |
записи» |
записи» |
записи» |
|
|
|
|
|
|
|
На этом решение данной задачи завершено.
'Vf Решение 6.2.2. b{434}.
В решении{434} задачи 6.2.2.a{434} в некоторых случаях мы получали ситуацию взаимной блокировки транзакций, но сейчас мы рассмотрим код, который гарантированно приводит к такой ситуации во всех трёх СУБД.
На низких уровнях изолированности транзакций у СУБД может появиться возможность избежать взаимной блокировки, потому мы используем уровень SERIALIZABLE. Исследование поведения СУБД при работе на других уровнях изолированности вам предлагается провести самостоятельно в задании 6.2.2.TSK.F{464}.
Важно отметить, что только MS SQL Server позволяет указывать приоритет транзакции, который учитывает при принятии решения о том, какая из двух взаимно заблокированных транзакций будет отменена, MySQL и Oracle принимают такое решение полностью самостоятельно.
Представленный ниже код работает по следующему алгоритму:
•транзакция A обновляет первую таблицу;
•транзакция B обновляет вторую таблицу;
• |
транзакция |
A пытается обновить вторую таблицу |
(ряд, заблокированный |
|
транзакцией B); |
|
|
• |
транзакция |
B пытается обновить первую таблицу |
(ряд, заблокированный |
|
транзакцией A); |
|
|
•наступает взаимная блокировка транзакций. Рассмотрим код, реализующий этот алгоритм.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 491/545
Пример 44: взаимодействие конкурирующих транзакций
Решение для MySQL выглядит следующим образом.
MySQL |
|
Решение 6.2.2.b |
|
|
|
|
||
1 |
-- Транзакция A: |
|
-- Транзакция B: |
|||||
2 |
SET autocommit = 0; |
|
SET autocommit = 0; |
|||||
3 |
SET SESSION TRANSACTION |
|
SET SESSION TRANSACTION ISOLATION |
|||||
4 |
ISOLATION LEVEL SERIALIZABLE; START |
LEVEL SERIALIZABLE; |
||||||
|
TRANSACTION; |
|
START TRANSACTION; |
|||||
|
UPDATE 'books' |
|
|
|
||||
|
SET |
|
'b_name' = |
|
SELECT SLEEP(3 ; |
|||
8 |
|
|
|
CONCAT('b_name', |
'.') |
|||
|
|
|
|
|
||||
|
WHERE |
'b id' = 1; |
|
|
|
|||
10 |
|
|
|
|
|
|
UPDATE 'subscribers' |
|
11 |
SELECT SLEEP(5); |
|
SET |
' s_name' = |
||||
12 |
|
|
|
|
|
|
|
CONCAT('s_name', '.') |
13 |
|
|
|
|
|
|
WHERE |
's id' = 1 |
|
|
|
|
|
||||
|
UPDATE 'subscribers' |
|
|
|
||||
15 |
SET |
|
's_name' = |
|
SELECT SLEEP(3 ; |
|||
16 |
|
|
|
CONCAT('s_name', |
'.') |
|||
|
|
|
|
|
||||
|
WHERE |
's id' = 1; |
|
|
|
|||
|
COMMIT; |
|
|
: 'books' |
||||
19 |
|
|
|
|
|
|
SET |
'b_name' = |
20 |
|
|
|
|
|
|
|
CONCAT('b_name', '.') |
21 |
|
|
|
|
|
|
WHERE |
'b id' = 1 |
|
|
|
|
|
|
|
|
|
22 |
|
|
|
|
|
|
COMMIT; |
|
|
|
|
|
|
|
|
|
|
Решение для MS SQL Server выглядит следующим образом. Обратите внимание на строку 5, в которой для первой транзакции устанавливается повышенный, а для второй — пониженный приоритет, в силу чего СУБД всегда будет отменять вторую транзакцию, позволяя первой успешно завершиться.
MS SQL I Решение 6.2.2.b |
1 |
-- Транзакция A: |
-- Транзакция B: |
||
2 |
SET IMPLICIT_TRANSACTIONS ON; |
SET IMPLICIT_TRANSACTIONS ON; |
||
3 |
SET TRANSACTION ISOLATION |
SET TRANSACTION ISOLATION LEVEL |
||
4 |
LEVEL SERIALIZABLE; |
SERIALIZABLE; |
||
5 |
SET DEADLOCK_PRIORITY HIGH; BEGIN |
SET DEADLOCK_PRIORITY LOW; BEGIN |
||
|
TRANSACTION; |
TRANSACTION; |
||
|
UPDATE |
[books] |
|
|
|
SET |
[b_name] = |
WAITFOR DELAY '00:00:03'; |
|
8 |
|
CONCAT([b_name], '.') |
||
|
|
|
||
9 |
WHERE |
[b id] = 1; |
|
|
10 |
|
|
UPDATE |
[subscribers] |
11 |
WAITFOR DELAY '00:00:05'; |
SET |
[s_name] = |
|
12 |
|
|
|
CONCAT([s_name], '.') |
13 |
|
|
WHERE |
[s id] = 1 |
14 |
UPDATE |
[subscribers] |
|
|
15 |
SET |
[s_name] = |
WAITFOR DELAY '00:00:03'; |
|
16 |
|
CONCAT ( [s_name] , '.') |
||
|
|
|
||
|
WHERE |
[s id] = 1; |
|
|
|
COMMIT; |
UPDATE |
[books] |
|
19 |
|
|
SET |
[b_name] = |
20 |
|
|
|
CONCAT([b_name], '.') |
21 |
|
|
WHERE |
[b id] = 1 |
22 |
|
|
COMMIT; |
|
|
|
|
|
|
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 492/545
Пример 44: взаимодействие конкурирующих транзакций
|
Решение для Oracle выглядит следующим образом. |
||||
Oracle I Решение 6.2.2.b | |
|
|
|||
1 |
-- Транзакция A: |
-- Транзакция B: ALTER SESSION SET |
|||
2 |
ALTER SESSION SET |
||||
ISOLATION_LEVEL = SERIALIZABLE; SET |
|||||
3 |
ISOLATION_LEVEL = SERIALIZABLE; |
||||
TRANSACTION READ WRITE; |
|||||
|
SET TRANSACTION READ WRITE; |
||||
|
|
|
|||
|
UPDATE "books" |
|
|
||
6 |
SET |
"b_name" = |
EXEC DBMS_LOCK.SLEEP(3); |
||
|
|
CONCAT "b_name", '.') |
|||
|
|
|
|
||
|
WHERE |
"b id" = 1; |
|
|
|
9 |
|
|
UPDATE "subscribers" |
||
10 |
EXEC DBMS_LOCK.SLEEP(5|; |
SET |
"s_name" = |
||
11 |
|
|
|
CONCAT("s_name" , '.') |
|
12 |
|
|
WHERE |
"s id" = 1 |
|
|
|
|
|
||
|
UPDATE "subscribers" |
|
|
||
15 |
SET |
"s_name" = |
EXEC DBMS_LOCK.SLEEP(3); |
||
16 |
|
CONCAT "s_name", '.') |
|||
|
|
|
|||
|
WHERE |
"s id" = 1; |
|
|
|
|
COMMIT; |
UPDATE "books" |
|||
19 |
|
|
SET |
"b_name" = |
|
20 |
|
|
|
CONCAT("b_name", '.') |
|
21 |
|
|
WHERE |
"b id" = 1 |
|
|
|
|
|
||
22 |
|
|
COMMIT; |
||
На этом решение данной задачи завершено.
Задание 6.2.2.TSK.A: повторить исследование, представленное в решении*434* задачи 6.2.2. a*434* и лично посмотреть на поведение всех трёх СУБД во всех рассмотренных ситуациях.
Задание 6.2.2.TSK.B: повторить исследование, представленное в решении*462 задачи 6.2.2. b*434* и лично посмотреть на поведение всех трёх СУБД во всех рассмотренных ситуациях.
(Ъ Задание 6.2.2.TSK.C: написать код, в котором запрос, инвертирующий значения поля sb_is_active таблицы subscriptions с Y на N и
наоборот, будет иметь максимальные шансы на успешное завершение в случае возникновения ситуации взаимной блокировки с другими транзакциями.
Задание 6.2.2.TSK.D: провести исследование поведения MySQL в контексте аномалии неповторяющегося чтения, выполняя первую операцию в каждой транзакции в режимах LOCK IN SHARE MODE и FOR UPDATE. (см.
решение*434* задачи 6.2.2.a*434*).
Задание 6.2.2.TSK.E: провести исследование поведения MS SQL Server в контексте аномалий потерянного обновления и неповторяющегося чтения, выполняя первую операцию в каждой транзакции с использованием «табличной подсказки38» UPDLOCK (см. решение*434* задачи 6.2.2.a*434*).
Задание 6.2.2.TSK.F: повторить решение*462* задачи 6.2.2.b*434* для всех трёх СУБД в остальных поддерживаемых ими уровнях изолированности транзакций, найти такие комбинации уровней изолированности, при которых взаимная блокировка транзакций не возникает.
38 https://msdn.microsoft.com/en-us/library/ms187373%28v=sql.110%29.aspx
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 493/545
Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах
6.2.3.Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах
Задача 6.2.3.a{465}: создать на таблице books триггер, определяющий уровень изолированности транзакции, в котором сейчас проходит операция вставки, и отменяющий операцию, если уровень изолированности транзакции отличен от SERIALIZABLE.
Задача 6.2.3.b{469}: создать хранимую функцию, порождающую исключительную ситуацию в случае запуска в режиме автоподтверждения транзакций.
Задача 6.2.3.c{471}: создать хранимую процедуру, выполняющую подсчёт количества записей в указанной таблице таким образом, чтобы запрос выполнялся максимально быстро (вне зависимости от параллельно выполняемых запросов), даже если в итоге он вернёт не совсем корректные данные.
Ожидаемый результат 6.2.3.a.
Если операция вставки данных в таблицу books выполняется в транзакции с уровнем изолированности, отличным от SERIALIZABLE, триггер отменяет эту операцию и порождает исключительную ситуацию.
Ожидаемый результат 6.2.3.b.
Если хранимая функция оказывается вызванной в момент, когда для текущей сессии с СУБД включён режим автоподтверждения транзакций, функция должна порождать исключительную ситуацию и прекращать свою работу.
Ожидаемый результат 6.2.3.C.
Хранимая процедура должна выполнять подсчёт записей в указанной таблице в транзакции с уровнем изолированности, обеспечивающим минимальную вероятность ожидания завершения конкурирующих транзакций или отдельных операций в них.
чРешение 6.2.3.a{465}.
Для простоты (отсутствия необходимости вручную выполнять вставку) и единообразия (поддержки всеми тремя СУБД) используем AFTER-триггеры.
Таким образом, представленные ниже решения будут отличаться только логикой определения уровня изолированности транзакций, т.к. в каждой СУБД соответствующий механизм реализован совершенно особенным, несовместимым с другими СУБД, образом.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 494/545