Handing Blocking and Deadlocks in SQL Stored Procedures

We recently had an issue at night during the period in which daily banner hit/view data is transferred from the daily table to the historical table.

During this time our large website was being hammered by BOTs and users and we were getting lots of timeout errors reported due the the tables we wanted to insert our hit records into being DELETED and UPDATED causing locks.

The default lock time is -1 (unlimited) but we had set it to our default command timeout of 30 seconds.

However if the DELETE or UPDATE in the data transfer job took over 30 seconds then the competing INSERT (to insert a banner hit or view) would time out and error with a database timeout due to the Blocking process not allowing our INSERT to do its job.

We tried a number of things including:

• Ensuring all tables were covered by indexes to speed up any record retrieval

• Reducing the DELETE into small batches of 1000 or 100 at a time in a WHILE loop to reduce the length of time the LOCK was held each time.

• Ensuring any unimportant SELECT statements from these tables were using WITH (NOLOCK) to get round any locking issues.

However none of these actually helped solve the problem so in the end we rewrote our stored procedure (SQL 2005 - 2008) so that it handled the LOCK TIMEOUT error and didn't return an error.

In SQL 2005 you can make use of TRY CATCH statements which meant that we could try a certain number of times to insert our data and if it failed we could just return quickly without an error as we also used a TRANSACTION to enable us to ROLLBACK or COMMIT the transaction.

We also set the LOCK_TIMEOUT to 500 milliseconds (so x 3 = 1.5 seconds) as if the insert couldn't be done in that time frame then there was no point logging it. We could have inserted it into another table to be added to our statistics later on but that is another point.

The code is below and shows you how to trap BLOCKING errors including DEADLOCKS and handle them.

Obviously this doesn't fix anything it just "masks" the problem from the end user and reduces the number of errors due to database timeouts due to long waiting blocked processes.

CREATE PROCEDURE [dbo].[usp_net_update_banner_hit]



@BannerIds varchar(200), -- CSV of banner IDs e.g 100,101,102

@HitType char(1) = 'V', -- V = banner viewed, H = banner hit





AS



SET NOCOUNT ON

SET LOCK_TIMEOUT 500 -- set to half a second



DECLARE @Tries tinyint	



SELECT @Tries = 1



-- loop for 3 attempts

WHILE @Tries <= 3

BEGIN

		BEGIN TRANSACTION

		BEGIN TRY

			

			-- insert our banner hits we are only going to wait half a second

			INSERT INTO tbl_BANNER_DATA

			(BannerFK, HitType, Stamp)

			SELECT  [Value], @HitType, getdate()

			FROM	dbo.udf_SPLIT(@BannerIds,',') -- UDF that splits a CSV into a table variable

			WHERE	[Value] > 0



			--if we are here its been successful ie no deadlock or blocking going on

			COMMIT

			

			-- leave our loop

			BREAK

		END TRY

		BEGIN CATCH



			--always rollback			

			ROLLBACK

			

				

			-- Check for Blocking errors 1222 or Deadlocks 1205 and if its a deadlock wait for a while to see if that helps

			IF ERROR_NUMBER() = 1205 OR ERROR_NUMBER() = 1222

			  BEGIN

					-- if its a deadlock wait 2 seconds then try again

					IF ERROR_NUMBER() = 1205

					  BEGIN

						-- wait 2 seconds to see if that helps the deadlock

						WAITFOR DELAY '00:00:02'

					  END	

					-- ELSE

					-- no need to wait for anything for BLOCKING ERRORS as our LOCK_TIMEOUT is going to wait for half a second anyway

					-- and if it hasnt finished by then (x 3) there is no point waiting any longer



			  END						



			--increment and try again

			SELECT @Tries = @Tries + 1

			

			-- carry on until we do it 3 times

			CONTINUE

			

		END CATCH;

END

Tweet