Detecting Locking and Transaction Conflict with Begin Try Handler in SQL Server 2005
Detecting Locking and Transaction Conflict with Begin Try Handler in SQL Server 2005
By : Kasim Wirama, MCDBA, MVP SQL Server
SQL Server 2005 introduces new error handling construct, it is more robust compare to previous version of SQL Server. The error handling construct is BEGIN TRY…END TRY/BEGIN CATCH…END CATCH; Regarding to error caused by locking, deadlock or update conflict that happen in snapshot transaction isolation level, it is very relevant to implement.
Error number for locking is 1222, for deadlock is 1205 and for update conflict is 3960. With new error handling construct, it is now possible to retry transaction inside TSQL code without round trip to application code outside database, so it is more efficient.
I will show you sample of database transaction retries by implementing new error handling construct below :
set lock_timeout 30000;
declare @retry int, @cnt int, @maxretries int, @j int, @errmsg varchar(200);
select @retry = 1, @cnt =0, @maxretries = 3;
while @retry = 1 and @cnt <= @maxretries
begin
set @retry = 0;
begin try
begin tran;
do some activities on first table
do some activities on second table
commit tran;
end try
begin catch
if error_number() = 1222
begin
if xact_state() <> 0 rollback tran;
set @errmsg = 'error 1222-locking';
raiserror(@errmsg, 16,1);
end
else if error_number() in (1205, 3906)
begin
if xact_state() <> 0 rollback tran;
select @retry = @retry + 1;
if @retry <= @maxretries
waitfor delay '00:00:05';
end
else
begin
if xact_state() <> 0 rollback tran;
set @errmsg = left(error_message(),200);
raiserror( @errmsg , 16,1);
end
end catch;
end
by default lock timeout is indefinite, it’s good practice to specify lock timeout to particular value, for example set lock timeout to 30 second, so transaction will not wait indefinitely when waiting for some resource that is held by other connection longer than 30 second.
Other thing you should be aware, that before you rollback transaction, it’s better you check condition XACT_STATE function, if it is -1 means that transaction is opened, and the only option is to rollback, if it is 1 means that transaction is opened, the you have option to commit or rollback, if it is 0 then there is no opened transaction.