MySQL 中的锁定是什么？什么时候会使用它？

Question

MySQL（或任何 RDBMS）中的锁定是什么？什么时候会使用它？带有示例的外行解释会很棒！

Answer 1

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

我去 ATM 机，将卡放入机器，机器检查我的余额为 200 美元

同时，你走进银行并要求 50 美元，柜员提出您的帐户并确认您有钱。

我请求提款 200 美元，机器计数我的钱给我 200 美元，并将我的余额设置为 0 美元

出纳员数算您的钱并给您 50 美元，然后系统将帐户余额更新为 150 美元（200 美元 - 50 美元提款）。

现在我们的账户里有 250 美元现金和 150 美元。利润200美元。

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

问题是，如果您以这种方式处理每个事务，那么我们将失去并发性，性能也会受到影响，因此根据场景使用不同的事务隔离级别，例如您可能不关心有人可以修改事务中已读取的数据。

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

您应该学习这些并了解它们的适用场景。

Answer 2

锁定对于避免两个用户同时修改数据至关重要。您可能认为这不太可能，但根据应用程序的不同，如果不同的用户频繁更改相同的数据，则存在重大风险。

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

然而，玛丽在 9 点 29 分打开了完全相同的屏幕和相同的记录。她当时看到的数据与约翰在 9:30 看到的数据相同。然后，她更新了记录，并在 9 点 31 分点击“保存”。

保存了哪些数据？约翰的还是玛丽的？

玛丽高兴地继续处理其他记录，当她稍后回来再次打开记录时，她发现她的更改丢失了，而她看到了约翰的更改！

请注意，必须明智地使用锁定，以防止意外的副作用。例如，假设您的程序在每次有人打开记录进行更改时都会锁定该记录。如果约翰锁定记录并打开会话屏幕去吃午饭或者失去连接，会发生什么情况？锁可以长时间保留在那里，锁定且不可更改，同时禁止其他人更改（甚至查看）该记录。其他考虑因素可能是性能，因为对于大量事务，数据库锁定和解锁记录的时间可能会变得很明显。

了解锁定对于维护用户满意度和数据完整性至关重要。请查看文档。

Answer 3

几天前，我回答了 关于SO的问题并给出了一个示例，演示了锁定 允许多个用户同时在具有递增 id 的表中插入行，而无需使用 AUTO_INCRMENT。

以以下模式为例：

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)

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