October 9, 2014 at 8:56 am
Hi All,
I am not particularly experienced with deadlocks, but I can't quite figure out what's going on here. With my limited understanding, the output below makes it appear as if the two processes both have an exclusive (X) lock on the same object, which is obviously impossible. Any ideas what might be going on here?
- <deadlock-list>
- <deadlock victim="process2d6e40748">
- <process-list>
- <process id="process2d6e40748" taskpriority="0" logused="236" waitresource="KEY: 194:320409990135808 (9b2baf00c0e8)" waittime="3047" ownerId="15144822439" transactionname="user_transaction" lasttranstarted="2014-10-09T15:10:46.877" XDES="0x4278383b0" lockMode="U" schedulerid="8" kpid="12928" status="suspended" spid="783" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-10-09T15:10:46.877" lastbatchcompleted="2014-10-09T15:10:46.863" lastattention="2014-10-09T15:10:46.877" clientapp="TASK" hostname="2008SEQ" hostpid="9416" loginname="SS" isolationlevel="read committed (2)" xactid="15144822439" currentdb="194" lockTimeout="4294967295" clientoption1="134217760" clientoption2="16416">
- <executionStack>
<frame procname="TASK.dbo.TK_GL_GET_NEXT_TRANS_INT_REF" line="1" stmtstart="372" stmtend="726" sqlhandle="0x0300c2001ab94862e4f5270194a300000100000000000000">UPDATE GL_SEQUENCE SET @NEXT_TRANS_INT_REF = SEQ_NEXT_TRANS = SEQ_NEXT_TRANS + 1 WHERE SEQ_COMPANY = @COMPANY AND SEQ_LEDGER = @LEDGER AND SEQ_TYPE = @ASEQ_TYPE</frame>
</executionStack>
<inputbuf>Proc [Database Id = 194 Object Id = 1648933146]</inputbuf>
</process>
- <process id="process9ed707b88" taskpriority="0" logused="26820" waitresource="KEY: 194:320409990135808 (b81181109ebc)" waittime="2973" ownerId="15144821756" transactionname="user_transaction" lasttranstarted="2014-10-09T15:10:46.313" XDES="0x8418c6e80" lockMode="U" schedulerid="5" kpid="3144" status="suspended" spid="670" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2014-10-09T15:10:46.950" lastbatchcompleted="2014-10-09T15:10:46.880" lastattention="2014-10-09T15:10:46.863" clientapp="TASK" hostname="2008SEQ" hostpid="11012" loginname="JS4" isolationlevel="read uncommitted (1)" xactid="15144821756" currentdb="194" lockTimeout="4294967295" clientoption1="134217760" clientoption2="16416">
- <executionStack>
<frame procname="TASK.dbo.TK_GL_GET_NEXT_TRANS_INT_REF" line="1" stmtstart="372" stmtend="726" sqlhandle="0x0300c2001ab94862e4f5270194a300000100000000000000">UPDATE GL_SEQUENCE SET @NEXT_TRANS_INT_REF = SEQ_NEXT_TRANS = SEQ_NEXT_TRANS + 1 WHERE SEQ_COMPANY = @COMPANY AND SEQ_LEDGER = @LEDGER AND SEQ_TYPE = @ASEQ_TYPE</frame>
</executionStack>
<inputbuf>Proc [Database Id = 194 Object Id = 1648933146]</inputbuf>
</process>
</process-list>
- <resource-list>
- <keylock hobtid="320409990135808" dbid="194" objectname="TASK.dbo.GL_SEQUENCE" indexname="PRI_SEQUENCE" id="lock84a7a5d80" mode="X" associatedObjectId="320409990135808">
- <owner-list>
<owner id="process9ed707b88" mode="X" />
</owner-list>
- <waiter-list>
<waiter id="process2d6e40748" mode="U" requestType="wait" />
</waiter-list>
</keylock>
- <keylock hobtid="320409990135808" dbid="194" objectname="TASK.dbo.GL_SEQUENCE" indexname="PRI_SEQUENCE" id="locke494f6a00" mode="X" associatedObjectId="320409990135808">
- <owner-list>
<owner id="process2d6e40748" mode="X" />
</owner-list>
- <waiter-list>
<waiter id="process9ed707b88" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
Thanks,
Matt
October 9, 2014 at 9:04 am
They're key locks. So the two processes each have an exclusive lock on a different row of the same index. Perfectly possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2014 at 9:07 am
Aaaah I see - many thanks....still getting my head round these a bit...!
October 10, 2014 at 4:29 am
Sorry just one more thing that's puzzling me about this....
The processes become deadlocked when both are attempting to run the following query:
UPDATE GL_SEQUENCE SET @NEXT_TRANS_INT_REF = SEQ_NEXT_TRANS = SEQ_NEXT_TRANS + 1 WHERE SEQ_COMPANY = @COMPANY AND SEQ_LEDGER = @LEDGER AND SEQ_TYPE = @ASEQ_TYPE
The thing confusing me is that, although not actually enforced by a composite key (no idea why, but I've checked there are no duplicates), the combination of SEQ_COMPANY, SEQ_LEDGER and SEQ_TYPE is unique to each row. Therefore, surely each instance of the query should only be locking a single row. Obviously, if all they were trying to do was update the same row at the same time with no other locks then this would result in a wait rather than a deadlock.
This is the whole transaction, which is contained within a stored procedure:
BEGIN TRANSACTION
UPDATE GL_SEQUENCE
SET @NEXT_TRANS_INT_REF = SEQ_NEXT_TRANS = SEQ_NEXT_TRANS + 1
WHERE SEQ_COMPANY = @COMPANY
AND SEQ_LEDGER = @LEDGER
AND SEQ_TYPE = @ASEQ_TYPE
if (@@error <> 0)
begin
ROLLBACK
RAISERROR ('UPDATE for Company %s, Ledger %s and SeqType %s raised error %d',
18, -1, @COMPANY, @LEDGER, @ASEQ_TYPE, @@ERROR, 0)
return(1)
end
SET @NEXT_TRANS_INT_REF = @NEXT_TRANS_INT_REF - 1
COMMIT TRANSACTION
If there was another part of the transaction which was locking additional rows in the table then I could understand how it could become deadlocked, but when both transactions are simply attempting to run a single UPDATE on a single row, then I can't figure it out. The only thing I can think of is that this stored procedure is called from within another transaction, and it is that transaction that is holding the lock on the other row(s) within that table....is that possible?! Or am I missing something obvious? (Wouldn't be the first time...)
October 10, 2014 at 4:39 am
Table definition, index definition please?
SQL may well be locking more than one row because it doesn't know that's unique and/or because there's no useful index for it to access that row by.
If the combo is unique, can you add a unique constraint?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 10, 2014 at 4:55 am
Table definition:
[TK_ROWID] [int] NOT NULL,
[SEQ_COMPANY] [varchar](6) NOT NULL,
[SEQ_LEDGER] [varchar](6) NOT NULL,
[SEQ_TYPE] [varchar](6) NOT NULL,
[SEQ_DESCRIPTION] [varchar](40) NULL,
[SEQ_SHORT_DESC] [varchar](20) NULL,
[SEQ_NEXT_BATCH] [int] NULL,
[SEQ_NEXT_TRANS] [int] NULL,
[SEQ_ACTIVE] [varchar](1) NULL,
CONSTRAINT [PRI_SEQUENCE] PRIMARY KEY CLUSTERED
(
[TK_ROWID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
Index definition:
NONCLUSTERED INDEX [IDX_GL_SEQUENCE1] ON [dbo].[GL_SEQUENCE]
(
[SEQ_TYPE] ASC,
[SEQ_LEDGER] ASC,
[SEQ_COMPANY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
Those are the only indexes/keys that currently exist on that table. I could possibly add a constraint, although I would have to check with the software suppliers first I guess...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply