Число транзакций после EXECUTE указывает на несовпадающее количество операторов BEGIN и COMMIT. Предыдущий счет = 1, текущий счет = 0
У меня есть процедура вставки хранимой процедуры, которая будет передавать данные в таблицу Table1, получать значение Column1 из таблицы Table1 и вызывать вторую хранимую процедуру, которая будет передавать данные в Table2.
Но когда я называю "Вторая хранимая процедура" как:
Exec USPStoredProcName
Это дает мне ошибку следующим образом:
Число транзакций после EXECUTE указывает на несовпадающее количество операторов BEGIN и COMMIT. Предыдущий счетчик = 1, текущий счетчик = 0.
Я прочитал ответы на другие подобные вопросы и не могу найти, где именно запутывается количество коммитов.
18 ответов
Если у вас есть блок TRY/CATCH, вероятной причиной является то, что вы перехватываете исключение прерывания транзакции и продолжаете. В блоке CATCH вы всегда должны проверять XACT_STATE() и обрабатывать соответствующие прерванные и неподтвержденные (обреченные) транзакции. Если вызывающий абонент начинает транзакцию, и вызываемый пользователь, например, заходит в тупик (который прервал транзакцию), как вызываемый абонент собирается сообщить вызывающему абоненту, что транзакция была прервана, и он не должен продолжать "обычный бизнес"? Единственно возможный способ - повторно вызвать исключение, заставив вызывающего обработать ситуацию. Если вы молча проглатываете прерванную транзакцию, и вызывающий абонент продолжает считать, что он все еще находится в исходной транзакции, это может гарантировать только беспредел (а ошибка, которую вы получаете, заключается в том, как механизм пытается защитить себя).
Я рекомендую вам перейти к обработке исключений и вложенных транзакций, которая показывает шаблон, который можно использовать с вложенными транзакциями и исключениями:
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction usp_my_procedure_name;
-- Do the actual work here
lbexit:
if @trancount = 0
commit;
end try
begin catch
declare @error int, @message varchar(4000), @xstate int;
select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
if @xstate = -1
rollback;
if @xstate = 1 and @trancount = 0
rollback
if @xstate = 1 and @trancount > 0
rollback transaction usp_my_procedure_name;
raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
end catch
end
go
У меня тоже была эта пробема. Для меня причина была в том, что я делал
return
commit
вместо
commit
return
в одной хранимой процедуре.
Обычно это происходит, когда транзакция запущена и либо не зафиксирована, либо не является откатом.
Если в вашей хранимой процедуре возникает ошибка, это может заблокировать таблицы базы данных, поскольку транзакция не завершена из-за некоторых ошибок времени выполнения при отсутствии обработки исключений. Вы можете использовать обработку исключений, как показано ниже. SET XACT_ABORT
SET XACT_ABORT ON
SET NoCount ON
Begin Try
BEGIN TRANSACTION
//Insert ,update queries
COMMIT
End Try
Begin Catch
ROLLBACK
End Catch
Это также может произойти, если ваша хранимая процедура сталкивается с ошибкой компиляции после открытия транзакции (например, таблица не найдена, недопустимое имя столбца).
Я обнаружил, что мне нужно использовать две хранимые процедуры: "рабочую" и оберточную с try/catch, с логикой, аналогичной описанной Remus Rusanu. Работающий перехватчик используется для обработки "обычных" сбоев, а перехватчик - для обработки ошибок сбоя компиляции.
https://msdn.microsoft.com/en-us/library/ms175976.aspx
Ошибки, на которые не влияет TRY…CATCH Construct
Следующие типы ошибок не обрабатываются блоком CATCH, когда они происходят на том же уровне выполнения, что и конструкция TRY… CATCH:
- Ошибки компиляции, такие как синтаксические ошибки, которые мешают запуску пакета.
- Ошибки, возникающие при перекомпиляции на уровне операторов, такие как ошибки разрешения имен объектов, возникающие после компиляции из-за отложенного разрешения имен.
Надеюсь, это поможет кому-то сэкономить несколько часов на отладку...
Имейте в виду, что если вы используете вложенные транзакции, операция ROLLBACK откатывает все вложенные транзакции, включая самую внешнюю.
При использовании в сочетании с TRY/CATCH это может привести к описанной вами ошибке. Подробнее здесь.
В моем случае ошибка была вызвана RETURN внутри BEGIN TRANSACTION. Так у меня было что-то вроде этого:
Begin Transaction
If (@something = 'foo')
Begin
--- do some stuff
Return
End
commit
и это должно быть:
Begin Transaction
If (@something = 'foo')
Begin
--- do some stuff
Rollback Transaction ----- THIS WAS MISSING
Return
End
commit
Избегать использования
RETURN
заявление, когда вы используете
BEGIN TRY
...
END TRY
BEGIN CATCH
...
END CATCH
а также
BEGIN, COMMIT & ROLLBACK
операторы в хранимых процедурах SQL
У меня было то же сообщение об ошибке, моя ошибка была в том, что у меня была точка с запятой в конце строки COMMIT TRANSACTION
Для меня после обширной отладки исправление было простым пропущенным броском; Заявление в подвохе после отката. Без этого уродливое сообщение об ошибке, что вы в конечном итоге.
begin catch
if @@trancount > 0 rollback transaction;
throw; --allows capture of useful info when an exception happens within the transaction
end catch
Это также может зависеть от того, как вы вызываете SP из своего кода C#. Если SP возвращает какое-либо значение типа таблицы, тогда вызывайте SP с помощью ExecuteStoreQuery, а если SP не возвращает никакого значения, вызывайте SP с помощью ExecuteStoreCommand.
Точной причиной этого сообщения является правило, которое подразумевает SQL Server: количество транзакций должно быть одинаковым в начале и в конце выполнения процедуры. Другими словами, процедура;
- не следует фиксировать/откатывать транзакцию, которую она не запускала. В этом случае предыдущий счетчик, отображаемый в сообщении об исключении, будет больше нуля, а текущий счетчик равен нулю. Лучший способ предотвратить это — зафиксировать счетчик транзакций (@@TRANCOUNT) в самом начале выполнения и использовать операторы транзакций, только если он равен нулю. Приведенная ниже примерная процедура представляет собой простейшую «защитную» структуру от ошибок такого типа. Если эта процедура вызывается в рамках существующей транзакции, она не начнет новую транзакцию и не попытается зафиксировать или откатить "унаследованную" транзакцию. Вместо этого он просто повторно выдает ту же ошибку в контекст вызывающей стороны. Это также хорошая практика, чтобы сохранить настоящую исходную процедуру ошибки.
- должен решить судьбу (зафиксировать или откатить) начатую транзакцию до завершения ее выполнения. В этом случае текущий счет будет больше, чем предыдущий счет.
Я настоятельно рекомендую внимательно прочитать Erland Sommarskog Error and Transaction Handling in SQL Server.
create or alter proc sp_err266
as
begin
set nocount on
set xact_abort on
declare @trancount int = @@trancount
if @trancount = 0
begin tran
begin try
raiserror('Raise an unexpected error...', 16, 1);
if XACT_STATE() = 1 and @trancount = 0
commit;
end try
begin catch
if XACT_STATE() <> 0 and @trancount = 0
rollback;
else
throw;
end catch
end
Убедитесь, что в одной процедуре / запросе нет нескольких транзакций, из которых одна или несколько оставлены незафиксированными.
В моем случае у меня случайно был запрос BEGIN TRAN в запросе
Я столкнулся с этой ошибкой один раз после пропуска этого оператора из моей транзакции.
COMMIT TRANSACTION [MyTransactionName]
На мой взгляд, принятый ответ в большинстве случаев является излишним.
Причиной ошибки часто является несоответствие BEGIN и COMMIT, как четко указано в ошибке. Это означает использование:
Begin
Begin
-- your query here
End
commit
вместо
Begin Transaction
Begin
-- your query here
End
commit
Пропуск транзакции после начала вызывает эту ошибку!
Для меня проблема заключалась в том, что я забыл добавить
output ключевое слово после некоторых выходных параметров вызова SP в транзакции.
Если у вас есть структура кода что-то вроде:
SELECT 151
RETURN -151
Тогда используйте:
SELECT 151
ROLLBACK
RETURN -151
Во вложенных процедурах ROLLBACK следует использовать с осторожностью, подробное объяснение здесь /questions/42410005/sql-server-vlozhennyie-tranzaktsii-v-hranimoj-protsedure/63346907#63346907
Для меня две начальные транзакции и транзакция с несколькими откатами вызывают эту проблему.
------------------------------------------------------------
BEGIN TRANSACTION
-- BEGING TRANSACTION
call of stored procedure -- ROLLBACK TRANASCTION
-- ROLLBACK TRANSACTION
ROLLBACK TRANSACTION
-----------------------------------------------------------
Он может выполнить откат только один раз, у него не будет нескольких операторов отката, также проверьте операторы возврата, которые вызывают проблему.