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

Скачать

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

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

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

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

•READ UNCOMMITTED;

•READ COMMITTED;

•REPEATABLE READ;

•SERIALIZABLE.

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

start cmd.exe /c "mysql -uПОЛЬЗЬВАТЕЛЬ -pПАРОЛЬ БАЗА_ДАННЫХ < a.sql & pause" start cmd.exe /c "mysql -uПОЛЬЗЬВАТЕЛЬ -pПАРОЛЬ БАЗА_ДАННЫХ < b.sql & pause"

Грязное чтение в 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 SELECT 1: ',

 

 

16

 

 

 

 

 

CURTIME());

 

 

17

 

SELECT

SLEEP(5);

 

SELECT

`sb_is_active`

 

 

18

 

 

 

 

FROM

`subscriptions`

 

 

19

 

 

 

 

WHERE

`sb_id` = 2;

 

 

10

 

SELECT

CONCAT('Tr A UPDATE: ',

 

 

 

 

 

11

 

 

CURTIME());

 

 

 

 

 

12

 

UPDATE

`subscriptions`

 

 

 

 

 

13

 

SET

`sb_is_active` =

 

SELECT

SLEEP(10);

 

14

 

CASE

 

 

 

 

 

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

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

17END

18WHERE `sb_id` = 2;

19

 

SELECT

CONCAT('Tr B SELECT 2: ',

20

 

 

CURTIME());

21

SELECT SLEEP(20);

SELECT

`sb_is_active`

22

 

FROM

`subscriptions`

23

 

WHERE

`sb_id` = 2;

24

 

SELECT

CONCAT('Tr B COMMIT: ',

25

 

 

CURTIME());

26

 

COMMIT;

 

27

SELECT CONCAT('Tr A ROLLBACK: ',

 

 

28

CURTIME());

 

 

29

ROLLBACK;

 

 

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

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

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

A: READ UNCOMMITTED

B: READ UNCOMMITTED

Tr A ID = 13

Tr B ID = 14

Tr A START: 17:21:01 in READ-UNCOMMITTED

Tr B START: 17:21:01 in READ-UNCOMMITTED

Tr A UPDATE: 17:21:06

Tr B SELECT 1: 17:21:01

Tr A ROLLBACK: 17:21:26

sb_is_active = Y

 

Tr B SELECT 2: 17:21:11

 

sb_is_active = N

 

Tr B COMMIT: 17:21:11

A: READ UNCOMMITTED

B: READ COMMITTED

Tr A ID = 15

Tr B ID = 16

Tr A START: 17:38:06 in READ-UNCOMMITTED

Tr B START: 17:38:06 in READ-COMMITTED

Tr A UPDATE: 17:38:12

Tr B SELECT 1: 17:38:06

Tr A ROLLBACK: 17:38:32

sb_is_active = Y

 

Tr B SELECT 2: 17:38:16

 

sb_is_active = Y

 

Tr B COMMIT: 17:38:16

A: READ UNCOMMITTED

B: REPEATABLE READ

Tr A ID = 18

Tr B ID = 17

Tr A START: 17:42:37 in READ-UNCOMMITTED

Tr B START: 17:42:37 in REPEATABLE-READ

Tr A UPDATE: 17:42:42

Tr B SELECT 1: 17:42:37

Tr A ROLLBACK: 17:43:02

sb_is_active = Y

 

Tr B SELECT 2: 17:42:47

 

sb_is_active = Y

 

Tr B COMMIT: 17:42:47

A: READ UNCOMMITTED

B: SERIALIZABLE

Tr A ID = 20

Tr B ID = 19

Tr A START: 17:48:19 in READ-UNCOMMITTED

Tr B START: 17:48:19 in SERIALIZABLE

Tr A UPDATE: 17:48:24

Tr B SELECT 1: 17:48:19

Tr A ROLLBACK: 17:48:49

sb_is_active = Y

 

Tr B SELECT 2: 17:48:29

 

