Mysql:有哪些锁？

MySQL 中的锁不是一个平铺的分类可以从三个维度理解锁的范围全局锁、表锁、行锁 锁的模式共享锁、排他锁、意向锁 锁住的对象记录、间隙、元数据、自增计数器等下面主要以最常用的 InnoDB 为例。一、共享锁和排他锁1. 共享锁 S Lock也叫读锁。事务获得共享锁后可以读取数据SELECT * FROM users WHERE id 1 FOR SHARE;特点S 锁和 S 锁兼容 S 锁和 X 锁冲突多个事务可以同时获得同一记录的共享锁但不能同时修改它。2. 排他锁 X Lock也叫写锁。用于修改或删除数据SELECT * FROM users WHERE id 1 FOR UPDATE; UPDATE users SET age 20 WHERE id 1; DELETE FROM users WHERE id 1;特点X 锁与 S 锁冲突 X 锁与 X 锁冲突一个事务持有排他锁时其他事务不能再获得冲突锁。InnoDB 的基本行锁模式就是 S 锁和 X 锁。二、意向锁意向锁是表级别的标记表示事务准备在表中的某些记录上加什么锁。意向锁含义IS准备在某些记录上加共享锁IX准备在某些记录上加排他锁例如UPDATE users SET age 20 WHERE id 1;大致会产生users 表上加 IX 锁 id1 对应索引记录上加 X 锁意向锁不是为了锁住整张表的数据而是让 MySQL 快速判断表级锁是否与已有行锁冲突。兼容关系可以简化为ISIXSXIS兼容兼容兼容冲突IX兼容兼容冲突冲突S兼容冲突兼容冲突X冲突冲突冲突冲突三、InnoDB 的记录锁InnoDB 所谓的“行锁”本质上通常是索引记录锁。UPDATE users SET name Tom WHERE id 10;如果id是主键就会锁住主键索引中id10的记录。主键索引1 5 [10] 20 ↑ 被锁定因此索引非常重要。如果没有合适索引MySQL可能需要扫描并锁住大量索引记录实际效果可能接近锁住整张表。四、间隙锁 Gap Lock间隙锁锁住的是两个索引值之间的空隙而不是现有记录。假设索引中有10、20、30间隙包括(-∞,10) (10,20) (20,30) (30,∞)如果锁住(10,20)其他事务不能在这个范围插入1119但通常不影响修改已经存在的10或20。间隙锁主要用于防止范围当前读出现幻行。五、临键锁 Next-Key Lock临键锁是Next-Key Lock 记录锁 记录前面的间隙锁例如锁住(10,20]意味着同时锁住间隙(10,20) 记录20在 RR 隔离级别下执行范围当前读SELECT * FROM users WHERE age BETWEEN 20 AND 30 FOR UPDATE;InnoDB 可能使用 Next-Key Lock 锁住相关索引记录及间隙阻止其他事务修改已有记录或向查询范围插入新记录。六、插入意向锁插入意向锁是一种特殊的间隙锁表示事务准备向某个间隙插入记录。索引中存在10、20两个事务分别执行INSERT INTO users(id) VALUES (12); INSERT INTO users(id) VALUES (18);虽然都向(10,20)插入但插入位置不同通常可以并发。如果该间隙已经被其他事务的间隙锁或 Next-Key Lock 锁住插入意向锁就需要等待。七、表锁可以显式锁住整张表LOCK TABLES users READ; LOCK TABLES users WRITE; UNLOCK TABLES;READ当前会话可以读不能写其他会话也可以读。WRITE当前会话可以读写其他会话通常不能访问。InnoDB 主要使用行级锁一般不建议通过LOCK TABLES手动锁住整张 InnoDB 表因为会明显降低并发能力。八、元数据锁 MDLMDL 的全称是 Metadata Lock用来保护表结构等数据库对象。事务执行START TRANSACTION; SELECT * FROM users;会获得users的元数据锁。如果事务一直没有结束另一个会话执行ALTER TABLE users ADD COLUMN phone VARCHAR(20);可能一直等待并显示Waiting for table metadata lockMDL 防止一个事务使用表时另一个会话突然删除或改变表结构。事务中的 MDL 通常要等到COMMIT或ROLLBACK才释放因此未提交的长事务经常造成 DDL 阻塞。九、AUTO-INC 锁这是 InnoDB 为AUTO_INCREMENT分配自增值使用的特殊机制CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT );多个事务同时插入时InnoDB 需要协调自增值的分配。是否使用表级AUTO-INC锁、持有多久取决于插入类型和innodb_autoinc_lock_mode。当前模式支持传统、连续和交错三种分配策略。十、全局锁和备份锁全局读锁可以通过以下命令获得FLUSH TABLES WITH READ LOCK;它会对整个 MySQL 实例加全局读锁通常允许查询但阻止数据修改常用于某些备份场景UNLOCK TABLES;此外LOCK INSTANCE FOR BACKUP可以获得实例级备份锁允许普通 DML 继续执行但阻止可能破坏备份一致性的部分操作。