Материал: Using_MySql,_MS_SQL_Server_and_Oracle

Пример 41: управление неявными транзакциями

в конфигурационном файле).

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 435/545

Пример 41: управление неявными транзакциями

Для решения данной задачи в MySQL необходимо использовать следующий набор запросов.

Решение 6.1.1 .а

1— Автоподтверждение выключено:

2SET autocommit = 0;

11SELECT COUNT(*)

12FROM 'subscribers'; — 5

16SELECT COUNT(*)

17FROM 'subscribers'; — 4

18

19— Автоподтверждение включено:

20SET autocommit = 1;

21

22SELECT COUNT(*)

23FROM 'subscribers'; — 4

25INSERT INTO 'subscribers'

29SELEcT COUNT(*)

30FROM 'subscribers'; — 5

31

32 ROLLBACK;

33

34SELECT COUNT(*)

35FROM 'subscribers'; — 5

Встроках 1-17 запросы выполняются в режиме отключённого автоподтверждения неявных транзакций: именно поэтому отмена транзакции в строке 14 проходит успешно и вставка данных, выполненная в строках 7-9, аннулируется.

Встроках 19-35 запросы выполняются в режиме включённого автоподтверждения неявных транзакций, и потому отмена транзакции в строке 32 ни на что не влияет: вставка данных, выполненная в строках 25-27, остаётся в силе.

MS SQL Server (как и MySQL) по умолчанию работает с включённым автоподтверждением неявных транзакций, т.е. любые изменения данных сразу же вступают в силу. За изменение данного поведения отвечает параметр IM-

PLICIT_TRANSACTIONS (которым в общем случае можно управлять только ло-

кально на протяжении сессии; общие идеи по управлению этим параметром на уроне настроек описаны здесь31).

Для решения данной задачи в MS SQL Server необходимо использовать следующий набор запросов. Обратите внимание, что параметр IMPLICIT_TRANSAC- TIONS в MS SQL Server по своей логике противоположен параметру autocommit в MySQL (т.е. для выключения автоподтверждения неявных транзакций необходимо

31https://msdn.microsoft.com/en-us/library/ms176031 %28SQL.90%29.aspx

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 436/545

Пример 41: управление неявными транзакциями

выполнить команду SET IMPLICIT_TRANSACTIONS ON).

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 437/545

Пример 41: управление неявными транзакциями

MS SQL І Решение 6.1.1.a

1-- Автоподтверждение выключено:

2SET IMPLICIT TRANSACTIONS ON;

11SELECT COUNT(*)

12FROM [subscribers]; -- 5

16SELECT COUNT(*)

17FRoM [subscribers]; -- 4

19-- Автоподтверждение включено:

20 SET IMPLICIT TRANSACTIONS OFF;

21

22SELECT COUNT(*)

23FRoM [subscribers]; -- 4

25INSERT INTO [subscribers]

29SELECT COUNT(*)

30FRoM [subscribers]; -- 5

32ROLLBACK; — Ошибка! Нет соответствующей транзакции, которую

33 — можно было бы отменить.

34

35 SELECT COUNT(*)

36 FROM [subscribers]; -- 5

Встроках 1-17 запросы выполняются в режиме отключённого автоподтверждения неявных транзакций: именно поэтому отмена транзакции в строке 14 проходит успешно и вставка данных, выполненная в строках 7-9, аннулируется.

Встроках 19-36 запросы выполняются в режиме включённого автоподтверждения неявных транзакций, и потому отмена транзакции в строке 32 ни на что не влияет: вставка данных, выполненная в строках 25-27, остаётся в силе.

В MS SQL Server существует одна важная особенность, которую необходимо учитывать. Если в режиме IMPLICIT_TRANSACTIONS ON использовать выражение BEGIN TRANSACTION, СУБД читает созданную транзакцию вложенной (@@TRANCOUNT принимает значение 2) и для успешного подтверждения её выполнения необходимо использовать выражение COMMIT TRANSACTION дважды. В противном случае вы рискуете или по-

лучить «подвисшую» транзакцию (которая так и не завершена), или потерять результаты модификации данных (если закроете соединение с СУБД). При этом ROLLBACK TRANSACTION работает в обоих режимах

одинаково, отменяя все транзакции вне зависимости от глубины их вложенности.

Эта проблема усугубляется тем, что при отладке запросов в средствах наподобие MS SQL Server Management Studio вы, как правило, работаете в рамках одного и того же соединения, и вместо «подвисшей» транзакции получаете продол

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 438/545

Пример 41: управление неявными транзакциями

жение предыдущей (не закрытой ранее). Потому в большинстве случаев при отладке всё работает правильно, а в реальных приложениях поведение становится неверным.

Продемонстрируем только что описанное поведение MS SQL Server.

Решение 6.1.1 .а (демонстр

Режим по умолчанию

2 SET IMPLICIT_TRANSACTIONS OFF;

3 PRINT @@TRANCOUNT; — 0

5 BEGIN TRANSACTION;

4 -- Старт первой ("родительской") транзакции

6PRINT @@TRANCOUNT; — 1

7-- Старт второй ("дочерней") транзакции

8BEGIN TRANSACTION;

9PRINT @@TRANCOUNT; — 2

10-- Подтверждение второй ("дочерней") транзакции

11COMMIT TRANSACTION;

12PRINT @@TRANCOUNT; — 1

13-- Подтверждение первой ("родительской") транзакции

14COMMIT TRANSACTION;

20-- Старт первой ("родительской") транзакции BEGIN TRANSACTION;

21PRINT @@TRANCOUNT; — 2

22-- Старт второй ("дочерней") транзакции BEGIN TRANSACTION;

23PRINT @@TRANCOUNT; — 3

24-- Подтверждение второй ("дочерней") транзакции COMMIT TRANSACTION;

25PRINT @@TRANCOUNT; — 2

26-- Подтверждение первой ("родительской") транзакции COMMIT TRANSACTION;

36-- Старт второй ("дочерней") транзакции BEGIN TRANSACTION;

37PRINT @@TRANCOUNT; — 2 -- Отмена всех транзакций ROLLBACK TRANSACTION;

42BEGIN TRANSACTION;

43PRINT @@TRANCOUNT; — 2

44-- Старт второй ("дочерней") транзакции BEGIN TRANSACTION;

45PRINT @@TRANCOUNT; — 3 -- Отмена всех транзакций ROLLBACK TRANSACTION;

47

48

49

50

51

52

53

54

55

56

57

58

Работа с MySQL, MS SQL Server и Oracle в примерах © Богдан Марчук Стр: 439/545