主页 / dba / 问题 / 1726
Accepted
Sathyajith Bhat
Asked: 2011-03-15 01:11:07 +0800 CST

跟踪、调试和修复行锁争用

  • 772

迟到了，我一直面临着很多行锁争用。争用的表似乎是一个特定的表。

这通常会发生 -

  • 开发人员 1 从 Oracle Forms 前端屏幕启动事务
  • 开发人员 2 从使用相同屏幕的不同会话开始另一个事务

大约 5 分钟后，前端似乎没有响应。检查会话显示行锁争用。每个人都抛出的“解决方案”是杀死会话：/

作为数据库开发人员

  • 可以做些什么来消除行锁争用？
  • 是否有可能找出存储过程的哪一行导致这些行锁争用
  • 减少/避免/消除此类编码问题的一般准则是什么？

如果这个问题感觉太开放/信息不足，请随时编辑/让我知道 - 我会尽力添加一些额外的信息。

有问题的表有很多插入和更新，我会说它是最繁忙的表之一。SP 相当复杂——为了简化——它从各种表中获取数据，将其填充到工作表中，在工作表上发生大量算术运算，并将工作表的结果插入/更新到相关表中。

数据库版本是 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit。逻辑流在两个会话中以相同的顺序执行，事务不会保持打开太久（或者至少我认为是这样），并且锁定发生在事务的主动执行期间。

更新：表格行数比我预期的要多，大约 310 万行。此外，在跟踪会话后，我发现该表的几个更新语句没有使用索引。为什么会这样——我不确定。where 子句中引用的列已编入索引。我目前正在重建索引。

oracle-10g locked-objects

3 个回答

  1. Best Answer

    是否有可能找出存储过程的哪一行导致了这些行锁争用？

    不完全是，但您可以获得导致锁定的 SQL 语句，进而识别过程中的相关行。

    SELECT sid, sql_text
FROM v$session s
LEFT JOIN v$sql q ON q.sql_id=s.sql_id
WHERE state = 'WAITING' AND wait_class != 'Idle'
AND event = 'enq: TX - row lock contention';

    减少/避免/消除此类编码问题的一般准则是什么？

    关于锁的Oracle 概念指南部分说，“只有在写入者修改行时才会锁定行”。另一个更新同一行的会话将等待第一个会话COMMIT或ROLLBACK在它可以继续之前。要消除问题，您可以对用户进行序列化，但这里有一些可以将问题减少到不成问题的程度。

    • COMMIT更频繁。每次都会COMMIT释放锁，因此如果您可以批量进行更新，那么另一个会话需要同一行的可能性就会降低。
    • 确保在不更改其值的情况下更新任何行。例如，UPDATE t1 SET f1=DECODE(f2,’a’,f1+1,f1);应该重写为更具选择性（读更少的锁）UPDATE t1 SET f1=f1+1 WHERE f2=’a’;。当然，如果更改语句仍会锁定表中的大多数行，那么更改只会带来可读性优势。
    • 确保您使用的是序列而不是锁定表以将最高当前值加一。
    • 确保您没有使用导致索引不被使用的函数。如果该函数是必要的，请考虑将其设为基于函数的索引。
    • 分组思考。考虑是否可以将运行执行更新的 PL/SQL 块的循环重写为单个更新语句。如果不是，那么也许批量处理可以与BULK COLLECT ... FORALL.
    • UPDATE减少第一个和第一个之间完成的工作COMMIT。例如，如果代码在每次更新后发送一封电子邮件，请考虑将电子邮件排队并在提交更新后发送。
    • 设计应用程序以通过执行SELECT ... FOR UPDATE NOWAITor来处理等待WAIT 2。然后，您可以发现无法锁定行并通知用户另一个会话正在修改相同的数据。
    • 10

  2. 我将从开发人员的角度提供答案。

    在我看来，当您遇到像您所描述的那样的行争用时，那是因为您的应用程序中有错误。在大多数情况下，这种类型的争用是丢失更新漏洞的标志。AskTom 上的这个帖子解释了丢失更新的概念：

    在以下情况下会发生丢失更新：

    第 1 节：读出汤姆的员工记录

    会话 2：读出汤姆的员工记录

    会话 1：更新 Tom 的员工记录

    会话 2：更新 Tom 的员工记录

    会话 2 将覆盖会话 1 的更改而不会看到它们——导致更新丢失。

    您经历了丢失更新的一个令人讨厌的副作用：会话 2 可能会被阻止，因为会话 1 尚未提交。然而，主要问题是会话 2 盲目地更新记录。假设两个会话都发出以下语句：

    UPDATE table SET col1=:col1, ..., coln=:coln WHERE id = :pk

    在这两个语句之后，会话 1 的修改已被覆盖，而会话 2 没有被通知该行已被会话 1 修改。

    丢失更新（以及争用的副作用）永远不会发生，它们是 100% 可以避免的。您应该通过两种主要方法使用锁定来防止它们：乐观锁定和悲观锁定。

    1）悲观锁定

    你想更新一行。在这种模式下，您将通过请求锁定该行（SELECT ... FOR UPDATE NOWAIT语句）来防止其他人修改该行。如果该行已被修改，您将收到一条错误消息，您可以优雅地将其翻译给最终用户（该行正在被另一个用户修改）。如果该行可用，请进行修改（更新），然后在事务完成时提交。

    2) 乐观锁定

    你想更新一行。但是，您不想保持对该行的锁定，可能是因为您使用多个事务来更新该行（基于 Web 的无状态应用程序），或者您可能不希望任何用户持有锁定太久（这可能会导致其他人被阻止）。在这种情况下，您不会立即请求锁定。您将使用标记来确保在发布更新时该行没有更改。您可以缓存所有列的值，也可以使用自动更新的时间戳列或基于序列的列。无论您选择什么，当您即将执行更新时，您将通过发出如下查询来确保该行上的标记没有更改：

    SELECT <...>
  FROM table
 WHERE id = :id
   AND marker = :marker
   FOR UPDATE NOWAIT

    如果查询返回一行，请进行更新。如果没有，这意味着有人在您上次查询后修改了该行。您必须从头开始重新启动该过程。

    注意：如果您完全信任访问您的数据库的所有应用程序，则可以依赖直接更新来进行乐观锁定。您可以直接发出：

    UPDATE table
   SET <...>, 
       marker = marker + 1
 WHERE id = :id;

    如果语句没有更新任何行，您就知道有人更改了该行，您需要从头开始。

    如果所有的应用程序都同意这个方案，你就永远不会被别人阻止，你也可以避免盲目更新。但是，如果您没有预先锁定行，如果另一个应用程序、批处理作业或直接更新没有实现乐观锁定，您仍然容易受到无限期锁定的影响。这就是为什么我建议始终锁定该行，无论您选择何种锁定方案（性能损失可以忽略不计，因为您在锁定行时检索了包括 rowid 在内的所有值）。

    TL;博士

    • 在没有事先锁定的情况下更新一行会使应用程序面临潜在的“冻结”。如果对 DB 的所有 DML 都实施乐观或悲观锁定，则可以避免这种情况。
    • 验证 SELECT 语句返回的值是否与任何先前的 SELECT 一致（以避免任何丢失更新问题）
    • 7

  3. 这个答案可能有资格进入 The Daily WTF。

    对，在跟踪会话并搜索之后USER_SOURCE-我找到了根本原因

    • 不出所料，原因是有缺陷的逻辑
    • 最近，在 SP 中添加了更新语句。update 语句基本上会更新整个表。显然，有问题的开发人员忘记了添加正确的 where 子句来更新所需的语句。
    • 如上所述，正在更新的表是交易量最大的表之一，并且有大量记录。更新将花费很长时间，令人痛苦。
    • 结果是其他会话无法获得表上的锁，并且将处于行锁争用中。
    • 5

相关问题