我怎样才能摆脱这个僵局呢？

Question

我有 2 个查询涉及下面的死锁图中显示的死锁情况。 （Seitensperre 的意思是页锁）

进程 55 中的查询是死锁受害者。这是一个包含餐桌订单和付款的选择。

进程 95 上的查询有几个查询 一开始，ist 进行了几次选择，将一些值存储到变量中（访问表顺序） 然后它会更新餐桌顺序以及餐桌付款后的情况。

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

以下是涉及的资源。

<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 条件的选择性以及是否有效地使用索引。

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

但最终，僵局并不能总是避免。您最好尽可能地进行优化，但始终处理选择一个或两个语句作为受害者的情况，并优雅地清理/现有的，或重试批处理。

Answer 2

最后发现是调用代码的问题。这很难解释，但基本上一个交易被打开并移交给几个方法。

我删除了交易，僵局就消失了。

当然，我必须再次重新实现事务，看看是否确实是事务处理的实现方式存在问题，或者事务是否跨度太长。

Answer 3

在事务内部，将查询更改

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

为如下所示：

SELECT @id_orderAddress = id_orderAddress FROM order WITH(UPDLOCK)
 WHERE [id] = @id_order

这可确保同一“订单”行的后续更新不会导致死锁 - “订单”行在事务开始时已被锁定。