MySQL锁在防止脏读、不可重复读和幻读中的策略

MySQL 锁机制概述

在多用户并发访问数据库的场景下，数据的一致性和完整性面临诸多挑战。脏读、不可重复读和幻读是并发事务处理中常见的问题，MySQL 通过锁机制来应对这些问题。MySQL 的锁可以从不同角度分类，如按锁的粒度分为表锁、行锁；按锁的类型分为共享锁（读锁）、排他锁（写锁）等。

锁的粒度

1. 表锁
   • 表锁是 MySQL 中最基本的锁策略，它会锁定整个表。当一个事务对表加锁后，其他事务对该表的读写操作都可能被阻塞。例如，在 MyISAM 存储引擎中，表锁是主要的锁机制。表锁的优点是实现简单，开销小，适合以读为主，并发写操作较少的场景。缺点是锁的粒度大，并发性能差。
   • 代码示例：

   -- 手动给表加读锁
   LOCK TABLES your_table_name READ;
   -- 执行读操作
   SELECT * FROM your_table_name;
   -- 释放锁
   UNLOCK TABLES;
   

   -- 手动给表加写锁
   LOCK TABLES your_table_name WRITE;
   -- 执行写操作
   INSERT INTO your_table_name (column1, column2) VALUES ('value1', 'value2');
   -- 释放锁
   UNLOCK TABLES;
   

2. 行锁
   • 行锁则是针对表中的某一行数据进行锁定。InnoDB 存储引擎支持行锁，它可以显著提高并发性能，因为不同事务可以同时操作不同行的数据。行锁的实现相对复杂，开销也比表锁大。行锁又分为共享锁（S 锁）和排他锁（X 锁）。共享锁允许事务对数据进行读取操作，多个事务可以同时持有同一行数据的共享锁；排他锁则阻止其他事务对该行数据进行读写操作。
   • 代码示例：

   -- 开启事务
   START TRANSACTION;
   -- 对某一行数据加共享锁
   SELECT * FROM your_table_name WHERE id = 1 LOCK IN SHARE MODE;
   -- 对某一行数据加排他锁
   SELECT * FROM your_table_name WHERE id = 1 FOR UPDATE;
   -- 提交事务
   COMMIT;
   

锁的类型

1. 共享锁（读锁）
   • 共享锁主要用于读操作，允许多个事务同时读取同一数据。例如，多个用户同时查询一篇文章的内容，他们可以同时获取该文章数据行的共享锁，从而实现并发读取，而不会相互阻塞。共享锁之间是兼容的，即多个事务可以同时持有同一数据的共享锁。
   • 代码示例：

   START TRANSACTION;
   SELECT * FROM product WHERE product_id = 123 LOCK IN SHARE MODE;
   -- 这里可以执行一些读取操作，其他事务可以并发读取相同数据
   COMMIT;
   

2. 排他锁（写锁）
   • 排他锁用于写操作，当一个事务获取了某数据的排他锁后，其他事务不能再获取该数据的任何锁（包括共享锁和排他锁），直到该事务释放锁。这保证了在写操作期间，数据不会被其他事务修改，确保数据的一致性。
   • 代码示例：

   START TRANSACTION;
   SELECT * FROM product WHERE product_id = 123 FOR UPDATE;
   -- 这里可以执行写操作，如更新产品价格
   UPDATE product SET price = price * 1.1 WHERE product_id = 123;
   COMMIT;
   

脏读问题及 MySQL 锁策略

脏读的定义

脏读是指一个事务读取到了另一个未提交事务修改的数据。例如，事务 A 修改了某条数据，但尚未提交，此时事务 B 读取了这条被修改但未提交的数据。如果事务 A 随后回滚，那么事务 B 读取到的数据就是无效的“脏数据”。

MySQL 防止脏读的锁策略

1. 事务隔离级别
   • MySQL 通过设置不同的事务隔离级别来防止脏读。READ - COMMITTED 隔离级别是 MySQL 默认的隔离级别之一，它可以防止脏读。在这个隔离级别下，一个事务只能读取已经提交的数据。当一个事务执行读取操作时，MySQL 使用行级锁和 MVCC（多版本并发控制）机制来确保读取到的数据是已提交的版本。
   • 代码示例：

   -- 设置事务隔离级别为 READ - COMMITTED
   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
   -- 开启事务
   START TRANSACTION;
   -- 执行读取操作，此时读取到的数据是已提交的
   SELECT * FROM user WHERE user_id = 1;
   COMMIT;
   

2. 锁机制
   • 除了事务隔离级别，MySQL 还通过锁机制来防止脏读。当一个事务对数据进行修改时，会自动获取排他锁。在事务提交或回滚之前，其他事务无法获取该数据的共享锁或排他锁，从而无法读取到未提交的数据。例如，在 InnoDB 存储引擎中，写操作会自动对相关数据行加排他锁。
   • 代码示例：

   -- 事务 A
   START TRANSACTION;
   UPDATE user SET balance = balance - 100 WHERE user_id = 1;
   -- 此时事务 A 持有 user_id = 1 这一行数据的排他锁
   -- 事务 B
   START TRANSACTION;
   -- 以下查询会被阻塞，因为事务 A 持有排他锁
   SELECT * FROM user WHERE user_id = 1;
   -- 事务 A 提交
   COMMIT;
   -- 事务 B 可以继续执行查询，此时读取到的数据是已提交的
   COMMIT;
   

不可重复读问题及 MySQL 锁策略

不可重复读的定义

