Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)

Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах

MS SQL Решение 6.2.3.b (код функции)

1CREATE FUNCTION NO_AUTOCOMMITT()

2RETURNS INT

3WITH SCHEMABINDING

4AS

5BEGIN

6DECLARE @autocommit INT;

7

8IF (@@TRANCOUNT = 0 AND (@@OPTIONS & 2 = 0))

9BEGIN

10SET @autocommit = 1;

11END

12ELSE IF (@@TRANCOUNT = 0 AND (@@OPTIONS & 2 = 2))

13BEGIN

14SET @autocommit = 0;

15END

16ELSE IF (@@OPTIONS & 2 = 0)

17BEGIN

18SET @autocommit = 1;

19END

20ELSE

21BEGIN

22SET @autocommit = 0;

23END;

24

25IF (@autocommit = 1)

26BEGIN

27-- В функциях MS SQL Server нельзя использовать RAISEERROR!

28-- RAISERROR ('Please, turn the autocommit off.', 16, 1);

29

30-- Обходной путь по порождению исключения:

31RETURN CAST('Please, turn the autocommit off.' AS INT);

32

33-- Отменить транзакцию из функции в MS SQL Server тоже нельзя.

34-- ROLLBACK TRANSACTION;

35END;

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 Стр: 470/545

Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах

Решение для Oracle выглядит следующим образом. Да, именно так и выглядит, т.к. в Oracle нет такого явления, как автоподтверждение транзакций — этот эффект может быть реализован некоторыми средствами работы с СУБД, но сама СУБД всегда ждёт явного COMMITT или ROLLBACK.

1CREATE FUNCTION NO_AUTOCOMMITT

2RETURN INT

3DETERMINISTIC

4IS

5BEGIN

6DBMS_OUTPUT.PUT_LINE('Have a nice day :)');

7RETURN 1;

8END;

Проверить работоспособность и корректность представленного решения можно выполнением следующего кода.

Oracle Решение 6.2.3.b (код для проверки работоспособности решения)

1SET SERVEROUTPUT ON;

2SELECT NO_AUTOCOMMITT FROM DUAL;

На этом решение данной задачи завершено.

Решение 6.2.3.c{465}.

Идея решения данной задачи состоит в том, чтобы использовать такой уровень изолированности транзакций, который меньше всего подвержен влиянию со стороны блокировок, порождённых другими транзакциями.

В MySQL таким уровнем является READ UNCOMMITTED, в MS SQL Server —

тоже READ UNCOMMITTED или SNAPSHOT (но SNAPSHOT может приводить к допол-

нительным расходам ресурсов), в Oracle чтение данных всегда происходит в независимом режиме, потому в этой СУБД можно использовать READ COMMITTED (тем более, что READ UNCOMMITTED в Oracle нет).

Решение для MySQL выглядит следующим образом.

MySQL Решение 6.2.3.c (код процедуры)

1DELIMITER $$

2CREATE PROCEDURE COUNT_ROWS(IN table_name VARCHAR(150),

4BEGIN

5SET SESSION TRANSACTION

6ISOLATION LEVEL READ UNCOMMITTED;

7

8SET @count_query =

9CONCAT('SELECT COUNT(1) INTO @rows_found

10FROM ', table_name);

11

12PREPARE count_stmt FROM @count_query;

13EXECUTE count_stmt;

14DEALLOCATE PREPARE count_stmt;

15

16SET rows_in_table := @rows_found;

17END;

18$$

19DELIMITER ;

Проверить работоспособность и корректность представленного решения можно выполнением следующего кода.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 471/545

Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах

MySQL Решение 6.2.3.c (код для проверки работоспособности решения)

1CALL COUNT_ROWS('subscriptions', @rows_in_table);

2SELECT @rows_in_table;

Решение для MS SQL Server выглядит следующим образом.

MS SQL Решение 6.2.3.c (код процедуры)

4AS

5DECLARE @count_query NVARCHAR(1000) = '';

6

7SET TRANSACTION ISOLATION

8LEVEL READ UNCOMMITTED;

9

10SET @count_query =

11CONCAT('SET @rows_f = (SELECT COUNT(1) FROM [', @table_name, '])');

12EXECUTE sp_executesql @count_query,

Проверить работоспособность и корректность представленного решения можно выполнением следующего кода.

MySQL Решение 6.2.3.c (код для проверки работоспособности решения)

1DECLARE @res INT;

2EXECUTE COUNT_ROWS 'subscriptions', @res OUTPUT;

3SELECT @res;

Решение для Oracle выглядит следующим образом.

Oracle Решение 6.2.3.c (код процедуры)

3count_query VARCHAR(1000) := '';

4BEGIN

5

6EXECUTE IMMEDIATE 'ALTER SESSION SET

7ISOLATION_LEVEL = READ COMMITTED';

8

9count_query :=

10'SELECT COUNT(1) FROM "' || table_name || '"';

11EXECUTE IMMEDIATE count_query INTO rows_in_table;

12END;

13/

Проверить работоспособность и корректность представленного решения можно выполнением следующего кода.

Oracle Решение 6.2.3.c (код для проверки работоспособности решения)

1DECLARE

2res NUMBER;

3BEGIN

4COUNT_ROWS('subscriptions', res);

5DBMS_OUTPUT.PUT_LINE('Rows: ' || res);

6END;

На этом решение данной задачи завершено.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 472/545

Пример 45: управление транзакциями в триггерах, хранимых функциях и процедурах

Задание 6.2.3.TSK.A: создать на таблице subscriptions триггер, определяющий уровень изолированности транзакции, в котором сейчас проходит операция обновления, и отменяющий операцию, если уровень изолированности транзакции отличен от REPEATABLE READ.

Задание 6.2.3.TSK.B: создать хранимую функцию, порождающую исключительную ситуацию в случае, если выполняются оба условия:

•режим автоподтверждения транзакций выключен;

•функция запущена из вложенной транзакции.

Подсказка: эта задача имеет решение только для MS SQL Server.

Задание 6.2.3.TSK.C: создать хранимую процедуру, выполняющую подсчёт количества записей в указанной таблице таким образом, чтобы она возвращала максимально корректные данные, даже если для достижения этого результата придётся пожертвовать производительностью.

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 473/545