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

Скачать

Заказать новую работу

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

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

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

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

 

1

 

-- Транзакция A:

 

-- Транзакция B:

 

 

2

 

SELECT

CONCAT('Tr A ID = ',

 

SELECT

CONCAT('Tr B ID = ',

 

 

3

 

 

CONNECTION_ID());

 

 

CONNECTION_ID());

 

 

4

 

SET autocommit = 0;

 

SET autocommit = 0;

 

 

5

 

SET SESSION TRANSACTION

 

SET SESSION TRANSACTION

 

 

 

 

ISOLATION LEVEL {УРОВЕНЬ};

 

ISOLATION LEVEL {УРОВЕНЬ};

 

 

6

 

START TRANSACTION;

 

START TRANSACTION;

 

 

7

 

SELECT

CONCAT('Tr A START: ',

 

SELECT

CONCAT('Tr B START: ',

 

 

8

 

 

CURTIME(), ' in ');

 

 

CURTIME(), ' in ');

 

 

9

 

SELECT

`VARIABLE_VALUE`

 

SELECT

`VARIABLE_VALUE`

 

 

10

 

FROM

`information_schema`.

 

FROM

`information_schema`.

 

 

11

 

 

`session_variables`

 

 

`session_variables`

 

 

12

 

WHERE

`VARIABLE_NAME` =

 

WHERE

`VARIABLE_NAME` =

 

 

13

 

 

'tx_isolation';

 

 

'tx_isolation';

 

 

 

 

 

 

 

 

 

 

 

14

 

 

 

 

SELECT

CONCAT('Tr A SELECT-1: ',

 

 

15

 

 

 

 

 

CURTIME());

 

 

16

 

SELECT

SLEEP(5);

 

 

 

 

 

17

 

 

 

 

SELECT

`sb_is_active`

 

 

18

 

 

 

 

FROM

`subscriptions`

 

 

19

 

 

 

 

WHERE

`sb_id` = 2;

 

 

20

 

SELECT

CONCAT('Tr A UPDATE: ',

 

 

 

 

 

21

 

 

CURTIME());

 

 

 

 

 

22

 

UPDATE

`subscriptions`

 

 

 

 

 

23

 

SET

`sb_is_active` =

 

 

 

 

24

 

CASE

 

 

SELECT

SLEEP(10);

25WHEN `sb_is_active` = 'Y' THEN 'N'

26WHEN `sb_is_active` = 'N' THEN 'Y'

27END

28WHERE `sb_id` = 2;

29SELECT CONCAT('Tr A COMMIT: ',

 

30

 

CURTIME());

 

 

 

 

31

 

COMMIT;

 

 

 

 

32

 

 

 

SELECT

CONCAT('Tr A SELECT-2: ',

 

33

 

 

 

 

CURTIME());

 

34

 

 

 

 

 

 

35

 

 

 

SELECT

`sb_is_active`

 

36

 

 

 

FROM

`subscriptions`

 

37

 

 

 

WHERE

`sb_id` = 2;

 

38

 

 

 

SELECT

CONCAT('Tr B COMMIT: ',

 

39

 

 

 

 

CURTIME());

 

40

 

 

 

COMMIT;

 

Приведём пример журнала выполнения этого кода для ситуации, когда транзакция A выполняется на уровне изолированности REPEATABLE READ и конкурирует с транзакцией B, последовательно выполняемой во всех поддерживаемых MySQL уровнях изолированности.

A: REPEATABLE READ

B: READ UNCOMMITTED

Tr A ID = 151

Tr B ID = 152

Tr A START: 20:24:12 in REPEATABLE-READ

Tr B START: 20:24:12 in READ-UNCOMMITTED

Tr A UPDATE: 20:24:17

Tr B SELECT-1: 20:24:12

Tr A COMMIT: 20:24:17

sb_is_active = N

 

Tr B SELECT-2: 20:24:22

 

sb_is_active = Y

 

Tr B COMMIT: 20:24:22

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

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

A: REPEATABLE READ

B: READ COMMITTED

Tr A ID = 153

Tr B ID = 154

Tr A START: 20:25:29 in REPEATABLE-READ

Tr B START: 20:25:29 in READ-COMMITTED

Tr A UPDATE: 20:25:34

Tr B SELECT-1: 20:25:29

Tr A COMMIT: 20:25:34

sb_is_active = Y

 

Tr B SELECT-2: 20:25:39

 

sb_is_active = N

 

Tr B COMMIT: 20:25:39

A: REPEATABLE READ

B: REPEATABLE READ

