Теоретические сведения для выполнения работы

Лабораторная работа № 7 (4 часа)

Блокировки и уровни изоляции транзакций

Цель работы: изучить действие уровней изоляции транзакций и управление блокировками в транзакциях в SQL Server 2008.

Теоретические сведения для выполнения работы

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

SQL Server поддерживает следующие пять уровней изоляции:

• READ UNCOMMITTED;
• READ COMMITTED;
• REPEATABLE READ;
• SERIALIZABLE;
• SNAPSHOT.

По умолчанию, устанавливается изоляция READ COMMITTED, и эта установка действует в рамках сеанса.

READ UNCOMMITED

Когда установлен уровень READ UNCOMMITTED, транзакция изменяющая данные не блокирует транзакции, читающие эти данные, а читающие не блокируют изменяющие. Таким образом, имеется возможность составить запрос таким образом, что получите грязные данные, которые ещё не сохранены в базе данных, и этим может быть нарушен принцип согласованности данных. Однако транзакция изменяющая данные заблокирует транзакции, также изменяющие эти данные (рис.1)

T1 T2

Рис. 1 Выполнение транзакций при уровне изоляции READ UNCOMMITTED

READ COMMITED

Когда установлен READ COMMITTED, прочитать можно только сохранённые данные. Но как только читающая транзакция завершит процесс чтения данных, даже если сама транзакция к этому моменту ещё не завершена, её блокировка уже не будет препятствовать изменениям в этих данных (рис.2). Основной принцип состоит в том, что пишущая транзакция всегда блокирует читающие транзакции, если они имеют уровни изоляции выше READ UNCOMMITTED.

T1 T2

Рис. 2 Выполнение транзакций при уровне изоляции READ COMMITTED

REPEATABLE READ

При использовании REPEATABLE READ в одной транзакции чтение одни и те же данных не будет давать разные результаты.. Поэтому, даже в моменты, когда чтение данных не выполняется, другие транзакции не смогут изменять данные. Однако они смогут осуществлять вставки новых данных в таблицу или в диапазоны данных, которые в этот момент не блокированы (рис.3). При этом уровне изоляции на все данные, которые читаются, устанавливается коллективная блокировка и она сохраняет эту блокировку до конца транзакции, Поэтому выполнение запроса несколько раз внутри транзакции всегда будет возвращать один и тот же результат.

T1 T2

Рис. 3 Выполнение транзакций при уровне изоляции REPEATABLE READ

SERIALIZABLE

Уровень SERIALIZABLE дополнительно по отношению к предыдущему уровню защищает все другие блоки данных от вставок. Это называется предотвращением фантомных чтений (рис.4). Этот уровень изоляции реализован с использованием метода блокировки диапазона ключей. Он блокирует индивидуальные строки путём установки блокировки на записи индексов в диапазоне ключей, а не на отдельные страницы или на всю таблицу. В этом случае никакая операция модификации данных в другой транзакции не может быть выполнена, потому что невозможны необходимые изменения индексных записей.

T1 T2

Рис. 4 Выполнение транзакций при уровне изоляции SERIALIZABLE

Если в запросе необходимо установить блокировку или её продолжительность отличную от устанавливаемой по действующему уровню изоляции, то это можно сделать, указав в команде соответствующие специальные ключевые слова (хинты).

Форматы команд с хинтами

SELECT … FROM table_name WITH (hint) WHERE …

INSERT table_name (list_col) WITH (hint) VALUES …

UPDATE table_name (list_col) WITH (hint) SET …

DELETE table_name (list_col) WITH (hint) WHERE …

Ключевые слова для явного указания типа блокировки (хинты)

NOLOCK (READUNCOMMITTED) – разрешает чтение незафиксированных данных, которые были изменены другими транзакциям.

HOLDLOCK (SERIALIZABLE) – устанавливает совмещаемую блокировку до завершения транзакции

UPDLOCK– определяет применение блокировки обновления до завершения транзакции

XLOCK – определяет применение монопольной блокировки на соответствующем уровне до завершения транзакции. Однако, это не совсем так. При использование XLOCKв SELECT SQL Server игнорирует эту блокировку, если читаемые строки не изменились (а SELECT–ом они не изменяются), т.е. нет «грязного чтения», и у читающей транзакции установлен уровень изоляции READ COMMITTED.

PAGLOCK – устанавливает блокировку страницы вместо таблицы

