sql server 事务锁死

zYiGJN 2018-05-09 10:39:18

BEGIN TRAN
declare @PREFIX nvarchar(50),@TABLENAME nvarchar(50)
set @PREFIX = '180509'; set @TABLENAME = 'T_HIS_REGISTER'
SET NOCOUNT ON
if ( exists (SELECT 1 FROM T_PK_SAAS WITH(NOLOCK) WHERE PREFIX = @PREFIX AND TABLENAME = @TABLENAME))
BEGIN
update T_PK_SAAS set sn = sn + 1 where PREFIX = @PREFIX AND TABLENAME = @TABLENAME
END
else
BEGIN
IF (EXISTS(SELECT 1 FROM T_PK_SAAS WITH(XLOCK) WHERE PREFIX = @PREFIX AND TABLENAME = @TABLENAME ))
BEGIN
update T_PK_SAAS set sn = sn + 1 where PREFIX = @PREFIX AND TABLENAME = @TABLENAME
END
ELSE
BEGIN
BEGIN TRY
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);
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
END
END
select sn from T_PK_SAAS where PREFIX = @PREFIX AND TABLENAME = @TABLENAME

COMMIT TRAN



就和标题说的并发冲突是生成主键的存储过程会发生事务锁死
...全文
898 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
xiaoxiangqing 2018-05-10
  • 打赏
  • 举报
回复
是不是不是按相同的方式运行语句
zYiGJN 2018-05-09
  • 打赏
  • 举报
回复
引用 6 楼 roy_88 的回复:
查看是否缺少索引,建上索引再试 e.g.
CREATE INDEX IX_T_PK_SAAS  ON T_PK_SAAS (PREFIX ,TABLENAME)INCLUDE(sn);
但是我看在sql执行的时候查看了update与select加的锁都是key锁
zYiGJN 2018-05-09
  • 打赏
  • 举报
回复
引用 4 楼 shoppo0505 的回复:
INSERT INTO T_PK_SAAS 时候,[FID] = @PREFIX + @TABLENAME,这个FID是PK?
中国风 2018-05-09
  • 打赏
  • 举报
回复
查看是否缺少索引,建上索引再试 e.g.
CREATE INDEX IX_T_PK_SAAS  ON T_PK_SAAS (PREFIX ,TABLENAME)INCLUDE(sn);
zYiGJN 2018-05-09
  • 打赏
  • 举报
回复
引用 1 楼 roy_88 的回复:
改为以下优化方法 e.g.
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;
<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>
这是我获取死锁的时候的详细信息
zYiGJN 2018-05-09
  • 打赏
  • 举报
回复
你好,那我能请问一下,为什么一个存储过程内单表的操作,会造成事务锁死,这个看不懂,头疼
shoppo0505 2018-05-09
  • 打赏
  • 举报
回复
INSERT INTO T_PK_SAAS 时候,[FID] = @PREFIX + @TABLENAME,这个FID是PK?
中国风 2018-05-09
  • 打赏
  • 举报
回复
改为以下优化方法 e.g.
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;
zYiGJN 2018-05-09
  • 打赏
  • 举报
回复
引用 8 楼 roy_88 的回复:
测过执行开销时长没?你按照#1/ #6 方法改,出锁死锁的机率应该很低 你要加锁只能在事务开始前加上 持续锁+更新锁 这会造成阻塞,影响性能
我找到原因了就是因为并发先查询存在与否会获取s锁,然后都在转化x锁但是s锁都不释放导致锁死,但是优化的方法就是你说的先update在判断rowcount,谢谢版主大大,结帖了
中国风 2018-05-09
  • 打赏
  • 举报
回复
测过执行开销时长没?你按照#1/ #6 方法改,出锁死锁的机率应该很低 你要加锁只能在事务开始前加上 持续锁+更新锁 这会造成阻塞,影响性能

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