GriGrim's questions

我在数据库中有一个过程，它将一些数据插入到同一个数据库的DB_A表中。Table_A

这是一个非常简单的过程。但是，我发现运行该过程的进程处于 SUSPENDED 或 RUNNING 状态很长时间，超过 30 分钟。

最奇怪的是，这个过程锁定了另一个数据库中的另外两个与该过程完全不相关的表。

流程如下：

use DB_A;
go

create procedure dbo.spAddUrls @sURL nvarchar(500), @dtDate date
as
begin
    set nocount on;

    declare @nID_ bigint = dbo.ufnGetHash (@sURL);

    begin try
        begin tran
            insert into dbo.TableA (ID, [URL], MapDate)
            values (@nID_, @sURL, @dtDate);
        commit transaction
    end try
    begin catch
        declare @ErrorMsg varchar(1000) = error_message();

        if @@trancount > 0
        begin
            rollback transaction;
            raiserror (@ErrorMsg, 8, 1);
        end
    end catch
end

但我看到数据库中DB_B两个不相关的表上有排他锁Table_B。Table_C

Table_AinDB_A没有任何触发器或外键。

为什么表被DB_B锁了？

注意：所有这些表和过程都是复制的一部分。但我不确定它们是否相关。

我遇到了这样的僵局：

我看到/process/@lockMode等于/owner/@mode。但为什么/keylock/@mode不等于/owner/@mode？是什么/keylock/@mode意思？

正如我从整个死锁中理解的那样，processVictim 既不应该使用 U 锁也不应该使用 X 锁。

这是整个僵局：

<deadlock-list>
 <deadlock victim="processVictim">

  <process-list>
   <process id="processVictim" taskpriority="0" logused="0" waitresource="KEY: 10:72057602742812672 (3100aad8604d)" waittime="2982" ownerId="222541347" transactionname="user_transaction" lasttranstarted="2018-04-28T00:17:29.680" XDES="0x7d6dcf990" lockMode="S" schedulerid="13" kpid="14052" status="background" spid="25" sbid="0" ecid="0" priority="0" trancount="1">
    <executionStack>
     <frame procname="Db1.notify.pRuleActivated" line="71" stmtstart="3296" stmtend="3508" sqlhandle="0x03000e00d9368812ce87f700aaa800000100000000000000">
if exists(select 1 from DbRepl.dbo.vRule where RuleId = @nRuleId_ and CloseDate is not null)     </frame>
     <frame procname="Db1.notify.pRuleActivatedHandler" line="21" stmtstart="928" sqlhandle="0x03000e000f89bc79375d210165a100000100000000000000">
EXEC notify.pRuleActivated
    @xmlMsg = @xmlMsg     </frame>
     <frame procname="Db1.notify.pQppQueueProcNotificationProcessingQueue" line="105" stmtstart="5906" stmtend="6154" sqlhandle="0x03000e0020de2a3dd505d00096a100000100000000000000">
exec @proc_ @xmlMsg = @msg_, @uuidConversationGroup = @cgid_, @uuidConversationHandle = @dh_, @nServiceId = @service_id_;     </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
   </process>

   <process id="processSurvivor" taskpriority="0" logused="149676" waitresource="KEY: 10:72057602742681600 (460029689516)" waittime="2904" ownerId="222536438" transactionname="user_transaction" lasttranstarted="2018-04-28T00:16:28.673" XDES="0x17ea1c780" lockMode="X" schedulerid="12" kpid="9864" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-04-28T00:17:34.753" lastbatchcompleted="2018-04-28T00:17:34.710" isolationlevel="read committed (2)" xactid="222536438" currentdb="10" lockTimeout="4294967295" clientoption1="671156320" clientoption2="128056">
    <executionStack>
     <frame procname="DbRepl.dbo._pReplUpd_dbo_tRule" line="34" stmtstart="1514" stmtend="6938" sqlhandle="0x03000a0067d8b8712176bf00c0a800000100000000000000">
update [dbo].[tRule] set
        [Id] = case substring(@bitmap,1,1) &amp; 2 when 2 then @c2 else [Id] end,
        [TypeId] = case substring(@bitmap,1,1) &amp; 4 when 4 then @c3 else [TypeId] end,
        [EventId] = case substring(@bitmap,1,1) &amp; 8 when 8 then @c4 else [EventId] end
where [RuleId] = @pkc1     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 10 Object Id = 1907939431]    </inputbuf>
   </process>
  </process-list>

  <resource-list>
   <keylock hobtid="72057602742812672" dbid="10" objectname="DbRepl.dbo.tEvent" indexname="PK_tEvent" id="lock261441400" mode="X" associatedObjectId="72057602742812672">
    <owner-list>
     <owner id="processSurvivor" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processVictim" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>

   <keylock hobtid="72057602742681600" dbid="10" objectname="DbRepl.dbo.tRule" indexname="PK_tRule" id="lock476f3d680" mode="U" associatedObjectId="72057602742681600">
    <owner-list>
     <owner id="processVictim" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processSurvivor" mode="X" requestType="convert"/>
    </waiter-list>
   </keylock>
  </resource-list>

 </deadlock>
</deadlock-list>

它的可视化：

我有 3 张桌子。#a是主要的一个和两个辅助表，#b并且#c.

create table #a (a int not null, primary key (a asc)) ;
create table #b (b int not null, primary key (b asc)) ;
create table #c (c int not null, primary key (c asc)) ;

insert into #a (a)
select x*10 + y
from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))x(x)
cross join (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))y(y) ;

insert into #b (b)
select a from #a where a % 5 > 0 ;

insert into #c (c)
select a from #a where a % 4 > 0 ;

如果我#a只用一个辅助表连接主表，查询计划中就会有 Merge Join。

select *
from #a a
inner join #b b on a = b ;

但是，如果我将主表#a与两个辅助表一起加入，则将只有嵌套循环。

select *
from #a a
inner join #b b on a = b
inner join #c c on a = c ;

为什么它会这样工作，我应该怎么做才能获得两个合并连接？

无inner merge join提示。