关键是,乐观锁定不是数据库功能,不适用于 MySQL 或其他数据库:乐观锁定是一种通过标准指令使用数据库应用的实践。
让我们举一个非常简单的例子,假设您希望在多个用户/客户端可以同时运行的代码中执行此操作:
- 从具有一个 ID 字段 (iD) 和两个数据字段 (val1、val2) 的行中选择数据
- 可选择使用数据进行计算
- 更新该行的数据
无锁定方式是:
注意:所有代码{大括号之间}都应位于应用程序代码中,而不是(必然)位于 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 时,有人已经修改了数据。
请注意,所有操作都是在没有交易的情况下完成的!这是可能的(没有事务)只是因为这是一个非常简单的示例,但这也表明乐观锁定的关键点并不在于事务本身。
那么交易呢?
- 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 检查。
在这种情况下,对于事务,我们将执行更新的时刻与提交更新的时刻分开。那么当“其他进程”在这个时间范围内执行更新时会发生什么?
要了解到底发生了什么,需要深入研究隔离级别的详细信息(以及如何在每个引擎上管理它们)。
以 Microsoft SQL Server 具有 READ_COMMITTED 的情况为例,更新的行
被锁定直到提交,因此“其他进程”不能对该行执行任何操作(一直等待),也不能执行 SELECT(实际上它只能 READ_COMMITTED)。
因此,由于“其他进程”活动被推迟,因此更新将失败。
VERSIONING 乐观锁定选项:
- 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 时都会修改)来检查是否有人比我们更快并更改了我们之间的行,而不是检查所有字段的值是否仍然相同。选择并更新。
这里没有事务是由于第一个示例中的简单性,与版本列的使用无关。
同样,此列的使用取决于应用程序代码中的实现,而不是数据库引擎功能。
除此之外,我认为还有其他几点会使这个答案太长(已经太长了),所以我现在只通过一些参考文献提及它们:
- 事务隔离级别(这里是 MySQL http://dev.mysql.com/doc/refman/5.6/en/dynindex-isolevel.html)关于事务对 SELECT 的影响。
- 对于主键未自动生成(或唯一约束)的表上的 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
然后按照您可能想到的任何顺序更改命令顺序和执行顺序。