Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах
6.2.3.Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах
Задача 6.2.3.a{465}: создать на таблице books триггер, определяющий уровень изолированности транзакции, в котором сейчас проходит операция вставки, и отменяющий операцию, если уровень изолированности транзакции отличен от SERIALIZABLE.
Задача 6.2.3.b{469}: создать хранимую функцию, порождающую исключительную ситуацию в случае запуска в режиме автоподтверждения транзакций.
Задача 6.2.3.c{471}: создать хранимую процедуру, выполняющую подсчёт количества записей в указанной таблице таким образом, чтобы запрос выполнялся максимально быстро (вне зависимости от параллельно выполняемых запросов), даже если в итоге он вернёт не совсем корректные данные.
Ожидаемый результат 6.2.3.a.
Если операция вставки данных в таблицу books выполняется в транзакции с уровнем изолированности, отличным от SERIALIZABLE, триггер отменяет эту операцию и порождает исключительную ситуацию.
Ожидаемый результат 6.2.3.b.
Если хранимая функция оказывается вызванной в момент, когда для текущей сессии с СУБД включён режим автоподтверждения транзакций, функция должна порождать исключительную ситуацию и прекращать свою работу.
Ожидаемый результат 6.2.3.c.
Хранимая процедура должна выполнять подсчёт записей в указанной таблице в транзакции с уровнем изолированности, обеспечивающим минимальную вероятность ожидания завершения конкурирующих транзакций или отдельных операций в них.
Решение 6.2.3.a{465}.
Для простоты (отсутствия необходимости вручную выполнять вставку) и единообразия (поддержки всеми тремя СУБД) используем AFTER-триггеры.
Таким образом, представленные ниже решения будут отличаться только логикой определения уровня изолированности транзакций, т.к. в каждой СУБД соответствующий механизм реализован совершенно особенным, несовместимым с другими СУБД, образом.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 465/545
Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах
Решение для MySQL выглядит следующим образом.
MySQL |
Решение 6.2.3.a (код триггера) |
1 DELIMITER $$
2
3CREATE TRIGGER `books_ins_trans`
4AFTER INSERT
5ON `books`
6FOR EACH ROW
7BEGIN
8DECLARE isolation_level VARCHAR(50);
9
10SET isolation_level =
11(
12SELECT `VARIABLE_VALUE`
13 |
|
FROM |
`information_schema`. |
14 |
|
|
`session_variables` |
15 |
|
WHERE |
`VARIABLE_NAME` = |
16 |
|
|
'tx_isolation' |
17 |
|
); |
|
18 |
|
|
|
|
|
|
|
19IF (isolation_level != 'SERIALIZABLE')
20THEN
21SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Please, switch your
22transaction to SERIALIZABLE isolation level and rerun this
23INSERT again.', MYSQL_ERRNO = 1001;
24END IF;
25
26END;
27$$
28
29 DELIMITER ;
Проверить работоспособность и корректность представленного решения можно выполнением следующего кода: первая попытка выполнить вставку закончится исключительной ситуацией, порождённой в триггере, а вторая попытка пройдёт успешно.
MySQL Решение 6.2.3.a (код для проверки работоспособности решения)
1SET SESSION TRANSACTION
2ISOLATION LEVEL READ COMMITTED;
3 |
|
|
|
4 |
|
INSERT INTO `books` |
|
5 |
|
|
(`b_name`, |
6 |
|
|
`b_year`, |
7 |
|
|
`b_quantity`) |
8 |
|
VALUES |
('И ещё одна книга', |
9 |
|
|
1985, |
10 |
|
|
2); |
11 |
|
|
|
12SET SESSION TRANSACTION
13ISOLATION LEVEL SERIALIZABLE;
14 |
|
|
|
15 |
|
INSERT INTO `books` |
|
16 |
|
|
(`b_name`, |
17 |
|
|
`b_year`, |
18 |
|
|
`b_quantity`) |
19 |
|
VALUES |
('И ещё одна книга', |
20 |
|
|
1985, |
21 |
|
|
2); |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 466/545
Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах
Решение для MS SQL Server выглядит следующим образом.
MS SQL Решение 6.2.3.a (код триггера)
1CREATE TRIGGER [books_ins_trans]
2ON [books]
3AFTER INSERT
4AS
5DECLARE @isolation_level NVARCHAR(50);
6
7SET @isolation_level =
8(
9SELECT [transaction_isolation_level]
10FROM [sys].[dm_exec_sessions]
11WHERE [session_id] = @@SPID
12);
13
14IF (@isolation_level != 4)
15BEGIN
16RAISERROR ('Please, switch your transaction to SERIALIZABLE isolation
17 |
|
level and rerun this INSERT again.', 16, 1); |
18ROLLBACK TRANSACTION;
19RETURN
20END;
21GO
Проверить работоспособность и корректность представленного решения можно выполнением следующего кода: первая попытка выполнить вставку закончится исключительной ситуацией, порождённой в триггере, а вторая попытка пройдёт успешно.
MS SQL Решение 6.2.3.a (код для проверки работоспособности решения)
1SET TRANSACTION ISOLATION
2LEVEL READ COMMITTED;
3 |
|
|
|
4 |
|
INSERT INTO [books] |
|
5 |
|
|
([b_name], |
6 |
|
|
[b_year], |
7 |
|
|
[b_quantity]) |
8 |
|
VALUES |
('И ещё одна книга', |
9 |
|
|
1985, |
10 |
|
|
2); |
11 |
|
|
|
|
|
|
|
12SET TRANSACTION ISOLATION
13LEVEL SERIALIZABLE;
14 |
|
|
|
15 |
|
INSERT INTO [books] |
|
16 |
|
|
([b_name], |
17 |
|
|
[b_year], |
18 |
|
|
[b_quantity]) |
19 |
|
VALUES |
('И ещё одна книга', |
20 |
|
|
1985, |
21 |
|
|
2); |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 467/545
Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах
Решение для Oracle выглядит следующим образом.
Oracle |
Решение 6.2.3.a (код триггера) |
1CREATE OR REPLACE TRIGGER "books_ins_trans"
2AFTER INSERT
3ON "books"
4FOR EACH ROW
5DECLARE
6isolation_level NVARCHAR2(150);
7trans_id VARCHAR(100);
8BEGIN
9trans_id := DBMS_TRANSACTION.LOCAL_TRANSACTION_ID(FALSE);
10SELECT CASE BITAND("transaction".flag, POWER(2, 28))
11 |
|
|
WHEN 0 THEN 'READ COMMITTED' |
12 |
|
|
ELSE 'SERIALIZABLE' |
13 |
|
|
END AS "session_isolation_level" |
14 |
|
INTO |
isolation_level |
15 |
|
FROM |
v$transaction "transaction" |
16 |
|
|
JOIN v$session "session" |
17 |
|
|
ON "transaction".addr = "session".taddr |
18 |
|
|
AND "session".sid = SYS_CONTEXT('USERENV', 'SID'); |
19 |
|
|
|
20IF (isolation_level != 'SERIALIZABLE')
21THEN
22RAISE_APPLICATION_ERROR(-20001, 'Please, switch your transaction
23to SERIALIZABLE isolation level and rerun this INSERT again.');
24END IF;
25
26 END;
Проверить работоспособность и корректность представленного решения можно выполнением следующего кода: первая попытка выполнить вставку закончится исключительной ситуацией, порождённой в триггере, а вторая попытка пройдёт успешно.
Oracle Решение 6.2.3.a (код для проверки работоспособности решения)
1ALTER SESSION SET
2ISOLATION_LEVEL = READ COMMITTED;
3 |
|
|
|
4 |
|
INSERT INTO "books" |
|
5 |
|
|
("b_name", |
6 |
|
|
"b_year", |
7 |
|
|
"b_quantity") |
8 |
|
VALUES |
('И ещё одна книга', |
9 |
|
|
1985, |
10 |
|
|
2); |
11 |
|
|
|
12ALTER SESSION SET
13ISOLATION_LEVEL = SERIALIZABLE;
14 |
|
|
|
15 |
|
INSERT INTO "books" |
|
16 |
|
|
("b_name", |
17 |
|
|
"b_year", |
18 |
|
|
"b_quantity") |
19 |
|
VALUES |
('И ещё одна книга', |
20 |
|
|
1985, |
21 |
|
|
2); |
На этом решение данной задачи завершено.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 468/545
Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах
Решение 6.2.3.b{465}.
Поскольку в условии задачи не сказано, что именно должна делать функция, мы ограничимся проверкой режима автоподтверждения транзакций и порождения исключительной ситуации в случае, если он включён.
Решение для MySQL выглядит следующим образом.
MySQL |
Решение 6.2.3.b (код функции) |
1DELIMITER $$
2CREATE FUNCTION NO_AUTOCOMMIT()
3RETURNS INT DETERMINISTIC
4BEGIN
5IF ((SELECT @@autocommit) = 1)
6THEN
7SIGNAL SQLSTATE '45001'
8SET MESSAGE_TEXT = 'Please, turn the autocommit off.',
9MYSQL_ERRNO = 1001;
10RETURN -1;
11END IF;
12 
13 -- Тут может быть какой-то полезный код :).
14
15RETURN 0;
16END$$
17
18 DELIMITER ;
Проверить работоспособность и корректность представленного решения можно выполнением следующего кода: первый вызов функции закончится исключительной ситуацией, а второй пройдёт успешно.
MySQL Решение 6.2.3.b (код для проверки работоспособности решения)
1SET autocommit = 1;
2SELECT NO_AUTOCOMMIT();
3
4SET autocommit = 0;
5SELECT NO_AUTOCOMMIT();
Решение для MS SQL Server выглядит следующим образом.
Обратите внимание на следующие важные моменты, характерные для MS SQL Server:
•состояние автоподтверждения транзакций можно определить лишь косвенно (строки 8-23 кода);
•явно породить исключительную ситуацию в коде хранимой функции невозможно, приходится использовать обходное решение (строки 27-31 кода);
•отменить транзакцию в коде хранимой функции невозможно, но в силу порождения исключительной ситуации транзакция будет остановлена.
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 469/545