Транзакции и блокировки объектов баз данных

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

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

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

• разделяемая (shared, S) - накладывается при выполнении операций чтения данных (например, SELECT). Никакая другая транзакция не сможет изменить или удалить данные, если на них установлена разделяемая блокировка. Разделяемая блокировка обычно освобождается после завершения чтения данных, но если только уровень изоляции транзакции установлен в REPEATABLE READ или выше, то разделяемая блокировка сохраняется до завершения транзакции.
• монопольная (exclusive, X) - накладывается при выполнении операций изменения данных (например, UPDATE). Никакая другая транзакция не сможет ни изменить, ни даже прочитать данные, если на них установления монопольная блокировка. Исключение: прочитать данные с установленной монопольной блокировкой возможно, если только уровень изоляции транзакции установлен в READ UNCOMMITTED. Монопольная блокировка освобождается после завершения транзакции.

Для просмотра установленных блокировок в Microsoft SQL Server используется системная хранимая процедура sp_lock.
Транзакция - последовательность операций, выполняемая как целостная логическая единица работы. Свойства транзакции:

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

Оператор BEGIN TRAN используется для начала транзакции. Для завершения транзакции используется либо оператор COMMIT TRAN (используется в том случае, если транзакция завершается успешно и все действия, выполнении в рамках транзакции должны быть сохранены в базе данных), либо оператор ROLLBACK TRAN (используется в том случае, если транзакция завершается с ошибками и все действия, выполнении в рамках транзакции должны быть отменены). Синтаксис транзакции выглядит так:

BEGIN TRAN
{операторы SQL}

{если ошибок не было}
COMMIT TRAN
{иначе, если возникли ошибки}
ROLLBACK TRAN

Управление транзакциями осуществляется на уровне соединения, а не пользователя. После начала транзакции в соединении и до ее успешного или неуспешного завершения все выполненные в этом соединении операторы SQL становятся частью транзакции.

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

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

Для управления взаимодействия транзакций между собой и, соответственно, управлением блокировками данных, возникающими при выполнении транзакций, используется установка уровня изоляции транзакции. Уровень изоляции транзакции определяет, какие блокировки накладываются на данные, обрабатываемые в рамках транзакций. Оператор SET TRANSACTION ISOLATION LEVEL используется для изменения уровня изоляции транзакции.

Синтаксис оператора SET TRANSACTION ISOLATION LEVEL выглядит так:

SET TRANSACTION ISOLATION LEVEL {уровень изоляции}

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

• READ UNCOMMITTED (неподтвержденное чтение) - транзакция с этим уровнем изоляции может читать записи, которые были изменены, но еще не зафиксированы другой транзакцией.
• READ COMMITTED (подтвержденное чтение) - транзакция с этим уровнем изоляции может читать только те записи, которые были изменены и уже зафиксированы другой транзакцией.
• REPEATABLE READ (повторяемое чтение) - транзакция с этим уровнем изоляции может читать только те записи, которые были изменены и уже зафиксированы другой транзакцией, и никакая другая транзакция не может изменить записи, которые были прочитаны в рамках этой транзакции.
• SERIALIZABLE (упорядочение) - транзакция с этим уровнем изоляции может читать только те записи, которые были изменены и уже зафиксированы другой транзакцией; никакая другая транзакция не может изменить записи, которые были прочитаны в рамках этой транзакции и никакая другая транзакция не может добавить или удалить записи из диапазона записей, прочитанного в рамках этой транзакции.

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