Документ: Using_MySql,_MS_SQL_Server_and_Oracle, страницы 476-480

Пример 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 в тран-
			транзакции B
A		значение		закции A ждёт	значение	закции A ждёт

транзакции			ждёт завер-	завершения B		завершения B
			шения A



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

	REPEATABLE	прочитать		(корректное)	тает исход-	(корректное)
			значение, SE-
	READ	незафикси-		значение, UP-	ное (кор-	значение, UP-
			LECT-2 в
		рованное		DATE в тран-	ректное)	DATE в тран-
			транзакции B
		значение		закции A ждёт	значение	закции A ждёт
			ждёт завер-
				завершения B		завершения B
			шения 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 Стр: 475/545

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

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

MS SQL I Решение 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 G		dbo G

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;
25	PRINT CONCAT('Tr A COMMIT: ',
26	dbo GET CT () ) ;

27	COMMIT;
28	PRINT CONCAT('TrC = ', @@TRANCOUNT);
29	COMMIT;
30	PRINT CONCAT('TrC = ',

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 = ',

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;

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

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

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

			Уровень изолированности транзакции B
		READ	READ	REPEATABLE	SNAPSHOT	SERIALIZABLE
		READ		REPEATABLE
		UNCOMMITTED	COMMITTED	READ
		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
					в режиме
					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
транзакции
транзакции					SNAPSHOT)
					SNAPSHOT)

					Обновление
					транзакции A
изолированности					сохранено,
изолированности					в режиме
		Обновление	Обновление	Взаимная бло-	транзакция B	Взаимная бло-
	REPEATABLE	транзакции A	транзакции A	кировка тран-	отменена (по-	кировка тран-
	READ	утеряно	утеряно	закций	пытка обновить	закций
		утеряно	утеряно	закций	заблоки-	закций
					заблоки-
					рованную запись
Уровень					SNAPSHOT)
Уровень					транзакции A
					Обновление
				Транзакция 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}).

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

Пример 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-	Обновление
	READ	DATE в тран-	DATE в тран-	DATE в тран-	DATE в тран-	транзакции A
A	COMMITTED	DATE в тран-	DATE в тран-	DATE в тран-	DATE в тран-	транзакции A
A
транзакции		закции 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 ждёт завершения сессии А». Здесь имеется в виду именно вся сессия взаимодействия с СУБД, а не просто транзакция. Из-за «забытого» COMMIT обе транзакции фактически завершаются именно в момент закрытия сессии с СУБД.

Чтобы получить ещё один вариант поведения СУБД, необходимо явно блокировать читаемые записи (UPDLOCK) в первой операции (чтении). В данном случае

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

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

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

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

MS SQL I Решение 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 LEVEL				SET TRANSACTION ISOLATION LEVEL
	{УРОВЕНЬ};				{УРОВЕНЬ};
	BEGIN TRANSACTION;				BEGIN TRANSACTION;
	PRINT CONCAT('Tr A START: ',				PRINT CONCAT('Tr B START: ',
8	dbo.GET_CT() , ' in ',				dbo.GET_CT(), ' in ',
	dbo G				dbo G
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;
	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'
21	WHEN	[sb_is_active] =	'N' THEN	'Y'
22	END				WAITFOR DELAY '00:00:10';
23	WHERE [sb_id] = 2;
24	PRINT CONCAT('Tr A COMMIT: ',
25	dbo.GET_CT() );
26	COMMIT;
27	PRINT CONCAT('TrC = ', @@TRANCOUNT);
28	COMMIT;
	PRINT 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);

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

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