sb_is_active = Y

 

Tr B COMMIT: 17:48:29

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

 

 

 

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

 

 

READ

READ

REPEATABLE

SERIALIZABLE

 

 

UNCOMMITTED

COMMITTED

READ

 

 

 

 

 

Транзакция B

Транзакция B

Транзакция B

Транзакция B оба

 

 

раза читает исходное

 

 

успевает прочи-

оба раза читает

оба раза читает

 

READ

(корректное) значе-

 

тать незафикси-

исходное (кор-

исходное (кор-

 

UNCOMMITTED

ние, UPDATE в тран-

 

рованное значе-

ректное) значе-

ректное) значе-

 

 

закции A ждёт завер-

A

 

ние

ние

ние

 

шения транзакции B

транзакции

 

 

 

 

 

Транзакция B

Транзакция B

Транзакция B

Транзакция B оба

 

 

 

 

раза читает исходное

 

 

успевает прочи-

оба раза читает

оба раза читает

 

READ

(корректное) значе-

 

тать незафикси-

исходное (кор-

исходное (кор-

 

COMMITTED

ние, UPDATE в тран-

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

рованное значе-

ректное) значе-

ректное) значе-

READ

ние, UPDATE в тран-

 

 

 

 

 

ние

ние

ние

закции A ждёт завер-

 

 

шения транзакции B

 

 

 

 

 

 

 

Транзакция B

Транзакция B

Транзакция B

Транзакция B оба

 

 

раза читает исходное

 

 

успевает прочи-

оба раза читает

оба раза читает

 

REPEATABLE

(корректное) значе-

 

тать незафикси-

исходное (кор-

исходное (кор-

 

 

 

Уровень

 

рованное значе-

ректное) значе-

ректное) значе-

Транзакция B оба

 

 

ние

ние

ние

закции A ждёт завер-

 

 

шения транзакции B

 

 

 

 

 

 

 

Транзакция B

Транзакция B

Транзакция B

раза читает исходное

 

 

успевает прочи-

оба раза читает

оба раза читает

 

 

(корректное) значе-

 

SERIALIZABLE

тать незафикси-

исходное (кор-

исходное (кор-

 

ние, UPDATE в тран-

 

 

рованное значе-

ректное) значе-

ректное) значе-

 

 

закции A ждёт завер-

 

 

ние

ние

ние

 

 

шения транзакции B

 

 

 

 

 

Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 436/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 A, SELECT: ',

 

 

16

 

CURTIME());

 

 

17

SELECT

`sb_is_active`

SELECT

SLEEP(5);

18

FROM

`subscriptions`

 

 

19

WHERE

`sb_id` = 2;

 

 

20

 

 

SELECT

CONCAT('Tr B, SELECT: ',

21

 

 

 

CURTIME());

22

SELECT

SLEEP(10);

SELECT

`sb_is_active`

23

 

 

FROM

`subscriptions`

24

 

 

WHERE

`sb_id` = 2;

25

SELECT

CONCAT('Tr A UPDATE: ',

 

 

26

 

CURTIME());

 

 

27

UPDATE

`subscriptions`

 

 

28

SET

`sb_is_active` = 'Y'

SELECT

SLEEP(10);

29

WHERE

`sb_id` = 2;

 

 

30

SELECT

CONCAT('Tr A COMMIT: ',

 

 

31

 

CURTIME());

 

 

32

COMMIT;

 

 

 

33

 

 

SELECT

CONCAT('Tr B UPDATE: ',

34

 

 

 

CURTIME());

35

 

 

UPDATE

`subscriptions`

36

SELECT

SLEEP(10);

SET

`sb_is_active` = 'N'

37

 

 

WHERE

`sb_id` = 2;

38

 

 

SELECT

CONCAT('Tr B COMMIT: ',

39

 

 

 

CURTIME());

40

 

 

COMMIT;

 

41

SELECT

CONCAT('After A, SELECT: ',

SELECT

CONCAT('After B, SELECT: ',

42

 

CURTIME());

 

CURTIME());

