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

Пример 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] =	WAITFOR DELAY '00:00:10';
19		CASE

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

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

22END

23WHERE [sb_id] = 2;

24		PRINT CONCAT('Tr B SELECT-2: ',
25		dbo.GET_CT());
26		SELECT	[sb_is_active]
27		FROM	[subscriptions]
28	WAITFOR DELAY '00:00:20';	WHERE	[sb_id] = 2;
29		PRINT CONCAT('Tr B COMMIT: ',
30		dbo.GET_CT());
31		COMMIT;
32		PRINT CONCAT('TrС = ', @@TRANCOUNT);
33		COMMIT;
34		PRINT CONCAT('TrC = ', @@TRANCOUNT);
35	PRINT CONCAT('Tr A ROLLBACK: ',

36dbo.GET_CT());

37ROLLBACK;

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

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

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

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

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

			Транзакция B	Транзакция B		Транзакция B
			оба раза чи-		Транзак-
		Транзакция		оба раза чи-		оба раза чи-
			тает исходное		ция B оба
		B успевает		тает исходное		тает исходное
			(корректное)		раза чи-
	READ	прочитать		(корректное)		(корректное)
			значение, SE-		тает исход-
	UNCOMMITTED	незафикси-		значение, UP-		значение, UP-
			LECT-2 в		ное (кор-
		рованное		DATE в тран-		DATE в тран-
			транзакции B		ректное)
		значение		закции A ждёт		закции A ждёт
			ждёт завер-		значение
				завершения B		завершения B
			шения A

			Транзакция B	Транзакция B		Транзакция B
			оба раза чи-		Транзак-
		Транзакция		оба раза чи-		оба раза чи-
			тает исходное		ция B оба
		B успевает		тает исходное		тает исходное
			(корректное)		раза чи-
	READ	прочитать		(корректное)		(корректное)
			значение, SE-		тает исход-
	COMMITTED	незафикси-		значение, UP-		значение, UP-
			LECT-2 в		ное (кор-
		рованное		DATE в тран-		DATE в тран-
A			транзакции B		ректное)
		значение		закции A ждёт		закции A ждёт
транзакции			ждёт завер-		значение
				завершения B		завершения B
			шения A


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

			(корректное)	тает исходное	раза чи-	тает исходное
	REPEATABLE	прочитать		(корректное)		(корректное)
			значение, SE-		тает исход-
	READ	незафикси-		значение, UP-		значение, UP-
			LECT-2 в		ное (кор-
		рованное		DATE в тран-		DATE в тран-
			транзакции B		ректное)
		значение		закции A ждёт		закции A ждёт
			ждёт завер-		значение

Уровень			шения A
		Транзакция	Транзакция B	оба раза чи-		оба раза чи-
			оба раза чи-	Транзакция B	Транзак-	Транзакция B

		B успевает	тает исходное	тает исходное	ция B оба	тает исходное
			(корректное)		раза чи-
		прочитать		(корректное)		(корректное)
	SNAPSHOT		значение, SE-		тает исход-
		незафикси-		значение, UP-		значение, UP-
			LECT-2 в		ное (кор-
		рованное		DATE в тран-		DATE в тран-
			транзакции B		ректное)
		значение		закции A ждёт		закции A ждёт
			ждёт завер-		значение
				завершения B		завершения B
			шения A

			Транзакция B	Транзакция B		Транзакция B
			оба раза чи-		Транзак-
		Транзакция		оба раза чи-		оба раза чи-
			тает исходное		ция B оба
		B успевает		тает исходное		тает исходное
			(корректное)		раза чи-
		прочитать		(корректное)		(корректное)
	SERIALIZABLE		значение, SE-		тает исход-
		незафикси-		значение, UP-		значение, UP-
			LECT-2 в		ное (кор-
		рованное		DATE в тран-		DATE в тран-
			транзакции B		ректное)
		значение		закции A ждёт		закции A ждёт
			ждёт завер-		значение
				завершения B		завершения B
			шения A

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 446/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 A SELECT: ',
11	dbo.GET_CT());
12	SELECT	[sb_is_active]	WAITFOR DELAY '00:00:05';
13	FROM	[subscriptions]
14	WHERE	[sb_id] = 2;

15			PRINT CONCAT('Tr B SELECT: ',
16			dbo.GET_CT());
17	WAITFOR DELAY '00:00:10';		SELECT	[sb_is_active]
18			FROM	[subscriptions]
19			WHERE	[sb_id] = 2;
20	PRINT CONCAT('Tr A UPDATE: ',
21	dbo.GET_CT());
22	UPDATE	[subscriptions]
23	SET	[sb_is_active] = 'Y'	WAITFOR DELAY '00:00:10';
24	WHERE	[sb_id] = 2;

25PRINT CONCAT('Tr A COMMIT: ',

26dbo.GET_CT());

27COMMIT;

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

29COMMIT;

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

