Stored Procedures Deadlock Issue

Question

Hi,

I have 2 stored procedures. Each procedure contains a call to a third stored proc. The deadlock occurs on calling the 3rd proc. If I remove the call from both procs, I get no deadlocks.

Any ideas? I need to call the 3rd stored proc to update a overall table. Anything I can change in my below code?

Stored Proc A...

	-- stored proc A
	BEGIN TRANSACTION
	
		IF EXISTS(SELECT UserID
				FROM [CountryA]
				WHERE (UserID = @UserID)
			)
		BEGIN
			UPDATE [CountryA]
			SET UserDetail = @Username
			WHERE (UserID = @UserID)	
		END
		ELSE
		BEGIN
			INSERT INTO [CountryA] (UserID, UserDetail) 
			VALUES (@UserID, @Username)
		END
		
		-- call main stored proc to update overall table
		EXEC dbo.[UpdateMain] @UserID	
	
	COMMIT	

	-- stored proc B
	BEGIN TRANSACTION
	
		IF EXISTS(SELECT UserID
				FROM [CountryB]
				WHERE (UserID = @UserID)
			)
		BEGIN
			UPDATE [CountryB]
			SET UserDetail = @Username
			WHERE (UserID = @UserID)	
		END
		ELSE
		BEGIN
			INSERT INTO [CountryB] (UserID, UserDetail) 
			VALUES (@UserID, @Username)
		END
		
		-- call main stored proc to update overall table
		EXEC dbo.[UpdateMain] @UserID	
	
	COMMIT	

Then the overall stored proc - UpdateMain...

	-- UPDATEMAIN STORED PROC
		DECLARE @CountUserA int;
		DECLARE @FirstEntryUserA int;

		Begin
			SET @CountUserA = (SELECT COUNT (UserID)
			FROM [CountryA]
			WHERE UserID = @UserID)
		End

		if (@CountUserA > 1)
		Begin
			SET @FirstEntryUserA = (SELECT TOP(1) UserID 
			FROM [CountryA]
			WHERE UserID = @UserID
			ORDER BY UserID ASC)

			DELETE FROM [CountryA]
			WHERE UserID = @UserID
			AND (UserID != @FirstEntryUserA)
		End

		Begin
			Set @UserADetail = (SELECT UserDetail
				FROM [CountryA]
				WHERE UserID = @UserID)
		End

		INSERT INTO UserMainDetails(ID, UserDetail) VALUES(@UserID, @UserDetail)

Thanks!

Answer 1

Take a look at transaction isolation level snapshot

http://msdn.microsoft.com/en-us/library/ms378149(v=sql.90).aspx

Or you can have Serializable isolation level setting in the third stored procedure, probably it will take some time but you do not get deadocks

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
MS SQL Consultants: Improves MS SQL Database Performance

Answer 2

Can you please enable the trace flag 1222 and share the deadlock graph from SQL error log to get some more clarity? 

---

Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
|Team Blog:- Team Blog

Answer 3

What exactly are you trying to accomplish?  I think the deadlock issue can be avoided with some refactoring but I hesitate to make specific suggestions without knowing your intent.

Be aware that you need to consider concurrency with the IF EXISTS...UPDATE...ELSE...INSERT pattern.  If two different connections execute the proc concurrently with the same @UserID value, you will insert duplicate rows.  You'll need to use a SERIALIZABLE transaction isolation level or locking hints to avoid such a race condition.

It appears to me that the purpose of of dbo.UpdateMain is to remove a duplicate (oldest) UserID row from CountryA (after an INSERT/UPDATE into either CountryA or CountryB).  Why are you doing that?  It just seems like an odd thing to do and maybe there is a better way.  But note that no rows will ever be deleted from CountryA because the DELETE specifies UserID = @UserID and UserID <> @FirstUserA.  The @FirstEntryUserA variable is set to the same @UserID in the previous SET statement so the result is effectively WHERE UserID = @UserID AND UserID <> @UserID.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 4

Personally, I would suggest that you capture the Deadlock Graph Event for your scenario.

This will identify for you precisely which resources/objects/keys are involved, and the locks taken that ultimately create a Deadlock event.

Armed with this information, you may for example discover that your query could benefit from index creation on the object involved because the queries are performing expensive operations such as table scans, resulting in a higher probability of a resource clash. 

I'm going to generalize here but for the most part Deadlocks occur due to the following:

Consider two transactions A and B that when both executed, result in a deadlock event.

