Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 41: управление неявными транзакциями
Oracle (в отличие от MySQL и MS SQL Server) не оперирует такими понятиями, как «неявная транзакция» и её автоподтверждение. Эта СУБД лишь автоматически подтверждает текущую транзакцию в случае, если выполняется выражение, модифицирующее структуру базы данных.
Однако клиентское ПО, организующее взаимодействие с Oracle, может иметь свои собственные настройки, отвечающие за автоматическое подтверждение транзакций, не обрамлённых явно выражениями по запуску и подтверждению или отмене.
В таком средстве как Oracle SQL Developer, например, соответствующий эффект достигается выполнением команды SET AUTOCOMMIT ON / OFF (эффект которой эквивалентен изменению параметра autocommit в MySQL).
Для решения данной задачи в Oracle необходимо использовать следующий набор запросов.
Oracle I Решение 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 |
(Ы'Иванов И.И.'); |
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 |
(Ы'Иванов И.И.'); |
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 в примерах © Богдан Марчук Стр: 440/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);
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') |
25 |
VALUES (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...');
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 в примерах © Богдан Марчук Стр: 441/545
Пример 41: управление неявными транзакциями
В строке 8 происходит определение текущего значения автоподтверждения неявных транзакций (в MySQL эту информацию можно извлечь из переменной @@ autocommit).
Встроках 12-18 происходит проверка необходимости изменения режима автоподтверждения неявных транзакций и само изменение (если это необходимо). В строках 37-34 происходит повторная проверка и возврат исходного значения, если оно было изменено.
Определение затраченного на выполнение операции вставки времени происходит за счёт получения текущего времени до (строка 21) и после (строка 28) выполнения цикла вставки (строки 22-27), а затем вычисления разности этих значений (строка 45).
Встроках 31-35 проверяется текущее значение режима автоподтверждения неявных транзакций и подтверждение выполняется явным образом в строке 34, если автоподтверждение выключено (здесь нас не интересует, было ли оно выключено изначально или в процессе выполнения нашей процедуры).
Теперь остаётся только вернуть значение затраченного на выполнение цикла вставки времени как результат работы хранимой процедуры (строка 48).
Для проверки работоспособности и оценки производительности MySQL в двух режимах работы с неявными транзакциями можно использовать следующие запросы.
|
|
|
для проверки |
|
|
|
CALL |
TEST_INSERT_SPEED 100000 |
1, |
@tmp); |
|
2 |
SELECT |
@tmp; |
|
|
|
4 |
CALL |
TEST_INSERT_SPEED 100000 |
0, |
@tmp); |
|
5 |
SELECT |
@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 в примерах © Богдан Марчук Стр: 442/545
Пример 41: управление неявными транзакциями
MS SQL I |
Решение 6.1.1 .b (код процедуры) (продолжение) |
| |
56SET @finish time = GETDATE();
57PRINT CONCAT('Finished insert of ', @records_count, ' records...');
58
59IF @use autocommit = 0)
60BEGIN
61PRINT 'Current autocommit mode is 0 (IMPLICIT TRANSACTIONS = ON).
62 |
Performing explicit commit.'; |
63COMMIT;
64END;
65IF @use autocommit != @old autocommit
66BEGIN
67PRINT CONCAT('Switching autocommit back to ', @old autocommit) ;
68IF 1@old autocommit = 1
69SET IMPLICIT TRANSACTIONS OFF;
70ELSE
71SET IMPLICIT TRANSACTIONS ON;
72END
73ELSE
74PRINT 'No changes in autocommit mode needed.';
75 |
|
76 |
SET @total time = CONVERT(VARCHAR(12 , |
77 |
DATEADD(ms, |
78 |
DATEDIFF ms @start time, @finish time , |
79 |
0), |
80 |
114 ; |
81PRINT CONCAT('Time used: ', @total time);
82RETURN;
83END;
84GO
Для проверки работоспособности и оценки производительности MS SQL Server в двух режимах работы с неявными транзакциями можно использовать следующие запросы.
MS SQL |
Решение 6.1.1.b (код для проверки работоспособности) |
|||
|
|
1 DECLARE @t TIME; |
|
|
2 |
SET IMPLICIT TRANSACTIONS ON |
|
|
|
3 |
EXECUTE TEST_INSERT_SPEED 10, |
1 @t OUTPUT; |
||
4 |
PRINT CONCAT ('Stored procedure |
has |
returned the following value: ', @t); |
|
5 |
|
|
|
|
6 |
DECLARE @t TIME; |
|
|
|
7 |
SET |
IMPLICIT_TRANSACTIONS ON |
|
|
8 |
EXECUTE TEST_INSERT_SPEED 10, |
0, @t OUTPUT; |
||
9 |
PRINT CONCAT ('Stored procedure |
has returned the following value: ', @t); |
||
На этом решение для MySQL завершено.
Переходим к Oracle. Поскольку данная СУБД вообще не оперирует таким понятием как «автоподтверждение неявных транзакций», мы можем работать лишь в одном из двух режимов (который явно выбираем и реализуем сами):
•выполнение подтверждения транзакции после каждой операции (аналог включённого автоподтверждения неявных транзакций);
•выполнение подтверждения транзакции после серии операций (аналог выключенного автоподтверждения неявных транзакций)
Учитывая этот факт, мы реализуем решение для Oracle по аналогии с MySQL и MS SQL Server, но без определения текущего режима автоподтверждения транзакций.
Поскольку взаимодействие Oracle с клиентским ПО всегда происходит в режиме транзакции (т.е. выполнение любого выражения по выборке или модифика
Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 444/545