锁定位 | InsertTrigger产生的表锁

本文仅记录，方便日后再遇到类似问题时，快速定位。

SQLServer检查会话存在的锁：

-- 从系统健康会话中提取死锁图
SELECT XEvent.query('.') AS DeadlockGraph
FROM (
    SELECT CAST(target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE s.name = 'system_health'
      AND st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)

可能返回如下结果：<?xml version="1.0" encoding="utf-8"?>

<event name="xml_deadlock_report" package="sqlserver" timestamp="2025-11-24T08:23:11.451Z">
  <data name="xml_report">
    <type name="xml" package="package0"/>
    <value>
      <deadlock>
        <victim-list>
          <victimProcess id="process599508188"/>
        </victim-list>
        <process-list>
          <process id="process599508188" taskpriority="0" logused="1632" waitresource="PAGE: 5:1:11247114 " waittime="4712" ownerId="11676356696" transactionname="user_transaction" lasttranstarted="2025-11-24T16:23:06.313" XDES="0x9064a3d20" lockMode="S" schedulerid="5" kpid="6788" status="suspended" spid="374" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2025-11-24T16:23:06.517" lastbatchcompleted="2025-11-24T16:23:06.317" lastattention="1900-01-01T00:00:00.317" clientapp=".Net SqlClient Data Provider" hostname="ECS-9E4A-101920" hostpid="11352" isolationlevel="read committed (2)" xactid="11676356696" currentdb="5" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">
            <executionStack>
              <frame procname="JianSun.dbo.insertTicket" line="6" stmtstart="350" stmtend="754" sqlhandle="0x03000500c9714011cb6af000d0b1000000000000000000000000000000000000000000000000000000000000">if (select count(*) from fys_Ticketing where [Delete] = 0 and OrderNum = @orderNum and ((Type &lt;&gt; @type and Type in ('1', '2', '3') and @type in ('1', '2', '3')) or (Type = @type and @type &lt;&gt; '5'))) &gt; 1</frame>
              <frame procname="adhoc" line="1" stmtstart="1512" stmtend="3954" sqlhandle="0x020000003a1a4e1e3b1d5e3882f9223d909d1875f4f9b5a10000000000000000000000000000000000000000">INSERT [dbo].[fys_Ticketing]([SubmitTime], [CheckTime], [BuyersNick], [ShopId], [TicketRise], [TicketTxt], [invoiceNum], [Num], [PayMent], [FinalPayMent], [BuyersName], [Address], [Mobile], [TaxNum], [RegisterRing], [KindUser], [Nots], [StatusId], [Type], [SkuCode], [SkuName], [Picture], [ReturnNum], [OrderNum], [Operation], [ErrorId], [Delete], [Province], [City], [Area], [ErrorReason], [ResumeID], [ReturnOrder], [TickeTascription], [EMailbox], [TicketTxtId], [ReopenID], [ReopenThree], [WrongPerson], [TicketingNots], [SourceGuid], [Expressno], [ReturnState], [ShopCode], [TicketingNum], [ShipTIme], [installcost], [installComplete], [Reopensgn], [addrsgn], [Revokesgn], [kfsh], [cwsh], [copynew], [copycount], [PriceDifference], [Add], [sendsgn], [RegisterPhone], [KindAccountID], [ticketNots], [xwPushStatus], [xwPushRes], [headType], [TicketSkus], [TicketUnits]) VALUES (@0, NULL, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14, @15, @16, @17, @18, @19, @20, NULL, @21, @22, NULL, @23, @24, @25, @26,</frame>
              <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">unknown</frame>
            </executionStack>
            <inputbuf>(@0 datetime2(7),@1 nvarchar(max) ,@2 bigint,@3 nvarchar(max) ,@4 nvarchar(max) ,@5 int,@6 int,@7 decimal(18,2),@8 decimal(18,2),@9 nvarchar(max) ,@10 nvarchar(max) ,@11 nvarchar(max) ,@12 nvarchar(max) ,@13 nvarchar(max) ,@14 nvarchar(max) ,@15 nvarchar(max) ,@16 int,@17 nvarchar(max) ,@18 nvarchar(max) ,@19 nvarchar(max) ,@20 nvarchar(max) ,@21 nvarchar(max) ,@22 nvarchar(max) ,@23 bit,@24 nvarchar(max) ,@25 nvarchar(max) ,@26 nvarchar(max) ,@27 nvarchar(max) ,@28 nvarchar(max) ,@29 nvarchar(max) ,@30 nvarchar(max) ,@31 nvarchar(max) ,@32 nvarchar(max) ,@33 nvarchar(max) ,@34 nvarchar(max) ,@35 datetime2(7),@36 bit,@37 bit,@38 int,@39 bit,@40 nvarchar(max) ,@41 nvarchar(max) ,@42 nvarchar(max) ,@43 tinyint,@44 nvarchar(max) ,@45 nvarchar(max) )INSERT [dbo].[fys_Ticketing]([SubmitTime], [CheckTime], [BuyersNick], [ShopId], [TicketRise], [TicketTxt], [invoiceNum], [Num], [PayMent], [FinalPayMent], [BuyersName], [Address], [Mobile], [TaxNum], [RegisterRing], [KindUser], [Nots], [StatusId], [Type], [SkuCode],</inputbuf>
          </process>
          <process id="process585e9a558" taskpriority="0" logused="717608" waitresource="PAGE: 5:1:11420220 " waittime="3566" ownerId="11676356758" transactionname="UPDATE" lasttranstarted="2025-11-24T16:23:06.670" XDES="0xe530e0d28" lockMode="IX" schedulerid="6" kpid="10260" status="suspended" spid="204" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2025-11-24T16:23:06.673" lastbatchcompleted="2025-11-24T16:23:06.673" lastattention="1900-01-01T00:00:00.673" clientapp=".Net SqlClient Data Provider" hostname="ECM-70E5" hostpid="14548" loginname="jsApplication" isolationlevel="read committed (2)" xactid="11676356758" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
              <frame procname="adhoc" line="1" stmtstart="56" sqlhandle="0x02000000fd8f66095c177e64b77e419a963bc5e91a6874a00000000000000000000000000000000000000000">update fys_Ticketing set TaxNum = Replace(TaxNum,' ',''),RegisterRing = Replace(RegisterRing,' ',''),KindUser = Replace(KindUser,' ','')</frame>
              <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">unknown</frame>
            </executionStack>
            <inputbuf>(@Parameter1 nvarchar(4000))update fys_Ticketing set TaxNum = Replace(TaxNum,' ',''),RegisterRing = Replace(RegisterRing,' ',''),KindUser = Replace(KindUser,' ','')</inputbuf>
          </process>
        </process-list>
        <resource-list>
          <pagelock fileid="1" pageid="11247114" dbid="5" subresource="FULL" objectname="JianSun.dbo.fys_Ticketing" id="lock91c2c6c80" mode="IX" associatedObjectId="72057594181189632">
            <owner-list>
              <owner id="process585e9a558" mode="IX"/>
            </owner-list>
            <waiter-list>
              <waiter id="process599508188" mode="S" requestType="wait"/>
            </waiter-list>
          </pagelock>
          <pagelock fileid="1" pageid="11420220" dbid="5" subresource="FULL" objectname="JianSun.dbo.fys_Ticketing" id="lockb65438980" mode="SIU" associatedObjectId="72057594181189632">
            <owner-list>
              <owner id="process599508188" mode="S"/>
            </owner-list>
            <waiter-list>
              <waiter id="process585e9a558" mode="IX" requestType="convert"/>
            </waiter-list>
          </pagelock>
        </resource-list>
      </deadlock>
    </value>
  </data>
</event>

发现产生了更新和插入的锁，其中一个看起来是存储过程JianSun.dbo.insertTicket。

那么到底是哪里出现的锁呢？

再执行定义表：

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.insertTicket'));

发现原来是前辈写的一个插入后置触发器~

create trigger insertTicket
on fys_Ticketing for insert
as
  declare @orderNum varchar(255), @type varchar(2)
  select @orderNum = OrderNum, @type = Type from inserted
  if (select count(*) from fys_Ticketing where [Delete] = 0 and OrderNum = @orderNum and ((Type <> @type and Type in ('1', '2', '3') and @type in ('1', '2', '3')) or (Type = @type and @type <> '5'))) > 1
begin
  print '已经存在'
  rollback transaction
end

改触发器和如下语句产生了表级锁，导致经常报错~

update fys_Ticketing set TaxNum = Replace(TaxNum,' ',''),RegisterRing = Replace(RegisterRing,' ',''),KindUser = Replace(KindUser,' ','')

后续的计划是将该触发器移动到应用层进行处理。