31			PRINT CONCAT('Tr B UPDATE: ',
32			dbo.GET_CT());
33			UPDATE	[subscriptions]
34	WAITFOR DELAY '00:00:10';		SET	[sb_is_active] = 'N'
35			WHERE	[sb_id] = 2;
36			PRINT CONCAT('Tr B COMMIT: ',
37			dbo.GET_CT());
38			COMMIT;
39			PRINT CONCAT('TrC = ', @@TRANCOUNT);
40			COMMIT;
41			PRINT CONCAT('TrC = ', @@TRANCOUNT);
42	PRINT CONCAT('Tr A SELECT AFTER: ',		PRINT CONCAT('Tr B SELECT AFTER: ',
43	dbo.GET_CT());		dbo.GET_CT());
44	SELECT	[sb_is_active]	SELECT	[sb_is_active]
45	FROM	[subscriptions]	FROM	[subscriptions]
46	WHERE	[sb_id] = 2;	WHERE	[sb_id] = 2;

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

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

			Уровень изолированности транзакции B
		READ	READ	REPEATABLE	SNAPSHOT	SERIALIZABLE
		UNCOMMITTED	COMMITTED	READ	SNAPSHOT	SERIALIZABLE
		UNCOMMITTED	COMMITTED	READ
					Обновление
				Обновление	транзакции A	Обновление
				Обновление	сохранено,	Обновление
				транзакции A	сохранено,	транзакции A
				транзакции A	транзакция B	транзакции A
		Обновление	Обновление	сохранено,	транзакция B	сохранено,
	READ	Обновление	Обновление	сохранено,	отменена (по-	сохранено,
	READ	транзакции A	транзакции A	UPDATE в	отменена (по-	UPDATE в
	UNCOMMITTED	транзакции A	транзакции A	UPDATE в	пытка обно-	UPDATE в
	UNCOMMITTED	утеряно	утеряно	транзакции A	пытка обно-	транзакции A
		утеряно	утеряно	транзакции A	вить заблоки-	транзакции A
				ждёт заверше-	вить заблоки-	ждёт заверше-
				ждёт заверше-	рованную за-	ждёт заверше-
				ния B	рованную за-	ния B
				ния B	пись в режиме	ния B
					пись в режиме
					SNAPSHOT)
					Обновление
				Обновление	транзакции A	Обновление
				Обновление	сохранено,	Обновление
				транзакции A	сохранено,	транзакции A
				транзакции A	транзакция B	транзакции A
		Обновление	Обновление	сохранено,	транзакция B	сохранено,
	READ	Обновление	Обновление	сохранено,	отменена (по-	сохранено,
	READ	транзакции A	транзакции A	UPDATE в	отменена (по-	UPDATE в
	COMMITTED	транзакции A	транзакции A	UPDATE в	пытка обно-	UPDATE в
	COMMITTED	утеряно	утеряно	транзакции A	пытка обно-	транзакции A
		утеряно	утеряно	транзакции A	вить заблоки-	транзакции A
				ждёт заверше-	вить заблоки-	ждёт заверше-
				ждёт заверше-	рованную за-	ждёт заверше-
A				ния B	рованную за-	ния B
A				ния B	пись в режиме	ния B
транзакции					пись в режиме
транзакции					SNAPSHOT)
					SNAPSHOT)
					Обновление
					транзакции A
изолированности					сохранено,
изолированности					пись в режиме
		Обновление	Обновление	Взаимная бло-	транзакция B	Взаимная бло-
	REPEATABLE	транзакции A	транзакции A	кировка тран-	отменена (по-	кировка тран-
	READ	утеряно	утеряно	закций	пытка обно-	закций
		утеряно	утеряно	закций	вить заблоки-	закций
					вить заблоки-
Уровень					рованную за-
Уровень					транзакции A
					SNAPSHOT)
					Обновление
				Транзакция A	сохранено,	Транзакция A
				отменена (по-	сохранено,	отменена (по-
				отменена (по-	транзакция B	отменена (по-
		Обновление	Обновление	пытка обно-	транзакция B	пытка обно-
		Обновление	Обновление	пытка обно-	отменена (по-	пытка обно-
	SNAPSHOT	транзакции A	транзакции A	вить заблоки-	отменена (по-	вить заблоки-
	SNAPSHOT	транзакции A	транзакции A	вить заблоки-	пытка обно-	вить заблоки-
		утеряно	утеряно	рованную за-	пытка обно-	рованную за-
		утеряно	утеряно	рованную за-	вить заблоки-	рованную за-
				пись в режиме	вить заблоки-	пись в режиме
				пись в режиме	рованную за-	пись в режиме
				SNAPSHOT)	рованную за-	SNAPSHOT)
				SNAPSHOT)	пись в режиме	SNAPSHOT)
					пись в режиме
					SNAPSHOT)
					Обновление
		Обновление	Обновление		транзакции A
		Обновление	Обновление		сохранено,
		транзакции A	транзакции A		сохранено,
		транзакции A	транзакции A		транзакция B
		утеряно,	утеряно,	Взаимная бло-	транзакция B	Взаимная бло-
		утеряно,	утеряно,	Взаимная бло-	отменена (по-	Взаимная бло-
	SERIALIZABLE	COMMIT в	COMMIT в	кировка тран-	отменена (по-	кировка тран-
	SERIALIZABLE	COMMIT в	COMMIT в	кировка тран-	пытка обно-	кировка тран-
		транзакции A	транзакции A	закций	пытка обно-	закций
		транзакции A	транзакции A	закций	вить заблоки-	закций
		ждёт заверше-	ждёт заверше-		вить заблоки-
		ждёт заверше-	ждёт заверше-		рованную за-
		ния B	ния B		рованную за-
		ния B	ния B		пись в режиме
					пись в режиме
					SNAPSHOT)

В учебных целях рассмотрим, что было бы, если бы в коде обеих транзакций мы «забыли» дописать второй COMMIT (см. подобранности в решении{408} задачи

6.1.1.a{408}).

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

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

			Уровень изолированности транзакции B
		READ	READ	REPEATABLE	SNAPSHOT	SERIALIZABLE
		UNCOMMITTED	COMMITTED	READ	SNAPSHOT	SERIALIZABLE
		UNCOMMITTED	COMMITTED	READ
		Обновление	Обновление	Обновление	Обновление
		транзакции A	транзакции A	транзакции A	транзакции A
	READ	утеряно, UP-	утеряно, UP-	утеряно, UP-	утеряно, UP-	Обновление
	READ	DATE в тран-	DATE в тран-	DATE в тран-	DATE в тран-	транзакции A
	UNCOMMITTED	DATE в тран-	DATE в тран-	DATE в тран-	DATE в тран-	транзакции A
	UNCOMMITTED	закции B ждёт	закции B ждёт	закции B ждёт	закции B ждёт	утеряно
		закции B ждёт	закции B ждёт	закции B ждёт	закции B ждёт	утеряно
		завершения	завершения	завершения	завершения
		сессии A	сессии A	сессии A	сессии A
		Обновление	Обновление	Обновление	Обновление
		транзакции A	транзакции A	транзакции A	транзакции A
	READ	утеряно, UP-	утеряно, UP-	утеряно, UP-	утеряно, UP-	Обновление
A	READ	DATE в тран-	DATE в тран-	DATE в тран-	DATE в тран-	транзакции A
A	COMMITTED	DATE в тран-	DATE в тран-	DATE в тран-	DATE в тран-	транзакции A
транзакции	COMMITTED	закции B ждёт	закции B ждёт	закции B ждёт	закции B ждёт	утеряно
		закции B ждёт	закции B ждёт	закции B ждёт	закции B ждёт	утеряно
		завершения	завершения	завершения	завершения
		завершения	завершения	завершения	завершения
		сессии A	сессии A	сессии A	сессии A
		Обновление	Обновление	Обновление	Обновление
изолированности		транзакции A	транзакции A	транзакции A	транзакции A
изолированности	REPEATABLE	утеряно, UP-	утеряно, UP-	утеряно, UP-	утеряно, UP-	Взаимная бло-
		утеряно, UP-	утеряно, UP-	утеряно, UP-	утеряно, UP-	Взаимная бло-
		DATE в тран-	DATE в тран-	DATE в тран-	DATE в тран-	кировка тран-
	READ	DATE в тран-	DATE в тран-	DATE в тран-	DATE в тран-	кировка тран-
	READ	закции B ждёт	закции B ждёт	закции B ждёт	закции B ждёт	закций
		закции B ждёт	закции B ждёт	закции B ждёт	закции B ждёт	закций
		завершения	завершения	завершения	завершения
		сессии A	сессии A	сессии A	сессии A
Уровень		Обновление	Обновление		Обновление
Уровень		транзакции A	транзакции A		транзакции A
		транзакции A	транзакции A		транзакции A
		утеряно, UP-	утеряно, UP-	Обновление	утеряно, UP-	Обновление
	SNAPSHOT	DATE в тран-	DATE в тран-	транзакции A	DATE в тран-	транзакции A
		закции B ждёт	закции B ждёт	утеряно	закции B ждёт	утеряно
		завершения	завершения		завершения
		сессии A	сессии A		сессии A
		Обновление	Обновление		Обновление
		транзакции A	транзакции A		транзакции A
		утеряно, UP-	утеряно, UP-	Взаимная бло-	утеряно, UP-	Взаимная бло-
	SERIALIZABLE	DATE в тран-	DATE в тран-	кировка тран-	DATE в тран-	кировка тран-
		закции B ждёт	закции B ждёт	закций	закции B ждёт	закций
		завершения	завершения		завершения
		сессии A	сессии A		сессии A

Обратите внимание на формулировку «UPDATE в транзакции B ждёт завершения сессии A». Здесь имеется в виду именно вся сессия взаимодействия с СУБД, а не просто транзакция. Из-за «забытого» COMMIT обе транзакции фактически завершаются именно в момент закрытия сессии с СУБД.

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

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

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