Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 41: управление неявными транзакциями
MS SQL Решение 6.1.1.a
1-- Автоподтверждение выключено:
2SET IMPLICIT_TRANSACTIONS ON;
3 |
|
|
|
4 |
|
SELECT |
COUNT(*) |
5 |
|
FROM |
[subscribers]; -- 4 |
6 |
|
|
|
7 |
|
INSERT |
INTO [subscribers] |
8 |
|
|
([s_name]) |
9 |
|
VALUES |
(N'Иванов И.И.'); |
10 |
|
|
|
11 |
|
SELECT |
COUNT(*) |
12 |
|
FROM |
[subscribers]; -- 5 |
13 |
|
|
|
14 |
|
ROLLBACK; |
|
15 |
|
|
|
16 |
|
SELECT |
COUNT(*) |
17 |
|
FROM |
[subscribers]; -- 4 |
18 |
|
|
|
19-- Автоподтверждение включено:
20SET IMPLICIT_TRANSACTIONS OFF;
21 |
|
|
|
22 |
|
SELECT |
COUNT(*) |
23 |
|
FROM |
[subscribers]; -- 4 |
24 |
|
|
|
25 |
|
INSERT |
INTO [subscribers] |
26 |
|
|
([s_name]) |
27 |
|
VALUES |
(N'Иванов И.И.'); |
28 |
|
|
|
29 |
|
SELECT |
COUNT(*) |
30 |
|
FROM |
[subscribers]; -- 5 |
31 |
|
|
|
32ROLLBACK; -- Ошибка! Нет соответствующей транзакции, которую
33-- можно было бы отменить.
34 |
|
|
|
35 |
|
SELECT |
COUNT(*) |
36 |
|
FROM |
[subscribers]; -- 5 |
Встроках 1-17 запросы выполняются в режиме отключённого автоподтверждения неявных транзакций: именно поэтому отмена транзакции в строке 14 проходит успешно и вставка данных, выполненная в строках 7-9, аннулируется.
Встроках 19-36 запросы выполняются в режиме включённого автоподтверждения неявных транзакций, и потому отмена транзакции в строке 32 ни на что не влияет: вставка данных, выполненная в строках 25-27, остаётся в силе.
В MS SQL Server существует одна важная особенность, которую необходимо учитывать. Если в режиме IMPLICIT_TRANSACTIONS ON использовать выражение BEGIN TRANSACTION, СУБД читает созданную транзакцию вложенной (@@TRANCOUNT принимает значение 2) и для успешного подтверждения её выполнения необходимо использовать выражение COMMIT TRANSACTION дважды. В противном случае вы рискуете или получить «подвисшую» транзакцию (которая так и не завершена), или потерять результаты модификации данных (если закроете соединение с СУБД). При этом ROLLBACK TRANSACTION работает в обоих режимах одинаково, отменяя все транзакции вне зависимости от глубины их вложенности.
Эта проблема усугубляется тем, что при отладке запросов в средствах наподобие MS SQL Server Management Studio вы, как правило, работаете в рамках одного и того же соединения, и вместо «подвисшей» транзакции получаете продол-
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 410/545
Пример 41: управление неявными транзакциями
жение предыдущей (не закрытой ранее). Потому в большинстве случаев при отладке всё работает правильно, а в реальных приложениях поведение становится неверным.
Продемонстрируем только что описанное поведение MS SQL Server.
MS SQL Решение 6.1.1.a (демонстрация особенности работы MS SQL Server)
1-- Режим по умолчанию
2SET IMPLICIT_TRANSACTIONS OFF;
3PRINT @@TRANCOUNT; -- 0
4-- Старт первой ("родительской") транзакции
5BEGIN TRANSACTION;
6PRINT @@TRANCOUNT; -- 1
7-- Старт второй ("дочерней") транзакции
8BEGIN TRANSACTION;
9PRINT @@TRANCOUNT; -- 2
10-- Подтверждение второй ("дочерней") транзакции
11COMMIT TRANSACTION;
12PRINT @@TRANCOUNT; -- 1
13-- Подтверждение первой ("родительской") транзакции
14COMMIT TRANSACTION;
15PRINT @@TRANCOUNT; -- 0
16
17-- Режим "неявных транзакций"
18SET IMPLICIT_TRANSACTIONS ON;
19PRINT @@TRANCOUNT; -- 0
20-- Старт первой ("родительской") транзакции
21BEGIN TRANSACTION;
22PRINT @@TRANCOUNT; -- 2
23-- Старт второй ("дочерней") транзакции
24BEGIN TRANSACTION;
25PRINT @@TRANCOUNT; -- 3
26-- Подтверждение второй ("дочерней") транзакции
27COMMIT TRANSACTION;
28PRINT @@TRANCOUNT; -- 2
29-- Подтверждение первой ("родительской") транзакции
30COMMIT TRANSACTION;
31PRINT @@TRANCOUNT; -- 1
32-- Необходим ещё и этот COMMIT
33COMMIT TRANSACTION;
34PRINT @@TRANCOUNT; -- 0
35
36-- Режим по умолчанию
37SET IMPLICIT_TRANSACTIONS OFF;
38-- Старт первой ("родительской") транзакции
39BEGIN TRANSACTION;
40PRINT @@TRANCOUNT; -- 1
41-- Старт второй ("дочерней") транзакции
42BEGIN TRANSACTION;
43PRINT @@TRANCOUNT; -- 2
44-- Отмена всех транзакций
45ROLLBACK TRANSACTION;
46PRINT @@TRANCOUNT; -- 0
47
48-- Режим "неявных транзакций"
49SET IMPLICIT_TRANSACTIONS ON;
50-- Старт первой ("родительской") транзакции
51BEGIN TRANSACTION;
52PRINT @@TRANCOUNT; -- 2
53-- Старт второй ("дочерней") транзакции
54BEGIN TRANSACTION;
55PRINT @@TRANCOUNT; -- 3
56-- Отмена всех транзакций
57ROLLBACK TRANSACTION;
58PRINT @@TRANCOUNT; -- 0
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 411/545
Пример 41: управление неявными транзакциями
Oracle (в отличие от MySQL и MS SQL Server) не оперирует такими понятиями, как «неявная транзакция» и её автоподтверждение. Эта СУБД лишь автоматически подтверждает текущую транзакцию в случае, если выполняется выражение, модифицирующее структуру базы данных.
Однако клиентское ПО, организующее взаимодействие с Oracle, может иметь свои собственные настройки, отвечающие за автоматическое подтверждение транзакций, не обрамлённых явно выражениями по запуску и подтверждению или отмене.
В таком средстве как Oracle SQL Developer, например, соответствующий эффект достигается выполнением команды SET AUTOCOMMIT ON / OFF (эффект которой эквивалентен изменению параметра autocommit в MySQL).
Для решения данной задачи в Oracle необходимо использовать следующий набор запросов.
Oracle Решение 6.1.1.a
1-- Автоподтверждение выключено:
2SET AUTOCOMMIT OFF;
3 |
|
|
|
4 |
|
SELECT |
COUNT(*) |
5 |
|
FROM |
"subscribers"; -- 4 |
6 |
|
|
|
7 |
|
INSERT |
INTO "subscribers" |
8 |
|
|
("s_name") |
9 |
|
VALUES |
(N'Иванов И.И.'); |
10 |
|
|
|
11 |
|
SELECT |
COUNT(*) |
12 |
|
FROM |
"subscribers"; -- 5 |
13 |
|
|
|
14 |
|
ROLLBACK; |
|
15 |
|
|
|
16 |
|
SELECT |
COUNT(*) |
17 |
|
FROM |
"subscribers"; -- 4 |
18 |
|
|
|
|
|
|
|
19-- Автоподтверждение включено:
20SET AUTOCOMMIT ON;
21 |
|
|
|
22 |
|
SELECT |
COUNT(*) |
23 |
|
FROM |
"subscribers"; -- 4 |
24 |
|
|
|
25 |
|
INSERT |
INTO "subscribers" |
26 |
|
|
("s_name") |
27 |
|
VALUES |
(N'Иванов И.И.'); |
28 |
|
|
|
29 |
|
SELECT |
COUNT(*) |
30 |
|
FROM |
"subscribers"; -- 5 |
31 |
|
|
|
32 |
|
ROLLBACK; |
|
33 |
|
|
|
34 |
|
SELECT |
COUNT(*) |
35 |
|
FROM |
"subscribers"; -- 5 |
|
|
|
|
Встроках 1-17 запросы выполняются в режиме отключённого автоподтверждения неявных транзакций: именно поэтому отмена транзакции в строке 14 проходит успешно и вставка данных, выполненная в строках 7-9, аннулируется.
Встроках 19-35 запросы выполняются в режиме включённого автоподтверждения неявных транзакций, и потому отмена транзакции в строке 32 ни на что не влияет: вставка данных, выполненная в строках 25-27, остаётся в силе.
На этом решение данной задачи завершено.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 412/545
Пример 41: управление неявными транзакциями
Решение 6.1.1.b{408}.
Данная задача призвана не только напомнить принципы работы с хранимыми процедурами и логику управления автоподтверждением неявных транзакций, она также демонстрирует разницу в производительности СУБД в ситуациях, когда при выполнении множества операций модификации данных каждая из них вступает в силу по-отдельности, и когда такие операции фиксируются по факту выполнения всей их группы целиком.
Традиционно мы начинаем решение с MySQL и сразу рассмотрим код.
MySQL |
Решение 6.1.1.b (код процедуры) |
1DELIMITER $$
2CREATE PROCEDURE TEST_INSERT_SPEED(IN records_count INT,
3 |
|
IN use_autocommit INT, |
4 |
|
OUT total_time TIME(6)) |
5BEGIN
6DECLARE counter INT DEFAULT 0;
7
8SET @old_autocommit = (SELECT @@autocommit);
9SELECT CONCAT('Old autocommit value = ', @old_autocommit);
10SELECT CONCAT('New autocommit value = ', use_autocommit);
11
12IF (use_autocommit != @old_autocommit)
13THEN
14SELECT CONCAT('Switching autocommit to ', use_autocommit);
15SET autocommit = use_autocommit;
16ELSE
17SELECT 'No changes in autocommit mode needed.';
18END IF;
19
20SELECT CONCAT('Starting insert of ', records_count, ' records...');
21SET @start_time = (SELECT NOW(6));
22WHILE counter < records_count DO
23 |
|
INSERT INTO |
`subscribers` |
24 |
|
|
(`s_name`) |
25VALUES (CONCAT('New subscriber ', (counter + 1)));
26SET counter = counter + 1;
27END WHILE;
28SET @finish_time = (SELECT NOW(6));
29SELECT CONCAT('Finished insert of ', records_count, ' records...');
30
31IF ((SELECT @@autocommit) = 0)
32THEN
33SELECT 'Current autocommit mode is 0. Performing explicit commit.';
34COMMIT;
35END IF;
36
37IF (use_autocommit != @old_autocommit)
38THEN
39SELECT CONCAT('Switching autocommit back to ', @old_autocommit);
40SET autocommit = @old_autocommit;
41ELSE
42SELECT 'No changes in autocommit mode were made. No restore needed.';
43END IF;
44
45SET total_time = (SELECT TIMEDIFF(@finish_time, @start_time));
46SELECT CONCAT('Time used: ', total_time);
47
48SELECT total_time;
49END;
50$$
51DELIMITER ;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 413/545
Пример 41: управление неявными транзакциями
В строке 8 происходит определение текущего значения автоподтверждения неявных транзакций (в MySQL эту информацию можно извлечь из переменной
@@autocommit).
Встроках 12-18 происходит проверка необходимости изменения режима автоподтверждения неявных транзакций и само изменение (если это необходимо). В строках 37-34 происходит повторная проверка и возврат исходного значения, если оно было изменено.
Определение затраченного на выполнение операции вставки времени происходит за счёт получения текущего времени до (строка 21) и после (строка 28) выполнения цикла вставки (строки 22-27), а затем вычисления разности этих значений (строка 45).
Встроках 31-35 проверяется текущее значение режима автоподтверждения неявных транзакций и подтверждение выполняется явным образом в строке 34, если автоподтверждение выключено (здесь нас не интересует, было ли оно выключено изначально или в процессе выполнения нашей процедуры).
Теперь остаётся только вернуть значение затраченного на выполнение цикла вставки времени как результат работы хранимой процедуры (строка 48).
Для проверки работоспособности и оценки производительности MySQL в двух режимах работы с неявными транзакциями можно использовать следующие запросы.
MySQL Решение 6.1.1.b (код для проверки работоспособности)
1CALL TEST_INSERT_SPEED(100000, 1, @tmp);
2SELECT @tmp;
3
4CALL TEST_INSERT_SPEED(100000, 0, @tmp);
5SELECT @tmp;
Вы можете самостоятельно произвести соответствующее исследование производительности. Здесь лишь отметим, что отключение автоподтверждения неявных транзакций может ускорить данную операцию вставки в десятки раз.
На этом решение для MySQL завершено.
Переходим к MS SQL Server. Внутренняя логика хранимой процедуры будет очень похожа на решение для MySQL, и главным отличием будет лишь способ32 определения режима автоподтверждения неявных транзакций. Т.к. данная СУБД не предоставляет эту информацию явным образом, мы попытаемся определить её косвенно.
Такое определение основано на информации об уровне вложенности текущей транзакции (@@TRANCOUNT) и настройках текущего соединения (@@OPTIONS). В строках 12-31 кода хранимой процедуры мы рассматриваем все возможные интересующие нас сочетания значений этих параметров, выводим отладочную информацию и определяем, включён ли режим подтверждения неявных транзакций.
Встроках 36-45 мы определяем необходимость изменения режима автоподтверждения и меняем его, если это требуется.
Встроках 47-57 совершенно аналогично с решением для MySQL выполняется цикл вставки указанного количества записей.
Встроках 59-64 проверяется, в каком режиме запущена хранимая процедура (в случае с MySQL мы ориентировались на текущее значение переменной @@autocommit, но т.к. в MS SQL Server её нет, а определение текущего режима
довольно нетривиально (см. строки 11-31), мы полагаем, что работа идёт в том режиме, который указан при вызове хранимой процедуры).
32 http://stackoverflow.com/questions/2919018/in-sql-server-how-do-i-know-what-transaction-mode-im-currently-using
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 414/545