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;原文地址：https://blog.csdn.net/weixin_46228563/article/details/142492015
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！