Tr A ID = 156

Tr B ID = 155

Tr A START: 20:26:24 in REPEATABLE-READ

Tr B START: 20:26:24 in REPEATABLE-READ

Tr A UPDATE: 20:26:29

Tr B SELECT-1: 20:26:24

Tr A COMMIT: 20:26:29

sb_is_active = N

 

Tr B SELECT-2: 20:26:34

 

sb_is_active = N

 

Tr B COMMIT: 20:26:34

A: REPEATABLE READ

B: SERIALIZABLE

Tr A ID = 157

Tr B ID = 158

Tr A START: 20:27:15 in REPEATABLE-READ

Tr B START: 20:27:15 in SERIALIZABLE

Tr A UPDATE: 20:27:20

Tr B SELECT-1: 20:27:15

Tr A COMMIT: 20:27:25

sb_is_active = Y

 

Tr B SELECT-2: 20:27:25

 

sb_is_active = Y

 

Tr B COMMIT: 20:27:25

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

 

 

 

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

 

 

READ

READ

REPEATABLE

SERIALIZABLE

 

 

UNCOMMITTED

COMMITTED

READ

 

 

 

A

 

Первый и вто-

Первый и вто-

Первый и второй

Первый и второй SELECT

READ

рой SELECT

рой SELECT

SELECT возвра-

возвратили одинаковые

транзакции

UNCOMMITTED

возвратили

возвратили

тили одинаковые

данные, транзакция A

 

 

 

разные данные

разные данные

данные

ждёт завершения B

 

 

Первый и вто-

Первый и вто-

Первый и второй

Первый и второй SELECT

изолированности

READ

рой SELECT

рой SELECT

SELECT возвра-

возвратили одинаковые

 

разные данные

разные данные

данные

ждёт завершения B

 

COMMITTED

возвратили

возвратили

тили одинаковые

данные, транзакция A

 

 

разные данные

разные данные

данные

ждёт завершения B

 

 

Первый и вто-

Первый и вто-

Первый и второй

Первый и второй SELECT

 

REPEATABLE

рой SELECT

рой SELECT

SELECT возвра-

возвратили одинаковые

 

READ

возвратили

возвратили

тили одинаковые

данные, транзакция A

Уровень

 

 

 

 

 

 

Первый и вто-

Первый и вто-

Первый и второй

Первый и второй SELECT

SERIALIZABLE

рой SELECT

рой SELECT

SELECT возвра-

возвратили одинаковые

возвратили

возвратили

тили одинаковые

данные, транзакция A

 

 

 

разные данные

разные данные

данные

ждёт завершения B

Чтобы получить другой вариант поведения СУБД, необходимо явно блокировать читаемые записи (SELECT … LOCK IN SHARE MODE или SELECT … FOR UPDATE)37 в первой операции чтения. В данном случае это не было сделано, чтобы продемонстрировать наиболее типичное поведение MySQL. Проверить же остальные случаи реакции СУБД вам предлагается самостоятельно в задании

6.2.2.TSK.D{464}.

37 http://dev.mysql.com/doc/refman/5.6/en/innodb-locking-reads.html

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

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

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

MySQL

Решение 6.2.2.a (код для исследования аномалии фантомного чтения)

1

-- Транзакция A:

-- Транзакция B:

2

SELECT CONCAT('Tr A ID = ',

SELECT

CONCAT('Tr B ID = ',

3

 

 

CONNECTION_ID());

 

CONNECTION_ID());

4

SET autocommit = 0;

SET autocommit = 0;

5

SET SESSION TRANSACTION

SET SESSION TRANSACTION

6

ISOLATION LEVEL {УРОВЕНЬ};

ISOLATION LEVEL {УРОВЕНЬ};

7

START TRANSACTION;

START TRANSACTION;

8

SELECT CONCAT('Tr A START: ',

SELECT

CONCAT('Tr B START: ',

9

 

 

CURTIME(), ' in ');

 

CURTIME(), ' in ');

10

SELECT

`VARIABLE_VALUE`

SELECT

`VARIABLE_VALUE`

11

FROM

`information_schema`.

FROM

`information_schema`.

12

 

 

`session_variables`

 

`session_variables`

13

WHERE

`VARIABLE_NAME` =

WHERE

`VARIABLE_NAME` =

14

 

 

'tx_isolation';

 

'tx_isolation';

 

 

 

 

 

 

15

 

 

 

SELECT

CONCAT('Tr B COUNT-1: ',

16

 

 

 

 

CURTIME());

17

SELECT SLEEP(5);

SELECT

COUNT(*)

