С помощью инструкции SET TRANSACTION ISOLATION LEVEL можно установить следующие уровни изоляции транзакции.
READ UNCOMMITTED – разрешает инструкциям читать строки, обновленные любой транзакцией и еще не зафиксированные в базе данных Этот уровень изоляции минимизирует вероятность конфликтов, но допускает «грязные» считывания (dirty reads) и неповторимые считывания.
READ COMMITTED – разрешает инструкциям в текущих соединениях и транзакциях производить неповторимые считывания, но запрещает «грязные» считывания (данные, обновленные транзакцией, открытой в другом соединении). Это уровень изоляции, принятый по умолчанию в SQL Server.
REPEATABLE READ – не разрешает транзакциям считывать незафиксированные измененные данные («грязные» считывания) и гарантирует сохранение совмещаемых блокировок до завершения текущей транзакции.
SNAPSHOT – требует, чтобы параметр базы данных ALLOW_SNAPSHOT_ISOLATION был равен значению ON. Этот уровень изоляции формирует моментальный снимок данных в момент их считывания в транзакцию, но не поддерживает блокировку данных. Могут выполнятся обновления из другой транзакции, но текущая транзакция не видит этих изменений, отраженных в последующих считываниях исходных данных. Если текущая транзакция модифицирует данные, эти модификации видны только в текущей транзакции.
SERIALIZABLE – не разрешает считывать данные, которые были обновлены, но не зафиксированы другими транзакциями. Кроме того, никакая другая транзакция не может до завершения текущей транзакции обновлять данные, которые были считаны текущей транзакцией. Уровень изоляции SERIALIZABLE защищает от фантомных считываний, но создает наивысший уровень конкуренции и блокирования [1].
Синтаксическая запись команды определения уровня изоляции транзакции:
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABL
}
После того как в сеансе выполнена инструкция SET TRANSACTION ISOLATION LEVEL, все транзакции в пределах этого соединения используют заданный уровень изоляции [1].
Функции IDENT_CURRENT, SCOPE_IDENTITY, @@IDENTITY
Для того, что бы узнать последний добавленный идентификатор (IDENTITY), используются функции IDENT_CURRENT, SCOPE_IDENTITY и @@IDENTITY.
Функция IDENT_CURRENT практически аналогична функциям удостоверений SQL Server SCOPE_IDENTITY и @@IDENTITY. Все три функции возвращают созданные последними значения идентификаторов. Однако эти функции различаются областью действия и сеансом.
IDENT_CURRENT возвращает последнее значение идентифицирующего столбца, созданное для конкретной таблицы в любом сеансе и области поиска.
@@IDENTITY возвращает последнее значение идентификатора, созданное для любой таблицы в текущем сеансе по всем областям поиска.
SCOPE_IDENTITY возвращает последнее значение идентификатора, созданное для любой таблицы в текущем сеансе по текущей области поиска.
Функция IDENT_CURRENT возвращает значение NULL, если вызвана для пустой таблицы или для таблицы без столбца идентификаторов.
Инструкции и транзакции, приведшие к ошибке, могут изменять текущее удостоверение для таблицы и вызывать появление пропусков в значениях столбца идентификаторов. Откат удостоверения не производится никогда, несмотря на то, что транзакция, пытавшаяся вставить в таблицу значение, не была зафиксирована. Например, если инструкция INSERT привела к ошибке из-за нарушения ограничения IGNORE_DUP_KEY, текущее удостоверение для таблицы все равно увеличивается.
Будьте внимательны при использовании функции IDENT_CURRENT для прогнозирования следующего создаваемого значения идентификатора. В действительности создаваемое значение может отличаться от полученного с помощью функции IDENT_CURRENT плюс IDENTITY_SEED, потому что в других сеансах могут выполняться операции вставки [3].
Содержание работы
1. Добавьте в таблицу товар (Product) поле количество (Quantity). Заполните поле произвольными значениями от 0 до 100.
2. Создайте хранимую процедуру AddBill, которая будет формировать чек, а затем возвращать его идентификатор. Входные параметры процедуры: BuyerID и EmployeeID. Все инструкции данной хранимой процедуры должны выполняться в транзакции.
3. Создайте хранимую процедуру добавления товара в чек. Назовите ее AddBillItem. Входные параметры процедуры: BillID, ProductID, Quantity. Установить уровень изоляции транзакции в режим SERIALIZABLE. После открытия транзакции (BEGIN TRANSACTION) необходимо проверить количество товара в магазине. В случае если товара в магазине недостаточно, то процедура должна завершить выполнение транзакции (ROLLBACK TRANSACTION). В противном случае хранимая процедура уменьшает доступное количество товара в магазине и добавляет запись о покупке в чек (добавляются записи в таблицу BillItem). Закрываем транзакцию (COMMIT TRANSACTION). Объясните выбор уровня изоляции транзакции.
4. Сделайте выборку содержимого чека с использованием запроса №1 из лабораторной работы №2.
5. Проверьте работоспособность написанных хранимых процедур. Попытайтесь создать чек и добавить в него 2 товара с достаточным в магазине количеством и 1 товар с недостаточным количеством. Убедитесь, что в чеке присутствуют только 2 проданных товара.
6. Подготовьте материал для включения в отчетную презентацию по курсу Базы данных: специальный курс.