Odd Deadlock Issue

[ousoonerjoe]

Using SQL Server 2008 R2

I have an intermittent deadlock that happens on one of our audit tables. From what I understand of Row Locking, it shouldn't be happening at all here. All our stored procedures have the following layout (trimmed for ease of debugging). Now with all things being equal, the -INSERT Audit- should never deadlock, right?

CREATE PROCEDURE cst_Insert_Template(
	@Argument	DataType)
AS

DECLARE @NewId	INT;

[COLOR=#3465A4]BEGIN TRY
	BEGIN TRANSACTION[/color];
		INSERT INTO Audit(ProcName, ...)
			VALUES(OBJECT_NAME(@@PROCID), ...);
	[COLOR=#3465A4]COMMIT TRANSACTION;

	BEGIN TRANSACTION;[/color]

		INSERT INTO TableX ()
			VALUES or SELECT...
	
		SET @NewId = SCOPE_IDENTITY();
	
		IF @NewId < 1 
			BEGIN
				RAISERROR(60000, 16, 1, 'Insert failed...');  
			END
		
	[COLOR=#3465A4]COMMIT TRANSACTION;

END TRY
BEGIN CATCH[/color]
	If @@TRANCOUNT > 0
		BEGIN
			[COLOR=#3465A4]ROLLBACK TRANSACTION[/color]
		END
	INSERT INTO ErrLog (Number, Severity, ErrState, ErrSource, ErrLine, ErrMsg, Form, AppID)
		SELECT ISNULL(ERROR_NUMBER(),-1), ISNULL(ERROR_SEVERITY(),0), ISNULL(ERROR_STATE(),0), 
			ISNULL(ERROR_PROCEDURE(),'Unknown.'), ISNULL(ERROR_LINE(),0), 
			ISNULL(ERROR_MESSAGE(),'ROLLBACK: Failed to Insert TableX Record.'), OBJECT_NAME(@@PROCID), 1;
[COLOR=#3465A4]END CATCH[/color]

Any suggestions or thoughts are appreciated.

Thank you.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[yelworcm]

you will need to get a bit more information. I have seen seemingly innocent statements deadlock on explicit transactions, and even on access methods to a table. If you run the following:

dbcc traceon (1222, -1)

on your SQL instance, you should get a wealth of information in your errorlog.

 

[ousoonerjoe]

haha... That's exactly how I found out that it was the source of the deadlock. The dump shows both the Offender and the Victim were dbo.Audit. There are no triggers. Three indexes (possibility?) that get rebuilt every weekend with the back up process, and do not have Page Locks enabled, only Row Locks.

(sorry for delay, tornadoes all around here).

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[yelworcm]

Well, keep your head down.

Now all you need to do is run a profiler trace to capture the events leading up to the deadlock.

I had a case where an application

1) began a transaction
2) did a table scan select on the table
3) updated a single row (with only a row lock)
4) did a second table scan select.

The entertaining part was that this application had multiple threads doing this on app startup. This caused some hilarity, especially since the 1222 output only declared the updates to be in conflict with one another. Once I ran a trace of the whole app for a while, I managed to step back in the history of both connections to the point where the transactions began. Are the deadlocks hopefully somewhat predictable as to when they happen?

 

[ousoonerjoe]

Thanks for the reply.

We can systematically recreate deadlocks and timeouts with Updates, etc. The problem we're having is that everything we know about the Deadlock rules states that they should not be in effect here.

Procedure1 INSERT 1 record into TableA.
Procedure2 INSERT 1 record into TableA.

In this scenario, the absolute worst case outcome is that Procedure2 gets a Timeout versus a Deadlock. So far, it only seems to occur on the same table. All other deadlocks we incur are self inflicted and obvious and can fix them quickly. Unfortunately, this INSERT deadlock is not something we can recreate either. It's a Gremlin in the system. I get a run of four or five of them, then I'll go several days of nothing. In the scheme of things, we are not an "high impact" shop in terms of database usage and concurrent connections. I'm completely perplexed on this one.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[yelworcm]

If you can, toss in a check of @@trancount just before the insert into the Audit table. I am hoping you will see that it is coming up one or two higher than you expect. If @@trancount = 0 at the beginning of the procedure, then you are right, there should be no deadlock.

If it is 0, then there has to be some sort of problem with how the system is determining statistics on one or more unique indexes. In effect, one insert would be reading the index for conflicts, while the other insert would be trying to modify it (rare, but it can happen). Have a look at the last stats update date for the unique indexes (heck, toss in the others, as well), and see if any on the audit table are wildly out of date:

select object_name(object_id), name, stats_date(object_id, index_id)
from sys.indexes
where is_unique = 1
  and object_id > 1000

 

[ousoonerjoe]

UPDATE:

It appears that it is an issue with the Indexes. 1 PK on an Identity Seed, 1 on a Date field, and 2 on VARCHAR fields with a 90% Fill Factor. It would seem as the indexes were splitting and re-ordering on INSERTS, the heavy traffic of that table couldn't keep up. Once we dropped the two VARCHAR indexes, all deadlocks vanished.



--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------