Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Пример 43: управление уровнем изолированности транзакций

1SELECT @@SPID;

2SET IMPLICIT_TRANSACTIONS ON;

3SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

4BEGIN TRANSACTION;

5SELECT [sb_finish]

6 FROM [subscriptions];

7-- WAITFOR DELAY '00:00:10';

8COMMIT TRANSACTION;

1SELECT @@SPID;

2SET IMPLICIT_TRANSACTIONS ON;

3BEGIN TRANSACTION;

4UPDATE [subscriptions]

6-- WAITFOR DELAY '00:00:10';

7COMMIT TRANSACTION;

Раскомментировав строку с WAITFOR DELAY '00:00:10' в соответствующем блоке кода, мы проэмулируем его долгое выполнение, что позволит нам не спеша несколько раз выполнить второй блок (в котором эта строка останется закомментированной) и посмотреть на результат.

На этом решение данной задачи для MS SQL Server завершено.

Переходим к Oracle. Продолжая аналогию с только рассмотренными решениями для MySQL и MS SQL, отметим, что:

•уровень изолированности транзакций в Oracle по умолчанию — READ COMMITTED (как и в MS SQL Server);

•в отличие от MySQL и MS SQL Server в Oracle нет уровня изолированности транзакций READ UNCOMMITTED;

•операции чтения и модификации данных в Oracle не блокируют друг друга35, потому решение текущей задачи сводится к простому выполнению необходимых запросов (но для сохранения единообразия мы будем придерживаться того же набора команд, что был использован в MySQL и MS SQL Server).

Рассмотрим код. Следующие два блока кода необходимо выполнять в отдельных соединениях с СУБД (отдельных сессиях), потому обязательно удостоверьтесь, что запросы во вторы строках каждого из блоков возвращают разные значения идентификаторов сессий. В Oracle SQL Developer открыть новое окно для выполнения запросов в отдельной сессии можно клавиатурной комбинацией

Ctrl+Shift+N.

В первых строках обоих блоков кода выполняется операция COMMIT, чтобы гарантировать выполнение дальнейших запросов в новой отдельной транзакции.

1COMMIT;

2SELECT SYS_CONTEXT('userenv', 'sessionid')

3 FROM DUAL;

4SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

5SELECT "sb_finish"

7-- EXEC DBMS_LOCK.SLEEP(10);

8COMMIT;

35 http://www.oracle.com/technetwork/issue-archive/2010/10-jan/o65asktom-082389.html

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 430/545

Пример 43: управление уровнем изолированности транзакций

1COMMIT;

2SELECT SYS_CONTEXT('userenv', 'sessionid')

3 FROM DUAL;

4SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

5SELECT "sb_finish"

7-- EXEC DBMS_LOCK.SLEEP(10);

8COMMIT;

Раскомментировав строку с EXEC DBMS_LOCK.SLEEP(10) в соответствующем блоке кода, мы проэмулируем его долгое выполнение, что позволит нам не спеша несколько раз выполнить второй блок (в котором эта строка останется закомментированной) и посмотреть на результат.

На этом решение данной задачи завершено.

Решение 6.2.1.b{428}.

Решение данной задачи подчиняется общей логике разделения уровней изолированности транзакций:

•чем уровень ниже, тем больше у СУБД возможностей выполнить запрос параллельно с другими, но тем выше вероятность получить некорректный результат;

•чум уровень выше, тем меньше у СУБД возможностей выполнить запрос параллельно с другими, но тем ниже вероятность получить некорректный результат;

•в MySQL и MS SQL Server самым низким уровнем является READ UNCOMMITTED, в Oracle — READ COMMITTED;

•во всех трёх СУБД самым высоким уровнем является SERIALIZABLE.

Учитывая эти факты, нам остаётся только написать код для выполнения одного и того же запроса на самом низком и самом высоком уровнях изолированности транзакций, а также подготовить проверочный код, который позволит увидеть разницу в работе этих двух вариантов выполнения основного кода.

Код для MySQL выглядит следующим образом.

MySQL Решение 6.2.1.b (максимально быстрое выполнение, возможны некорректные данные)

1SELECT CONNECTION_ID();

2SET autocommit = 0;

3SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

4START TRANSACTION;

5SELECT `sb_subscriber`,

6COUNT(`sb_book`) AS `sb_has_books`

9GROUP BY `sb_subscriber`;

10COMMIT;

MySQL Решение 6.2.1.b (максимально корректные данные, возможно долгое выполнение)

1SELECT CONNECTION_ID();

2SET autocommit = 0;

3SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

4START TRANSACTION;

5SELECT `sb_subscriber`,

6COUNT(`sb_book`) AS `sb_has_books`

9GROUP BY `sb_subscriber`;

10COMMIT;

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 431/545

Пример 43: управление уровнем изолированности транзакций

MySQL Решение 6.2.1.b (проверочный код)

1SELECT CONNECTION_ID();

2SET autocommit = 0;

3START TRANSACTION;

4UPDATE `subscriptions`

5 SET `sb_is_active` =

6CASE

7WHEN `sb_is_active` = 'Y' THEN 'N'

8WHEN `sb_is_active` = 'N' THEN 'Y'

9END;

10SELECT SLEEP(10);

11COMMIT;

Код для MS SQL Server выглядит следующим образом.

MS SQL Решение 6.2.1.b (максимально быстрое выполнение, возможны некорректные данные)

1SELECT @@SPID;

2SET IMPLICIT_TRANSACTIONS ON;

3SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

4BEGIN TRANSACTION;

5SELECT [sb_subscriber],

6COUNT([sb_book]) AS [sb_has_books]

9GROUP BY [sb_subscriber];

10COMMIT TRANSACTION;

MS SQL Решение 6.2.1.b (максимально корректные данные, возможно долгое выполнение)

1SELECT @@SPID;

2SET IMPLICIT_TRANSACTIONS ON;

3SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

4BEGIN TRANSACTION;

5SELECT [sb_subscriber],

6COUNT([sb_book]) AS [sb_has_books]

9GROUP BY [sb_subscriber];

10COMMIT TRANSACTION;

MS SQL Решение 6.2.1.b (проверочный код)

1SELECT @@SPID;

2SET IMPLICIT_TRANSACTIONS ON;

3BEGIN TRANSACTION;

4UPDATE [subscriptions]

5 SET [sb_is_active] =

6CASE

7WHEN [sb_is_active] = 'Y' THEN 'N'

8WHEN [sb_is_active] = 'N' THEN 'Y'

9END;

10WAITFOR DELAY '00:00:10';

11COMMIT TRANSACTION;

Код для Oracle выглядит следующим образом.

Oracle Решение 6.2.1.b (максимально быстрое выполнение, возможны некорректные данные)

1COMMIT;

2SELECT SYS_CONTEXT('userenv','sessionid') FROM DUAL;

3SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

4SELECT "sb_subscriber",

5COUNT("sb_book") AS "sb_has_books"

6 FROM "subscriptions"

7WHERE "sb_is_active" = 'Y'

8GROUP BY "sb_subscriber";

9COMMIT;

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 432/545

Пример 43: управление уровнем изолированности транзакций

1COMMIT;

2SELECT SYS_CONTEXT('userenv','sessionid') FROM DUAL;

3SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

4SELECT "sb_subscriber",

5COUNT("sb_book") AS "sb_has_books"

6 FROM "subscriptions"

7WHERE "sb_is_active" = 'Y'

8GROUP BY "sb_subscriber";

9COMMIT;

Oracle Решение 6.2.1.b (проверочный код)

1COMMIT;

2SELECT SYS_CONTEXT('userenv','sessionid') FROM DUAL;

3UPDATE "subscriptions"

4 SET "sb_is_active" =

5CASE

6WHEN "sb_is_active" = 'Y' THEN 'N'

7WHEN "sb_is_active" = 'N' THEN 'Y'

