"Мертвые" блокировки

"Мертвые", или тупиковые, блокировки характерны для многопользовательских систем. "Мертвая" блокировка возникает, когда две транзакции блокируют два блока данных и для завершения любой из них нужен доступ к данным, заблокированным ранее другой транзакцией. Для завершения каждой транзакции необходимо дождаться, пока блокированная другой транзакцией часть данных будет разблокирована. Но это невозможно, так как вторая транзакция ожидает разблокирования ресурсов, используемых первой.

Без применения специальных механизмов обнаружения и снятия "мертвых" блокировок нормальная работа транзакций будет нарушена. Если в системе установлен бесконечный период ожидания завершения транзакции (а это задано по умолчанию), то при возникновении "мертвой" блокировки для двух транзакций вполне возможно, что, ожидая освобождения заблокированных ресурсов, в тупике окажутся и новые транзакции. Чтобы избежать подобных проблем, в среде MS SQL Server реализован специальный механизм разрешения конфликтов тупикового блокирования.

Для этих целей сервер снимает одну из блокировок, вызвавших конфликт, и откатывает инициализировавшую ее транзакцию. При выборе блокировки, которой необходимо пожертвовать, сервер исходит из соображений минимальной стоимости.

Полностью избежать возникновения "мертвых" блокировок нельзя. Хотя сервер и имеет эффективные механизмы снятия таких блокировок, все же при написании приложений следует учитывать вероятность их возникновения и предпринимать все возможные действия для предупреждения этого. "Мертвые" блокировки могут существенно снизить производительность, поскольку системе требуется достаточно много времени для их обнаружения, отката транзакции и повторного ее выполнения.

Для минимизации возможности образования "мертвых" блокировок при разработке кода транзакции следует придерживаться следующих правил:

• выполнять действия по обработке данных в постоянном порядке, чтобы не создавать условия для захвата одних и тех же данных;

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

• минимизировать длительность транзакции и выполнять ее по возможности в одном пакете;

• применять как можно более низкий уровень изоляции.

Уровни изоляции 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 Товар (читает измененные неподтвержденные данные)
7. INSERT Товар (Название, остаток) VALUES ('SS',999)	8. SELECT * FROM Товар (читает измененные неподтвержденные данные)
12. ROLLBACK TRANSACTION TRA	9. UPDATE Товар SET остаток=остаток+10 WHERE КодТовара=4 (блокируется до окончания конкурирующей транзакции )
10. DELETE FROM Товар WHERE КодТовара=4 (блокируется до окончания конкурирующей транзакции )	11. INSERT Товар(Название, остаток) VALUES ('SS',999) (выполняется)
13. ROLLBACK TRANSACTION TRB SELECT @@TRANCOUNT
Таблица 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 (блокируется до окончания конкурирующей транзакции )
9. INSERT Товар (Название, остаток) VALUES ('SS',999)		10. INSERT Товар(Название, остаток) VALUES ('SS',999) (выполняется)
11. ROLLBACK TRANSACTION TRA SELECT @@TRANCOUNT		12. ROLLBACK TRANSACTION TRB SELECT @@TRANCOUNT

Таблица 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 (выполняется, т.к. данные захвачены текущей транзакцией )
7. INSERT Товар (Название, остаток) VALUES ('SS',999)(выполняется)	8. DELETE FROM Товар WHERE КодТовара=4 (выполняется, т.к. данные захвачены текущей транзакцией )
10. ROLLBACK TRANSACTION TRA SELECT @@TRANCOUNT	9. INSERT Товар(Название, остаток) VALUES ('SS',999) (выполняется)
	11. ROLLBACK TRANSACTION TRB SELECT @@TRANCOUNT
Таблица 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 (выполняется, т.к. данные захвачены текущей транзакцией )
7. INSERT Товар (наименование, остаток) VALUES ('SS',999)(блокируется)		8. DELETE FROM Товар WHERE КодТовара=4 (выполняется, т.к. данные захвачены текущей транзакцией )
10. ROLLBACK TRANSACTION TRA SELECT @@TRANCOUNT		9. INSERT Товар(Название, остаток) VALUES ('SS',999) (выполняется)
		11. ROLLBACK TRANSACTION TRB SELECT @@TRANCOUNT

Лекция 17: Основные методы защиты данных. Управление пользователями Рассматривается система безопасности, принятая в языке SQL. Излагаются общие правила разграничения доступа. Описываются режимы аутентификации и компоненты структуры безопасности (пользователи, роли баз данных), администрирование системы безопасности (создание учетных записей и управление ими, управление пользователями и ролями). Дается определение прав пользователя на доступ к объектам базы данных. Рассматриваются неявные права, вопросы запрета доступа и неявного отклонения доступа, а также конфликты доступа.

Управление пользователями базы данных

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

Управление пользователями в среде MS SQL Server

Рассмотрим вопрос создания пользователей в среде MS SQL Server.

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

В системе SQL-сервер организована двухуровневая настройка ограничения доступа к данным. На первом уровне необходимо создать так называемую учетную запись пользователя (login), что позволяет ему подключиться к самому серверу, но не дает автоматического доступа к базам данных. На втором уровне для каждой базы данных SQL-сервера на основании учетной записи необходимо создать запись пользователя. На основе прав, выданных пользователю как пользователю базы данных (user), его регистрационное имя (login) получает доступ к соответствующей базе данных. В разных базах данных login одного и того же пользователя может иметь одинаковые или разные имена user с разными правами доступа. Иначе говоря, с помощью учетной записи пользователя осуществляется подключение к SQL-серверу, после чего определяются его уровни доступа для каждой базы данных в отдельности.

