MySQL 锁

pptg大约 4 分钟

1. 锁的种类

MySQL中根据加锁的范围，分为：全局锁、表级锁和行锁三类

1.1 全局锁

在加锁后，数据库将变为只读状态，任何增删改或者对表结构进行调整的语句都会阻塞。需要手动释放或者断开会话才会解开锁，MySQL通过以下方式使用全局锁：

-- 加锁
flush tables with read lock
-- 释放锁
unlock tables

全局锁主要应用于全盘逻辑备份，但在备份期间会影响正常业务的进行。如果数据库的引擎支持可重复读的隔离界别的话，在备份前开启事务会先创建Read View，这样备份的数据就只会操作Read View了，其他的业务依然可以正常进行。

1.2 表级锁

MySQL的表级锁包括：

表锁
元数据锁（MDL）
意向锁
AUTO-INC锁

1.2.1 表锁

MySQL可以对表加：

共享锁（读锁）：多个事务可以同时持有读锁，但不能获得写锁，只能读取
独占锁（写锁）：只有持有写锁的事务可以执行写操作，其他事务不可以读写

-- 共享锁（读锁）
lock tables user read;

-- 独占锁（写锁）
lock tables user write;

1.2.2 元数据锁

元数据锁不需要显示的使用，当对数据库表进行操作时，会自动给表添加元数据锁MDL，并在事务提交后自动释放：

CRUD：自动添加MDL读锁
表结构变更：自动添加MDL写锁

注意

申请MDL锁会形成一个队列，队列中写锁优先级高于读锁，所以某线程申请不到MDL写锁，会导致后续申请读锁的操作也被阻塞。

1.2.3 意向锁

在InnoDB中，对表的某些记录添加共享或独占锁前，都要在表级别上添加一个意向共享锁或意向独占锁。其作用在于快速的判断表内是否有行级锁

因此，增删改操作会对表添加意向独占锁，后续再对记录添加独占锁，而普通的Select不需要加锁，而是通过MVCC实现。

1.2.4 AUTO-INC锁

在插入数据时，可以不指定主键的值，而是让其自增。这一功能是通过AUTO-INC锁实现的。在插入数据时，会加一个锁级别的AUTO-INC锁，然后处理自动递增的值，等插入语句执行完之后再释放掉。

注意

注意这里不是事务提交后释放，而是插入语句执行完就释放

在事务持有AUTO-INC锁时，为了确保自增，其他的插入会被阻塞。为了优化插入的效率，MySQL在5.1.22后提供了轻量级锁来实现自增，该锁会在获取完自增值后就进行释放。然而轻量级锁在和binlog的statement一起使用的时候会导致主从数据不一致：

-- 存在Table use、user_b 结构相同
-- id, name, age
-- 1 , a   , 10
-- 2 , b   , 20
-- 3 , c   , 30
-- 4 , d   , 40

-- 当两线程同时执行语句
-- Thread A:
insert into t2 values(null, e, 50);
-- Thread B:
insert into t2(name, user) select name, user from user;

对于上述情况，因为轻量级锁是获取完自增值后就释放，所以A的命令可能会插在B中间执行。而binlog的statement生成的语句在从user_b执行的，所以会导致id不相同。对此，需要将binlog_format设置为row，在binlog中记录主库分配的自增值

1.3 行级锁

InnoDB引擎支持行级锁，而MyISAM不支持

普通的select语句是使用MVCC机制的快照读实现的，而如果使用复杂的select语句则会加锁，称之为锁定读

-- 共享锁
select ... lock in share mode;

-- 独占锁
select ... for update;

行级锁根据影响范围分为三种：

Record Lock：记录锁，只锁定一条记录
Gap Lock：间隙锁，锁定一个范围，但不包括记录本身，用于防止在某个范围内插入记录而导致幻读
Next-Key Lock：Record Lock + Gap Lock的组合，既锁定记录又锁定范围

在插入记录时，如果该位置已被间隙锁包含，那么插入操作就会阻塞并生成一个插入意向锁，表示等待插入