首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >MySQL中的乐观锁定

MySQL中的乐观锁定
EN

Stack Overflow用户
提问于 2013-07-03 00:52:17
回答 1查看 27.2K关注 0票数 53

我在MySQL中找不到任何关于乐观锁定的细节。我读到启动一个事务使两个实体上的更新保持同步,然而,这并不能阻止两个用户同时更新数据,从而导致冲突。

显然乐观的锁定会解决这个问题吗?这在MySQL中是如何应用的。对此是否有SQL语法/关键字?或者MySQL是否有默认行为?

谢谢你们。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2013-09-15 06:12:56

关键是乐观锁定不是一个数据库特性,既不适用于MySQL,也不适用于其他数据库:乐观锁定是使用带有标准指令的DB应用的一种实践。

让我们举一个非常简单的例子,假设你想在一个多个用户/客户端可以并发运行的代码中完成这个任务:

从具有一个ID字段(iD)和两个数据字段(val1、

  • )的行中选择数据使用data

  • UPDATE data of

进行计算

不锁定的方法是:

注意:所有代码(在大括号内)都应该放在应用程序代码中,而不是(必须)放在SQL端

代码语言:javascript
复制
- 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}

乐观的锁定方式是:

代码语言:javascript
复制
- 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时,这两件事一起让您的代码意识到有人已经修改了其间的数据。请注意,所有操作都是在没有事务的情况下完成的!这是可能的(没有事务),因为这是一个非常简单的示例,但这也说明乐观锁定的关键点不在事务本身。

那么事务又如何呢?

代码语言:javascript
复制
 - 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}

最后一个示例显示,如果您在某个点检查冲突,并发现在您已经修改了其他表/行时发生了冲突。使用事务..then,您可以回滚从一开始所做的所有更改。显然,这取决于您(知道您的应用程序正在做什么)来决定每个可能的冲突的回滚操作量有多大,并基于此来决定将事务边界放在哪里,以及使用特殊的UPDATE + AffectedRows检查检查冲突的位置。

在这种情况下,对于事务,我们将执行更新的时刻与提交更新的时刻分开。那么,当“其他进程”在此时间范围内执行更新时会发生什么呢?要确切了解发生了什么,需要深入研究隔离级别的细节(以及它们在每个引擎上是如何管理的)。例如,在Microsoft SQL Server使用READ_COMMITTED的情况下,更新的行将被锁定,直到提交,因此“其他进程”不能对这些行什么也不做(一直在等待),SELECT也不能(实际上它只能是READ_COMMITTED)。因此,由于“其他进程”活动被推迟,它的更新将失败。

版本控制乐观锁定选项:

代码语言:javascript
复制
 - 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}

这里显示的不是检查所有字段的值是否仍然相同,而是可以使用专用字段(每次进行更新时都会修改)来查看是否有人比我们更快,并在SELECT和UPDATE之间更改行。这里没有事务是由于第一个示例中的简单性,与version列的使用无关。同样,此列的使用取决于应用程序代码中的实现,而不是数据库引擎特性。

更重要的是,我认为还有其他几点会使这个答案太长(已经太长了),所以我现在只提到一些参考:

  • transaction isolation level (here for MySQL)关于事务对SELECTs的影响。
  • 对于主键未自动生成(或唯一约束)的表的insert,它将自动失败,而无需特别检查两个进程是否尝试在必须唯一的位置插入相同的值。
  • 如果没有id列(主键或唯一约束),则单个SELECT + UPDATE也需要事务处理,因为在其他人进行修改后,与UPDATE的where子句的标准匹配的行数可能会超出预期。

如何在实践中检验并获得自信

由于隔离级别的值和实现可能不同,所以最好的建议(和这个站点中的通常一样)是在使用的平台/环境上执行测试。

这看起来可能很困难,但实际上在任何DB开发环境中都可以很容易地完成,使用两个单独的窗口,在每个窗口上启动一个事务,然后逐个执行命令。

在某些情况下,您将看到命令的执行将无限期地继续。然后,当在另一个窗口中调用COMMIT或ROLLBACK时,它将完成执行。

下面是一些非常基本的命令,可以像刚才描述的那样进行测试。

使用这些命令创建表和一个有用的行:

代码语言:javascript
复制
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);

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

代码语言:javascript
复制
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

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

票数 162
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/17431338

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档