什么是MySQL中的锁定（Locking），何时使用它？

Question

什么是MySQL中的锁定（或任何关系型数据库），在什么情况下会使用它？通过示例来进行生动易懂的解释最好不过了！

- Imran

---

这是一个对于并发用户非常有用的优化：http://www.devshed.com/c/a/MySQL/MySQL-Optimization-part-2/ - James Black

Answer 1

我们有一个联合银行账户，余额为200美元

我去自动取款机，把卡插进机器里，机器检查到我的余额是200美元

同时，你走进银行要求50美元，出纳员查看你的账户并确认你有这笔钱。

我请求提取200美元，机器计算出我的钞票，给了我200美元，并将我的余额设置为0

出纳员清点你的钱并给你50美元，然后系统更新账户余额为150美元（200美元 - 50美元提款）。

现在我们手头上有250美元现金和150美元的账户余额。 200美元的利润。

数据库应该使用锁来防止两个交易同时发生。

问题是，如果每个交易都以这种方式处理，那么我们会失去并发性，并且性能会受到影响，因此根据情况使用不同的“事务隔离级别”，例如您可能不关心某人可以修改在事务中已读取的数据。

http://en.wikipedia.org/wiki/Isolation_%28database_systems%29

您应该学习并了解适用的方案。

- Chris Diver

---

谢谢@Chris Diver的详细解释！我现在明白了;-) - Imran

Answer 2

锁定可以避免两个用户同时修改数据，这对于应用程序来说是非常重要的。你可能认为这种情况不太可能发生，但是根据应用程序的不同，如果同一数据频繁被不同的用户更改，那么存在较大的风险。

想象以下情况，没有使用锁定：John打开了他的屏幕（他不知道他正在使用数据库，他只是一个看着漂亮屏幕的终端用户），修改了一些数据，然后点击“保存”。假设John在9:30打开了屏幕，并在9:32保存了数据。

然而，Mary在9:29准确地打开了相同的屏幕和相同的记录。她在那时看到了与John在9:30看到的相同的数据。然后，她更新了记录，并在9:31点击了“保存”。

保存了哪些数据？是John的还是Mary的？

Mary高高兴兴地继续处理其他记录，当她稍后再次打开记录时，她发现她的更改丢失了，取而代之的是John的更改！

请注意，必须明智地使用锁定以防止意外的副作用。例如，假设您的程序每次有人打开并进行更改时都会锁定记录。如果John锁定该记录，然后将屏幕留在那里去吃午饭或失去连接怎么办？该锁可能会长时间保持锁定和不可更改状态，同时禁止其他人更改（甚至查看）该记录。其他考虑可能是性能，因为数据库锁定和解锁记录的时间可能会对大量交易产生明显影响。

了解锁定对于维护用户和数据完整性非常重要。请查看文档。

- luiscolorado

---

版本控制是解决您所描述的用例的更好方案。您实际上无法保持那么长时间的锁定...... 我会这样做，当John点击保存时，读取数据+版本，将它们与您向用户显示的内容进行比较，然后对数据进行更新，检查刚刚读取的版本是否与之前两分钟读取的版本相同。如果版本不同，这意味着其他人已经修改了您的数据 - 您必须刷新它。 - ripper234

@ripper234：这就是为什么我说锁定需要考虑多个方面才能找到最佳方法。对于所述场景，您是正确的，我接受您的建议。我并没有打算在这里进行完整的讨论，也不可能。目的是提供一个简单易懂的答案。 - luiscolorado

Answer 3

几天前，我回答了SO上的一个问题，并给出了一个示例，展示了在不使用AUTO_INCREMENT的情况下，锁定允许多个用户同时向具有递增id的表中插入行的情况。

考虑以下模式作为示例：

CREATE TABLE demo_table (id int) ENGINE=INNODB;

-- // Add few rows
INSERT INTO demo_table VALUES (1), (2), (3);

然后我们可以这样做：

START TRANSACTION;

-- // Get the MAX(id) so that we increment it by one
SELECT @x := MAX(id) FROM your_table FOR UPDATE;

+---------------+
| @x := MAX(id) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)

FOR UPDATE 语法实际上是在此查询中对读取的行加锁。

在不提交事务的情况下，我们启动另一个独立的会话（模拟并发用户），并执行相同的操作：

START TRANSACTION;

-- // Get the MAX(id) as well
SELECT MAX(id) FROM demo_table FOR UPDATE;

在运行此查询之前，数据库将等待前一个会话中设置的锁被释放。

因此，切换到前一个会话后，我们可以插入新行并提交事务：

-- // Insert a new row with id = MAX(id) + 1
INSERT INTO demo_table VALUES (@x + 1);

COMMIT;

第一次会话提交事务后，锁将被解除，并返回第二个会话中的查询：

+---------+
| MAX(id) |
+---------+
|       4 |
+---------+
1 row in set (8.19 sec)

请注意，如果不加锁，则第二个会话将立即返回，但是MAX(id)的值为3而不是4。如果两个会话都插入一个id = MAX(id) + 1的行，则两者都将插入id = 4。您可以模拟相同的测试，而不使用FOR UPDATE，以了解在没有锁定的情况下如何处理此问题。

- Daniel Vassallo