Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 44: взаимодействие конкурирующих транзакций
Итоговые результаты взаимодействия транзакций таковы.
|
|
|
Уровень изолированности транзакции B |
|||
|
|
|
READ COMMITTED |
SERIALIZABLE |
||
|
|
|
READ ONLY |
READ WRITE |
READ ONLY |
READ WRITE |
|
|
|
Первый и вто- |
Первый и вто- |
Первый и вто- |
Первый и вто- |
|
|
|
рой SELECT |
рой SELECT |
рой SELECT |
рой SELECT |
|
|
|
возвратили |
возвратили |
возвратили |
возвратили |
|
|
READ ONLY |
одинаковые |
одинаковые |
одинаковые |
одинаковые |
|
|
данные, UP- |
данные, UP- |
данные, UP- |
данные, UP- |
|
|
|
|
||||
A |
READ |
|
DATE в тран- |
DATE в тран- |
DATE в тран- |
DATE в тран- |
|
закции A за- |
закции A за- |
закции A за- |
закции A за- |
||
транзакции |
COMMITTED |
|
||||
|
прещён (R/O) |
прещён (R/O) |
прещён (R/O) |
прещён (R/O) |
||
|
|
|||||
|
|
|
||||
|
|
|
Первый и вто- |
Первый и вто- |
Первый и вто- |
Первый и вто- |
|
|
|
рой SELECT |
рой SELECT |
рой SELECT |
рой SELECT |
изолированности |
|
READ WRITE |
возвратили |
возвратили |
возвратили |
возвратили |
|
|
одинаковые |
одинаковые |
одинаковые |
одинаковые |
|
|
|
|
одинаковые |
разные дан- |
одинаковые |
одинаковые |
|
|
|
данные |
ные |
данные |
данные |
|
|
|
Первый и вто- |
Первый и вто- |
Первый и вто- |
Первый и вто- |
|
|
|
рой SELECT |
рой SELECT |
рой SELECT |
рой SELECT |
|
|
|
возвратили |
возвратили |
возвратили |
возвратили |
Уровень |
SERIALIZABLE |
READ ONLY |
закции A за- |
закции A за- |
закции A за- |
закции A запре- |
|
||||||
|
|
|
данные, UP- |
данные, UP- |
данные, UP- |
данные, UP- |
|
|
|
DATE в тран- |
DATE в тран- |
DATE в тран- |
DATE в тран- |
|
|
|
прещён (R/O) |
прещён (R/O) |
прещён (R/O) |
щён (R/O) |
|
|
|
Первый и вто- |
Первый и вто- |
Первый и вто- |
Первый и вто- |
|
|
|
рой SELECT |
рой SELECT |
рой SELECT |
рой SELECT |
|
|
READ WRITE |
возвратили |
возвратили |
возвратили |
возвратили |
|
|
|
одинаковые |
разные дан- |
одинаковые |
одинаковые |
|
|
|
данные |
ные |
данные |
данные |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 460/545
Пример 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 |
GET_CT FROM DUAL; |
GET_CT |
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.SLEEP(10); |
||
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" > 500; |
37 |
SELECT 'Tr A ROLLBACK: ' || |
|
|
|
38 |
GET_CT FROM DUAL; |
EXEC DBMS_LOCK.SLEEP(15); |
||
39 |
ROLLBACK; |
|
|
|
40 |
|
|
SELECT |
'Tr B COUNT-3: ' || |
41 |
|
|
GET_CT |
FROM DUAL; |
42 |
|
|
SELECT |
COUNT(*) |
43 |
|
|
FROM |
"subscriptions" |
44 |
|
|
WHERE |
"sb_id" > 500; |
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 Стр: 461/545
Пример 44: взаимодействие конкурирующих транзакций
Итоговые результаты взаимодействия транзакций таковы.
|
|
|
Уровень изолированности транзакции B |
|||
|
|
|
READ COMMITTED |
SERIALIZABLE |
||
|
|
|
READ ONLY |
READ WRITE |
READ ONLY |
READ WRITE |
|
|
|
INSERT в |
INSERT в |
INSERT в |
INSERT в |
A |
|
READ ONLY |
транзакции 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) |
|
|
|
|
«фантомной |
«фантомной |
«фантомной |
«фантомной |
|
|
|
записи» |
записи» |
записи» |
записи» |
|
|
|
INSERT в |
INSERT в |
INSERT в |
INSERT в |
|
|
READ ONLY |
транзакции A |
транзакции A |
транзакции A |
транзакции A |
|
|
запрещён |
запрещён |
запрещён |
запрещён |
|
|
|
|
||||
Уровень |
SERIALIZABLE |
|
|
|
|
|
READ WRITE |
доступа к |
доступа к |
доступа к |
доступа к |
||
|
|
Транзакция B |
Транзакция B |
Транзакция B |
Транзакция B |
|
|
|
|
не получает |
не получает |
не получает |
не получает |
|
|
|
«фантомной |
«фантомной |
«фантомной |
«фантомной |
|
|
|
записи» |
записи» |
записи» |
записи» |
На этом решение данной задачи завершено.
Решение 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 Стр: 462/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 |
||||
4 |
ISOLATION LEVEL SERIALIZABLE; |
ISOLATION LEVEL SERIALIZABLE; |
||||
5 |
START TRANSACTION; |
START TRANSACTION; |
||||
6 |
UPDATE |
`books` |
|
|
||
7 |
SET |
|
`b_name` = |
SELECT |
SLEEP(3); |
|
8 |
|
|
|
CONCAT(`b_name`, '.') |
|
|
9 |
WHERE |
`b_id` = 1; |
|
|
||
|
|
|
|
|
|
|
10 |
|
|
|
|
UPDATE |
`subscribers` |
11 |
SELECT |
SLEEP(5); |
SET |
`s_name` = |
||
12 |
|
|
|
|
|
CONCAT(`s_name`, '.') |
13 |
|
|
|
|
WHERE |
`s_id` = 1; |
14 |
UPDATE |
`subscribers` |
|
|
||
15 |
SET |
|
`s_name` = |
SELECT |
SLEEP(3); |
|
16 |
|
|
|
CONCAT(`s_name`, '.') |
|
|
17 |
WHERE |
`s_id` = 1; |
|
|
||
18 |
COMMIT; |
|
UPDATE |
`books` |
||
19 |
|
|
|
|
SET |
`b_name` = |
20 |
|
|
|
|
|
CONCAT(`b_name`, '.') |
21 |
|
|
|
|
WHERE |
`b_id` = 1; |
22 |
|
|
|
|
COMMIT; |
|
Решение для MS SQL Server выглядит следующим образом. Обратите внимание на строку 5, в которой для первой транзакции устанавливается повышенный, а для второй — пониженный приоритет, в силу чего СУБД всегда будет отменять вторую транзакцию, позволяя первой успешно завершиться.
MS SQL |
|
Решение 6.2.2.b |
|
|
||
|
|
|
||||
1 |
-- Транзакция A: |
-- Транзакция B: |
||||
2 |
SET IMPLICIT_TRANSACTIONS ON; |
SET IMPLICIT_TRANSACTIONS ON; |
||||
3 |
SET TRANSACTION ISOLATION |
SET TRANSACTION ISOLATION |
||||
4 |
LEVEL SERIALIZABLE; |
LEVEL SERIALIZABLE; |
||||
5 |
SET DEADLOCK_PRIORITY HIGH; |
SET DEADLOCK_PRIORITY LOW; |
||||
6 |
BEGIN TRANSACTION; |
BEGIN TRANSACTION; |
||||
6 |
UPDATE |
[books] |
|
|
||
7 |
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], '.') |
|
|
17 |
WHERE |
[s_id] = 1; |
|
|
||
|
|
|
|
|
||
18 |
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 Стр: 463/545
Пример 44: взаимодействие конкурирующих транзакций
Решение для Oracle выглядит следующим образом.
Oracle |
|
Решение 6.2.2.b |
|
|
|
1 |
-- Транзакция A: |
-- Транзакция B: |
|||
2 |
ALTER SESSION SET |
ALTER SESSION SET |
|||
3 |
ISOLATION_LEVEL = SERIALIZABLE; |
ISOLATION_LEVEL = SERIALIZABLE; |
|||
4 |
SET TRANSACTION READ WRITE; |
SET TRANSACTION READ WRITE; |
|||
5 |
UPDATE |
"books" |
|
|
|
6 |
SET |
"b_name" = |
EXEC DBMS_LOCK.SLEEP(3); |
||
7 |
|
|
CONCAT("b_name", '.') |
|
|
8 |
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; |
14 |
UPDATE |
"subscribers" |
|
|
|
15 |
SET |
"s_name" = |
EXEC DBMS_LOCK.SLEEP(3); |
||
16 |
|
|
CONCAT("s_name", '.') |
|
|
17 |
WHERE |
"s_id" = 1; |
|
|
|
18 |
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 Стр: 464/545