Mysql的锁你了解哪些？

MySQL中的锁是用于控制多个用户对共享资源的并发访问，以确保数据的完整性和一致性。

一、按锁的性质分类

1. 乐观锁（Optimistic Locking）

• 假设并发操作时不会发生冲突，只在提交事务时检查数据是否被其他事务修改过。

• 适用于读多写少的场景。

• 实现方式通常是通过记录版本号或时间戳来判断数据是否被修改。

2. 悲观锁（Pessimistic Locking）

• 假设并发操作时会发生冲突，因此在操作期间持有锁来避免冲突。

• 适用于写多读少的场景。

• 实现方式通常是通过SELECT ... FOR UPDATE等语句显式地对数据加锁。

二、按锁的粒度分类

1. 全局锁（Global Lock）

• 对整个数据库实例加锁，限制除了超级用户外的所有查询和修改操作。

• 典型用法是FLUSH TABLES WITH READ LOCK，用于全库逻辑备份。

• 另一种方法是设置SET GLOBAL readonly=true，但不建议使用，因为它对主从复制有影响。

2. 表级锁（Table-Level Lock）

• 对整个表加锁，其他连接无法修改或读取该表的数据，但可以对其他表进行操作。

• MyISAM存储引擎默认使用表级锁。

• 在InnoDB存储引擎中，表级锁主要用于处理DDL操作，如ALTER TABLE。

3. 页级锁（Page Lock）

• 对数据页（通常是连续的几个行）加锁，控制并发事务对该页的访问。

• 不是所有存储引擎都支持页级锁，且其应用场景相对较少。

4. 行级锁（Row-Level Lock）

• 对单个行加锁，只锁定需要修改的数据行，其他行可以被同时修改或读取。

• InnoDB存储引擎支持行级锁，通过索引实现。

• 行级锁可以显著提高并发性，但需要消耗更多的系统资源。

三、按锁的功能分类

1. 共享锁（Shared Lock, S锁）

• 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

• 典型用法是SELECT ... LOCK IN SHARE MODE。

2. 排他锁（Exclusive Lock, X锁）

• 允许获取排他锁的事务更新数据，阻止其他事务获取相同数据集的共享锁和排他锁。

• 典型用法是SELECT ... FOR UPDATE。

3. 意向锁（Intention Locks）

• 是一种表级锁，用于表明事务稍后将对表中的某个行加锁。

• 分为意向共享锁（IS）和意向排他锁（IX）。

4. 间隙锁（Gap Lock）

• 锁定一个范围的键，但不包括这些键的实际值。

• 用于防止幻读，确保索引间隙不变。

5. 临键锁（Next-Key Lock）

• 是行锁和间隙锁的组合，锁定一个范围，并且锁定记录本身。

• InnoDB在可重复读隔离级别下默认使用临键锁。

四、其他锁

1. 元数据锁（Metadata Lock, MDL）

• 用于保护数据字典对象，如表结构，防止DDL与DML操作之间的冲突。

• 当执行DDL语句时，会自动加上MDL写锁；当执行DML语句时，会自动加上MDL读锁。

五、锁的应用场景

• 表级锁：适用于需要重建表索引或进行长时间的数据备份时。

• 行级锁：适用于需要更新或删除某些行数据时，确保数据的一致性和完整性。

• 间隙锁：适用于需要确保某个范围内的数据不会被其他事务修改时，如防止幻读。

六、SQL演示

一、全局锁

全局锁是对整个数据库实例进行加锁。在MySQL中，可以使用FLUSH TABLES WITH READ LOCK命令来实现全局锁。

-- 对所有表加上读锁，直到执行UNLOCK TABLES
FLUSH TABLES WITH READ LOCK;-- 解锁
UNLOCK TABLES;

注意：全局锁会阻塞所有除超级用户外的写操作，因此应谨慎使用，通常只在全库逻辑备份时使用。

二、表级锁

表级锁是对整个表进行加锁。在MyISAM存储引擎中，表级锁是默认的锁机制。在InnoDB存储引擎中，表级锁通常用于DDL操作。

-- 在MyISAM中，对my_table加读锁
LOCK TABLES my_table READ;-- 在MyISAM中，对my_table加写锁
LOCK TABLES my_table WRITE;-- 解锁
UNLOCK TABLES;-- 在InnoDB中，执行DDL操作时会自动加表级锁
ALTER TABLE my_table ADD COLUMN new_column INT;

三、行级锁

行级锁是对单行数据进行加锁。在InnoDB存储引擎中，行级锁是通过索引实现的，可以通过SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE来显式加锁。

-- 开启一个事务
START TRANSACTION;-- 对id为1的行加排他锁
SELECT * FROM my_table WHERE id = 1 FOR UPDATE;-- 对id为2的行加共享锁
SELECT * FROM my_table WHERE id = 2 LOCK IN SHARE MODE;-- 提交事务
COMMIT;

注意：在事务提交或回滚之前，其他事务无法修改被加锁的行。

四、意向锁

意向锁是一种表级锁，用于表明事务稍后将对表中的某个行加锁。意向锁分为意向共享锁（IS）和意向排他锁（IX）。InnoDB存储引擎会自动管理意向锁，用户无需显式添加。

-- 意向锁是由InnoDB自动管理的，用户无法直接添加
-- 但是可以通过SHOW ENGINE INNODB STATUS来查看意向锁的信息

五、间隙锁和临键锁

间隙锁和临键锁是InnoDB在可重复读隔离级别下使用的锁机制，用于防止幻读。间隙锁锁定一个范围的键，但不包括这些键的实际值；临键锁则是行锁和间隙锁的组合。

-- 假设my_table有一个id列，且id为1, 2, 3的行存在-- 开启一个事务
START TRANSACTION;-- 对id在1和2之间的间隙加锁（间隙锁）
SELECT * FROM my_table WHERE id BETWEEN 1 AND 2 FOR UPDATE;
-- 此时，无法插入id为1.5的行-- 对id为2的行及其前面的间隙加锁（临键锁）
SELECT * FROM my_table WHERE id <= 2 FOR UPDATE;
-- 此时，无法插入id为1.5或2.5的行，也无法更新id为2的行-- 提交事务
COMMIT;

注意：间隙锁和临键锁是在InnoDB内部自动管理的，用户无法直接指定使用哪种锁。但是，通过选择合适的隔离级别和查询语句，可以影响InnoDB使用哪种锁。

六、元数据锁（MDL）

元数据锁用于保护数据字典对象，如表结构。当执行DDL语句时，会自动加上MDL写锁；当执行DML语句时，会自动加上MDL读锁。用户无需显式添加MDL。

-- MDL是由MySQL自动管理的，用户无法直接添加
-- 但是可以通过SHOW PROCESSLIST或INFORMATION_SCHEMA.INNODB_LOCKS等视图来查看MDL的信息-- 执行DDL语句时，会自动加上MDL写锁
ALTER TABLE my_table ADD COLUMN new_column INT;-- 执行DML语句时，会自动加上MDL读锁
SELECT * FROM my_table;