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

Внимание! Если размещение файла нарушает Ваши авторские права, то обязательно сообщите нам

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

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

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

		UNCOMMITTED	COMMITTED	READ

				Первый и второй		Первый и второй
		Первый и второй	Первый и второй	SELECT	Первый и второй	SELECT
				возвратили	SELECT	возвратили
	READ	SELECT	SELECT
				одинаковые	возвратили	одинаковые
	UNCOMMITTED	возвратили	возвратили
				данные, тран-	одинаковые	данные, тран-
		разные данные	разные данные
				закция A ждёт	данные	закция A ждёт

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

				Первый и второй		Первый и второй
		Первый и второй	Первый и второй	SELECT	Первый и второй	SELECT
				возвратили	SELECT	возвратили
	READ	SELECT	SELECT
				одинаковые	возвратили	одинаковые
A	COMMITTED	возвратили	возвратили

транзакции				данные, тран-	одинаковые	данные, тран-
		разные данные	разные данные
				закция A ждёт	данные	закция A ждёт


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

				Первый и второй		Первый и второй
изолированности		Первый и второй	Первый и второй	SELECT	Первый и второй	SELECT
				возвратили	SELECT	возвратили

	REPEATABLE	SELECT	SELECT
				одинаковые	возвратили	одинаковые
	READ	возвратили	возвратили
				данные, тран-	одинаковые	данные, тран-
		разные данные	разные данные
				закция A ждёт	данные	закция A ждёт

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

Уровень				Первый и второй		Первый и второй
		Первый и второй	Первый и второй	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 Стр: 480/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 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 COUNT-1: ',
11			dbo GET_CT ));
12	WAITFOR DELAY '00:00:05';		SELECT COUNT(*)
13			FROM	[subscriptions]
14			WHERE	[sb id] > 5001
	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],
25		[sb_start]	WAITFOR DELAY '00:00:10';
26		[sb_finish],	WAITFOR DELAY '00:00:10';
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
35	SET 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 1
	PRINT CONCAT('Tr A ROLLBACK: ',
44	dbo GET_CT ());		WAITFOR DELAY '00:00:15';
45	ROLLBACK;		WAITFOR DELAY '00:00:15';
45	ROLLBACK;
	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 Стр: 481/545

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

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

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

		UNCOMMITTED	COMMITTED	READ

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

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

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

транзакции		томную запись»
			COUNT-2 ждёт	2 ждёт	записи»	транзакции A ждёт


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

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

Уровень
		Транзакция B	Транзакция B не	Транзакция B не	Транзакция B	Транзакция B не

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

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

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

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

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

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

oREAD COMMITTED;

oSERIALIZABLE.

•Режимы:

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

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

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

start cmd.exe /с "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 Стр: 482/545

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

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

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

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

Oracl	і	Решение 6.2.2.a (код и запрос для проверки работоспособности сервисных функций)			і
e	і				і
e
1	CREATE FUNCTION GET IDS AND ISOLATION LEVEL
2	RETURN NVARCHAR2
3	IS
4	session id NUMBER(10);
5	session sn NUMBER(10);
6	session isolation level NVARCHAR2(150 ;
7	trans id VARCHAR 100);
8	BEGIN
9	trans id := DBMS TRANSACTION.LOCAL TRANSACTION ID(FALSE);
10	SELECT "session" sid AS "session id"
11		"session" serial# AS "session sn",
12		CASE BITAND("transaction" flag	POWER 2	28))
13		WHEN 0 THEN 'READ COMMITTED'
14		ELSE 'SERIALIZABLE'
15		END AS "session isolation level"
16	INTO	session id
17		session sn,
18		session isolation level
19	FROM	v$transaction "transaction"
20		JOIN v$session "session"
21		ON "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;

28CREATE FUNCTION GET CT

29RETURN NVARCHAR2

30IS

31BEGIN

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

33END;

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) мы не будем следить за текущим уровнем транзакций.

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

Г рязное чтение в Oracle не существует, но проверить это можно выполнением в двух отдельных сессиях следующих блоков кода:

Oracle			Решение 6.2.2.a (код для исследования аномалии грязного чтения)
1	-- Транзакция A:						-- Транзакция B: ALTER SESSION SET
2	ALTER SESSION SET						-- Транзакция B: ALTER SESSION SET
2	ALTER SESSION SET						ISOLATION_LEVEL = {УРОВЕНЬ}; SET
3	ISOLATION_LEVEL = {УРОВЕНЬ};						ISOLATION_LEVEL = {УРОВЕНЬ}; SET
3	ISOLATION_LEVEL = {УРОВЕНЬ};						TRANSACTION {РЕЖИМ}; SELECT 'Tr B: '
4	SET TRANSACTION {РЕЖИМ};						TRANSACTION {РЕЖИМ}; SELECT 'Tr B: '
4	SET TRANSACTION {РЕЖИМ};							\|\|
5	SELECT 'Tr A: ' \|\|							\|\|
5	SELECT 'Tr A: ' \|\|						GET_IDS_AND_ISOLATION_LEVEL FROM DUAL
6	GET_IDS_AND_ISOLATION_LEVEL FROM DUAL						GET_IDS_AND_ISOLATION_LEVEL FROM DUAL
6	GET_IDS_AND_ISOLATION_LEVEL FROM DUAL						"
		"					SELECT 'Tr B START: ' \|\|
8	SELECT 'Tr A START: ' \|\|						SELECT 'Tr B START: ' \|\|
8	SELECT 'Tr A START: ' \|\|							T FROM DUAL;
				T FROM DUAL				T FROM DUAL;
				T FROM DUAL
10							SELECT 'Tr B SELECT-1: '
11							GET_CT FROM DUAL;
12	EXEC DBMS_LOCK.SLEEP(5);						SELECT "sb_is_active"
13							FROM	"subscriptions"
14							WHERE	"sb id" = 2;
		SELECT 'Tr A UPDATE: '
16	GET_CT FROM DUAL
17	UPDATE "subscriptions"
18	SET			"sb_is_active" =
19		CASE					EXEC DBMS_LOCK.SLEEP(10);
20		WHEN		"sb_is_active" =	'Y' THEN	'N'
21		WHEN		"sb_is_active" =	'N' THEN	'Y'
22		END
		WHERE		"sb id" = 2;
24							SELECT 'Tr B SELECT-2: '
25							GET_CT FROM DUAL;
26							SELECT "sb_is_active"
27							FROM	"subscriptions"
28	EXEC DBMS_LOCK.SLEEP(20				;		WHERE	"sb_id" = 2;
29							SELECT 'Tr B COMMIT: ' \|\|
30							GET_CT FROM DUAL;
31							COMMIT;
		SELECT 'Tr A ROLLBACK: '

33GET_CT FROM DUAL

34ROLLBACK;

Источник: https://studfile.net/preview/16420333/

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

_-___-~1
_3 тема - Диффузия
_Роджерс Карл, Брак и его альтернативы
'Заповит' Рейнгольда Глиэра: стилистические особенности
'Создание и продвижение интернет - платформы реферальной системы' на примере компании Бартер–кард
[БИЛЕТЫ] IMK №3
[ТЕСТЫ] ИМК №1 по физиологии (база 1 семестр и на экз)
[ТЕСТЫ] Спланхнология
[ТЕСТЫ] ЦНС - ЛУЧШИЕ 2020
+ Проблемы криминологии. Курс лекций