• Timing - For example if transaction A starts and finishes before B has begun, then there is no issue. (Hence optimizing transactions to be short lived to limit the possibility of "bad timing").
• Lock Granularity - More granular locks, for example row rather than table, reduce the chance of contention on a physical resource. (This is why suitable indexing is important).
• Logical process ordering - Consider do the business processes that the transactions perform have to be executed in their current order? (Could they be modified to access resources in the same order for example, thereby removing the resource conflict?)
  

Think about your current use case and how you can apply these considerations.

---

John Sansom | SQL Server DBA Blog | @SQLBrit on Twitter

Answer 5

Hi,

Thanks for the help. I have updated my stored procs to use MERGE, which will prevent duplicates. However each call to UpdateMain stored proc causes Deadlock when stored proc A and stored proc B are called simultaneously.

What do I need to change? UpdateMain stored proc is needed to check data of CountryA and CountryB and populate a CountryOverallStatus table...

		-- Stored Proc A
		MERGE [CountryA] AS target
		USING (SELECT @UserID,@Period,@CountryA_Address,@CurrentDateTime) 
			AS source (UserID,Period, CountryA_Address, CurrentDateTime)
		ON (target.UserID = source.UserID)
		AND (target.Period = source.Period)
		WHEN MATCHED THEN 
			UPDATE 	SET CountryA_Address = @CountryA_Address,
				    CurrentDateTime = @CurrentDateTime
		WHEN NOT MATCHED THEN	
			INSERT (UserID, Period, CountryA_Address, CurrentDateTime)
			VALUES (source.UserID, source.Period, source.CountryA_Address, source.CurrentDateTime);

		-- call main stored proc to update overall table
		EXEC dbo.[UpdateMain] @UserID, @Period	


		-- Stored Proc B
		MERGE [CountryB] AS target
		USING (SELECT @UserID,@Period,@CountryB_Address,@CurrentDateTime) 
			AS source (UserID,Period, CountryB_Address, CurrentDateTime)
		ON (target.UserID = source.UserID)
		AND (target.Period = source.Period)
		WHEN MATCHED THEN 
			UPDATE 	SET CountryB_Address = @CountryB_Address,
				    CurrentDateTime = @CurrentDateTime
		WHEN NOT MATCHED THEN	
			INSERT (UserID, Period, CountryB_Address, CurrentDateTime)
			VALUES (source.UserID, source.Period, source.CountryB_Address, source.CurrentDateTime);

		-- call main stored proc to update overall table
		EXEC dbo.[UpdateMain] @UserID, @Period	

					

		-- Stored Proc UpdateMain
		@UserID nvarchar(5),
		@Period datetime

		DECLARE @MainAddress nvarchar(255);
		DECLARE @CountryA_Address nvarchar(255);
		DECLARE @CountryB_Address nvarchar(255);

		Set @CountryA_Address = (SELECT CountryA_Address
			FROM [CountryA]
			WHERE UserID = @UserID
			AND Period = @Period)

		Set @CountryB_Address = (SELECT CountryB_Address
			FROM [CountryB]
			WHERE UserID = @UserID
			AND Period = @Period)

		IF @CountryA_Address IS NOT NULL 
			AND @CountryB_Address IS NOT NULL
		BEGIN
		 MERGE [CountryOverallStatus] AS target
		 USING (SELECT @UserID,@Period,@CountryA_Address,@CountryB_Address,'Y') 
			AS source (UserID,Period,CountryA_Address,CountryB_Address,Complete)
		 ON (target.UserID = source.UserID)
		 AND (target.Period = source.Period)
		 WHEN MATCHED THEN 
			UPDATE 	SET CountryA_Address = @CountryA_Address,
				    CountryB_Address = @CountryB_Address,
				    Complete = 'Y'
		 WHEN NOT MATCHED THEN	
			INSERT (UserID, Period, CountryA_Address, CountryB_Address, Complete)
			VALUES (source.UserID, source.Period, source.CountryA_Address, source.CountryB_Address, 'Y');
		END

Answer 6

What do I need to change? UpdateMain stored proc is needed to check data of CountryA and CountryB and populate a CountryOverallStatus table...

One of the keys to avoiding deadlocks (and improve performance) is to touch only the data needed.  It looks to me like a unique clustered index (or clustered primary key or unique constraint) on UserID and Period of the CountryOverallStatus table will help optimize the query.

Also, consider adding a HOLDLOCK hint to your MERGE statements as described in http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx.  This will help avoid a primary key violation or duplicate data.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/