8END;

9EXEC DBMS_LOCK.SLEEP(10);

10COMMIT;

Для всех трёх СУБД проверочный код необходимо выполнять в отдельной сессии (см. пояснения в решении{428} задачи 6.2.1.a{428}), при этом основной код надо выполнять до начала работы проверочного, во время его работы и после его завершения — это позволит наглядно увидеть, какие данные и в какой момент времени СУБД будет извлекать из базы данных.

Ещё один вариант поведения СУБД можно увидеть, заменив в проверочном коде последнюю команду с COMMIT на ROLLBACK.

Обратите особое внимание на отличие поведения Oracle от MySQL и MS SQL Server: даже в SERIALIZABLE-режиме запрос вернёт результаты без задержки.

На этом решение данной задачи завершено.

Задание 6.2.1.TSK.A: написать запросы, которые, будучи выполненными параллельно, обеспечивали бы следующий эффект:

•первый запрос должен считать количество выданных на руки и возвращённых в библиотеку книг и не зависеть от запросов на обновление таблицы subscriptions (не ждать их завершения);

•второй запрос должен инвертировать значения поля sb_is_active таблицы subscriptions с Y на N и наоборот и не зависеть от первого запроса (не ждать его завершения).

Задание 6.2.1.TSK.B: написать запросы, которые, будучи выполненными параллельно, обеспечивали бы следующий эффект:

•первый запрос должен считать количество выданных на руки и возвращённых в библиотеку книг;

•второй запрос должен инвертировать значения поля sb_is_active таблицы subscriptions с Y на N и наоборот для читателей с нечёт-

ными идентификаторами, после чего делать паузу в десять секунд и отменять данное изменение (отменять транзакцию).

Исследовать поведение все трёх СУБД при выполнении первого запроса до, во время и после завершения выполнения второго запроса, повторив этот эксперимент для всех поддерживаемых конкретной СУБД уровней изолированности транзакций.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 433/545

Пример 44: взаимодействие конкурирующих транзакций

6.2.2. Пример 44: взаимодействие конкурирующих транзакций

Задача 6.2.2.a{434}: продемонстрировать во всех трёх СУБД все аномалии конкурентного доступа для всех возможных комбинаций уровней изолированности транзакций.

Задача 6.2.2.b{462}: продемонстрировать во всех трёх СУБД ситуацию гарантированного получения взаимной блокировки транзакций и реакцию СУБД на такую ситуацию.

Ожидаемый результат 6.2.2.a.

Поскольку решение данной задачи и является ожидаемым результатом, см.

решение{434} 6.2.2.a.

Ожидаемый результат 6.2.2.b.

Поскольку решение данной задачи и является ожидаемым результатом, см.

решение{462} 6.2.2.b.

Решение 6.2.2.a{434}.

К аномалиям конкурентного доступа относятся:

•грязное чтение (dirty read) — чтение промежуточного состояния данных до того, как модифицирующая их транзакция будет подтверждена или отменена;

•потерянное обновление (lost update) — модификация одной и той же информации двумя и более транзакциями, при которой в силу вступают изменения, выполненные транзакцией, которая была подтверждена последней (а изменения, выполненные остальными транзакциями, теряются);

•неповторяющееся чтение (non-repeatable read) — получение различных результатов выполнения одного и того же запроса на чтение в рамках одной транзакции;

•фантомное чтение (phantom read) — временное появление (исчезновение) в наборе данных, с которым работает транзакция, тех или иных записей в силу их изменения другой транзакцией.

Для удобства навигации приведём таблицу, показывающую номера страниц,

скоторых начинается рассмотрение той или иной аномалии в каждой СУБД.

Также отметим, что поскольку протоколы исследований будут выглядеть однотипно во всех СУБД, для экономии места мы ниже приведём их только для MySQL, причём в рамках исследования каждой аномалии конкурентного доступа для первой транзакции покажем только один уровень изолированности, а для второй — все поддерживаемые данной СУБД уровни изолированности.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 434/545