В системе SQL-сервер существуют дополнительные объекты – роли, которые определяют уровень доступа к объектам SQL-сервера. Они разделены на две группы: назначаемые для учетных записей пользователя сервера и используемые для ограничения доступа к объектам базы данных.

Итак, на уровне сервера система безопасности оперирует следующими понятиями:

• аутентификация ;

• учетная запись ;

• встроенные роли сервера.

На уровне базы данных применяются следующие понятия;

• пользователь базы данных;

• фиксированная роль базы данных;

• пользовательская роль базы данных.

Режимы аутентификации

SQL Server предлагает два режима аутентификации пользователей:

• режим аутентификации средствами Windows NT/2000;

• смешанный режим аутентификации (Windows NT Authentication and SQL Server Authentication).

Администрирование системы безопасности

Для создания пользователя в среде MS SQL Server следует предпринять следующие шаги:

1. Создать в базе данных учетную запись пользователя, указав для него пароль и принятое по умолчанию имя базы данных (процедура sp_addlogin ).

2. Добавить этого пользователя во все необходимые базы данных (процедура sp_adduser ).

3. Предоставить ему в каждой базе данных соответствующие привилегии (команда GRANT ) .

Создание новой учетной записи может быть произведено с помощью системной хранимой процедуры:

sp_addlogin

[@login=] 'учетная_запись'

[, [@password=] 'пароль']

[, [@defdb=] 'база_данных_по_умолчанию']

После завершения аутентификации и получения идентификатора учетной записи (login ID) пользователь считается зарегистрированным, и ему предоставляется доступ к серверу. Для каждой базы данных, к объектам которой он намерен получить доступ, учетная запись пользователя (login) ассоциируется с пользователем (user) конкретной базы данных, что осуществляется посредством процедуры:

sp_adduser

[@loginame=] 'учетная_запись'

[, [@name_in_db=] 'имя_пользователя']

[, [@grpname=] 'имя_роли']

Отобразить учетную запись Windows NT в имя пользователя позволяет хранимая процедура:

sp_grantdbaccess

[@login=] ‘учетная_запись’

[, [@name_in_db=]‘имя_пользователя’]

Пользователь, который создает объект в базе данных (таблицу, хранимую процедуру, просмотр), становится его владельцем. Владелец объекта (database object owner dbo) имеет все права доступа к созданному им объекту. Чтобы пользователь мог создать объект, владелец базы данных (dbo) должен предоставить ему соответствующие права. Полное имя создаваемого объекта включает в себя имя создавшего его пользователя .

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

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

sp_changeobjectowner

[@objname=] ‘имя_объекта’

[@newowner=] ‘имя_владельца’

Роль позволяет объединить в одну группу пользователей, выполняющих одинаковые функции.

В SQL Server реализовано два вида стандартных ролей: на уровне сервера и на уровне баз данных. При установке SQL Server создаются фиксированные роли сервера (например, sysadmin с правом выполнения любых функций SQL-сервера) и фиксированные роли базы данных (например, db_owner с правом полного доступа к базе данных илиdb_accessadmin с правом добавления и удаления пользователей ). Среди фиксированных ролей базы данных существует роль public, которая имеет специальное назначение, поскольку ее членами являются все пользователи, имеющие доступ к базе данных.

Можно включить любую учетную запись SQL Server (login) или учетную запись Windows NT в любую роль сервера.

Роли базы данных позволяют объединять пользователей в одну административную единицу и работать с ней как с обычным пользователем. Можно назначить права доступа к объектам базы данных для конкретной роли, при этом автоматически все члены этой роли наделяются одинаковыми правами.

В роль базы данных можно включить пользователей SQL Server, роли SQL Server, пользователей Windows NT.

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

• создание новой роли:

• sp_addrole

• [@rolename=] 'имя_роли'

[, [@ownername=] 'имя_владельца']

• добавление пользователя к роли:

• sp_addrolemember

• [@rolename=] 'имя_роли',

[@membername=] 'имя_пользователя'

• удаление пользователя из роли:

• sp_droprolemember

• [@rolename=] 'имя_роли',

[@membername=] 'имя_пользователя'

• удаление роли:

• sp_droprole

[@rolename=] 'имя_роли'

Управление доступом к данным

Определение привилегий в стандарте языка

Каждая СУБД должна поддерживать механизм, гарантирующий, что доступ к базе данных смогут получить только те пользователи, которые имеют соответствующее разрешение. Язык SQL включает операторы GRANT и REVOKE, предназначенные для организации защиты таблиц в базе данных. Механизм защиты построен на использованииидентификаторов пользователей, предоставляемых им прав владения и привилегий.

Идентификатором пользователя называется обычный идентификатор языка SQL, применяемый для обозначения некоторого пользователя базы данных. Каждомупользователю должен быть назначен собственный идентификатор, присваиваемый администратором базы данных. Из очевидных соображений безопасности идентификатор пользователя, как правило, связывается с некоторым паролем. Каждый выполняемый СУБД SQL-оператор выполняется от имени какого-либо пользователя. Идентификатор пользователя определяет, на какие объекты базы данных пользователь может ссылаться и какие операции с этими объектами он имеет право выполнять.

Каждый созданный в среде SQL объект имеет своего владельца, который изначально является единственной персоной, знающей о существовании данного объекта и имеетправо выполнять с ним любые операции.

Привилегиями, или правами, называются действия, которые пользователь имеет право выполнять в отношении данной таблицы базы данных или представления. В стандарте SQL определяется следующий набор привилегий: