Материал: Using_MySql,_MS_SQL_Server_and_Oracle

Пример 41: управление неявными транзакциями

24 END;

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 455/545

Пример 41: управление неявными транзакциями

Для проверки работоспособности полученного решения можно использовать следующий запрос.

Oracle Решение 6.1.2.b (код для проверки работоспособности)

1 EXECUTE CHANGE_DATES

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

Задание 6.1.2.TSK.A: создать хранимую процедуру, которая:

•добавляет каждой книге два случайных жанра;

•отменяет совершённые действия, если в процессе работы хотя бы одна операция вставки завершилась ошибкой в силу дублирования значения первичного ключа таблицы m2m_books_genres (т.е. у такой книги уже был такой жанр).

Задание 6.1.2.TSK.B: создать хранимую процедуру, которая:

•увеличивает значение поля b_quantity для всех книг в два раза;

•отменяет совершённое действие, если по итогу выполнения операции среднее количество экземпляров книг превысит значение 50.

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 456/545

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

6.2. Конкурирующие транзакции

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

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

•первый запрос должен добавлять ко всем датам возврата книг один день

ине зависеть от запросов на чтение из таблицы subscriptions (не

ждать их завершения);

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

О Задача 6.2.1.b{431}: написать два запроса, каждый из которых будет считать количество выданных каждому читателю книг, но при этом:

•один запрос должен выполняться максимально быстро (даже ценой предоставления не совсем достоверных данных);

•другой запрос должен предоставлять гарантированно достоверные данные (даже ценой большого времени выполнения).

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

При любом варианте запуска («первый, потом второй» или «второй, потом первый») запросы работают параллельно, и ни один из них не ожидает завершения другого.

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

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

ЧРешение 6.2.1 .a{428}.

ВMySQL при использовании механизма доступа InnoDB запросы на обновление данных по умолчанию имеют более высокий приоритет, чем запросы на чтение данных.

Если в момент запуска обновления уже выполняется операция чтения, обновление будет ожидать её завершения только в одном случае: если она запущена

втранзакции с уровнем изолированности SERIALIZABLE. Учитывая, что уровнем

изолированности транзакций по умолчанию является REPEATABLE READ, с первой

частью задачи у нас нет особых проблем: обновление начнётся сразу же.

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

мы получать «сырые данные» (изменения, ещё не вступившие в силу) или же хотим получать только данные, сохранённые в базе данных при подтверждении транзакции? В первом случае запрос на чтение нужно выполнять в транзакции с уровнем изолированности READ UNCOMMITTED, во втором — в транзакции с уровнем изоли-

рованности READ COMMITTED или REPEATABLE READ.

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

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

Остаётся лишь реализовать данную идею в коде. Следующие два блока кода необходимо выполнять в отдельных соединениях с СУБД (отдельных сессиях), потому обязательно удостоверьтесь, что запросы в первых строках каждого из блоков возвращают разные значения идентификаторов сессий. В MySQL Workbench вы можете открыть несколько копий одного соединения с СУБД33, и они будут работать в разных сессиях.

Решение 6.2.1.a

1SELECT CONNECTION_ID();

2SET autocommit = 0;

3START TRANSACTION;

4UPDATE 'subscriptions'

6— SELECT SLEEP(10);

7COMMIT;

1SELECT CONNECTION_ID();

2SET autocommit = 0;

3SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

4START TRANSACTION;

5SELECT 'sb_finish'

6FROM 'subscriptions';

7— SELECT SLEEP(10);

8COMMIT;

Раскомментировав строку с SELECT SLEEP(10) в соответствующем блоке

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

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

Переходим к MS SQL Server. Логика поведения данной СУБД почти совпадает с логикой MySQL, но есть и отличия:

•уровнем изолированности транзакций в MS SQL по умолчанию является READ COMMITTED (это не влияет на решение данной задачи);

•при выполнении запроса на чтение в транзакции с уровнем изолированности READ COMMITTED MS SQL Server в отличие от MySQL не вернёт мгновенно

текущие актуальные данные, а будет ждать завершения конкурирующих транзакций, выполняющих модификацию данных (из этого следует, что для соблюдения условия задачи мы обязаны выполнять запрос на чтение в транзакции с уровнем изолированности READ UNCOMMITTED).

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

33https://dev.mysql.com/doc/workbench/en/wb-mysql-connections-new.html

34https://msdn.microsoft.com/en-us/library/ms174195.aspx

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

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

MS SQL Решение 6.2.1.a

1 SELECT @@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, чтобы гарантировать выполнение дальнейших запросов в новой отдельной транзакции.

.1.a

1COMMIT;

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

3FROM DUAL;

4SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

5SELECT "sb finish"

6FROM "subscriptions" ORDER BY "sb_finish" ASC;

7— EXEC DBMS_LOCK.SLEEP(10);

8COMMIT;

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

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