Документ: Using_MySql,_MS_SQL_Server_and_Oracle(1), страницы 451-455

Пример 44: взаимодействие конкурирующих транзакций

Неповторяющееся чтение в MS SQL Server может быть исследовано вы-

полнением в двух отдельных сессиях следующих блоков кода:

MS SQL Решение 6.2.2.a (код для исследования аномалии неповторяющегося чтения)

1	-- Транзакция A:		-- Транзакция B:
2	PRINT CONCAT('Tr A ID = ', @@SPID);		PRINT CONCAT('Tr B ID = ', @@SPID);
3	SET IMPLICIT_TRANSACTIONS ON;		SET IMPLICIT_TRANSACTIONS ON;
4	SET TRANSACTION ISOLATION		SET TRANSACTION ISOLATION
5	LEVEL {УРОВЕНЬ};		LEVEL {УРОВЕНЬ};

6	BEGIN TRANSACTION;		BEGIN TRANSACTION;
7	PRINT CONCAT('Tr A START: ',		PRINT CONCAT('Tr B START: ',
8	dbo.GET_CT(), ' in ',		dbo.GET_CT(), ' in ',
9	dbo.GET_ISOLATION_LEVEL());		dbo.GET_ISOLATION_LEVEL());
10			PRINT CONCAT('Tr B SELECT-1: ',
11			dbo.GET_CT());
12	WAITFOR DELAY '00:00:05';		SELECT	[sb_is_active]
13			FROM	[subscriptions]
14			WHERE	[sb_id] = 2;
15	PRINT CONCAT('Tr A UPDATE: ',
16	dbo.GET_CT());
17	UPDATE	[subscriptions]
18	SET	[sb_is_active] =
19	CASE
20	WHEN [sb_is_active] = 'Y' THEN 'N'		WAITFOR DELAY '00:00:10';

21WHEN [sb_is_active] = 'N' THEN 'Y'

22END

23WHERE [sb_id] = 2;

24PRINT CONCAT('Tr A COMMIT: ',

25dbo.GET_CT());

26COMMIT;

27PRINT CONCAT('TrC = ', @@TRANCOUNT);

28COMMIT;

29PRINT CONCAT('TrC = ', @@TRANCOUNT);

30	PRINT CONCAT('Tr B SELECT-2: ',
31	dbo.GET_CT());
32	SELECT	[sb_is_active]
33	FROM	[subscriptions]
34	WHERE	[sb_id] = 2;
35	PRINT CONCAT('Tr B COMMIT: ',
36	dbo.GET_CT());
37	COMMIT;
38	PRINT CONCAT('TrC = ', @@TRANCOUNT);
39	COMMIT;
40	PRINT CONCAT('TrC = ', @@TRANCOUNT);

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

Пример 44: взаимодействие конкурирующих транзакций

Итоговые результаты взаимодействия транзакций таковы.

			Уровень изолированности транзакции B
		READ	READ	REPEATABLE	SNAPSHOT	SERIALIZABLE
		UNCOMMITTED	COMMITTED	READ	SNAPSHOT	SERIALIZABLE
		UNCOMMITTED	COMMITTED	READ
				Первый и вто-		Первый и вто-
		Первый и вто-	Первый и вто-	рой SELECT	Первый и вто-	рой SELECT
	READ	рой SELECT	рой SELECT	возвратили	рой SELECT	возвратили
	READ	возвратили	возвратили	одинаковые	возвратили	одинаковые
	UNCOMMITTED	возвратили	возвратили	одинаковые	возвратили	одинаковые
	UNCOMMITTED	разные дан-	разные дан-	данные, тран-	одинаковые	данные, тран-
		разные дан-	разные дан-	данные, тран-	одинаковые	данные, тран-
		ные	ные	закция A ждёт	данные	закция A ждёт
				завершения B		завершения B
				Первый и вто-		Первый и вто-
		Первый и вто-	Первый и вто-	рой SELECT	Первый и вто-	рой SELECT
	READ	рой SELECT	рой SELECT	возвратили	рой SELECT	возвратили
