deadlocks with insertions into same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

deadlocks with insertions into same table

Author

Topic

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2011-10-05 : 17:18:10

One of my applications inserts a row into a status table. A trigger on the table fires so that when the status id inserted is a certain value it will call a sproc which inserts a row into an accounting table. This whole thing is wrapped in a transaction so it should all be getting rolled back when there are errors.
On occasion two threads of the application insert into the status table at nearly the same millisecond, and when both end up calling the sproc to insert into the accounting table a deadlock occurs and one is killed. SQL Server 2008 R2 appears to be taking page locks when the deadlocks occur. Do I need to tell the sproc to only use row locks instead of page locks?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-05 : 17:33:58

Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

DBCC TRACEON(1222,-1)

--
Gail Shaw
SQL Server MVP

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2011-10-05 : 17:43:43

I have about 50 lines of XML from the SQL Deadlock Detector application and I have an image from Profiler, do you want to see both or just one?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-05 : 17:46:26

The XML. An image is near-useless as it won't show all the additional properties.

--
Gail Shaw
SQL Server MVP

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2011-10-05 : 17:52:31

<EVENT_INSTANCE>
<EventType>DEADLOCK_GRAPH</EventType>
<PostTime>2011-09-30T14:58:01.937</PostTime>
<SPID>29</SPID>
<TextData>
<deadlock-list>
<deadlock victim="processd045288">
<process-list>
<process id="processd045288" taskpriority="0" logused="2216" waitresource="PAGE: 6:3:374592" waittime="1431" ownerId="1055970894" transactionname="user_transaction" lasttranstarted="2011-09-30T14:58:00.380" XDES="0xe67f0ee80" lockMode="IX" schedulerid="39" kpid="10756" status="suspended" spid="243" sbid="2" ecid="0" priority="0" trancount="2"
lastbatchstarted="2011-09-30T14:58:00.393" lastbatchcompleted="2011-09-30T14:58:00.393" clientapp=".Net SqlClient Data Provider"
hostname="SERVR8" hostpid="4892" isolationlevel="serializable (4)" xactid="1055970894" currentdb="6" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="ECOMDB.dbo.CreateGPTransaction" line="11" stmtstart="210" sqlhandle="0x030006000fceef48d90d3801069e00000100000000000000">insert into GPTransaction (SyncTime, TransactionID, TransactionType) values(null, @TranID, @TranType)</frame>
<frame procname="ECOMDB.dbo.NewReportTransaction" line="22" stmtstart="1274" stmtend="1448" sqlhandle="0x030006008116d84acd9a4601639f00000000000000000000">exec CreateGPTransaction @ReportID, 0 </frame>
<frame procname="adhoc" line="1" stmtstart="108" stmtend="406" sqlhandle="0x020000008fa7fc080db75a1b3b76318ace546fb52b6a1bf3">insert [dbo].[ReportStatus]([ReportID], [UserEmail], [StatusID], [StatusTimeStamp], [SubStatusID], [ExpiredDate]) values (@0, @1, @2, @3, @4, null)</frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown</frame>
</executionStack>
<inputbuf>(@0 int,@1 nvarchar(50),@2 int,@3 datetime2(7),@4 int)insert [dbo].[ReportStatus]([ReportID], [UserEmail], [StatusID], [StatusTimeStamp], [SubStatusID], [ExpiredDate]) values (@0, @1, @2, @3, @4, null) select [ReportStatusID], [RowVersion] from [dbo].[ReportStatus] where @@ROWCOUNT > 0 and [ReportStatusID] = scope_identity()</inputbuf>
</process>
<process id="processb08a988" taskpriority="0" logused="2216" waitresource="PAGE: 6:3:374592" waittime="1432" ownerId="1055970836" transactionname="user_transaction" lasttranstarted="2011-09-30T14:58:00.350" XDES="0x800cf410" lockMode="IX" schedulerid="14" kpid="6388" status="suspended" spid="199" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-09-30T14:58:00.363" lastbatchcompleted="2011-09-30T14:58:00.363"
clientapp=".Net SqlClient Data Provider" hostname="SERVR6" hostpid="7140" isolationlevel="serializable (4)" xactid="1055970836" currentdb="6" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128056">
<executionStack>
<frame procname="ECOMDB.dbo.CreateGPTransaction" line="11" stmtstart="210" sqlhandle="0x030006000fceef48d90d3801069e00000100000000000000">insert into GPTransaction (SyncTime, TransactionID, TransactionType) values(null, @TranID, @TranType)</frame>
<frame procname="ECOMDB.dbo.NewReportTransaction" line="22" stmtstart="1274" stmtend="1448" sqlhandle="0x030006008116d84acd9a4601639f00000000000000000000">exec CreateGPTransaction @ReportID, 0 </frame>
<frame procname="adhoc" line="1" stmtstart="108" stmtend="406" sqlhandle="0x020000008fa7fc080db75a1b3b76318ace546fb52b6a1bf3">insert [dbo].[ReportStatus]([ReportID], [UserEmail], [StatusID], [StatusTimeStamp], [SubStatusID], [ExpiredDate]) values (@0, @1, @2, @3, @4, null)</frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">unknown</frame>
</executionStack>
<inputbuf>(@0 int,@1 nvarchar(50),@2 int,@3 datetime2(7),@4 int)insert [dbo].[ReportStatus]([ReportID], [UserEmail], [StatusID], [StatusTimeStamp], [SubStatusID], [ExpiredDate]) values (@0, @1, @2, @3, @4, null) select [ReportStatusID], [RowVersion] from [dbo].[ReportStatus] where @@ROWCOUNT > 0 and [ReportStatusID] = scope_identity()</inputbuf>
</process>
</process-list>
<resource-list>
<pagelock fileid="3" pageid="374592" dbid="6" objectname="ECOMDB.dbo.GPTransaction" id="lock99abe9380" mode="S" associatedObjectId="72057597973233664">
<owner-list>
<owner id="processb08a988" mode="S" />
</owner-list>
<waiter-list>
<waiter id="processd045288" mode="IX" requestType="convert" />
</waiter-list>
</pagelock>
<pagelock fileid="3" pageid="374592" dbid="6" objectname="ECOMDB.dbo.GPTransaction" id="lock99abe9380" mode="S" associatedObjectId="72057597973233664">
<owner-list>
<owner id="processd045288" mode="S" />
</owner-list>
<waiter-list>
<waiter id="processb08a988" mode="IX" requestType="convert" />
</waiter-list>
</pagelock>
</resource-list>
</deadlock>
</deadlock-list>
</TextData>
<TransactionID />
<LoginName>sa</LoginName>
<StartTime>2011-09-30T14:58:01.937</StartTime>
<EventSequence>1641385</EventSequence>
<IsSystem>1</IsSystem>
<SessionLoginName />
</EVENT_INSTANCE>

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-05 : 18:31:26

Ok, the root cause here is a conversion of the locks from shared to IX. IX is not compatible with shared locks.

Are you doing a select anywhere? Can you post the code of ECOMDB.dbo.CreateGPTransaction and ECOMDB.dbo.NewReportTransaction and the definition and indexes on ECOMDB.dbo.GPTransaction?

--
Gail Shaw
SQL Server MVP

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2011-10-05 : 19:40:29

There's so much going on in this database that it's hard to know exactly, but there's a good chance a SELECT is happening against either or both of the tables. NewReportTransaction is the trigger that gets fired from ReportStatus, and that in turn calls CreateGPTransaction (below).

CREATE TRIGGER NewReportTransaction
   ON  ReportStatus
   AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;
declare @StatusID int
declare @ReportID int
declare @ReportStatusID int

SELECT @StatusID = StatusID, @ReportID = ReportID, @ReportStatusID = ReportStatusID from Inserted	
	
if (@StatusID = 5)  
  begin  
    if not exists (select * from GPTransaction where TransactionID = @ReportID and TransactionType = 0)
    exec CreateGPTransaction @ReportID, 0 
  end
END

CREATE PROCEDURE CreateGPTransaction
@TranID int,
@TranType int
AS
SET NOCOUNT ON
insert into GPTransaction (SyncTime, TransactionID, TransactionType) values(null, @TranID, @TranType)


CREATE TABLE GPTransaction(
	[GPTransactionID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[SyncTime] [datetime] NULL,
	[TransactionID] [int] NOT NULL,
	[TransactionType] [int] NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
 CONSTRAINT [PK_GPTransaction] PRIMARY KEY CLUSTERED 
(
	[GPTransactionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-10-05 : 19:49:58

The deadlock xml output indicates this select is occurring:

select [ReportStatusID], [RowVersion] from [dbo].[ReportStatus] where @@ROWCOUNT > 0 and [ReportStatusID] = scope_identity()

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2011-10-05 : 19:57:21

Am I correct that page locks are occurring on the GPTransaction table? I was hoping to disable lock escalation on that table but I'm assuming that won't help since table locks are not the problem. Does ROWLOCK work on INSERTs or would that not solve the problem?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-06 : 04:54:13

Yes, there are page locks, but don't think lock hints yet. Most deadlocks are a result of poor indexing or poor code.

I suspect it's that if exists with the insert after. There's a way of rewriting that into one statement, but first, what indexes on the GPTransactionTable?

--
Gail Shaw
SQL Server MVP

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2011-10-06 : 13:07:54

GPTransaction only has the clustered primary key on the identity column, no other indexes. Putting one on TransactionID and TransactionType seems like a good first step. Would changing the "SELECT * FROM GPTransaction..." to "SELECT 1 FROM GPTransaction" in the IF statement make it so I don't need to include every column in the index?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-06 : 14:10:40

The exists doesn't care about columns and won't use or return any columns.

Yes, put an index on TransactionID and TransactionType. Unique if that combo is (which the exists implies). Without that index, the IF Exists is doing a table scan and in the serialisable isolation level that will be kept to the end of the transaction, meaning any other concurrent insert will deadlock no matter what lock hints you use.

Why serialisable? I can see repeatable read being needed here, but serialisable seems a bit overkill.

--
Gail Shaw
SQL Server MVP

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2011-10-06 : 14:19:08

Thanks! I don't believe my developers explicitly chose Serializable. Either the Entity Framework is doing it automatically or it's happening because we wrapped the calls in a transaction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources