我有2个查询与下面的Deadlockgraph中显示的死锁情况有关。 （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
最佳答案
如果您可以发布每个过程中涉及的语句，将很有帮助，但是这里...
如果SELECT语句正在进行聚合，则它们可以获取表锁。如果用例允许，可以尝试使用WITH(NOLOCK)提示。UPDATE语句实际上取决于要修改的记录的范围-WHERE条件的选择性以及它是否有效利用索引。
如果涉及的表具有任何触发器，则还需要仔细检查该代码。以我的经验，它们是造成死锁情况的最常见原因。特别是当发布的声明表面上看起来相对简单时。如果确实找到触发器，请尝试禁用它们并在测试环境中运行这两个语句，以验证它们是否导致了死锁。
最后，尽管始终不能避免死锁。您最好优化自己的功能，但始终要处理以下情况：选择一个或两个语句作为受害者，并优雅地清理/存在或重试该批处理。