MySQL 如何在MySQL中锁定多个表？

在MySQL中，锁是控制数据库并发性的重要机制。当两个或多个用户同时尝试修改同一数据集时，锁可以防止数据不一致问题的出现。在某些场景下，我们可能需要同时锁定多个表以保证数据的一致性，那么如何在MySQL中锁定多个表呢？本文将为大家介绍。

阅读更多：MySQL 教程

MySQL 锁机制简介

在MySQL中，有两种类型的锁：共享锁和排它锁。

共享锁（读锁）：多个事务可以共享同一个共享锁，读取该数据但是不能修改它。
排它锁（写锁）：只有一个事务可以获得排它锁，可以对数据进行修改操作，其他事务需要等待该锁释放。

MySQL中锁的分类主要分为行级锁和表级锁。

行级锁：锁定某一行记录，其他行不受影响（InnoDB）。
表级锁：锁定整张表，其他事务无法对这张表进行操作（MyISAM）。

锁定多个表的场景

在实际业务场景中，可能会存在需要同时修改多个表的情况，为了保证数据的一致性，需要同时锁定多个表。例如：转账场景，需要锁定转出账户和转入账户所在的两张表。

如何在MySQL中锁定多个表？

MySQL提供了语句SELECT ... FOR UPDATE可以在查询中获得锁。当一个事务获取了锁之后，其他事务必须等待该锁释放才能进行操作。我们可以利用这个特性锁定多个表的数据。

以下是一个在MySQL中锁定两个表的例子：

START TRANSACTION;

SELECT * FROM account WHERE id = '123' FOR UPDATE;
SELECT * FROM transactions WHERE account_id = '123' FOR UPDATE;

UPDATE account SET balance = balance - 500 WHERE id = '123';
INSERT INTO transactions (account_id, amount, type) VALUES ('123', 500, 'withdrawal');

COMMIT;

在上述的例子中，我们首先开启一个事务 START TRANSACTION，然后使用 SELECT ... FOR UPDATE 语句分别锁定了 account 和 transactions 表的记录。最后对这两张表进行了修改操作，并提交事务 COMMIT。

需要注意的是，使用锁要非常小心，不当的使用会导致死锁问题。尽可能的减小锁住的范围，并尽快释放已获得的锁。

实战演练

我们通过实例演示如何在MySQL中锁定多个表。

步骤一：创建两张表

运行以下SQL语句创建表 account 和 transactions：

CREATE TABLE account (
  id VARCHAR(50) PRIMARY KEY,
  name VARCHAR(50),
  balance DECIMAL(8, 2)
);

CREATE TABLE transactions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  account_id VARCHAR(50),
  amount DECIMAL(8, 2),
  type VARCHAR(10),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO account (id, name, balance)
VALUES ('123', 'Tom', 1500), ('456', 'Jerry', 5000);

INSERT INTO transactions (account_id, amount, type)
VALUES ('123', 1500, 'deposit'), ('456', 5000, 'deposit');

步骤二：模拟转账并锁定两个表

运行以下SQL语句模拟转账并锁定两个表：

START TRANSACTION;

SELECT * FROM account WHERE id = '123' FOR UPDATE;
SELECT * FROM transactions WHERE account_id = '123' FOR UPDATE;

UPDATE account SET balance = balance - 500 WHERE id = '123';
INSERT INTO transactions (account_id, amount, type) VALUES ('123', 500,'withdrawal');

COMMIT;

步骤三：查询结果

运行以下SQL语句查询 account 和 transactions 表中的记录：

SELECT * FROM account;

输出结果如下：

+-----+-------+---------+
| id  | name  | balance |
+-----+-------+---------+
| 123 | Tom   | 1000.00 |
| 456 | Jerry | 5000.00 |
+-----+-------+---------+

运行以下SQL语句查询 transactions 表中的记录：

SELECT * FROM transactions;

输出结果如下：

+----+------------+--------+------------+---------------------+
| id | account_id | amount | type       | created_at          |
+----+------------+--------+------------+---------------------+
|  1 | 123        | 1500.00 | deposit    | 2022-10-20 13:23:49 |
|  2 | 456        | 5000.00 | deposit    | 2022-10-20 13:23:49 |
|  3 | 123        | 500.00  | withdrawal | 2022-10-20 13:34:06 |
+----+------------+--------+------------+---------------------+

我们可以看到，转账操作成功，并且 account 表中的 balance 字段已经被正确修改。