Транзакции и блокировка транзакций

Создание вложенных транзакций

В SQL Server разрешаются вложенные транзакции, т.е. транзакции внутри транзакции. В случае вложенных транзакций вы должны явно фиксировать каждую внутреннюю транзакцию, чтобы SQL Server получал информацию об окончании внутренней транзакции и мог освободить ресурсы, используемые этой транзакцией, когда будет фиксирована внешняя транзакция. Если ресурсы блокированы, другие пользователи не могут получать доступа к этим ресурсам. Хотя вы должны явным образом включать оператор фиксации COMMIT для каждой транзакции, SQL Server не выполняет фактического фиксирования внутренних транзакций, пока не произойдет успешное фиксирование внешней транзакции; одновременно с этим SQL Server освобождает все ресурсы, используемые внутренними и внешней транзакциями. При неуспешном фиксировании внешней транзакции фиксирование внутренних транзакций не выполняется и происходит откат внешней и всех внутренних транзакций. После фиксирования внешней транзакции выполняется фиксирование внутренних транзакций. Иными словами, SQL Server по сути игнорирует операторы COMMIT внутри внутренних вложенных транзакций – в том смысле, что внутренние транзакции не фиксируются в ожидании окончательного фиксирования или отката внешней транзакции, чтобы определить статус завершения всех внутренних транзакций. (Это разъясняется в примерах на врезках "Практические советы" далее.) Кроме того, в случае использования оператора отката ROLLBACK во внешней транзакции или в любой из внутренних транзакций происходит откат всех этих транзакций. В оператор ROLLBACK нельзя включать имя внутренней транзакции; в этом случае SQL Server возвратит сообщение об ошибке. Можно включать имя внешней транзакции, имя точки сохранения или не включать никакого имени. (Описание точек сохранения приводится в разделе "Точки сохранения" далее.)

Практические советы.

Практические советы

Рассмотрим пример вложенной транзакции, включающей в себя хранимую процедуру. Эта хранимая процедура содержит явную транзакцию и вызывается из другой явной транзакции. Поэтому транзакция в хранимой процедуре становится внутренней вложенной транзакцией. В следующей последовательности SQL показаны операторы, используемые для создания хранимой процедуры, и транзакция, которая вызывает эту хранимую процедуру. (Для упрощения в этом примере используется оператор PRINT, а не реальные операторы модифицирования данных.)

USE MyDB
GO
CREATE PROCEDURE Place_Order       --Создает хранимую процедуру
AS
BEGIN TRAN place_order_tran
PRINT 'Здесь должны быть SQL-операторы, выполняющие задачи по заказам'
COMMIT TRAN place_order_tran
GO

BEGIN TRAN Order_tran                --Начинает внешнюю транзакцию
PRINT 'Поместите заказ'
EXEC Place_Order                       --Вызывает хранимую процедуру, которая
                                         --начинает внутреннюю транзакцию
COMMIT TRAN Order_tran               --Фиксирует внутреннюю и внешнюю
                                         --транзакции
GO

Выполнив эту программу, вы увидите результаты обоих операторов PRINT. Транзакция place_order_tran должна содержать внутри хранимой процедуры оператор COMMIT, отмечающий конец этой транзакции, но на самом деле это фиксирование не произойдет, пока не будет выполнено фиксирование транзакции Order_tran. Фиксирование или откат place_order_tran будет зависеть только от фиксирования Order_tran.

Хотя SQL Server не выполняет фактического фиксирования внутренних транзакций по оператору COMMIT, но все же для каждого оператора COMMIT происходит изменение системной переменной @@TRANCOUNT. Эта переменная следит за количеством активных транзакций на одно соединение с пользователем. При отсутствии активных транзакций значение @@TRANCOUNT равно 0. В начале каждой транзакции (с помощью BEGIN TRAN ) значение @@TRANCOUNT увеличивается на 1. После фиксирования каждой транзакции значение @@TRANCOUNT уменьшается на 1. Когда значение @@TRANCOUNT становится равным 0, фиксируется внешняя транзакция. Если во внешней транзакции или в любой из внутренних транзакций выполняется оператор ROLLBACK, то значение @@TRANCOUNT устанавливается равным 0. Помните, что для правильного уменьшения @@TRANCOUNT вы должны указывать фиксирование ( COMMIT ) для каждой внутренней транзакции. Вы можете проверять значение @@TRANCOUNT, чтобы определять наличие активных транзакций. Чтобы увидеть значение @@TRANCOUNT, используйте оператор SELECT @@TRANCOUNT.

Практические советы.

Использование @@TRANCOUNT

Рассмотрим пример того, как SQL Server использует переменную @@TRANCOUNT. Предположим, что имеется вложенная транзакция, состоящая из двух транзакций: одной внутренней и одной внешней, как в предыдущем примере. После запуска обеих транзакций, но до того, как они фиксированы, значение @@TRANCOUNT равно 2. Внешняя транзакция не может быть фиксирована, поскольку @@TRANCOUNT имеет ненулевое значение. После оператора COMMIT внутренней транзакции SQL Server уменьшает @@TRANCOUNT до 1. После оператора COMMIT внешней транзакции значение @@TRANCOUNT уменьшится до 0, и будет выполнено фактическое фиксирование внешней и внутренней транзакций. Следующая программа основана на предыдущем примере, но включает в себя считывание значений @@TRANCOUNT:

USE MyDB 
GO 
DROP PROCEDURE Place_Order 
GO 
CREATE PROCEDURE Place_Order    --Создает хранимую процедуру.
AS
BEGIN TRAN place_order_tran        --Приращение TRANCOUNT
PRINT 'Здесь должны быть SQL-операторы, выполняющие задачи по заказам'
SELECT @@TRANCOUNT as TRANCOUNT_2     
COMMIT TRAN place_order_tran       --Уменьшение TRANCOUNT.
GO

SELECT @@TRANCOUNT as TRANCOUNT_initial 
BEGIN TRAN Order_tran              --Приращение TRANCOUNT.
PRINT 'Place an order'
SELECT @@TRANCOUNT as TRANCOUNT_1
EXEC Place_Order                     --Вызывает хранимую процедуру, которая
                                       --начинает внутреннюю  транзакцию.
SELECT @@TRANCOUNT as TRANCOUNT_3 
COMMIT TRAN Order_tran               --Уменьшение TRANCOUNT.
SELECT @@TRANCOUNT as TRANCOUNT_4
GO

При выполнении этой программы вы увидите на экране значения @@TRANCOUNT, которые выводятся в следующем порядке: 0, 1, 2, 1, 0.

Примечание. Для явных транзакций, в которых используется BEGIN TRAN, вы должны фиксировать каждую транзакцию явным образом. При использовании вложенных транзакций SQL Server не сможет фиксировать внешние и внутренние транзакции, пока не будет указано явное фиксирование всех внутренних транзакций с помощью оператора COMMIT.

Неявный режим

В неявном режиме транзакция автоматически начинается при использовании определенных операторов T-SQL и продолжается, пока не появится оператор явного окончания COMMIT или ROLLBACK. Если оператор окончания не указан, то при отсоединении пользователя происходит откат данной транзакции. Следующие операторы T-SQL являются началом новой транзакции в неявном режиме:

• ALTER TABLE
• CREATE
• DELETE
• DROP
• FETCH
• GRANT
• INSERT
• OPEN
• REVOKE
• SELECT
• TRUNCATE TABLE
• UPDATE

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

Чтобы задать неявный режим транзакций, вы можете использовать следующий оператор T-SQL:

SET IMPLICIT_TRANSACTIONS {ON | OFF}

Значение ON активизирует неявный режим, и OFF отключает его. После отключения неявного режима используется режим автофиксации.

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