February 27, 2013 at 9:37 am
Hi all,
Below are two scenarios, however, what I am trying to accomplish is to catch deadlock errors and re-try the DML that was chosen as the deadlock victim. Does anyone know of way to accomplish what I am trying to accomplish without a doomed transaction given the constraints? I have seen many posts that this appears to be a limitation of the dbengine, but am looking for confirmation.
In both scenarios, I have a SProc - let's called it "X" - that is called mostly from within triggers (that performs some denormalized calculations and cannot be changed at this time) - in which deadlocks are often occurring.
Scenario 1
•In SProc X, I want to catch any 1205 errors, and re-try the query that receives the 1205 error
•However, whenever I re-try the query I receive an error that the transaction is "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction"
Scenario 2
•In SProc Y, that runs DML statements that will fire triggers that call SProc X, I want to catch any 1205 errors, and re-try the query that receives the 1205 error
•If SProc Y is called outside any transaction, I can begin a new transaction and rollback if a deadlock occurred and successfully retry the DML
•However, if SProc Y is called inside a transaction, I need to perform a SAVE TRAN and rolback to the savepoint. Whenever I re-try the query I receive an error that the transaction is "The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.". For my logic to work, I cannot rollback the entire transaction, so SProc Y only performs as expected outside a transaction
TIA,
Dave
February 27, 2013 at 11:08 am
Read up on XACT_ABORT. XACT_ABORT is by default set to OFF in most instances for code in SQL Server, but inside a trigger it is set to ON by default.
February 27, 2013 at 12:46 pm
Cadavre (2/27/2013)
Read up on XACT_ABORT. XACT_ABORT is by default set to OFF in most instances for code in SQL Server, but inside a trigger it is set to ON by default.
thanks Cadavre however I have tried setting SET XACT_ABORT OFF in the trigger as well as in the SProc
February 27, 2013 at 1:35 pm
If XACT_STATE() returns -1 [uncommitable transaction], you can't do anything else except rollback the current transaction. That is a logical restriction within SQL Server itself.
In that case, you would have to re-issue the earlier statement(s) in the transaction to get them to apply again after the rollback.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 28, 2013 at 7:28 am
So I am not hearing anyone disagree with my conclusion that there is no way to accomplish this...:ermm:
February 28, 2013 at 8:00 am
davidsatz (2/28/2013)
So I am not hearing anyone disagree with my conclusion that there is no way to accomplish this...:ermm:
I do disagree, we have deadlock retry logic on our product. The logic works by passing the error number out from the trigger and into the sproc, then rolling back to the savepoint if XACT_STATE() <> -1 otherwise the transaction is doomed so rollback the entire transaction.
February 28, 2013 at 8:08 am
davidsatz (2/28/2013)
So I am not hearing anyone disagree with my conclusion that there is no way to accomplish this...:ermm:
I'm not exactly sure what you are looking to do, it would be helpful if you would provide some DDL to have a look (follow the link at the bottom to my signature - it explains what kind of details are expected from OP).
Anyway... If you are asking about possibility to log error which causes rallback from within a trigger, the answer is - it is possible. Here is small demonstration:
create table _test (id int, hundreddevidebyid int)
go
create table _test_log (logid int identity(1,1), error varchar(2000))
go
create trigger tr_test on _test for insert
as
begin
begin try
update t
set hundreddevidebyid = 100/i.id
from _test t
join inserted i on i.id = t.id
end try
begin catch
declare @err varchar(2000) = error_message();
rollback;
insert _test_log select @err;
end catch
end
insert _test (id) select 50;
insert _test (id) select 25;
insert _test (id) select 0;
select * from _test;
select * from _test_log;
You will see, that the third INSERT fails as it will cause division by 0, but the error is logged into the table.
Now, if you amend the trigger and comment out the ROLLBACK , the logging will stop working.
So the rule is: if you want to log errors within a trigger then your trigger should have error-handling with explicit ROLLBACK...
February 28, 2013 at 2:42 pm
thanks you for all the help - today is crazy day at work - tomorrow I will try to post some portion of the SQL involved to help clarify
February 28, 2013 at 2:57 pm
Eugene Elutin (2/28/2013)
davidsatz (2/28/2013)
So I am not hearing anyone disagree with my conclusion that there is no way to accomplish this...:ermm:I'm not exactly sure what you are looking to do, it would be helpful if you would provide some DDL to have a look (follow the link at the bottom to my signature - it explains what kind of details are expected from OP).
Anyway... If you are asking about possibility to log error which causes rallback from within a trigger, the answer is - it is possible. Here is small demonstration:
create table _test (id int, hundreddevidebyid int)
go
create table _test_log (logid int identity(1,1), error varchar(2000))
go
create trigger tr_test on _test for insert
as
begin
begin try
update t
set hundreddevidebyid = 100/i.id
from _test t
join inserted i on i.id = t.id
end try
begin catch
declare @err varchar(2000) = error_message();
rollback;
insert _test_log select @err;
end catch
end
insert _test (id) select 50;
insert _test (id) select 25;
insert _test (id) select 0;
select * from _test;
select * from _test_log;
You will see, that the third INSERT fails as it will cause division by 0, but the error is logged into the table.
Now, if you amend the trigger and comment out the ROLLBACK , the logging will stop working.
So the rule is: if you want to log errors within a trigger then your trigger should have error-handling with explicit ROLLBACK...
He wants his original SQL statement to complete, but I'm not sure that's possible once you've encountered a constraint error. A relational transaction is, by definition, "all or none": so SQL can't just apply the "good" rows and ignore the "bad" ones.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 1, 2013 at 3:48 am
...
He wants his original SQL statement to complete, but I'm not sure that's possible once you've encountered a constraint error. A relational transaction is, by definition, "all or none": so SQL can't just apply the "good" rows and ignore the "bad" ones.
If so, then it's impossible. You are absolutely right, SQL cannot commit "good part" of a query and roll-back only "bad one". It's call transaction atomicity rule - all or nothing!
March 2, 2013 at 1:15 pm
Here is sample SQL code for my SProc:
DECLARE @retry INT = 3;
DECLARE @waitForTime CHAR(8) = '00:00:01'-- 1 SECOND
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
DECLARE @TranCount int = @@trancount;
DECLARE @TranName varchar(32)
BEGIN TRY
WHILE @retry > 0
BEGIN
BEGIN TRY
IF @TranCount > 0
BEGIN
SET @TranName = 'CS' + CAST( NEWID() AS VARCHAR(36))-- create almost unique save tran name
SAVE TRANSACTION @TranName;
END
ELSE
BEGIN TRANSACTION;
/******************************************************************************
-- INSERT / Update data...from which we occassionally get 1205 deadlocks errors
******************************************************************************/
IF @TranCount = 0
COMMIT TRANSACTION;
SET @retry = 0;
END TRY
BEGIN CATCH
DECLARE @ErrorNumber INT= ERROR_NUMBER();
DECLARE @XACTState INT= XACT_STATE();
SET @ErrorMessage = ERROR_MESSAGE();-- since this is inner catch only use sql error
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
IF @XACTState <> -1 --If the transaction is still valid then roll back to the savepoint
AND @TranCount > 0 -- if we started a savepoint and this is deadlock
ROLLBACK TRANSACTION @TranName;
ELSE
IF @@TRANCOUNT > 0 -- we started the transaction, so roll it back or it was doomed transaction
ROLLBACK TRANSACTION;
IF ((@TranCount > 0 AND @XACTState <> -1) OR @TranCount = 0)
AND @ErrorNumber = 1205 -- we can retry if the transaction was not doomed and it was a deadlock error
BEGIN
-- If Deadlock Error, reduce @retry count for next retry
SET @retry = @retry - 1;
PRINT 'Deadlock detected, retrying update: ' + @ErrorMessage
WAITFOR DELAY @waitForTime; -- 1 second
END
ELSE
BEGIN
-- If a different error exit retry WHILE Loop
SET @retry = -1;
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END
END CATCH;
END; -- End WHILE loop.
-- some more code to deal with return values
RETURN 0
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION -- Rollback Tran
SELECT
@ErrorMessage = ERROR_MESSAGE() + ' Error occurred in ''' + ERROR_PROCEDURE() + ''' at line #' + CAST(ERROR_LINE() AS varchar(30)) + '.',
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
RETURN -1
END CATCH
GO
March 8, 2013 at 8:13 am
I'm not exactly sure what you are looking to do, it would be helpful if you would provide some DDL to have a look .
DECLARE @retry INT = 3;
DECLARE @waitForTime CHAR(8) = '00:00:01'-- 1 SECOND
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
DECLARE @TranCount int = @@trancount;
DECLARE @TranName varchar(32)
BEGIN TRY
WHILE @retry > 0
BEGIN
BEGIN TRY
IF @TranCount > 0
BEGIN
SET @TranName = 'CS' + CAST( NEWID() AS VARCHAR(36))-- create almost unique save tran name
SAVE TRANSACTION @TranName;
END
ELSE
BEGIN TRANSACTION;
/******************************************************************************
-- INSERT / Update data...from which we occassionally get 1205 deadlocks errors
******************************************************************************/
IF @TranCount = 0
COMMIT TRANSACTION;
SET @retry = 0;
END TRY
BEGIN CATCH
DECLARE @ErrorNumber INT= ERROR_NUMBER();
DECLARE @XACTState INT= XACT_STATE();
SET @ErrorMessage = ERROR_MESSAGE();-- since this is inner catch only use sql error
SET @ErrorSeverity = ERROR_SEVERITY();
SET @ErrorState = ERROR_STATE();
IF @XACTState <> -1 --If the transaction is still valid then roll back to the savepoint
AND @TranCount > 0 -- if we started a savepoint and this is deadlock
ROLLBACK TRANSACTION @TranName;
ELSE
IF @@TRANCOUNT > 0 -- we started the transaction, so roll it back or it was doomed transaction
ROLLBACK TRANSACTION;
IF ((@TranCount > 0 AND @XACTState <> -1) OR @TranCount = 0)
AND @ErrorNumber = 1205 -- we can retry if the transaction was not doomed and it was a deadlock error
BEGIN
-- If Deadlock Error, reduce @retry count for next retry
SET @retry = @retry - 1;
PRINT 'Deadlock detected, retrying update: ' + @ErrorMessage
WAITFOR DELAY @waitForTime; -- 1 second
END
ELSE
BEGIN
-- If a different error exit retry WHILE Loop
SET @retry = -1;
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END
END CATCH;
END; -- End WHILE loop.
-- some more code to deal with return values
RETURN 0
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION -- Rollback Tran
SELECT
@ErrorMessage = ERROR_MESSAGE() + ' Error occurred in ''' + ERROR_PROCEDURE() + ''' at line #' + CAST(ERROR_LINE() AS varchar(30)) + '.',
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
RETURN -1
END CATCH
GO
March 10, 2013 at 7:07 am
Maybe you should take a look at Service Broker architecture if deadlocks are a big problem? All changes will be applied sequentially?
Alternatively try to understand why they occur.
March 10, 2013 at 5:31 pm
siggemannen (3/10/2013)
Maybe you should take a look at Service Broker architecture if deadlocks are a big problem? All changes will be applied sequentially?Alternatively try to understand why they occur.
we cannot use server broker because the transaction is expected to be completed immediately; the UI queries the results right after the data is saved
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply