Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 41: управление неявными транзакциями
Остаётся только восстановить исходное значение IMPLICIT_TRANSACTIONS (строки 65-74) и определить, сколько времени заняло выполнение цикла вставки записей (строки 76-80). Такая громоздкая конструкция с использованием функций CONVERT, DATEADD, DATEDIFF необходима для того, чтобы получить на выходе затраченное время в удобной для человека форме.
Итак, вот код процедуры.
|
MS SQL |
Решение 6.1.1.b (код процедуры) |
||
|
1 |
|
CREATE PROCEDURE TEST_INSERT_SPEED @records_count INT, |
|
|
2 |
|
|
@use_autocommit INT, |
|
3 |
|
|
@total_time TIME OUTPUT |
4AS
5BEGIN
6DECLARE @counter INT = 0;
7DECLARE @old_autocommit INT = 0;
8DECLARE @start_time TIME;
9DECLARE @finish_time TIME;
10
11IF (@@TRANCOUNT = 0 AND (@@OPTIONS & 2 = 0))
12BEGIN
13PRINT 'IMPLICIT_TRANSACTIONS = OFF, no transaction is running.';
14SET @old_autocommit = 1;
15END
16ELSE IF (@@TRANCOUNT = 0 AND (@@OPTIONS & 2 = 2))
17BEGIN
18PRINT 'IMPLICIT_TRANSACTIONS = ON, no transaction is running.';
19SET @old_autocommit = 0;
20END
21ELSE IF (@@OPTIONS & 2 = 0)
22BEGIN
23PRINT 'IMPLICIT_TRANSACTIONS = OFF, explicit transaction is running.';
24SET @old_autocommit = 1;
25END
26ELSE
27BEGIN
28PRINT 'IMPLICIT_TRANSACTIONS = ON, implicit or explicit transaction
29is running.';
30SET @old_autocommit = 0;
31END;
32
33PRINT CONCAT('Old autocommit value = ', @old_autocommit);
34PRINT CONCAT('New autocommit value = ', @use_autocommit);
35
36IF (@use_autocommit != @old_autocommit)
37BEGIN
38PRINT CONCAT('Switching autocommit to ', @use_autocommit);
39IF (@use_autocommit = 1)
40SET IMPLICIT_TRANSACTIONS OFF;
41ELSE
42SET IMPLICIT_TRANSACTIONS ON;
43END
44ELSE
45PRINT 'No changes in autocommit mode needed.';
46
47PRINT CONCAT('Starting insert of ', @records_count, ' records...');
48SET @start_time = GETDATE();
49WHILE (@counter < @records_count)
50BEGIN
51INSERT INTO [subscribers]
52 |
|
([s_name]) |
53VALUES (CONCAT('New subscriber ', (@counter + 1)));
54SET @counter = @counter + 1;
55END;
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 415/545
Пример 41: управление неявными транзакциями
MS SQL |
Решение 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).
62Performing explicit commit.';
63COMMIT;
64END;
65IF (@use_autocommit != @old_autocommit)
66BEGIN
67PRINT CONCAT('Switching autocommit back to ', @old_autocommit);
68IF (@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 (код для проверки работоспособности)
1DECLARE @t TIME;
2SET IMPLICIT_TRANSACTIONS ON
3EXECUTE TEST_INSERT_SPEED 10, 1, @t OUTPUT;
4PRINT CONCAT ('Stored procedure has returned the following value: ', @t);
5
6DECLARE @t TIME;
7SET IMPLICIT_TRANSACTIONS ON
8EXECUTE TEST_INSERT_SPEED 10, 0, @t OUTPUT;
9PRINT CONCAT ('Stored procedure has returned the following value: ', @t);
На этом решение для MySQL завершено.
Переходим к Oracle. Поскольку данная СУБД вообще не оперирует таким понятием как «автоподтверждение неявных транзакций», мы можем работать лишь в одном из двух режимов (который явно выбираем и реализуем сами):
•выполнение подтверждения транзакции после каждой операции (аналог включённого автоподтверждения неявных транзакций);
•выполнение подтверждения транзакции после серии операций (аналог выключенного автоподтверждения неявных транзакций)
Учитывая этот факт, мы реализуем решение для Oracle по аналогии с MySQL
иMS SQL Server, но без определения текущего режима автоподтверждения транзакций.
Поскольку взаимодействие Oracle с клиентским ПО всегда происходит в режиме транзакции (т.е. выполнение любого выражения по выборке или модифика-
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 416/545
Пример 41: управление неявными транзакциями
ции данных происходит внутри транзакции), в начале работы нашей хранимой процедуры мы должны выполнить операцию COMMIT (строка 12), чтобы завершить текущую транзакцию (если она есть).
В строках 16-27 мы выполняем цикл вставки, в котором мы можем явно инициировать подтверждение вставки каждой отдельной записи (строка 24), если нам нужно эмулировать режим автоподтверждения неявных транзакций. Если такая эмуляция не нужна, то все выполненные в цикле вставки подтверждаются как набор операций (строка 35).
|
Oracle |
|
Решение 6.1.1.b (код процедуры) |
|
|
|
1 |
|
CREATE OR REPLACE PROCEDURE TEST_INSERT_SPEED(records_count IN INT, |
||
|
2 |
|
|
use_autocommit |
IN INT, |
3 |
|
|
total_time OUT |
NVARCHAR2) |
|
4AS
5counter INT := 0;
6start_time TIMESTAMP;
7finish_time TIMESTAMP;
8diff_time INTERVAL DAY TO SECOND;
9BEGIN
10DBMS_OUTPUT.PUT_LINE('Autocommit value = ' || use_autocommit);
11DBMS_OUTPUT.PUT_LINE('Committing previous transaction...');
12COMMIT;
13DBMS_OUTPUT.PUT_LINE('Starting insert of ' || records_count ||
14 |
|
' records |
...'); |
|
|
|
|
15start_time := CURRENT_TIMESTAMP;
16WHILE (counter < records_count)
17LOOP
18INSERT INTO "subscribers"
19 |
("s_name") |
20VALUES (CONCAT('New subscriber ', (counter + 1)));
21IF (use_autocommit = 1)
22THEN
23DBMS_OUTPUT.PUT_LINE('Committing small transaction...');
24COMMIT;
25END IF;
26counter := counter + 1;
27END LOOP;
28finish_time := CURRENT_TIMESTAMP;
29DBMS_OUTPUT.PUT_LINE('Finished insert of ' || records_count ||
30 |
|
' records...'); |
31 |
|
|
32IF (use_autocommit = 0)
33THEN
34DBMS_OUTPUT.PUT_LINE('Committing one big transaction...');
35COMMIT;
36END IF;
37
38diff_time := finish_time - start_time;
39total_time := TO_CHAR(EXTRACT(hour FROM diff_time)) || ':' ||
40 |
|
TO_CHAR(EXTRACT(minute |
FROM |
diff_time)) || ':' || |
41 |
|
TO_CHAR(EXTRACT(second |
FROM |
diff_time ), 'fm00.000000' ); |
42 |
|
|
|
|
43DBMS_OUTPUT.PUT_LINE('Time used: ' || total_time);
44END;
Для проверки работоспособности и оценки производительности Oracle в двух режимах работы с неявными транзакциями можно использовать следующие запросы.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 417/545
Пример 41: управление неявными транзакциями
Oracle Решение 6.1.1.b (код для проверки работоспособности)
1DECLARE
2t NVARCHAR2(100);
3BEGIN
4TEST_INSERT_SPEED(10, 1, t);
5DBMS_OUTPUT.PUT_LINE('Stored procedure has returned
6 |
|
The following value: ' || t); |
7 |
|
END; |
8 |
|
|
|
|
|
9DECLARE
10t NVARCHAR2(100);
11BEGIN
12TEST_INSERT_SPEED(10, 0, t);
13DBMS_OUTPUT.PUT_LINE('Stored procedure has returned
14 |
|
The following value: ' || t); |
15 |
|
END; |
На этом решение данной задачи завершено.
Задание 6.1.1.TSK.A: сравнить скорость работы представленной в решении{413} задачи 6.1.1.b{408} хранимой процедуры при вставке в обоих режимах автоподтверждения неявных транзакций для 10, 100, 1000, 10000, 100000 записей во всех трёх СУБД.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 418/545
Пример 42: управление явными транзакциями
6.1.2. Пример 42: управление явными транзакциями
Задача 6.1.2.a{419}: создать хранимую процедуру, которая:
•добавляет каждому читателю три случайных книги с датой выдачи, равной текущей дате, и датой возврата, равной «текущая дата плюс месяц»;
•отменяет совершённые действия, если по итогу выполнения операции хотя бы у одного читателя на руках окажется более десяти книг.
Задача 6.1.2.b{425}: создать хранимую процедуру, которая:
•изменяет все даты возврата книг на «плюс три месяца»;
•отменяет совершённое действие, если по итогу выполнения операции среднее время чтения книги превысит 4 месяца.
Ожидаемый результат 6.1.2.a.
Хранимая процедура выполняет указанные в условии задачи действия, в конце своей работы сигнализируя о фиксации или отмене внесённых изменений.
Ожидаемый результат 6.1.2.b.
Хранимая процедура выполняет указанные в условии задачи действия, в конце своей работы сигнализируя о фиксации или отмене внесённых изменений.
Решение 6.1.2.a{419}.
Решение данной задачи для MySQL будет примечательно рассмотрением способа работы с несколькими курсорами внутри одной хранимой процедуры.
Для получения решения мы будем должны:
•запустить транзакцию (строка 5);
•открыть курсор для извлечения идентификаторов всех читателей (строка 15);
•для каждого идентификатора читателя выполнить вложенный цикл (строки 16-55), в котором:
o открыть курсор для извлечения трёх идентификаторов случайных книг (строка 13);
o для каждого полученного идентификатора книги произвести вставку в таблицу выдач книг (строки 33-51);
o закрыть курсор для извлечения трёх идентификаторов случайных книг (строка 52);
•закрыть курсор для извлечения идентификаторов всех читателей (строка 56);
•проверить, было ли нарушено условие о недопустимости нахождения на руках у одного читателя более десяти книг (строки 58-70) и:
o если условие было нарушено, отменить транзакцию (строка 66);
o если условие не было нарушено, подтвердить транзакцию (строка 69).
Несмотря на громоздкость синтаксиса и длительное описание, сам алгоритм тривиален: это обычный вложенный цикл. Что в этой задаче представляет интерес, так это уже упомянутая ранее работа с двумя курсорами.
Обратите внимание, что конструкция DECLARE CONTINUE HANDLER FOR NOT FOUND SET не подразумевает указание имени курсора, т.е. предполагается, что он
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 419/545