A	READ	возвратили	возвратили	одинаковые	возвратили	одинаковые
A	COMMITTED	возвратили	возвратили	одинаковые	возвратили	одинаковые
транзакции	COMMITTED	разные дан-	разные дан-	данные, тран-	одинаковые	данные, тран-
		разные дан-	разные дан-	данные, тран-	одинаковые	данные, тран-
		ные	ные	закция A ждёт	данные	закция A ждёт
		ные	ные	закция A ждёт	данные	закция A ждёт
				завершения B		завершения B
				Первый и вто-		Первый и вто-
изолированности		Первый и вто-	Первый и вто-	рой SELECT	Первый и вто-	рой SELECT
изолированности	REPEATABLE	рой SELECT	рой SELECT	возвратили	рой SELECT	возвратили
		рой SELECT	рой SELECT	возвратили	рой SELECT	возвратили
		возвратили	возвратили	одинаковые	возвратили	одинаковые
	READ	возвратили	возвратили	одинаковые	возвратили	одинаковые
	READ	разные дан-	разные дан-	данные, тран-	одинаковые	данные, тран-
		разные дан-	разные дан-	данные, тран-	одинаковые	данные, тран-
		ные	ные	закция A ждёт	данные	закция A ждёт
				завершения B		завершения B
Уровень				Первый и вто-		Первый и вто-
Уровень		Первый и вто-	Первый и вто-	рой SELECT	Первый и вто-	рой SELECT
		Первый и вто-	Первый и вто-	рой SELECT	Первый и вто-	рой SELECT
		рой SELECT	рой SELECT	возвратили	рой SELECT	возвратили
	SNAPSHOT	возвратили	возвратили	одинаковые	возвратили	одинаковые
		разные дан-	разные дан-	данные, тран-	одинаковые	данные, тран-
		ные	ные	закция A ждёт	данные	закция A ждёт
				завершения B		завершения B
				Первый и вто-		Первый и вто-
		Первый и вто-	Первый и вто-	рой SELECT	Первый и вто-	рой SELECT
		рой SELECT	рой SELECT	возвратили	рой SELECT	возвратили
	SERIALIZABLE	возвратили	возвратили	одинаковые	возвратили	одинаковые
		разные дан-	разные дан-	данные, тран-	одинаковые	данные, тран-
		ные	ные	закция A ждёт	данные	закция A ждёт
				завершения B		завершения B

Чтобы получить ещё один вариант поведения СУБД, необходимо явно блокировать читаемые записи (UPDLOCK) в первой операции чтения. В данном случае это не было сделано, чтобы продемонстрировать наиболее типичное поведение MS SQL Server. Проверить же остальные случаи реакции СУБД вам предлагается самостоятельно в задании 6.2.2.TSK.E{464}.

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

Пример 44: взаимодействие конкурирующих транзакций

Фантомное чтение в MS SQL Server может быть исследовано выполнением в двух отдельных сессиях следующих блоков кода:

	MS SQL		Решение 6.2.2.a (код для исследования аномалии фантомного чтения)
	1	-- Транзакция A:		-- Транзакция B:
	2	PRINT CONCAT('Tr A ID = ', @@SPID);		PRINT CONCAT('Tr B ID = ', @@SPID);
	3	SET IMPLICIT_TRANSACTIONS ON;		SET IMPLICIT_TRANSACTIONS ON;
	4	SET TRANSACTION ISOLATION		SET TRANSACTION ISOLATION
	5	LEVEL {УРОВЕНЬ};		LEVEL {УРОВЕНЬ};

	6	BEGIN TRANSACTION;		BEGIN TRANSACTION;
	7	PRINT CONCAT('Tr A START: ',		PRINT CONCAT('Tr B START: ',
	8	dbo.GET_CT(), ' in ',		dbo.GET_CT(), ' in ',
	9	dbo.GET_ISOLATION_LEVEL());		dbo.GET_ISOLATION_LEVEL());
	10			PRINT CONCAT('Tr B COUNT-1: ',
	11			dbo.GET_CT());
	12	WAITFOR DELAY '00:00:05';		SELECT	COUNT(*)
	13			FROM	[subscriptions]
	14			WHERE	[sb_id] > 500;
	15	PRINT CONCAT('Tr A INSERT: ',
	16	dbo.GET_CT());
	17
	18	SET IDENTITY_INSERT
	19	[subscriptions] ON;
	20
	21	INSERT INTO [subscriptions]
	22		([sb_id],
	23		[sb_subscriber],
	24		[sb_book],	WAITFOR DELAY '00:00:10';
	25		[sb_start],
	26		[sb_finish],
	27		[sb_is_active])
	28		VALUES (1000,
	29		1,
	30		1,
	31		'2025-01-12',
	32		'2026-02-12',
	33		'N');
