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下令来实现全局锁。

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

复制代码

注意：全局锁会阻塞全部除超级用户外的写操作，因此应审慎利用，通常只在全库逻辑备份时利用。
二、表级锁

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

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

复制代码

三、行级锁

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

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

复制代码

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

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

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

复制代码

五、间隙锁和临键锁

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

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

复制代码

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

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

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

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。