Материал: Using_MySql,_MS_SQL_Server_and_Oracle
Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах
Решение для MySQL выглядит следующим образом.
MySQL |
Решение 6.2.3.a (код триггера) |
||
1 |
DELIMITER $$ |
||
2 |
|
|
|
3 |
CREATE TRIGGER 'books ins trans' |
||
4 |
AFTER INSERT |
||
5 |
ON 'books' |
|
|
6 |
|
FOR EACH ROW |
|
7 |
|
BEGIN |
|
8 |
|
DECLARE isolation level VARCHAR(50 ; |
|
9 |
|
|
|
10 |
|
SET isolation level = |
|
11 |
|
( |
|
12 |
|
SELECT 'VARIABLE VALUE' |
|
13 |
|
FROM |
'information schema' |
14 |
|
|
'session variables' |
15 |
|
WHERE |
'VARIABLE NAME' = |
16 |
|
|
'tx isolation' |
17 |
|
); |
|
18 |
|
|
|
19 |
|
IF (isolation level != 'SERIALIZABLE') |
|
20 |
|
THEN |
|
21 |
|
SIGNAL SQLSTATE '45001' SET MESSAGE TEXT = 'Please, switch your |
|
22 |
|
transaction to SERIALIZABLE isolation level and rerun this |
|
23 |
|
INSERT again.', MYSQL ERRNO = 1001 |
|
24 |
|
END IF; |
|
25 |
|
|
|
26 |
|
END; |
|
27 |
$$ |
|
|
28 |
|
|
|
29 |
DELIMITER ; |
|
|
|
|
|
|
Проверить работоспособность и корректность представленного решения можно выполнением следующего кода: первая попытка выполнить вставку закончится исключительной ситуацией, порождённой в триггере, а вторая попытка пройдёт успешно.
MySQL і Решение 6.2.3.a (код для проверки работоспособности решения)
1SET SESSION TRANSACTION
2ISOLATION LEVEL READ COMMITTED;
4INSERT INTO 'books'
5 |
|
('b name', |
6 |
|
'b_year', |
7 |
|
'b_quanti ty') |
8 |
VALUES |
('И ещё одна книга', |
9 |
|
1985, |
10 |
|
2 ; |
11 |
|
|
12SET SESSION TRANSACTION
13ISOLATION LEVEL SERIALIZABLE;
15INSERT INTO 'books'
16 |
|
('b name', |
17 |
|
'b year', |
18 |
|
'b_quanti ty') |
19 |
VALUES |
('И ещё одна книга', |
20 |
|
1985 |
21 |
|
2 ; |
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 495/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
SET @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 |
QL 1 Решение 6.2.3.a (код для проверки работоспособности решения) |
||
1 |
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; |
||
2 |
|
|
|
3 |
INSERT INTO [books] |
|
|
4 |
|
([b_name] , |
|
5 |
|
[b_year], |
|
6 |
|
[b_quantity]) |
|
7 |
VALUES |
('И ещё одна книга', |
|
8 |
|
1985, 2 |
; |
9 |
|
|
|
10SET TRANSACTION ISOLATION
11LEVEL SERIALIZABLE;
12 |
|
|
|
13 |
INSERT INTO [books] |
|
|
|
|
|
|
14 |
|
([b_name], |
|
|
|
|
|
15 |
|
[b_year], |
|
|
|
|
|
16 |
|
[b_quantity]) |
|
|
|
|
|
17 |
VALUES |
('И ещё одна книга', |
|
|
|
|
|
18 |
|
1985, 2 |
; |
|
|
|
|
19 |
|
|
|
20
21
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 496/545
Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах
Решение для Oracle выглядит следующим образом.
Oracl |
і |
Решение 6.2.3.a (код триггера) |
| |
||
e |
|||||
|
|
|
|
||
1 |
CREATE OR REPLACE TRIGGER "books ins trans" |
||||
2 |
AFTER INSERT |
|
|||
3 |
ON "books" |
|
|
||
4 |
FOR EACH ROW |
|
|||
5 |
|
DECLARE |
|
|
|
6 |
|
isolation level NVARCHAR2 150); |
|||
7 |
|
trans id VARCHAR(100 ; |
|
||
8 |
|
BEGIN |
|
|
|
9 |
|
trans id := DBMS TRANSACTION.LOCAL TRANSACTION ID(FALSE); |
|||
10 |
|
SELECT 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;
15INSERT 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 Стр: 497/545
Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах
'ЙАЙ'
Решение 6.2.3.b{465}.
Поскольку в условии задачи не сказано, что именно должна делать функция, мы ограничимся проверкой режима автоподтверждения транзакций и порождения исключительной ситуации в случае, если он включён.
Решение для MySQL выглядит следующим образом.
MySQL I |
Решение 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 (код для проверки работоспособности решения)
1 SET autocommit = 1;
2 SELECT 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 Стр: 498/545
Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах
MS SQL I |
Решение 6.2.3.b (код функции) |
| |
1
2
3
4
5
6
7
8
9CREATE FUNCTION NO_AUTOCOMMITT() RETURNS INT WITH SCHEMABINDING AS BEGIN
10DECLARE @autocommit INT;
11
12IF (@@TRANCOUNT = 0 AND (@@OPTIONS & 2 = 0))
13BEGIN
14SET @autocommit = 1
15END
16ELSE IF (@@TRANCOUNT = 0 AND (@@OPTIONS & 2 = 2|) BEGIN
17SET @autocommit = 0 END
18ELSE IF (@@OPTIONS & 2 = 0)
19BEGIN
20SET @autocommit = 1 END
21ELSE
22BEGIN SET @autocommit = 0
23END;
24
25IF @autocommit = 1)
26BEGIN -- В функциях MS SQL Server нельзя использовать RAISEERROR!
28 |
-- RAISERROR ('Please, |
turn the autocommit |
off.', 16, 1); |
|
29 |
|
|
|
|
30 |
-- Обходной путь по порождению исключения: RETURN |
|
||
31 |
CAST('Please, turn the |
autocommit off.' AS |
|
INT); |
32 |
|
|
|
|
33 |
-- Отменить транзакцию |
из функции в MS SQL Server тоже нельзя. |
||
34— ROLLBACK TRANSACTION;
35END;
36 37 -- Тут может быть какой-то полезный код :).
38
39RETURN 0;
40END;
41GO
Проверить работоспособность и корректность представленного решения можно выполнением следующего кода: первый вызов функции закончится исключительной ситуацией, а второй пройдёт успешно.
MS SQL Решение 6.2.3.b (код для проверки работоспособности решения)
1SET IMPLICIT_TRANSACTIONS OFF;
2SELECT dbo NO_AUTOCOMMITT();
3
4SET IMPLICIT_TRANSACTIONS ON;
5SELECT dbo NO AUTOCOMMITT();
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016-2018 Стр: 499/545