34

35SET IDENTITY_INSERT

36[subscriptions] OFF;

37		PRINT CONCAT('Tr B COUNT-2: ',
38		dbo.GET_CT());
39	WAITFOR DELAY '00:00:10';
40		SELECT	COUNT(*)
41		FROM	[subscriptions]
42		WHERE	[sb_id] > 500;
43	PRINT CONCAT('Tr A ROLLBACK: ',
44	dbo.GET_CT());	WAITFOR DELAY '00:00:15';
45	ROLLBACK;
46	PRINT CONCAT('TrC = ', @@TRANCOUNT);
47		PRINT CONCAT('Tr B COUNT-3: ',
48		dbo.GET_CT());
49		SELECT	COUNT(*)
50		FROM	[subscriptions]
51		WHERE	[sb_id] > 500;
52		PRINT CONCAT('Tr B COMMIT: ',
53		dbo.GET_CT());
54		COMMIT;
55		PRINT CONCAT('TrC = ', @@TRANCOUNT);
56		COMMIT;
57		PRINT CONCAT('TrC = ', @@TRANCOUNT);

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

Пример 44: взаимодействие конкурирующих транзакций

Итоговые результаты взаимодействия транзакций таковы.

			Уровень изолированности транзакции B
		READ	READ	REPEATABLE	SNAPSHOT	SERIALIZABLE
		UNCOMMITTED	COMMITTED	READ

			Транзакция B	Транзакция B	Транзакция	Транзакция B не
		Транзакция B	не получает до-	не получает до-		получает до-
					B не полу-
		успевает обра-	ступа к «фан-	ступа к «фан-		ступа к «фан-
	READ				чает до-
		ботать «фан-	томной за-	томной за-		томной записи»,
	UNCOMMITTED				ступа к
		томную за-	писи», её	писи», её		INSERT в тран-
					«фантомной
		пись»	COUNT-2 ждёт	COUNT-2 ждёт		закции A ждёт
					записи»
			завершения A	завершения A		завершения B

			Транзакция B	Транзакция B	Транзакция	Транзакция B не
		Транзакция B	не получает до-	не получает до-		получает до-
					B не полу-
		успевает обра-	ступа к «фан-	ступа к «фан-		ступа к «фан-
	READ				чает до-
		ботать «фан-	томной за-	томной за-		томной записи»,
A	COMMITTED				ступа к
		томную за-	писи», её	писи», её		INSERT в тран-
транзакции					«фантомной
		пись»	COUNT-2 ждёт	COUNT-2 ждёт		закции A ждёт
					записи»

			завершения A	завершения A		завершения B

		Транзакция B	Транзакция B	Транзакция B		Транзакция B не
		успевает обра-			Транзакция
изолированности			не получает до-	не получает до-		получает до-
		ния B			B не полу-

		ботать «фан-	ступа к «фан-	ступа к «фан-		ступа к «фан-
	REPEATABLE	томную за-			чает до-
			томной за-	томной за-		томной записи»,
	READ	пись», INSERT			ступа к
			писи», её	писи», её		INSERT в тран-
		в транзакции A			«фантомной
			COUNT-2 ждёт	COUNT-2 ждёт		закции A ждёт
		ждёт заверше-			записи»
			завершения A	завершения A		завершения B

