乐观锁定MySQL [英] Optimistic locking in MySQL
问题描述
我找不到任何关于乐观锁定MySQL的细节。
我读到启动一个事务保持两个实体同步的更新，但是 - 它不会阻止两个用户同时更新数据，导致冲突。
I can't find any details on optimistic locking in MySQL. I read that starting a transaction keep updates on two entities synced, however - it doesn't stop two users updating the data at the same time causing a conflict.
显然乐观锁定将解决这个问题？如何在MySQL中应用。这是否有SQL语法/关键字？
Apparently optimistic locking will solve this issue? How is this applied in MySQL. Is there SQL syntax / keyword for this? Or does MySQL have default behavior?
推荐答案
关键是乐观锁定不是一个数据库功能，不是MySQL和其他人：乐观锁是一种使用DB使用标准指令应用的做法。
The point is that Optimistic Locking is not a database feature, not for MySQL nor for others: optimistic locking is a practice that is applied using the DB with standard instructions.
让我们有一个非常简单的例子，并说你想在多个用户/客户端可以同时运行的代码中执行此操作：
Let's have a very simple example and say that you want to do this in a code that multiple users/clients can run concurrently:
- 可选择使用数据进行计算
- 更新的数据包含一个ID字段（iD）和两个数据字段（val1，val2）
- 行
NO LOCKING的方式是：
{curl brakets}之间的目标是在应用程序代码中，而不是（必然）在SQL端
The NO LOCKING way to is:
NOTE: all code {between curl brakets} is intended to be in the app code and not (necessarly) in the SQL side
- 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}
OPTIMISTIC LOCKING方法是： h2>
The OPTIMISTIC LOCKING way is:
- 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。
注意，所有已经完成没有交易！这是可能的（没有交易）只是因为这是一个非常简单的例子，但这也告诉乐观锁的关键点不在交易本身。
Note that the key point is in the structure of the UPDATE instruction and the subsequent number of affected rows check. It is this two things together that let your code realize that someone has already modified the data in between when you have executed the SELECT and UPDATE. Notice that all has been done without transactions! This has been possible (absence of transactions) only because this is a very simple example but this tells also that the key point for Optimistic locking is not in transactions themselves.
- 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检查冲突。
This last example shows that if you check for collisions at some point and discover a collision has been happened when you have already modified other tables/rows.. ..then with transactions you are able to rollback ALL the changes that you've done since the beginning. Obviously it is up to you (that know what your application is doing) to decide how large is the amount of operations to rollback for each possible collision an based on this decide where to put the transactions boundaries and where to check for collisions with the special UPDATE + AffectedRows check.
在这种情况下，我们已经分离了当我们从提交的时刻开始执行UPDATE的时刻。那么当其他进程在这个时间框架中执行更新时会发生什么？
要知道发生什么，需要深入研究隔离级别的细节（以及如何在每个引擎上管理）。
作为一个例子，在Micosoft SQL Server与READ_COMMITTED的情况下，更新的行
被锁定，直到COMMIT所以其他进程不能做任何事情（保持等待）的行， （其实它只能READ_COMMITTED）。
因为其他进程活动被延迟，它的UPDATE将失败。
In this case with transactions we have separated the moment when we perform the UPDATE from the moment when it is committed. So what happens when an "other process" performs an update in this time frame? To know what happens exactly requires delving in the details of isolation level (and how is managed on each engine). As an example in the case of Micosoft SQL Server with READ_COMMITTED the updated rows are locked until the COMMIT so "other process" can't do nothing (is kept waiting) on that rows, neither a SELECT (in fact it can only READ_COMMITTED). So since the "other process" activity is deferred it's UPDATE will fail.
- 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和UPDATE之间的行。
这里没有事务是由于简单，如第一个例子，并且与版本列使用无关。
这个列的使用是由应用程序代码中的实现而不是数据库引擎的功能。
Here is shown that instead than checking the if the value is still the same for all the fields we can use a dedicated field (that is modified each time we do an UPDATE) to see if anyone was quicker than us and changed the row between our SELECT and UPDATE. Here the absence of transactions is due to the simplicity as in the first example and is not related with the version column use. Again this column use is up to the implementation in the application code and not a database engine feature.
还有其他一些点，我认为使得这个答案太长了（已经太长了），所以我现在只提及一些引用：
More than this there are other points which I think would make this answer too much long (is already too much long) so I only mention them by now with some references:
- 事务隔离级别此处为MySQL ）关于对SELECT的事务影响。
- 对于具有未自动生成（或唯一约束）的主键的表，INSERT将自动失败，无需特定检查，如果两个进程尝试插入相同的值， 。
- 如果您没有ID列（主键或唯一约束），则单个SELECT + UPDATE需要事务，因为您可能会有比其他人做出的修改更多的行为，匹配UPDATE的WHERE子句的条件。
- transaction isolation level (here for MySQL) about transaction effect on SELECTs.
- for the INSERT on tables with Primary Keys not autogenerated (or unique constraints) it will automatically fails with no need of particular checking if two processes try to insert the same values where it must be unique.
- if you have no id column (primary key or unique constraints) also a single SELECT + UPDATE require transactions because you could have the surprise than after modifications made by others there are more rows than expected matching the criteria of the UPDATE's WHERE clause.
由于隔离级别值和实现可能不同，最好的建议（如本网站通常所述）是对使用的平台/环境执行测试。
Since the isolation level value and implementation may be different the best advice (as usual in this site) is to perform a test on the used platform / environment.
这似乎很难，但在现实中，它可以很容易地从任何DB开发环境使用两个单独的窗口，开始每个一个事务，然后一个接一个执行命令。
It may seem difficult but in reality it can be done quite easily from any DB developmemt enviroment using two separate windows and starting on each one a transaction then executing the commands one by one.
在某些时候，您将看到命令执行无限期。
然后当在另一个窗口上被调用COMMIT或ROLLBACK时，它完成执行。
At some point you will see that the the command execution continues indefinitely. Then when on the other window it is called COMMIT or ROLLBACK it completes the execution.
这里有一些基本的命令准备好测试，
Here are some very basic commands ready to be tested as just described.
使用这些创建表格和一个有用的行：
Use these for creating the table and one useful row:
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);
然后在两个不同的窗口中逐步执行以下操作：
Then the following on two different windows and step by step:
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
然后更改命令的顺序和执行顺序
then change the order of commands and order of execution in any order you may think.
这篇关于乐观锁定MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
