Как откатить транзакцию ms sql

Откат распределенной транзакции в SQL Server

Если при выполнении распределенной транзакции с Microsoft SQL Server на сервере произошла ошибка I/O или произошло отключение сервера / базы данных, тогда транзакция в координаторе (DTC) и на других базах будет откачена (ROLLBACK), а в пострадавшей базе данных на SQL Server может не откатиться. Это потому, что в результате отключения / сбоя, СУБД технически не могла её откатить.

Зависшая распределенная транзакция будет проявляться, в том числе, тем, что в базе не будет обрезаться журнал (TRANSACTION LOG) даже в режиме восстановления SIMPLE.

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

Как диагностировать зависшую распределенную транзакцию?

Чтобы проверить первичные симптомы и убедиться, что зависшая транзакция есть, нужно выполнить:

DBCC OPENTRAN

Эта команда может выдать приблизительно следующее:

Transaction information for database ‘. ‘.

Oldest active transaction:
SPID (server process ID): 53
UID (user ID) : -1
Name : DTCXact
LSN : (1517932:234765:1)
Start time : Jul 3 2013 3:41:04:643AM
SID : 0x010600000000000550000000cc62defa0799cb7d28fedbc8b4ce1b1ace9fa59b
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Обратите внимание на имя DTCXact — это означает, что транзакцию инициировал Distributed Transaction Coordinator.

Ок. Узнав SPID, мы можем узнать что за процесс работал в транзакции:

EXEC sp_who 53

Мы можем его убить (KILL . ). Но не надо этого делать.

Зависшую распределенную транзакцию прекращение клиентского процесса не остановит — она существует теперь только в пострадавшей базе данных, сам процесс и координатор про неё уже забыли.

Как завершить зависшую распределенную транзакцию?

Интернет на вопрос «ROLLBACK DTCXact» и «DTCXact» выдает дурацкие статьи на общие темы или обсуждения без ответа на вопрос.

Недавно, я использовал такой способ — перевести базу в однопользовательский режим с откатом всех транзакций:

ALTER DATABASE [. ] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Убедился, что транзакция откатилась, и попытался перевести в многопользовательский:

ALTER DATABASE [. ] SET MULTI_USER WITH NOWAIT

Правда после этого, как я подозреваю, из-за логического повреждения (которое было не следствием, а причиной зависшей распределенной транзакции) база вошла в режим SUSPECT. Пришлось её выключить (Take Off) и включить (Take On). Потребовался, видимо, не только возврат страниц данных «назад» путем отката, но и восстановление части страниц «вперед» по журналу с какого-то момента.

В результате база заработала, распределенная транзакция откатилась, журнал усёкся.

Как откатить транзакцию ms sql

Иногда возникает желание посмотреть что именно сейчас делается в SQL: какие запросы выполняются и какие транзакции активны?

Сделать это можно следующим образом.

Текущие запросы, с их текстами

select session_id, status, wait_type, command, last_wait_type , qt.text sql_text , total_elapsed_time/1000 as [total_elapsed_time, sec], wait_time/1000 as [wait_time, sec], (total_elapsed_time - wait_time)/1000 as [work_time, sec] , percent_complete from sys.dm_exec_requests as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt where session_id >= 50 and session_id <> @@spid -- чтоб исключить текущую сессию и этот запрос

Самая долгая транзакция

DBCC OPENTRAN

Возвращает результаты в виде
Oldest active transaction:
SPID (server process ID): 65
UID (user ID) : -1
Name : user_transaction
LSN : (14627553:1424:2)
Start time : Mar 12 2018 5:25:34:807PM
SID : 0x01

Подробности транзакции по SPID

DECLARE @sqltext VARBINARY(128) SELECT @sqltext = sql_handle FROM sys.sysprocesses WHERE spid = [SPID, полученный из DBCC OPENTRAN] SELECT * FROM sys.dm_exec_sql_text(@sqltext) GO

Принудительно откатить транзакцию, можно убив процесс

KILL [SPID, полученный из DBCC OPENTRAN]

ROLLBACK TRANSACTION (Transact-SQL)

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

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

Синтаксис

--Applies to SQL Server and Azure SQL Database ROLLBACK < TRAN | TRANSACTION >[ transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable ] [ ; ] 

-- Applies to Synpase Data Warehouse in Microsoft Fabric, Azure Synapse Analytics and Parallel Data Warehouse Database ROLLBACK < TRAN | TRANSACTION >[ ; ] 

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

transaction_name
Имя, присвоенное транзакции в BEGIN TRANSACTION. Аргумент transaction_name должен соответствовать правилам для идентификаторов, однако используются только первые 32 символа имени транзакции. При вложении транзакций аргумент transaction_name должен быть именем транзакции из самой внешней инструкции BEGIN TRANSACTION. Аргумент transaction_name всегда учитывает регистр, даже если экземпляр SQL Server регистр не учитывает.

@tran_name_variable
Имя определенной пользователем переменной, содержащей допустимое имя транзакции. Переменная должна быть объявлена с типом данных char, varchar, nchar или nvarchar.

savepoint_name
Аргумент savepoint_name из инструкции SAVE TRANSACTION. Аргумент savepoint_name должен соответствовать требованиям, предъявляемым к идентификаторам. Используйте аргумент savepoint_name, если откат по условию должен влиять только на часть транзакции.

@savepoint_variable
Имя пользовательской переменной, содержащей допустимое имя точки сохранения. Переменная должна быть объявлена с типом данных char, varchar, nchar или nvarchar.

Обработка ошибок

Инструкция ROLLBACK TRANSACTION не выдает никаких сообщений пользователю. Если нужны предупреждения в хранимой процедуре или триггере, используйте инструкции RAISERROR или PRINT. Инструкция RAISERROR предпочтительна для отображения ошибок.

Общие замечания

Инструкция ROLLBACK TRANSACTION без аргумента savepoint_name или transaction_name откатывает изменения на начало транзакции. При наличии вложенных транзакций эта инструкция откатывает все внутренние транзакции к началу самой внешней инструкции BEGIN TRANSACTION. В обоих случаях инструкция ROLLBACK TRANSACTION уменьшает системную функцию @@TRANCOUNT до 0. Инструкция ROLLBACK TRANSACTION savepoint_name не уменьшает @@TRANCOUNT.

Инструкция ROLLBACK TRANSACTION не может ссылаться на аргумент savepoint_name в распределенных транзакциях, запущенных явно с помощью инструкции BEGIN DISTRIBUTED TRANSACTION или вызванных из локальной транзакции.

Нельзя выполнить откат транзакции после выполнения инструкции COMMIT TRANSACTION, кроме случая, когда инструкция COMMIT TRANSACTION связана с вложенной транзакцией, которая содержится внутри откатываемой транзакции. В этом случае будет выполнен откат вложенной транзакции, даже если для нее была выполнена инструкция COMMIT TRANSACTION.

Внутри транзакции допускается использование повторяющихся имен точки сохранения, но инструкция ROLLBACK TRANSACTION, использующая повторяющееся имя точки сохранения, откатывает транзакцию лишь к самой последней точке, установленной с помощью инструкции SAVE TRANSACTION для этого имени.

Совместимость

В хранимых процедурах инструкция ROLLBACK TRANSACTION без аргументов savepoint_name или transaction_name откатывает все инструкции к самой внешней инструкции BEGIN TRANSACTION. Вызов инструкции ROLLBACK TRANSACTION в хранимой процедуре является причиной того, что значение @@TRANCOUNT после завершения хранимой процедуры отличается от значения @@TRANCOUNT при выдаче хранимой процедурой информационного сообщения. Это сообщение не влияет на последующую обработку.

Если инструкция ROLLBACK TRANSACTION запускается в триггере, происходит следующее:

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

Значение @@TRANCOUNT увеличивается на единицу при срабатывании триггера даже в режиме автоматической фиксации. (Система обрабатывает триггер как неявную вложенную транзакцию.)

Инструкция ROLLBACK TRANSACTION в хранимой процедуре не влияет на последующие инструкции в пакете, вызвавшем процедуру; последующие инструкции в пакете выполняются. Инструкции ROLLBACK TRANSACTION в триггерах уничтожают пакет, содержащий инструкцию, вызвавшую триггер; последующие инструкции в пакете не выполняются.

Эффект, оказываемый инструкцией ROLLBACK на курсоры, определяется тремя правилами:

1. Если параметр CURSOR_CLOSE_ON_COMMIT установлен в ON, инструкция ROLLBACK закрывает, но не освобождает все открытые курсоры.
2. Если параметр CURSOR_CLOSE_ON_COMMIT установлен в OFF, инструкция ROLLBACK не влияет на открытые синхронные курсоры типа STATIC или INSENSITIVE или асинхронные курсоры типа STATIC, которые были полностью заполнены. Открытые курсоры любого другого типа закрываются, но не освобождаются.
3. Ошибка, которая уничтожает пакет и формирует внутренний откат, освобождает все курсоры, которые были объявлены в пакете, содержащем ошибочную инструкцию. Все курсоры освобождаются в зависимости от их типа или установок параметра CURSOR_CLOSE_ON_COMMIT. Это относится и к курсорам, объявленным в хранимых процедурах, вызываемых ошибочным пакетом. Курсоры, объявленные в пакете перед ошибочным, подчиняются правилам 1 и 2. Ошибка взаимоблокировки является примером ошибки такого типа. Инструкция ROLLBACK в триггере также автоматически формирует этот тип ошибки.

Режим блокировки

Инструкция ROLLBACK TRANSACTION с параметром savepoint_name освобождает все блокировки, полученные после точки сохранения, за исключением укрупненных блокировок и блокировок преобразования. Такие блокировки не освобождаются и не переводятся в прежний режим.

Разрешения

Необходимо быть членом роли public.

Примеры

В следующем примере демонстрируется эффект отката именованной транзакции: После создания таблицы следующие инструкции запускают именованную транзакцию, вставляют две строки и откатывают транзакцию, именованную в переменной @TransactionName. Другой оператор вне именованной транзакции вставляет две строки. Запрос возвращает результаты предыдущих инструкций.

USE tempdb; GO CREATE TABLE ValueTable ([value] INT); GO DECLARE @TransactionName VARCHAR(20) = 'Transaction1'; BEGIN TRAN @TransactionName INSERT INTO ValueTable VALUES(1), (2); ROLLBACK TRAN @TransactionName; INSERT INTO ValueTable VALUES(3),(4); SELECT [value] FROM ValueTable; DROP TABLE ValueTable; 

value ----- 3 4 

Фиксация и откат транзакций

Для фиксации или отката транзакции в режиме ручной фиксации приложение вызывает SQLEndTran. Драйверы для СУБД, поддерживающие транзакции, обычно реализуют эту функцию, выполнив инструкцию COMMIT или ROLLBACK . Диспетчер драйверов не вызывает SQLEndTran , если подключение находится в режиме автоматической фиксации; оно просто возвращает SQL_SUCCESS, даже если приложение пытается откатить транзакцию. Так как драйверы для СУБД, которые не поддерживают транзакции, всегда находятся в режиме автоматической фиксации, они могут либо реализовать SQLEndTran для возврата SQL_SUCCESS без каких-либо действий, либо не реализовать его вообще.

Приложения не должны фиксировать или откатывать транзакции путем выполнения инструкций COMMIT или ROLLBACK с помощью SQLExecute или SQLExecDirect. Последствия этого не определены. Возможные проблемы включают в себя драйвер, который больше не знает, когда транзакция активна, и эти инструкции завершаются ошибкой в источниках данных, которые не поддерживают транзакции. Вместо этого эти приложения должны вызывать SQLEndTran .

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

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