18

 

 

 

FROM

`subscriptions`

19

 

 

 

WHERE

`sb_id` > 500;

20

SELECT CONCAT('Tr A INSERT: ',

 

 

21

 

 

CURTIME());

 

 

22

INSERT INTO `subscriptions`

 

 

23

 

 

(`sb_id`,

 

 

24

 

 

`sb_subscriber`,

 

 

25

 

 

`sb_book`,

 

 

26

 

 

`sb_start`,

SELECT

SLEEP(10);

27

 

 

`sb_finish`,

 

 

28

 

 

`sb_is_active`)

 

 

29

 

VALUES (1000,

 

 

30

 

 

1,

 

 

31

 

 

1,

 

 

32

 

 

'2025-01-12',

 

 

33

 

 

'2026-02-12',

 

 

34

 

 

'N');

 

 

35

 

 

 

SELECT

CONCAT('Tr B COUNT-2: ',

36

SELECT SLEEP(10);

 

CURTIME());

37

 

 

 

SELECT

COUNT(*)

38

 

 

 

FROM

`subscriptions`

39

 

 

 

WHERE

`sb_id` > 500;

40

SELECT CONCAT('Tr A ROLLBACK: ',

SELECT

SLEEP(15);

41

 

 

CURTIME());

 

 

42

ROLLBACK;

 

 

 

 

 

 

 

 

43

 

 

 

SELECT

CONCAT('Tr B COUNT-3: ',

44

 

 

 

 

CURTIME());

45

 

 

 

SELECT

COUNT(*)

46

 

 

 

FROM

`subscriptions`

47

 

 

 

WHERE

`sb_id` > 500;

48

 

 

 

SELECT

CONCAT('Tr B COMMIT: ',

49

 

 

 

 

CURTIME());

50

 

 

 

COMMIT;

 

Приведём пример журнала выполнения этого кода для ситуации, когда транзакция A выполняется на уровне изолированности SERIALIZABLE и конкурирует с транзакцией B, последовательно выполняемой во всех поддерживаемых MySQL уровнях изолированности.

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

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

A: SERIALIZABLE

B: READ UNCOMMITTED

Tr A ID = 198

Tr B ID = 197

Tr A START: 21:08:32 in SERIALIZABLE

Tr B START: 21:08:32 in READ-UNCOMMITTED

Tr A INSERT: 21:08:37

Tr B COUNT-1: 21:08:32

Tr A ROLLBACK: 21:08:47

COUNT(*) = 0

 

Tr B COUNT-2: 21:08:42

 

COUNT(*) = 1

 

Tr B COUNT-3: 21:08:57

 

COUNT(*) = 0

 

Tr B COMMIT: 21:08:57

A: SERIALIZABLE

B: READ COMMITTED

Tr A ID = 200

Tr B ID = 199

Tr A START: 21:09:34 in SERIALIZABLE

Tr B START: 21:09:34 in READ-COMMITTED

Tr A INSERT: 21:09:39

Tr B COUNT-1: 21:09:34

Tr A ROLLBACK: 21:09:49

COUNT(*) = 0

 

Tr B COUNT-2: 21:09:44

 

COUNT(*) = 0

 

Tr B COUNT-3: 21:09:59

 

COUNT(*) = 0

 

Tr B COMMIT: 21:09:59

A: SERIALIZABLE

B: REPEATABLE READ

Tr A ID = 201

Tr B ID = 202

Tr A START: 21:10:28 in SERIALIZABLE

Tr B START: 21:10:28 in REPEATABLE-READ

Tr A INSERT: 21:10:33

Tr B COUNT-1: 21:10:28

Tr A ROLLBACK: 21:10:43

COUNT(*) = 0

 

Tr B COUNT-2: 21:10:38

 

COUNT(*) = 0

 

Tr B COUNT-3: 21:10:53

 

COUNT(*) = 0

 

Tr B COMMIT: 21:10:53

A: SERIALIZABLE

B: SERIALIZABLE

Tr A ID = 203

Tr B ID = 204

Tr A START: 21:11:29 in SERIALIZABLE

Tr B START: 21:11:29 in SERIALIZABLE

Tr A INSERT: 21:11:34

Tr B COUNT-1: 21:11:29

Tr A ROLLBACK: 21:11:44

COUNT(*) = 0

 

Tr B COUNT-2: 21:11:39

 

COUNT(*) = 0

 

Tr B COUNT-3: 21:11:59

 

COUNT(*) = 0

 

Tr B COMMIT: 21:11:59

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

 

 

 

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

 

 

 

READ

READ

REPEATABLE

 

SERIALIZABLE

 

 

UNCOMMITTED

COMMITTED

READ

 

 

 

 

 

A

 

Транзакция B

Транзакция B не

Транзакция B

Транзакция B не получает

READ

успевает обра-

получает до-

не получает до-

доступа к «фантомной за-

транзакции

UNCOMMITTED

ботать «фан-

ступа к «фантом-

ступа к «фан-

писи», COUNT-3 ждёт за-

 

 

 

томную запись»

ной записи»

томной записи»

вершения транзакции A

 

 

Транзакция B

Транзакция B не

Транзакция B

Транзакция B не получает

изолированности

READ

успевает обра-

получает до-

не получает до-

доступа к «фантомной за-

 

томную запись»

ной записи»

томной записи»

вершения транзакции A

 

COMMITTED

ботать «фан-

ступа к «фантом-

ступа к «фан-

писи», COUNT-3 ждёт за-

 

 

томную запись»

ной записи»

томной записи»

вершения транзакции A

 

 

Транзакция B

Транзакция B не

Транзакция B

Транзакция B не получает

 

REPEATABLE

успевает обра-

получает до-

не получает до-

доступа к «фантомной за-

 

READ

ботать «фан-

ступа к «фантом-

ступа к «фан-

писи», COUNT-3 ждёт за-

Уровень

 

 

 

 

 

 

ботать «фан-

ступа к «фантом-

ступа к «фан-

писи», COUNT-3 ждёт за-

 

 

Транзакция B

Транзакция B не

Транзакция B

Транзакция B не получает

 

SERIALIZABLE

успевает обра-

получает до-

не получает до-

доступа к «фантомной за-

 

 

 

 

 

 

 

 

томную запись»

ной записи»

томной записи»

вершения транзакции A

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

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

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

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

•READ UNCOMMITTED;

•READ COMMITTED;

•REPEATABLE READ;

•SNAPSHOT;

•SERIALIZABLE.

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

start cmd.exe /c "sqlcmd -S КОМПЬЮТЕР\СЕРВЕР -i a.sql & pause" start cmd.exe /c "sqlcmd -S КОМПЬЮТЕР\СЕРВЕР -i b.sql & pause"

Для упрощения кода приведённых далее запросов создадим функции GET_ISOLATION_LEVEL и GET_CT, возвращающие, соответственно, значение текущего уровня изолированности транзакции и значение текущего времени.

MS SQL

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

1CREATE FUNCTION GET_ISOLATION_LEVEL()

2RETURNS NVARCHAR(50)

3BEGIN

4DECLARE @IsolationLevel NVARCHAR(50);

5SET @IsolationLevel = (

6SELECT CASE [transaction_isolation_level]

7WHEN 0 THEN 'Unspecified'

8WHEN 1 THEN 'Read Uncommitted'

9WHEN 2 THEN 'Read Committed'

10WHEN 3 THEN 'Repeatable Read'

11WHEN 4 THEN 'Serializable'

12WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL

13FROM [sys].[dm_exec_sessions]

14WHERE [session_id] = @@SPID);

15RETURN @IsolationLevel;

16END;

17GO

18

19CREATE FUNCTION GET_CT()

20RETURNS NVARCHAR(50)

21BEGIN

22DECLARE @CT NVARCHAR(50);

23SET @CT = CONVERT(NVARCHAR(12), GETDATE(), 114);

24RETURN @CT;

25END;

26GO

27

28PRINT dbo.GET_ISOLATION_LEVEL();

29PRINT dbo.GET_CT();

Обратите внимание на два важных момента:

•для обеспечения работоспособности уровня изолированности транзакции SNAPSHOT необходимо выполнить команду ALTER DATABASE

[имя_базы_данных] SET ALLOW_SNAPSHOT_ISOLATION ON;

•в представленном ниже коде мы будем дважды подтверждать каждую транзакцию, показывая текущий уровень вложенности транзакций (@@TRANCOUNT), что вызвано особенностью работы MS SQL Server в режиме IMPLICIT_TRANSACTIONS ON: в этом режиме начало транзакции переводит @@TRANCOUNT в значение 2, а не в 1.

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

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

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

02_ЛР
05. Частная патология ССС - Инфаркт миокарда
07. ПА_Лекция 7_Патология энд.жел
1. Охарактеризуйте роль Банка России как кредитора последней инстанции
1. Понятие регулярности полетов ла факторы, влияющие на регулярность вылета
1. Строение механизмов
107-112
11 занятие бх
1131
1230