"Мертвые", или тупиковые, блокировки характерны для многопользовательских систем. "Мертвая" блокировка возникает, когда две транзакции блокируют два блока данных и для завершения любой из них нужен доступ к данным, заблокированным ранее другой транзакцией. Для завершения каждой транзакции необходимо дождаться, пока блокированная другой транзакцией часть данных будет разблокирована. Но это невозможно, так как вторая транзакция ожидает разблокирования ресурсов, используемых первой.
Без применения специальных механизмов обнаружения и снятия "мертвых" блокировок нормальная работа транзакций будет нарушена. Если в системе установлен бесконечный период ожидания завершения транзакции (а это задано по умолчанию), то при возникновении "мертвой" блокировки для двух транзакций вполне возможно, что, ожидая освобождения заблокированных ресурсов, в тупике окажутся и новые транзакции. Чтобы избежать подобных проблем, в среде MS SQL Server реализован специальный механизм разрешения конфликтов тупикового блокирования.
Для этих целей сервер снимает одну из блокировок, вызвавших конфликт, и откатывает инициализировавшую ее транзакцию. При выборе блокировки, которой необходимо пожертвовать, сервер исходит из соображений минимальной стоимости.
Полностью избежать возникновения "мертвых" блокировок нельзя. Хотя сервер и имеет эффективные механизмы снятия таких блокировок, все же при написании приложений следует учитывать вероятность их возникновения и предпринимать все возможные действия для предупреждения этого. "Мертвые" блокировки могут существенно снизить производительность, поскольку системе требуется достаточно много времени для их обнаружения, отката транзакции и повторного ее выполнения.
Для минимизации возможности образования "мертвых" блокировок при разработке кода транзакции следует придерживаться следующих правил:
выполнять действия по обработке данных в постоянном порядке, чтобы не создавать условия для захвата одних и тех же данных;
избегать взаимодействия с пользователем в теле транзакции ;
минимизировать длительность транзакции и выполнять ее по возможности в одном пакете;
применять как можно более низкий уровень изоляции.
Уровень изоляции определяет степень независимости транзакций друг от друга. Наивысшим уровнем изоляции является сериализуемость, обеспечивающая полную независимость транзакций друг от друга. Каждый последующий уровень соответствует требованиям всех предыдущих и обеспечивает дополнительную защиту транзакций .
SQL Server поддерживает все четыре уровня изоляции, определенные стандартом ANSI. Уровень изоляции устанавливается командой:
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE }
READ UNCOMMITED – незавершенное чтение, или допустимо черновое чтение. Низший уровень изоляции, соответствующий уровню 0. Он гарантирует только физическую целостность данных: если несколько пользователей одновременно изменяют одну и ту же строку, то в окончательном варианте строка будет иметь значение, определенное пользователем, последним изменившим запись. По сути, для транзакции не устанавливается никакой блокировки, которая гарантировала бы целостность данных. Для установки этого уровня используется команда:
· SET TRANSACTION ISOLATION
LEVEL READ UNCOMMITTED
READ COMMITTED – завершенное чтение, при котором отсутствует черновое, "грязное" чтение. Тем не менее в процессе работы одной транзакции другая может быть успешно завершена и сделанные ею изменения зафиксированы. В итоге первая транзакция будет работать с другим набором данных. Это проблема неповторяемого чтения. Данный уровень изоляции установлен в SQL Server по умолчанию и устанавливается посредством команды:
· SET TRANSACTION ISOLATION
LEVEL READ COMMITTED
REPEATABLE READ – повторяющееся чтение. Повторное чтение строки возвратит первоначально считанные данные, несмотря на любые обновления, произведенные другими пользователями до завершения транзакции. Тем не менее на этом уровне изоляции возможно возникновение фантомов . Его установка реализуется командой:
· SET TRANSACTION ISOLATION
LEVEL REPEATABLE READ
SERIALIZABLE – сериализуемость. Чтение запрещено до завершения транзакции. Это максимальный уровень изоляции, который обеспечивает полную изоляциютранзакций друг от друга. Он устанавливается командой:
· SET TRANSACTION ISOLATION
LEVEL SERIALIZABLE
В каждый момент времени возможен только один уровень изоляции.
Таблица 16.1. Уровень изоляции конкурирующей транзакции принят по умолчанию (READ COMMITTED). В примере шаги 4, 6 и 8 демонстрируют черновое чтение. Шаги 9 и 10 блокируются, потому что данные захвачены конкурирующей транзакцией.
Пользователь user1 Конкурирующая транзакция
Пользователь user2 Текущая транзакция
USE basa_user2
BEGIN TRANSACTION TRA
USE basa_user2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION TRB
1. SELECT * FROM Товар
2. SELECT * FROM Товар
3. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4
4. SELECT * FROM Товар (читает измененные неподтвержденные данные)
5. DELETE FROM Товар WHERE КодТовара=4
6. SELECT * FROM Товар (читает измененные неподтвержденные данные)
Таблица 16.2. Уровень изоляции конкурирующей транзакции принят по умолчанию (READ COMMITTED). В примере шаги 4, 6 и 8 демонстрируют блокировку данных, захваченных другой транзакцией, в то время как работа с другими данными разрешается (шаг 10).
Пользователь user1 Конкурирующая транзакция
Пользователь user2 Текущая транзакция
USE basa_user2
BEGIN TRANSACTION TRA
USE basa_user2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION TRB
1. SELECT * FROM Товар
2. SELECT * FROM Товар
3. UPDATE Товар SET остаток=остаток+10 (захватывает данные)
4. SELECT * FROM Товар WHERE КодТовара=4 (блокируется до окончания конкурирующей транзакции )
5. DELETE FROM Товар WHERE КодТовара=4
6. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4(блокируется до окончания конкурирующей транзакции )
7. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4 (выполняется той транзакцией, которая первой захватила данные на изменение или удаление)
8. DELETE FROM Товар WHERE КодТовара=4 (блокируется до окончания конкурирующей транзакции )
Таблица 16.3. Уровень изоляции конкурирующей транзакции принят по умолчанию (READ COMMITTED). На шаге 2 транзакция захватила данные чтением и блокирует работу с ними со стороны конкурирующей транзакции (шаги 3, 5), которая может лишь добавлять записи (шаг 7).
Пользователь user1 Конкурирующая транзакция
Пользователь user2 Текущая транзакция
USE basa_user2
BEGIN TRANSACTION TRA
USE basa_user2
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION TRB
1. SELECT * FROM Товар
2. SELECT * FROM Товар (захватывает данные)
3. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4(блокируется)
4. SELECT * FROM Товар (блокируется до окончания конкурирующей транзакции )
5. DELETE FROM Товар WHERE КодТовара=4 (блокируется)
6. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4 (выполняется, т.к. данные захвачены текущей транзакцией )
Таблица 16.4. Уровень изоляции конкурирующей транзакции принят по умолчанию (READ COMMITTED). Пример демонстрирует, что текущая транзакция захватила данные чтением (шаг 2) и блокирует любые действия с ними со стороны конкурирующей транзакции вплоть до вставки данных (шаг 7).
Пользователь user1 Конкурирующая транзакция
Пользователь user2 Текущая транзакция
USE basa_user2
BEGIN TRANSACTION TRA
USE basa_user2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION TRB
1. SELECT * FROM Товар
2. SELECT * FROM Товар (захватывает данные)
3. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4(блокируется)
4. SELECT * FROM Товар (выполняется)
5. DELETE FROM Товар WHERE КодТовара=4 (блокируется)
6. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4 (выполняется, т.к. данные захвачены текущей транзакцией )
Лекция 17: Основные методы защиты данных. Управление пользователями Рассматривается система безопасности, принятая в языке SQL. Излагаются общие правила разграничения доступа. Описываются режимы аутентификации и компоненты структуры безопасности (пользователи, роли баз данных), администрирование системы безопасности (создание учетных записей и управление ими, управление пользователями и ролями). Дается определение прав пользователя на доступ к объектам базы данных. Рассматриваются неявные права, вопросы запрета доступа и неявного отклонения доступа, а также конфликты доступа.
