sql-server - 我怎样才能摆脱这个僵局？

Question

我有 2 个查询涉及下面死锁图中显示的死锁情况。 (Seitensperre 表示页面锁定)

进程 55 中的查询是死锁牺牲品。它是一个包含表订单和付款的选择。

进程 95 上的查询有几个查询一开始 ist 做了几个选择来将一些值存储到变量中(访问表顺序)然后它会更新餐 table 顺序，并在该餐 table 付款后更新。

我不明白这种情况怎么会出现死锁。你能解释一下死锁是什么造成的，我能做些什么吗？我想我只是很难阅读死锁图。

这里是涉及的资源。

<resource-list>
   <objectlock lockPartition="0" objid="1104059019" subresource="FULL" dbid="9" objectname="mycompany.dbo.order" id="lock1b9596980" mode="S" associatedObjectId="1104059019">
    <owner-list>
     <owner id="process443bac8" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process20fc5eda8" mode="IX" requestType="wait"/>
    </waiter-list>
   </objectlock>
   <pagelock fileid="1" pageid="1825971" dbid="9" objectname="mycompany.dbo.Payment" id="lock1bca33000" mode="IX" associatedObjectId="72057594063159296">
    <owner-list>
     <owner id="process20fc5eda8" mode="IX"/>
    </owner-list>
    <waiter-list>
     <waiter id="process443bac8" mode="S" requestType="wait"/>
    </waiter-list>
   </pagelock>
  </resource-list>

编辑

这是更新查询(进程 95)

ALTER   PROCEDURE [dbo].[updateOrderDetails]
(
    @id_order               int,
    @customerComment        NText,
    @salutationBilling      nvarchar(50) = '00',
    @companyNameBilling     nvarchar(100)= ''

        ...some more Parameters
)
AS
DECLARE @user_change int, @id_orderAddress int,
        @id_voucherType int, @id_orderPayment int, @id_paymentMode int


SET NOCOUNT ON;
SET ANSI_NULLS ON

SELECT @user_change = 0
SELECT @id_orderAddress = 0
SELECT @id_voucherType = 0
SELECT @id_orderPayment = 0
SELECT @id_paymentMode = 0


SELECT @user_change = id FROM user
 WHERE logonName = @user_str

SELECT @id_orderAddress = id_orderAddress FROM order
 WHERE [id] = @id_order

SELECT @id_voucherType = [id] FROM voucherType
 WHERE [name] = @voucherTypeName


SELECT @id_orderPayment = [id_orderPayment] FROM order 
 WHERE [id] = @id_order 

SELECT @id_paymentMode = [id] FROM paymentMode
 WHERE [name] = @paymentModeName


IF @user_change = 0 GOTO ERR
IF @id_voucherType = 0 GOTO ERR

UPDATE order
SET
[id_voucherType]        =       @id_voucherType,
[customerComment]       =       @customerComment,
[causeOfCancellation]   =       @causeOfCancellation
...some more fields to update

WHERE
[id] = @id_order

IF @id_orderAddress = 0 GOTO ERR

UPDATE Address
SET
[salutationBilling]     =    @salutationBilling,
[companyNameBilling]    =    @companyNameBilling,
[firstNameBilling]      =    @firstNameBilling
...some more fields to update
WHERE
[id] = @id_orderAddress

IF @id_orderPayment = 0 OR @id_paymentMode = 0 GOTO ERR
UPDATE Payment
SET
[id_paymentMode]        =   @id_paymentMode,
[customerBankDepositor] =   @customerBankDepositor,
[customerBank]          =   @customerBank,
[customerBankCode]      =   @customerBankCode,
...some more fields to update
WHERE
[id] = @id_orderPayment

IF @@Error > 0 Goto ERR


RETURN 0

ERR:

return  -1;
SET QUOTED_IDENTIFIER ON

这是选择查询(过程 55)

ALTER  PROCEDURE [dbo].[searchOrders]
(
  @SelectType           INT
 ,@searchB2B            INT
 ,@VoucherNumber        NVARCHAR(50)  = null
 ,@FirstNameBilling     NVARCHAR(100) = null
    ... some more parameters
)

AS

SET NOCOUNT ON;

IF  @SelectType = 0 and LEN(@VoucherNumber) > 0
 BEGIN
    SELECT DISTINCT  (o.id)
                    ,o.voucherNumber
                    ...some more columns

    FROM             order AS o
    LEFT JOIN        orderAssignment AS oa  ON o.id = oa.id_order
    LEFT JOIN        voucherType AS vt      ON o.id_voucherType = vt.id
    LEFT JOIN        Payment AS op      ON o.id_orderPayment = op.id
    LEFT JOIN        paymentMode AS pm      ON op.id_paymentMode = pm.id
    LEFT JOIN        orderAddress AS addr   ON o.id_orderAddress = addr.id
    LEFT JOIN        user AS u1             ON o.user_change = u1.id
    LEFT JOIN        user as u2             ON oa.id_user = u2.id   
    LEFT JOIN        b2bAccount as b2b      ON o.id_b2bAccount = b2b.id


    WHERE            o.voucherNumber like @VoucherNumber
    AND              o.isB2B = @searchB2B
 END
...some more cases depending on @SelectType but the actual query is with @SelectType = 0

RETURN

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON

Answer 1

如果您能发布每个过程中涉及的语句，那将会很有帮助，但是这里...

如果 SELECT 语句进行聚合，它们可以获得表锁。如果您的用例允许，您可以尝试使用 WITH(NOLOCK) 提示。

UPDATE 语句实际上取决于被修改记录的范围 - WHERE 条件的选择性以及它是否有效地使用索引。

如果涉及的表有任何触发器，您还需要仔细检查该代码。根据我的经验，它们是导致死锁情况的最常见原因。特别是当所发布的声明表面上看起来比较简单时。如果确实找到了触发器，请尝试禁用它们并在测试环境中运行这两个语句以验证它们是否导致了死锁。

不过，死锁终究是无法避免的。您最好尽可能优化，但始终处理一个或两个语句被选为受害者的情况，并优雅地清理/现有或重试批处理。