数据库锁

云程序员 2021年05月05日 11次浏览

内容来自网络多篇文章截取

全局锁

全局锁是粒度最大的锁,基本上也使用不上,就像我们家的大门一样,控制着整个数据库实例。全局锁就是对整个数据库实例加锁,让整个数据库处于只读状态。

MySQL 提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL),加锁之后整个数据库实例处于只读状态,有关数据操作的命令都会被挂起阻塞,例如数据更新语句、数据定义语句、更新类事务语句等等。

所以全局锁一般只用于全库备份的时候,一般只用在不支持一致性读的存储引擎做全库备份时,比如 MyISAM 这种不支持一致性读的存储引擎做全库备份时需要使用全局锁,像 InnoDB 引擎做全库备份时不需要使用全局锁。

表级锁

表级锁是 MySQL 最基本的锁策略,并且是开销最小的策略,它锁住的不是整个数据库实例,而是一张表。

表级锁跟全局锁一样,MySQL 数据库提供了加锁的命令:lock tables … read/write。例如 lock tables t1 read, t2 write; 命令,则其他线程写 t1、读写 t2 的语句都会被阻塞。同时,线程 A 在执行 unlock tables 之前,也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许,自然也不能访问其他表。

我们可以使用 unlock tables 主动释放锁,如果没有使用的话,在客户端断开的时候自动释放。

表级锁存在一个问题,如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。

为了解决这个问题,MySQL 5.5版本之后引入了元数据锁(meta data lock,MDL),MDL 是数据库自动加锁,当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。

MDL 锁有以下两个特点:

读锁之间不互斥,因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。

行级锁

行级锁顾名思义就是针对数据库表中的行记录加锁,行级锁可以最大程度的支持并发处理,但是同时也带来了最大的锁开销。

行级锁比较容易理解,比如事务 A 更新了一行,而这时候事务 B 也要更新同一行,则必须等事务 A 的操作完成后才能进行更新。

行级锁是由存储引擎各自实现的,也并不是所有的存储引擎都支持行级锁,比如 MyISAM 引擎就不支持行级锁,这意味着 MyISAM 存储引擎要控制并发只能使用表级锁。

InnoDB 引擎实现了行级锁,InnoDB 存储引擎中实现了两种标准的行级锁:

共享锁(S Lock):允许事务读一行
排它锁(X Lock):允许事务删除和更新一行
共享锁是兼容锁,就是当一个事务已经获得了行 r 的共享锁,其他事务可以立即获得行 r 的共享锁,因为读并未改变行 r 的数据。

排他锁是非兼容锁,如果有事务想获取行 r 的排他锁,若行 r 上有共享锁或者排它锁,则它必须等其他事务释放行 r 的锁。

在 InnoDB 存储引擎中,默认情况下使用的是一致性的非锁定行读,也就是通过行多版本控制器来读取行数据,我们可以显示的为行加上共享锁和排它锁,语句如下:

SELECT ..... FOR UPDATE:对读取的行记录加一个排它锁,其他事务想要在这些行上加任何锁都会被阻塞
SELECT ....... LOCK IN SHARE MODE:对读取的行记录加一个共享锁,其他事务可以向被锁定的记录加共享锁,但是想要加排它锁。则会被阻塞。

间隙锁

(Gap Lock)是Innodb在可重复读提交下为了解决幻读问题时引入的锁机制,所以,你如果把隔离级别设置为读提交的话,就没有间隙锁了。但同时,你要解决可能出现的数据和日志不一致问题,需要把 binlog 格式设置为 row(下面的所有案例没有特意强调都使用可重复读隔离级别)幻读的问题存在是因为新增或者更新操作,这时如果进行范围查询的时候(加锁查询),会出现不一致的问题,这时使用不同的行锁已经没有办法满足要求,需要对一定范围内的数据进行加锁,间隙锁就是解决这类问题的。

在可重复读隔离级别下,数据库是通过行锁和间隙锁共同组成的临键锁(next-key lock),来实现的

