四、MySQL锁
4.1 MySQL有哪些锁?
4.1.1 全局锁
全局锁就是**对整个数据库实例加锁,主要用于全库逻辑备份**等场景。
flush tables with read lock # 加全局锁unlock tables # 解锁
加上全局(读)锁后,整个数据库都是只读状态。若数据库的数据较多,导致整个处理流程较慢,数据库长时间为只读状态,造成业务停滞、服务长时间不可用。
因此,由于InnoDB支持事务,支持可重复读的隔离级别,在备份数据库之前先开启事务。整个事务执行期间都在用这个 Read View,而且由于 MVCC 的支持,备份期间业务依然可以对数据进行更新操作。而如MylSAM等不支持事务的引擎,就只能通过全局锁的方式。
4.1.2 表级锁
MySQL中存在多种表级锁:
-
表锁
锁住整张表,命令如下:
# 对表t_student加锁 lock tables t_student read; # 表读锁 lock tables t_student write; # 表读锁unlock tablse # 释放锁
表锁除了会限制别的线程的读写外,也会限制本线程接下来的读写操作。
如果本线程对学生表加了「共享表锁」,那么本线程接下来如果要对学生表执行写操作的语句,是会被阻塞的,当然其他线程对学生表进行写操作时也会被阻塞,直到锁被释放。
尽量避免在使用 InnoDB 引擎的表使用表锁,因为表锁的颗粒度太大,会影响并发性能,InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。
-
元数据锁(meta data lock,MDL)
无需显示地调用MDL,每次访问一张表时会自动加上。MDL 是为了保证当用户对表执行 CRUD(增删改查) 操作时,防止其他线程对这个表结构做了变更。MDL 是在事务提交后才会释放,这意味着**事务执行期间,MDL 是一直持有的**。
因此如果存在一个长事务对某个表加上了MDL读锁,如果此时有线程尝试修改这个表的结构,但无法拿到MDL写锁,则陷入阻塞。此后任何想要读或者写的线程都无法执行而是阻塞等待(因为申请 MDL 锁的操作会形成一个队列,队列中写锁获取优先级高于读锁)。
所以为了能安全的对表结构进行变更,在对表结构变更前,先要看看数据库中的长事务,是否有长事务已经对表加上了 MDL 读锁,如果可以考虑 kill 掉这个长事务,然后再做表结构的变更。
-
意向锁
意向锁是指示一个事务**在未来可能会请求对某些资源的锁定**。
- 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前,需要先在**表级别**加上一个「意向共享锁」;
- 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前,需要先在**表级别**加上一个「意向独占锁」;
当执行插入、更新、删除操作,需要先对表加上「意向独占锁」,然后对该记录加独占锁。查询不需要,因为查询是通过MVCC实现一致性读的,无需加锁。
意向共享锁和意向独占锁是表级锁,不会和行级的共享锁和独占锁发生冲突,而且意向锁之间也不会发生冲突,只会和共享表锁(
lock tables ... read
)和独占表锁(lock tables ... write
)发生冲突。如果没有「意向锁」,那么加「独占表锁」时,就需要遍历表里所有记录,查看是否有记录存在独占锁,这样效率会很慢。那么有了「意向锁」,由于在对记录加独占锁前,先会加上表级别的意向独占锁,那么在加「独占表锁」时,直接查该表是否有意向独占锁,如果有就意味着表里已经有记录被加了独占锁,这样就不用去遍历表里的记录。意向锁的目的是为了快速判断表里是否有记录被加锁。
-
AUTO-INC锁(自增锁)
一个表中的主键通常是自增的,在插入数据时,数据库会自动给主键赋值递增的值,这主要是通过AUTO-INC锁来实现的。在插入数据时,会加一个表级别的 AUTO-INC 锁,然后为被
AUTO_INCREMENT
修饰的字段赋值递增的值,等插入语句执行完成后,才会把 AUTO-INC 锁释放掉。AUTO-INC 锁是特殊的表锁机制,锁不是再一个事务提交后才释放,而是再执行完插入语句后就会立即释放。
4.1.3 行级锁
InnoDB 引擎是支持行级锁的,而 MyISAM 引擎并不支持行级锁。行级锁的类型主要有三类:
-
记录锁(Record Lock)
将一条记录加锁,又分为共享锁(读锁、S锁)和排他锁(读锁、X锁),不同事物之间可能存在冲突关系。
-
间隙锁(Gap Lock)
锁定一个范围,不包含记录,间隙锁不存在互斥关系(因为不涉及到具体记录,于是不同事务可以同时包含共同范围的间隙锁),只存在于可重复读隔离级别下,用来防止可重复读隔离级别下的幻读现象。
-
临键锁(Next-Key Lock)
就是Record Lock和Gap Lock的组合,锁定记录本身和一个范围。即能**保护该记录,又能阻止其他事务将新纪录插入到被保护记录前面的间隙中**。因此,虽然间隙锁是多个事务相互兼容的,但记录锁会存在冲突关系。
还有一种特殊的间隙锁:插入意向锁
它是指当一个插入操作发现插入的位置被加了间隙锁,那么这个线程想要向这个区域加上一个“插入意向锁”,只能阻塞等待,直到间隙锁被释放。
4.1.4 乐观锁与悲观锁
-
乐观锁
一种思想,认为对同一个数据的并发操作发生概率较小,不需要每次都对数据上锁。常通过时间戳、版本号机制来实现。适用于读操作比较多的场景。
-
悲观锁
一种思想,认为总是会发生并发冲突,具有强烈独占性和排他性,通过锁机制来保护数据。适用于写操作比较多的场景。
4.2 MySQL是如何加行级锁的?
4.2.1 什么SQL语句会加行级锁?
InnoDB 引擎支持行级锁,与表级锁相比,行级锁的并发性能要好很多。
普通的 select 语句是不会对记录加锁的(除了串行化隔离级别),因为它属于快照读,是通过 **MVCC(多版本并发控制)实现**的。但是可以通过显式指定的方式给select语句加行级锁:
select ... lock in shared mode; # 对读取的记录加共享锁
select ... for update; # 对读取的记录加排他锁
update 和 delete 操作都会加行级锁,且锁的类型都是独占锁(X型锁)。
4.2.2 InnoDB两阶段锁协议
在InnoDB引擎中,可重复读隔离级别下,行级锁遵顼两阶段锁协议:在需要的时候加上,事务提交或出现回滚时才会释放(而不是操作完成了立即释放)。
4.2.2 MySQL行级锁加锁规则
MySQL中(InnoDB引擎)行级锁**加锁的对象是索引,加锁的基本单位是Next-Key Lock**。在一些场景下,Next-Key Lock会退化成记录锁或间隙锁。
在能使用记录锁或者间隙锁就能避免幻读现象的场景下, next-key lock 就会退化成记录锁或间隙锁。
-
唯一索引等值查询
若记录「存在」,在索引树上定位到这一条记录后,将该记录的索引中的 next-key lock 会退化成「记录锁」。
若记录「不存在」,在索引树找到第一条大于该查询记录的记录后,将该记录的索引中的 next-key lock 会退化成「间隙锁」。
-
唯一索引范围查询
当唯一索引进行范围查询时,会对每一个扫描到的索引加 next-key 锁,在一些情况下的索引的next-key锁会退化为记录锁或间隙锁。
-
非唯一索引等值查询
当我们用非唯一索引进行等值查询的时候,因为存在两个索引,一个是主键索引,一个是非唯一索引(二级索引),所以在加锁时,同时会对这两个索引都加锁,但是对主键索引加锁的时候,只有满足查询条件的记录才会对它们的主键索引加锁。针对非唯一索引等值查询时,对于扫描到的二级索引记录加 next-key 锁,在某些情况下的二级索引的锁会退化为间隙锁。
-
非唯一索引范围查询
非唯一索引和主键索引的范围查询的加锁也有所不同,不同之处在于非唯一索引范围查询,索引的 next-key lock 不会有退化为间隙锁和记录锁的情况,也就是非唯一索引进行范围查询时,对二级索引记录加锁都是加 next-key 锁。
4.2.3 没有索引的查询会发生什么?
对于存在索引的查询,查询语句都有使用索引查询,也就是查询记录的时候,是通过索引扫描的方式查询的,然后对扫描出来的记录进行加锁。
如果**锁定读查询语句,没有使用索引列作为查询条件,或者查询语句没有走索引查询,导致扫描是全表扫描。那么,每一条记录的索引上都会加 next-key 锁,这样就相当于锁住的全表,这时如果其他事务对该表进行增、删、改操作的时候,都会被阻塞**。
注意:这里是说对整张表的索引都加锁,而不是对表加锁。
4.2.4 可重复读隔离级别中Next-Key Lock可以防止删除操作导致的幻读吗?
前面说到,在可重复读隔离级别中,通过使用「记录锁+间隙锁」可以很大概率上避免新插入数据带来的幻读现象。
这种方案可以防止删除操作带来的幻读现象吗?
可以大概率避免,因为**当前读**的语句会对索引加Next-Key Lock,其他事务对被加锁的记录和间隙上增、删、改的操作都会被阻塞。
4.2.5 MySQL中加了什么锁会导致死锁?
死锁的四个条件:互斥、占有且等待、不可强占用、循环等待
由于间隙锁不会互斥,而当想要插入数据时,如果某个范围正好存在间隙锁,那么这个插入事务会向这个区域加上一个**“插入意向锁”并等待,直到间隙锁被释放**。
如上图所示,事务A和事务B在分别执行完update语句后,都含有一个间隙锁(事务结束后才会释放锁)。从下图的表中数据可以分析得到,两个事务的间隙锁的范围都是(20,30)。
因此,两个事务分别向对方持有的间隙锁范围内插入一条记录,而**插入操作为了获取到插入意向锁,都在等待对方事务的间隙锁释放**,于是就造成了循环等待,满足了死锁的四个条件,因此发生了死锁。
4.2.6 如何避免死锁?
-
破坏死锁条件
互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁,这些条件必然成立,但是只要破坏任意一个条件就死锁就不会成立。
-
设置事务等待锁超时时间
当一个事务阻塞等待时间超过阈值后,直接对该事务进行回滚,避免死锁。
-
开启死锁检测
MySQL支持死锁检测,开启后当发现死锁时,会主动回滚死锁链条中的某个事务,解除死锁。
4.3 InnoDB使用表锁还是行锁?
为了保证并发性能,InnoDB引擎在绝大部分情况使用行级锁。
使用表级锁的情况:
- 表比较大,需要对表中全部或大部分数据进行更新;
- 事务涉及到多个表,比较复杂,行锁可能会引起死锁,导致事务大量回滚。
资料参考
内容大多参考自:图解MySQL介绍 | 小林coding (xiaolincoding.com)