43

SELECT

`sb_is_active`

SELECT

`sb_is_active`

44

FROM

`subscriptions`

FROM

`subscriptions`

45

WHERE

`sb_id` = 2;

WHERE

`sb_id` = 2;

 

 

 

 

 

Приведём пример журнала выполнения этого кода для ситуации, когда транзакция A выполняется на уровне изолированности READ COMMITTED и конкурирует

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

A: READ COMMITTED

B: READ UNCOMMITTED

Tr A ID = 77

Tr B ID = 76

Tr A START: 19:12:19 in READ-COMMITTED

Tr B START: 19:12:19 in READ-UNCOMMITTED

Tr A, SELECT: 19:12:19

Tr B, SELECT: 19:12:24

sb_is_active = N

sb_is_active = N

Tr A UPDATE: 19:12:29

Tr B UPDATE: 19:12:34

Tr A COMMIT: 19:12:29

Tr B COMMIT: 19:12:34

After A, SELECT: 19:12:39

After B, SELECT: 19:12:34

sb_is_active = N

sb_is_active = N

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

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

A: READ COMMITTED

B: READ COMMITTED

Tr A ID = 80

Tr B ID = 79

Tr A START: 19:14:43 in READ-COMMITTED

Tr B START: 19:14:43 in READ-COMMITTED

Tr A, SELECT: 19:14:43

Tr B, SELECT: 19:14:48

sb_is_active = N

sb_is_active = N

Tr A UPDATE: 19:14:53

Tr B UPDATE: 19:14:58

Tr A COMMIT: 19:14:53

Tr B COMMIT: 19:14:58

After A, SELECT: 19:15:03

After B, SELECT: 19:14:58

sb_is_active = N

sb_is_active = N

A: READ COMMITTED

B: REPEATABLE READ

Tr A ID = 83

Tr B ID = 82

Tr A START: 19:17:00 in READ-COMMITTED

Tr B START: 19:17:00 in REPEATABLE-READ

Tr A, SELECT: 19:17:00

Tr B, SELECT: 19:17:05

sb_is_active = N

sb_is_active = N

Tr A UPDATE: 19:17:10

Tr B UPDATE: 19:17:15

Tr A COMMIT: 19:17:10

Tr B COMMIT: 19:17:15

After A, SELECT: 19:17:20

After B, SELECT: 19:17:15

sb_is_active = N

sb_is_active = N

A: READ COMMITTED

B: SERIALIZABLE

Tr A ID = 86

Tr B ID = 85

Tr A START: 19:19:07 in READ-COMMITTED

Tr B START: 19:19:06 in SERIALIZABLE

Tr A, SELECT: 19:19:07

Tr B, SELECT: 19:19:11

sb_is_active = N

sb_is_active = N

Tr A UPDATE: 19:19:17

Tr B UPDATE: 19:19:21

Tr A COMMIT: 19:19:17

Tr B COMMIT: 19:19:21

After A, SELECT: 19:19:27

After B, SELECT: 19:19:21

sb_is_active = N

sb_is_active = N

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

 

 

 

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

 

 

READ

READ

REPEATABLE

SERIALIZABLE

 

 

UNCOMMITTED

COMMITTED

READ

 

 

 

A

 

Обновление

Обновление

Обновление

 

транзакции

READ

Обновление транзак-

транзакции A

транзакции A

транзакции A

UNCOMMITTED

ции A утеряно

 

 

утеряно

утеряно

утеряно

 

 

 

 

 

 

 

 

 

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

READ

Обновление

Обновление

Обновление

Обновление транзак-

утеряно

утеряно

утеряно

 

 

 

COMMITTED

транзакции A

транзакции A

транзакции A

ции A утеряно

 

утеряно

утеряно

утеряно

 

 

 

 

 

 

 

 

 

 

REPEATABLE

Обновление

Обновление

Обновление

Обновление транзак-

 