加锁规则有以下特性,我们会在后面的案例中逐一解释:

  1. 加锁的基本单位是临键锁(next-key lock),他是前开后闭原则

  2. 插叙过程中访问的对象会增加锁

  3. 索引上的等值查询--给唯一索引加锁的时候,next-key lock升级为行锁

  4. 索引上的等值查询--向右遍历时最后一个值不满足查询需求时,next-key lock 退化为间隙锁

  5. 唯一索引上的范围查询会访问到不满足条件的第一个值为止

间隙锁案例

案例数据

id(主键)c(普通索引)d(无索引
555
101010
151515
202020
252525

以上数据为了解决幻读问题,更新的时候不只是对上述的五条数据增加行锁,还对于中间的取值范围增加了6间隙锁,(-∞,5](5,10](10,15](15,20](20,25](25,+supernum] (其中supernum是数据库维护的最大的值。为了保证间隙锁都是左开右闭原则。)

案例一:间隙锁简单案例

步骤事务A事务B
1begin; select * from t where id = 11 for update;-
2-begin; insert into user value(12,12,12) file
3commit;-

当有如下事务A和事务B时,事务A会对数据库表增加(10,15]这个区间锁,这时insert id = 12 的数据的时候就会因为区间锁(10,15]而被锁住无法执行

案例二: 间隙锁死锁问题

步骤事务A事务B
1begin; select * from t where id = 9 for update;-
2-begin; select * from t where id = 6 for update;
3-insert into user value(7,7,7)
4insert into user value(7,7,7)-

不同于写锁相互之间是互斥的原则,间隙锁之间不是互斥的,如果一个事务A获取到了(5,10]之间的间隙锁,另一个事务B也可以获取到(5,10]之间的间隙锁。这时就可能会发生死锁问题,如下案例。

事务A获取到(5,10]之间的间隙锁不允许其他的DDL操作,在事务提交,间隙锁释放之前,事务B也获取到了间隙锁(5,10],这时两个事务就处于死锁状态

案例三: 等值查询—唯一索引

步骤事务A事务B事务C
1begin; update u set d= d+ 1 where id = 7;--
2-begin; insert into u (8,8,8);-
3--update set d = d+ 1 where id = 10

加锁的范围是(5,10]的范围锁
由于数据是等值查询,并且表中最后数据id = 10 不满足id= 7的查询要求,故id=10 的行级锁退化为间隙锁,(5,10)
所以事务B中id=8会被锁住,而id=10的时候不会被锁住

案例四: 等值查询—普通索引

步骤事务A事务B事务C
1begin; select id form t where c = 5 lock in share mode;--
2-update t set d = d + 1 where id = 5-
3--insert into values (7,7,7)
  1. 加锁的范围是(0,5],(5,10]的范围锁
  2. 由于c是普通索引,根据原则4,搜索到5后继续向后遍历直到搜索到10才放弃,故加锁范围为(5,10]
  3. 由于查询是等值查询,并且最后一个值不满足查询要求,故间隙锁退化为(5,10)
  4. 因为加锁是对普通索引c加锁,而且因为索引覆盖,没有对主键进行加锁,所以事务B执行正常
  5. 因为加锁范围(5,10)故事务C执行阻塞
    需要注意的是,lock in share mode 6. 因为覆盖索引故没有锁主键索引,如果使用for update 程序会觉得之后会执行更新操作故会将主键索引一同锁住

案例五: 范围查询—唯一索引

步骤事务A事务B事务C
1begin; select * form t where id >= 10 and id <11 for update;--
2-insert into values(8,8,8)file-
3--update t set d = d+ 1 where id = 15
  1. next-key lock 增加范围锁(5,10]

  2. 根据原则5,唯一索引的范围查询会到第一个不符合的值位置,故增加(10,15]

  3. 因为等值查询有id =10 根据原则3间隙锁升级为行锁,故剩余锁[10,15]

  4. 因为查询并不是等值查询,故[10,15]不会退化成[10,15)

  5. 故事务B(13,13,13)阻塞,事务C阻塞

案例六: 范围查询—普通索引

步骤事务A事务B事务C
1begin; select * form t where c >= 10 and c <11 for update;--
2-insert into values(8,8,8)-
3--update t set d = d+ 1 where c = 15
  1. next-key lock 增加范围锁(5,10],(10,15]

  2. 因为c是非唯一索引,故(5,10]不会退化为10

  3. 因为查询并不是等值查询,故[10,15]不会退化成[10,15)

  4. 所以事务B和事务C全部堵塞

间隙锁和MVVC

上面说到(Gap Lock)是Innodb在可重复读提交下为了解决幻读问题时引入的锁机制,那么幻读是什么、GapLock又解决了什么问题?我们一起了解下面的内容吧

事务的隔离级别

  1. Read Uncommitted(未提交读)

  2. Read Commited(提交读)

  3. Repeatable Read(可重复读)

  4. serializable(可串行化)

各个隔离级别可能出现的问题

隔离级别脏读不可重复读幻读
未提交读
提交读
可重复读
可串行化

MVCC

mvcc对版本并发控制(Multi-Version Conncurrency Control)是mysql中基于乐观锁原理实现的隔离级别的方式。用于实现读已提交和可重复读取隔离级别。

《高性能MySQL》中MVCC的解释如下

InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的。这两个列,一个保存了行的创建时间,一个保存行的过期时间(或删除时间),当然存储的并不是实际的时间值,而是系统版本号(system version number).每开始一个新的事务,系统版本号都会自动递增,事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行记录的版本号进行比较。下面看一下在repeatable Read 隔离级别下,MVCC具体是如何操作的。
	
	SELECT
		(1)InnoDB只查找版本遭遇当前事务版本的数据行(行的系统版本号小于或等于事务的系统版本号),这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的。
		(2)行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行,在事务开始之前没有被删除。
		只有符合上述两个条件的记录,才能作为查询结果。
	
	INSERT
		innoDB为新插入的每一行保存当前系统版本作为行版本号。
	
	DELETE
		innoDB为删除的每一行保存当前系统版本号作为行删除标识。
	
	UPDATE
		InnoDB为插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识

保存这两个额外系统版本号,使大多数读操作都可以不用加锁,这样设计使得读数据操作很简单,性能很好,并且也能保证只读取到符合标准的行。不足之处的每行记录都需要额外的空间存储。需要做更多的行检查工作,以及一些额外的维护工作。

MVCC只在repeatable read 和read committed两个隔离级别下工作,其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行,而不是符合当前师傅版本的数据行。而serializable则会对所有读取的行加锁

快照读和当前读

在RR级别中,通过MVCC机制,虽然让数据变得可重复读,但我们读到的数据可能是历史数据,不是数据库最新的数据,这个读取历史数据的方式,我们叫他快照度,而读取数据库最新版本数据的方式,叫做当前读。

快照读

当执行select操作,innodb默认会执行快照度,会记录下这个select后的结果,之后select的时候就会返回这次快照的数据,即使其他事务提交了不会影响当前select的数据,这就实现了可重复读了。快照的生成当在第一次执行select的时候,也就是说假设当A开启了事务,然后没有执行任何操作,这个时候B insert了一条数据然后commit,这时候A执行select,满额返回的数据中心就会有B添加的那条数据,之后无论再有其他事务commit都没有关系,因为快照已经生成了,后面的select都是根据快照来的。

当前读

对于会对数据修改的操作(update,insert,delete)都是采用当前读的模式。在执行这几个操作时会读取最新的版本号记录,写操作后把版本号改为了当前事务的版本号,所以即使是被的事务提交的数据也可以查询到。假设要update一条数据,但是在另一个事务中已经delete掉这条数据并且commit了,如果update就会产生冲突,所以在update的时候需要知道最新的数据,也正是因为这样才导致幻读。

MVVC和间隙锁结合

MVVC产生幻读的原因是,行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB 只好引入新的锁,也就是间隙锁 (GapLock)。当然,间隙锁也会带来一些并发问题。