Уровень
		успевает обра-	ступа к «фан-	ступа к «фан-		ступа к «фан-
			Транзакция B	Транзакция B	Транзакция	Транзакция B не
		Транзакция B	не получает до-	не получает до-		получает до-
					B не полу-

	SNAPSHOT	ботать «фан-	томной за-	томной за-	чает до-	томной записи»,
					ступа к
		томную за-	писи», её	писи», её		INSERT в тран-
					«фантомной
		пись»	COUNT-2 ждёт	COUNT-2 ждёт		закции A ждёт
					записи»
			завершения A	завершения A		завершения B

			Транзакция B	Транзакция B	Транзакция	Транзакция B не
		Транзакция B	не получает до-	не получает до-		получает до-
					B не полу-
		успевает обра-	ступа к «фан-	ступа к «фан-		ступа к «фан-
					чает до-
	SERIALIZABLE	ботать «фан-	томной за-	томной за-		томной записи»,
					ступа к
		томную за-	писи», её	писи», её		INSERT в тран-
					«фантомной
		пись»	COUNT-2 ждёт	COUNT-2 ждёт		закции A ждёт
					записи»
			завершения A	завершения A		завершения B

На этом решение для MS SQL Server завершено.

Переходим к Oracle. Данная СУБД поддерживает два уровня изолированности транзакций и два режима транзакций, комбинации которых мы и рассмотрим:

•Уровни изолированности:

oREAD COMMITTED;

oSERIALIZABLE.

•Режимы:

oREAD ONLY (фактически, это — тоже уровень изолированности, экви-

валентный REPEATABLE READ и/или SERIALIZABLE в других СУБД); o READ WRITE.

Для выполнения эксперимента используем командный файл:

start cmd.exe /c "echo exit | sqlplus ПОЛЬЗОВАТЕЛЬ/ПАРОЛЬ@КОМПЬЮТЕР @a.sql & pause"

start cmd.exe /c "echo exit | sqlplus ПОЛЬЗОВАТЕЛЬ/ПАРОЛЬ@КОМПЬЮТЕР @b.sql & pause"

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

Пример 44: взаимодействие конкурирующих транзакций

Для упрощения кода приведённых далее запросов создадим функции

GET_IDS_AND_ISOLATION_LEVEL и GET_CT, возвращающие, соответственно, зна-

чение параметров транзакции и значение текущего времени.

Oracle

Решение 6.2.2.a (код и запрос для проверки работоспособности сервисных функций)

1CREATE FUNCTION GET_IDS_AND_ISOLATION_LEVEL

2RETURN NVARCHAR2

3IS

4session_id NUMBER(10);

5session_sn NUMBER(10);

6session_isolation_level NVARCHAR2(150);

7trans_id VARCHAR(100);

8BEGIN

9trans_id := DBMS_TRANSACTION.LOCAL_TRANSACTION_ID(FALSE);

10SELECT "session".sid AS "session_id",

11"session".serial# AS "session_sn",

12CASE BITAND("transaction".flag, POWER(2, 28))

13WHEN 0 THEN 'READ COMMITTED'

14ELSE 'SERIALIZABLE'

15END AS "session_isolation_level"

16 INTO session_id,

17session_sn,

18session_isolation_level

19	FROM v$transaction "transaction"

20JOIN v$session "session"

21ON "transaction".addr = "session".taddr

22		AND "session".sid = SYS_CONTEXT('USERENV', 'SID');
23

24RETURN 'ID = ' || session_id || ', SN = ' || session_sn || ', in ' ||

25session_isolation_level;

26END;

27

28CREATE FUNCTION GET_CT

29RETURN NVARCHAR2

30IS

31BEGIN

32RETURN TO_CHAR(SYSTIMESTAMP,'HH24:MI:SS.FF');

33END;

34

35 SELECT GET_CT FROM DUAL;

26SELECT GET_IDS_AND_ISOLATION_LEVEL FROM DUAL;

Ксожалению, в Oracle 11gR2 не существует документированного способа различить READ ONLY и READ WRITE транзакции, равно как не существует и спо-

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

Поскольку Oracle (как и MySQL) не поддерживает вложенные транзакции, здесь (в отличие от MS SQL Server) мы не будем следить за текущим уровнем транзакций.

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

Смотрите также: