MySQL中的乐观锁定

Question

我找不到有关MySQL中乐观锁定的任何详细信息。我读到开始事务使两个实体上的更新保持同步，但是，它不会停止两个用户同时更新数据而引起冲突。

显然乐观锁定会解决这个问题吗？这在MySQL中如何应用。是否有SQL语法/关键字呢？还是MySQL具有默认行为？

谢谢你们。

Answer 1

关键是，乐观锁定不是数据库功能，不适用于MySQL或其他功能：乐观锁定是一种使用带有标准指令的DB进行的实践。

让我们有一个非常简单的示例，并说您想用多个用户/客户端可以同时运行的代码来做到这一点：

1. 从具有一个ID字段（iD）和两个数据字段（val1，val2）的行中选择数据
2. （可选）使用数据进行计算
3. 更新该行的数据

NO LOCKING的方式是：

注意：所有代码{在大括号之间}都应位于应用程序代码中，而不是（有必要）在SQL端中

- SELECT iD, val1, val2
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2
       WHERE iD = @theId;
 - {go on with your other code}

最佳锁定方式是：

- SELECT iD, val1, val2
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2
       WHERE iD = @theId
           AND val1 = @oldVal1
           AND val2 = @oldVal2;
 - {if AffectedRows == 1 }
 -     {go on with your other code}
 - {else}
 -     {decide what to do since it has gone bad... in your code}
 - {endif}

请注意，关键点在于UPDATE指令的结构以及后续受影响的行数检查。正是这两件事使您的代码意识到执行SELECT和UPDATE时之间已经有人修改了数据。请注意，所有操作都没有交易！之所以能够这样做（没有事务），是因为这是一个非常简单的示例，但这也表明，乐观锁定的关键不在事务本身中。

那TRANSACTIONS呢？

 - SELECT iD, val1, val2
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - BEGIN TRANSACTION;
 - UPDATE anotherTable
       SET col1 = @newCol1,
           col2 = @newCol2
       WHERE iD = @theId;
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2
       WHERE iD = @theId
           AND val1 = @oldVal1
           AND val2 = @oldVal2;
 - {if AffectedRows == 1 }
 -     COMMIT TRANSACTION;
 -     {go on with your other code}
 - {else}
 -     ROLLBACK TRANSACTION;
 -     {decide what to do since it has gone bad... in your code}
 - {endif}

最后一个示例显示，如果您在某个时刻检查冲突，并且发现已经修改了其他表/行的情况下发生了冲突，那么通过事务，您便可以回滚自此以来所做的所有更改开始。显然，由您（知道您的应用程序在做什么）决定每次可能发生的冲突要回滚的操作量是多少，并以此为基础确定在何处放置事务边界以及在何处检查与特殊对象之间的冲突。
UPDATE + AffectedRows检查。

在这种情况下，对于事务，我们将执行UPDATE的时间与提交UPDATE的时间分开了。那么，当“其他进程”在此时间范围内执行更新时，会发生什么呢？要知道到底发生了什么，需要深入研究隔离级别的细节（以及如何在每个引擎上进行管理）。以带有READ_COMMITTED的Microsoft
SQL
Server为例，更新的行被锁定，直到COMMIT为止，因此“其他进程”不能对该行执行任何操作（保持等待状态），而SELECT（实际上只能执行READ_COMMITTED）
。因此，由于“其他进程”活动被推迟，因此UPDATE将失败。

VERSIONING OPTIMISTIC LOCKING选项：

 - SELECT iD, val1, val2, version
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2,
           version = version + 1
       WHERE iD = @theId
           AND version = @oldversion;
 - {if AffectedRows == 1 }
 -     {go on with your other code}
 - {else}
 -     {decide what to do since it has gone bad... in your code}
 - {endif}

这里显示的是，代替检查所有字段的值是否仍然相同，我们可以使用专用字段（每次执行UPDATE时都会对其进行修改）来查看是否有人比我们要快并且更改了我们之间的行选择和更新。这里没有事务是由于如第一个示例中的简单性，与版本列的使用无关。同样，此列的使用取决于应用程序代码中的实现，而不是数据库引擎功能。

不仅如此，我还认为其他一些问题会使答案变得太长（已经太长），因此我现在仅以一些参考文献提及它们：

• 有关SELECT上的事务影响的事务隔离级别（此处为MySQL）。
• 对于主键未自动生成（或唯一约束）的表上的INSERT，它将自动失败，无需特别检查两个进程是否尝试在必须唯一的位置插入相同的值。
• 如果没有id列（主键或唯一约束），则单个SELECT + UPDATE要求事务，因为与其他人进行修改之后相比，与UPDATE的WHERE子句的条件匹配的行比预期的多，您可能会感到惊讶。

如何检查实践并获得自信

由于隔离级别的值和实现可能有所不同，因此最好的建议（与本站点一样）是在二手平台/环境上进行测试。

这看起来似乎很困难，但实际上，可以在任何数据库开发环境中使用两个独立的窗口轻松完成此操作，并在每个窗口上开始一个事务，然后一个接一个地执行命令。

在某些时候，您将看到命令执行无限期地继续。然后，在另一个窗口上称为COMMIT或ROLLBACK时，它完成执行。

正如刚才所述，这里有一些非常基本的命令可供测试。

使用这些来创建表和一个有用的行：

CREATE TABLE theTable(
    iD int NOT NULL,
    val1 int NOT NULL,
    val2 int NOT NULL
)
INSERT INTO theTable (iD, val1, val2) VALUES (1, 2 ,3);

然后在两个不同的窗口中逐步进行以下操作：

BEGIN TRAN

SELECT val1, val2 FROM theTable WHERE iD = 1;

UPDATE theTable
  SET val1=11
  WHERE iD = 1 AND val1 = 2 AND val2 = 3;

COMMIT TRAN

然后以您认为可能的任何顺序更改命令顺序和执行顺序。