数据库锁定问题

发布于 2024-08-10 13:06:54 字数 1225 浏览 7 评论 0原文

请。我们在生产数据库上获得了大量锁定，该数据库最近的流量大幅增加。我们使用 IdeaBlade 进行大部分数据访问。

我使用 Sql Profiler 得到以下跟踪：

deadlock victim="process84af28"
  resource-list
    keylock hobtid="72057594096451584" dbid="6" objectname="cpc_db.dbo.Prefix_ChildTableName" indexname="PK_Prefix_ChildTableName" id="lock45982ac0" mode="X" associatedObjectId="72057594096451584"
      owner-list
owner id="processb852e8" mode="X" 
   owner-list
   waiter-list
    waiter id="process84af28" mode="S" requestType="wait" 
    waiter id="processb855b8" mode="RangeS-U" requestType="wait" 
   waiter-list
  keylock
  keylock hobtid="72057594096451584" dbid="6" objectname="cpc_db.dbo.Prefix_ChildTableName" indexname="PK_Prefix_ChildTableName" id="lock513c3bc0" mode="RangeS-U" associatedObjectId="72057594096451584"
   owner-list
    owner id="processb855b8" mode="RangeS-U" 
   owner-list
   waiter-list
    waiter id="processb852e8" mode="RangeS-U" requestType="wait" 
   waiter-list
  keylock
 resource-list
deadlock

有人有想法吗？

我不是 DBA，但此跟踪似乎表明：

在子表中的一行上具有独占锁 X 的进程正在尝试获取同一资源上的选择更新锁（似乎并不有意义）
另一个进程具有选择更新锁仍在尝试获取选择更新锁

有人澄清吗？

我们如何才能最大限度地减少或消除僵局？

原文

Pls. we've been getting A LOT of locks on a production database that's recently witnessed substantially increased traffic. We are using IdeaBlade for most of the data access.

I got the following trace using Sql Profiler:

deadlock victim="process84af28"
  resource-list
    keylock hobtid="72057594096451584" dbid="6" objectname="cpc_db.dbo.Prefix_ChildTableName" indexname="PK_Prefix_ChildTableName" id="lock45982ac0" mode="X" associatedObjectId="72057594096451584"
      owner-list
owner id="processb852e8" mode="X" 
   owner-list
   waiter-list
    waiter id="process84af28" mode="S" requestType="wait" 
    waiter id="processb855b8" mode="RangeS-U" requestType="wait" 
   waiter-list
  keylock
  keylock hobtid="72057594096451584" dbid="6" objectname="cpc_db.dbo.Prefix_ChildTableName" indexname="PK_Prefix_ChildTableName" id="lock513c3bc0" mode="RangeS-U" associatedObjectId="72057594096451584"
   owner-list
    owner id="processb855b8" mode="RangeS-U" 
   owner-list
   waiter-list
    waiter id="processb852e8" mode="RangeS-U" requestType="wait" 
   waiter-list
  keylock
 resource-list
deadlock

Ideas anyone?

I'm not a DBA but this trace seems to indicate that:

A process with an exclusive lock X on a row in the Child Table is attempting to acquire a Select-Update lock on the same resource (doesn't seem to make sense)
Another process with a Select-Update lock is still trying to acquire a Select-Update lock

Clarifications anyone?

How can we minimize or eliminate the deadlocks?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

橙幽之幻 2024-08-17 13:06:54

首先需要注意以下几点：

您正在使用可序列化事务< /a>，最严格的悲观锁定形式。很可能，您不需要这个（我们知道您正在使用可序列化事务，因为 KEY 锁仅适用于此隔离级别）。正如 Remus 上面提到的，您绝对应该在此处查看其他最有可能的选项。
上面的输出似乎被截断了一点，您应该有名为 process-list 的部分，其中包含将进程信息映射到 spid 和查询的信息

从上面的输出中可以看出：

processb852e8 owns an exclusive lock on index "cpc_db.dbo.Prefix_ChildTableName.PK_Prefix_ChildTableName"
    process84af28 is waiting for a shared KEY lock
    processb855b8 is also waiting for a Shared Range-Update KEY lock

processb855b8 owns Shared Range-Update lock on index "cpc_db.dbo.Prefix_ChildTableName.PK_Prefix_ChildTableName" (the same index)
    processb852e8 is waiting on a Shared Range-Update KEY lock

独占锁是某种写入（即更新、删除、插入），RangeS-U 锁可能是更新，但如果没有看到映射的信息就无法判断。

Bart Duncan 有几篇关于破译跟踪输出的精彩帖子（如果您掌握了全部内容），请参阅第 1 部分和第 2 部分。您还可以查看并发和脚本的一般概述此处。

Couple of things to note first off:

You're using serializable transactions, the most restrictive form of pessimistic locking. Chances are, you don't need this (we know you are using serializable transactions as KEY locks only apply to this isolation level). As Remus mentions above, you should definitely look into other options here most likely.
It seems like the output above has been truncated a bit, you should have sections called process-list with information mapping the process information to spids and queries

From what you can tell in the output above:

processb852e8 owns an exclusive lock on index "cpc_db.dbo.Prefix_ChildTableName.PK_Prefix_ChildTableName"
    process84af28 is waiting for a shared KEY lock
    processb855b8 is also waiting for a Shared Range-Update KEY lock

processb855b8 owns Shared Range-Update lock on index "cpc_db.dbo.Prefix_ChildTableName.PK_Prefix_ChildTableName" (the same index)
    processb852e8 is waiting on a Shared Range-Update KEY lock

The exclusive lock is a write of some kind (i.e. update, delete, insert), the RangeS-U locks is likely an update, but no way to tell without seeing the information mapped.

Bart Duncan has a couple of great posts on deciphering the trace output if you have it all, see part 1 and part 2. You can also see an overview of concurrency and scripts in general here.

回复收藏 0 原文

忆梦 2024-08-17 13:06:54

模式=“RangeS-U”

范围锁定？停止使用高事务隔离级别。坚持阅读承诺。如果您使用 CLR TransactionScope 对象，请让它们使用 Read Commited 隔离（默认情况下它们使用 Seralized，恶心）。尝试在数据库上打开读已提交快照隔离。请参阅使用快照隔离。

回复收藏 0 原文

草莓酥 2024-08-17 13:06:54

罪魁祸首似乎是：-

owner id="processb855b8" mode="RangeS-U"

这似乎锁定了一组行。
它正在等待 process84af28 重新创建一行，
它正在等待 processb852e8 释放一行
它正在等待第一个进程释放一行。

SQLServer 通过终止中间的进程以允许其他两个进程完成来解决死锁。

您应该查看您的隔离级别。最佳实践是使用最低的
“选择”多行时可用的锁定级别。
仅当您很可能更新当前事务中的行时，才对“选定”行使用更高级别。

并且永远不要在等待外部服务或用户操作时锁定行。

the culprit seems to be:-

owner id="processb855b8" mode="RangeS-U"

This would seem to have locked a set of rows.
Its waiting for a row to be realeaed by process84af28,
which is waiting for a row to be realeased by processb852e8
which is waiting for a row to be released by the first process.

SQLServer is resolving the deadlock by killing the process in the middle allowing the other two to complete.

You should look at your isolation levels. Best practice is to use the lowest
available level of locking when "select"ing multiple rows.
Only use a higher level on a "select"ed row if you are very likely to update the row in the current transaction.

And NEVER, not ever, leave a row locked while waiting for an external service or a user action.

回复收藏 0 原文