ROWLOCK – устанавливает блокировку на уровне строки

TABLOCK – устанавливает соответствующую блокировку на уровне таблицы

TABLOCKХ – устанавливает монопольную блокировку на уровне таблицы до завершена транзакция

READCOMMITTED – определяет правила для чтения, как для уровня изоляции READ COMMITTED (либо блокировка строк либо управление версиями, в зависимости, что установлено)

READCOMMITTEDLOCK – определяет правила для чтения, как для уровня изоляции READ COMMITTED с использованием блокировки

REPEATABLEREAD – определяет выполнение просмотра с семантикой блокировки, как для уровня изоляции REPEATABLE READ

Выполнения работы.

Задание.

Загрузить среду управления SSMS.

Подключите или используйте базу данных «Заказы» из 2-й лабораторной работы.

Задача 1. Протестируйте конкурентное выполнение процедуры ДобавитьЗаказКолТовар, текст которой приводился в лекции.

Задание 1.

Создайте в редакторе запросов сценарий выполнения процедуры ДобавитьЗаказКолТовар, исключив из нее переменные путём их замены на какие-либо действительные константы. Для этого, выберете код заказа и какой-либо код товар со склада, входящий в этот заказ и используйте их в запросах. Определите остатки по выбранному товару на складе. Задайте значение константы для количества добавляемого товара в заказ, таким образом, чтобы оно был меньше значения остатка на складе не более, чем в 2 раза. Запишите текущее состояние данных для выбранных товара и заказов.

Задание 2.

Подготовьте параллельное выполнение созданного сценария. Для этого откройте второе окно в редакторе запросов и скопируйте туда подготовленный сценарий (выполнение сценариев из разных окон будет соответствовать двум разным соединениям). Скорректируйте в нем номер заказа и количество добавляемого товара таким образом, чтобы в сумме с первой транзакцией оно было больше, чем в текущих остатках на складе (см. рис. 1)

Рис. 1. Вид редактора запросов SSMS для параллельного выполнения сценария процедурыДобавитьЗаказКолТовар

Задание 3.

Выполните команды поочередно из одного и другого окна, имитирую параллельное выполнение сценариев разными клиентами(выполнение по одной команде делается путём её предварительного выделения и нажатия кнопки ). Ход процесса определяйте по полученным данным.

Задание 4.

Просмотрите данные в соответствующих таблицах, к чему это привело. Осмыслите ситуацию и обсудите её с преподавателем, локализуйте причину проблемы. (Если у вас не появилось проблем, то это значит, что вы не параллельно выполнили сценарии).

Задача 2. Скорректируйте сценарий процедуры ДобавитьЗаказКолТовар, так, чтобы исправить имеющуюся проблему конкурентного её выполнения.

Задание 1.

Верните данные о товаре и заказах в соответствующих таблицах в исходное состояние.

Задание 2.

Внесите в код сценариев последовательно следующие изменения:

а) перенесите начало транзакции до чтения данных;

б) повысьте уровень изоляции транзакции;

в) измените устанавливаемый тип блокировки в операции чтения.

Протестируйте параллельное выполнение для каждого из указанных изменений сценария. Предъявите результаты преподавателю и дайте их объяснения.

Задание 3.

Скорректируйте процедуры ДобавитьЗаказКолТовар с учётом выполнения предыдущего задания.

Задача 3. Протестируйте конкурентное выполнение процедуры НовыйКлиент, которая была создана на лабораторной работе №6.

Задание 1.

Создайте в редакторе запросов сценарии пошагового выполнения процедуры в разных транзакциях для следующих данных, передаваемых в параметрах процедуры:

ТРАЗАКЦИЯ 1

'111100000', 'ОАО "ИНВЕСТ"','Петров К.И.', 'г.Минск, ул. Гая, 36', '245-78-23', null, 'Иванов И.С.','245-78-25'

ТРАЗАКЦИЯ 2

'111100000', 'ОАО "ИНВЕСТ"','Петров К.И.', 'г.Минск, ул. Гая, 36', '245-78-23', null, 'Сидоров С.И.','211-45-78'

Задание 2.

Выполните параллельно сценарии процедуры в разных транзакциях. Сформулируйте суть проблемы конкурентного выполнения.

Задание 3.

Определите пути решения проблемы:

а) требуется изменить только определение транзакции?

б) требуется ли изменить определение транзакции и использовать другой уровень изоляции транзакции?