不可重复读是指在一个事务内，多次读取同一数据时，得到的结果不一致。这通常是因为在该事务执行期间，其他事务对该数据进行了修改并提交。例如，事务 A 先读取了某条数据，然后事务 B 修改并提交了这条数据，事务 A 再次读取时，得到了不同的结果。

MySQL 防止不可重复读的锁策略

1. 事务隔离级别
   • REPEATABLE - READ 隔离级别可以防止不可重复读。在这个隔离级别下，MySQL 使用 MVCC 和锁机制确保在一个事务内多次读取同一数据时，结果保持一致。当事务启动时，会记录一个系统版本号（system version number，简称 SVN），在事务执行期间，所有读取操作都会基于这个 SVN 来获取数据版本，即使其他事务修改并提交了数据，当前事务读取到的仍然是启动时的版本。
   • 代码示例：

   -- 设置事务隔离级别为 REPEATABLE - READ
   SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
   -- 事务 A
   START TRANSACTION;
   SELECT * FROM order WHERE order_id = 1;
   -- 此时记录了当前的 SVN
   -- 事务 B
   START TRANSACTION;
   UPDATE order SET status = 'completed' WHERE order_id = 1;
   COMMIT;
   -- 事务 A 再次读取
   SELECT * FROM order WHERE order_id = 1;
   -- 事务 A 读取到的数据仍然是事务开始时的版本，不受事务 B 修改的影响
   COMMIT;
   

2. 锁机制
   • 在 REPEATABLE - READ 隔离级别下，MySQL 还使用共享锁来防止不可重复读。当一个事务读取数据时，会对读取的数据行加共享锁，直到事务结束。在事务持有共享锁期间，其他事务不能对这些数据行加排他锁进行修改，从而保证了在事务内多次读取结果的一致性。
   • 代码示例：

   -- 事务 A
   START TRANSACTION;
   SELECT * FROM product WHERE product_id = 456 LOCK IN SHARE MODE;
   -- 事务 A 持有 product_id = 456 这一行数据的共享锁
   -- 事务 B
   START TRANSACTION;
   -- 以下更新操作会被阻塞，因为事务 A 持有共享锁
   UPDATE product SET stock = stock - 1 WHERE product_id = 456;
   -- 事务 A 提交
   COMMIT;
   -- 事务 B 可以继续执行更新操作
   COMMIT;
   

幻读问题及 MySQL 锁策略

幻读的定义

幻读是指在一个事务内，多次执行相同的查询时，得到的结果集行数不同。这通常是因为在事务执行期间，其他事务插入或删除了符合查询条件的数据。例如，事务 A 第一次查询满足某个条件的数据有 5 条，然后事务 B 插入了 2 条符合条件的数据并提交，事务 A 再次执行相同查询时，结果集变成了 7 条，仿佛出现了“幻觉”。

MySQL 防止幻读的锁策略

1. 事务隔离级别
   • SERIALIZABLE 隔离级别可以防止幻读。在 SERIALIZABLE 隔离级别下，MySQL 将事务的执行方式变成串行化，即每个事务依次执行，避免了并发事务之间的相互干扰。当一个事务执行查询时，会对查询范围内的数据加锁，防止其他事务插入或删除符合条件的数据。
   • 代码示例：

   -- 设置事务隔离级别为 SERIALIZABLE
   SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   -- 事务 A
   START TRANSACTION;
   SELECT * FROM customer WHERE age > 30;
   -- 事务 A 对符合 age > 30 条件的数据加锁
   -- 事务 B
   START TRANSACTION;
   -- 以下插入操作会被阻塞，因为事务 A 对相关范围加锁
   INSERT INTO customer (name, age) VALUES ('new_customer', 35);
   -- 事务 A 提交
   COMMIT;
   -- 事务 B 可以继续执行插入操作
   COMMIT;
   

2. 锁机制
   • InnoDB 存储引擎通过间隙锁（Gap Lock）和临键锁（Next - Key Lock）来防止幻读。间隙锁是对两个相邻数据之间的“间隙”加锁，临键锁则是间隙锁和行锁的组合，它锁定的范围包括数据行本身和其前面的间隙。例如，当一个事务执行查询 SELECT * FROM user WHERE age BETWEEN 20 AND 30 FOR UPDATE; 时，InnoDB 会使用临键锁，不仅锁定 age 在 20 到 30 之间的行数据，还会锁定 20 之前和 30 之后的间隙，防止其他事务在这个范围内插入新的数据，从而避免幻读。
   • 代码示例：

   -- 事务 A
   START TRANSACTION;
   SELECT * FROM user WHERE age BETWEEN 25 AND 35 FOR UPDATE;
   -- 事务 A 使用临键锁锁定 age 在 25 到 35 之间的数据行及相关间隙
   -- 事务 B
   START TRANSACTION;
   -- 以下插入操作会被阻塞，因为事务 A 的临键锁
   INSERT INTO user (name, age) VALUES ('new_user', 30);
   -- 事务 A 提交
   COMMIT;
   -- 事务 B 可以继续执行插入操作
   COMMIT;
   

在实际应用中，需要根据业务场景的并发特性和数据一致性要求，合理选择事务隔离级别和锁策略。例如，对于读多写少的场景，可以选择 REPEATABLE - READ 隔离级别，利用共享锁和 MVCC 机制保证数据一致性和并发性能；对于写操作频繁且对数据一致性要求极高的场景，可能需要考虑 SERIALIZABLE 隔离级别，但要注意其对并发性能的影响。同时，通过合理的索引设计可以优化锁的性能，减少锁争用，提高数据库的整体并发处理能力。