транзакции A

транзакции A

транзакции A

 

READ

ции A утеряно

 

 

 

 

Уровень

 

 

 

 

 

 

Обновление

Обновление

Обновление

Возможна взаимная

 

 

 

SERIALIZABLE

транзакции A

транзакции A

транзакции A

блокировка с отменой

 

 

утеряно

утеряно

утеряно

транзакции B

Если в данном эксперименте убрать чтение информации перед её обновлением (строки 15-19 для транзакции A, и строки 20-24 для транзакции B), то при любой комбинации уровней изолированности результат будет одним и тем же: изменения, выполненные транзакцией A, будут утеряны.

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

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

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

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

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

IN SHARE MODE для первой операции чтения. Важно отметить, что в некоторых

случаях взаимная блокировка нарушает работу обеих транзакций, но в большинстве случаев СУБД отменяет транзакцию B, позволяя транзакции A успешно выполниться.

 

 

 

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

 

 

 

READ

READ

REPEATABLE

SERIALIZABLE

 

 

UNCOMMITTED

COMMITTED

READ

 

 

 

A

 

 

 

 

 

транзакции

READ

Взаимная блоки-

Взаимная блоки-

Взаимная блоки-

Взаимная блоки-

UNCOMMITTED

ровка транзакций

ровка транзакций

ровка транзакций

ровка транзакций

 

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

 

 

 

 

 

READ

Взаимная блоки-

Взаимная блоки-

Взаимная блоки-

Взаимная блоки-

COMMITTED

ровка транзакций

ровка транзакций

ровка транзакций

ровка транзакций

 

 

 

 

 

 

 

 

REPEATABLE

Взаимная блоки-

Взаимная блоки-

Взаимная блоки-

Взаимная блоки-

 

READ

ровка транзакций

ровка транзакций

ровка транзакций

ровка транзакций

Уровень

 

 

 

 

 

 

ровка транзакций

ровка транзакций

ровка транзакций

ровка транзакций

 

SERIALIZABLE

Взаимная блоки-

Взаимная блоки-

Взаимная блоки-

Взаимная блоки-

 

 

 

 

 

 

 

 

 

 

 

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

 

 

 

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

 

 

 

READ

READ

REPEATABLE

SERIALIZABLE

 

 

UNCOMMITTED

COMMITTED

READ

 

 

 

A

 

Обновление тран-

Обновление тран-

Обновление тран-

Обновление тран-

READ

закции A утеряно,

закции A утеряно,

закции A утеряно,

закции A утеряно,

транзакции

UNCOMMITTED

транзакция B ждёт

транзакция B ждёт

транзакция B ждёт

транзакция B ждёт

 

 

 

завершения A

завершения A

завершения A

завершения A

 

 

Обновление тран-

Обновление тран-

Обновление тран-

Обновление тран-

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

READ

закции A утеряно,

закции A утеряно,

закции A утеряно,

закции A утеряно,

 

завершения A

завершения A

завершения A

завершения A

 

COMMITTED

транзакция B ждёт

транзакция B ждёт

транзакция B ждёт

транзакция B ждёт

 

 

завершения A

завершения A

завершения A

завершения A

 

 

Обновление тран-

Обновление тран-

Обновление тран-

Обновление тран-

 

REPEATABLE

закции A утеряно,

закции A утеряно,

закции A утеряно,

закции A утеряно,

 

READ

транзакция B ждёт

транзакция B ждёт

транзакция B ждёт

транзакция B ждёт

Уровень

 

 

 

 

 

 

транзакция B ждёт

транзакция B ждёт

транзакция B ждёт

транзакция B ждёт

 

 

Обновление тран-

Обновление тран-

Обновление тран-

Обновление тран-

 

SERIALIZABLE

закции A утеряно,

закции A утеряно,

закции A утеряно,

закции A утеряно,

 

 

 

 

 

 

 

завершения A

завершения A

завершения A

завершения A

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

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

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

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

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