22,206
社区成员
发帖
与我相关
我的任务
分享
CREATE INDEX IX_T_PK_SAAS ON T_PK_SAAS (PREFIX ,TABLENAME)INCLUDE(sn);
<deadlock-list>
<deadlock victim="process239236508">
<process-list>
<process id="process239236508" taskpriority="0" logused="268" waitresource="KEY: 5:72057594637189120 (e400f8433bb2)" waittime="2248" ownerId="322950029" transactionname="implicit_transaction" lasttranstarted="2018-05-09T09:22:20.683" XDES="0x13ad99950" lockMode="U" schedulerid="4" kpid="468" status="suspended" spid="153" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2018-05-09T09:22:20.683" lastbatchcompleted="2018-05-09T09:22:20.683" clientapp="Microsoft JDBC Driver for SQL Server" hostname="iZhc0v6l21noh2Z" hostpid="0" loginname="sa" isolationlevel="read committed (2)" xactid="322950029" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
<executionStack>
<frame procname="CYYFHIS.dbo.CrePk" line="8" stmtstart="490" stmtend="662" sqlhandle="0x03000500f4c3cf6874363901d9a800000100000000000000">
update T_PK_SAAS set sn = sn + 1 where PREFIX = @PREFIX AND TABLENAME = @TABLENAME </frame>
<frame procname="adhoc" line="1" stmtstart="130" sqlhandle="0x01000500c9cd9223e07c1aa5000000000000000000000000">
EXEC dbo.CrePk @P0,@P1,@P2 OUT </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000) OUTPUT)EXEC dbo.CrePk @P0,@P1,@P2 OUT </inputbuf>
</process>
<process id="process2455e2bc8" taskpriority="0" logused="560" waitresource="KEY: 5:72057594637189120 (861b25f966bb)" waittime="2054" ownerId="322950027" transactionname="implicit_transaction" lasttranstarted="2018-05-09T09:22:20.653" XDES="0x155bbf950" lockMode="S" schedulerid="2" kpid="11296" status="suspended" spid="98" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-05-09T09:22:20.877" lastbatchcompleted="2018-05-09T09:22:20.877" clientapp="Microsoft JDBC Driver for SQL Server" hostname="iZhc0v6l21noh2Z" hostpid="0" loginname="sa" isolationlevel="read committed (2)" xactid="322950027" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128058">
<executionStack>
<frame procname="CYYFHIS.dbo.CrePk" line="6" stmtstart="280" stmtend="466" sqlhandle="0x03000500f4c3cf6874363901d9a800000100000000000000">
if ( exists (SELECT 1 FROM T_PK_SAAS WHERE PREFIX = @PREFIX AND TABLENAME = @TABLENAME)) </frame>
<frame procname="adhoc" line="1" stmtstart="130" sqlhandle="0x01000500c9cd9223e07c1aa5000000000000000000000000">
EXEC dbo.CrePk @P0,@P1,@P2 OUT </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000) OUTPUT)EXEC dbo.CrePk @P0,@P1,@P2 OUT </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594637189120" dbid="5" objectname="CYYFHIS.dbo.T_PK_SAAS" indexname="PK_T_PK_SAAS" id="lock2197d0980" mode="X" associatedObjectId="72057594637189120">
<owner-list>
<owner id="process2455e2bc8" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process239236508" mode="U" requestType="wait"/>
</waiter-list>
</keylock>
<keylock hobtid="72057594637189120" dbid="5" objectname="CYYFHIS.dbo.T_PK_SAAS" indexname="PK_T_PK_SAAS" id="lock19f182b80" mode="X" associatedObjectId="72057594637189120">
<owner-list>
<owner id="process239236508" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process2455e2bc8" mode="S" requestType="wait"/>
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
这是我获取死锁的时候的详细信息
BEGIN TRY
BEGIN TRAN;
DECLARE @PREFIX NVARCHAR(50)
, @TABLENAME NVARCHAR(50);
SET @PREFIX='180509';
SET @TABLENAME='T_HIS_REGISTER';
SET NOCOUNT ON;
UPDATE T_PK_SAAS
SET sn=sn+1
WHERE PREFIX=@PREFIX
AND TABLENAME=@TABLENAME;
IF @@ROWCOUNT=0
INSERT INTO T_PK_SAAS ([FID]
, [TABLENAME]
, [PREFIX]
, [SERVERID]
, [SN]
, [CURDATE]
, [OPERATOR]
, [OPERATEDATE]
, [OPERATEDEPT]
, [UPFLAG]
, [MYROWID]
, [FYID])
VALUES (@PREFIX+@TABLENAME, @TABLENAME, @PREFIX, NULL, '1', @PREFIX, NULL, GETDATE(), NULL, N'1', NULL, NULL);
SELECT sn
FROM T_PK_SAAS
WHERE PREFIX=@PREFIX
AND TABLENAME=@TABLENAME;
COMMIT TRAN;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE();
ROLLBACK TRAN;
END CATCH;