[1] 前言
索引列数据锁的设计主要用来解决并发带来的问题。当一个业务场景中出现多用户共享同一资源,当出现并发访问的时候,数据库需要合理的控制资源的访问规则,锁就是用来控制这些访问规则的。
根据加锁的范围,MySQL里的锁大致可以划分为全局锁,表级锁和行锁三类,如下图:
[2] 全局锁
全局锁是对整个数据库实例加锁。使用了全局锁之后,整个库处于只读状态,其他写操作会被阻塞。
应用场景:做全库逻辑备份,即把整个库中每个表都select出来存成文本。
[3] 表级锁
· 表级锁开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低;
· 表级锁更适合于以查询为主,并发用户少,只有少量按索引条件更新数据的应用,如Web 应用。
表级锁分为表锁、元数据锁、意向锁。
[3.1] 表锁
· 表锁分为共享锁和排它锁。
共享锁 = 读锁 ; 排它锁 = 写锁。
· 共享锁(S):允许一个事务去读表,阻止其他事务获得相同表的排他锁。
· 排他锁(X):允许获得排他锁的事务更新表,阻止其他事务取得相同数据集的共享读锁和排他写锁。
共享锁:A开了表的共享锁,那么A可以读表,其他事务也能读表,但是都不能写。
排他锁:A开了表的排他锁,那么A可以读表,A可以写表,其他事务不可读不可写这个表。
[3.2] 元数据锁
MDL全称为metadata lock,即元数据锁。MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务(显式或隐式)的时候,不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁,来保护表的元数据信息,用于解决或者保证DDL操作与DML操作之间的一致性。
对于引入MDL,其主要解决了2个问题,一个是事务隔离问题,比如在可重复隔离级别下,会话A在2次查询期间,会话B对表结构做了修改,两次查询结果就会不一致,无法满足可重复读的要求;另外一个是数据复制的问题,比如会话A执行了多条更新语句期间,另外一个会话B做了表结构变更并且先提交,就会导致slave在重做时,先重做alter,再重做update时就会出现复制错误的现象。
元数据锁是server层的锁,表级锁,每执行一条DML、DDL语句时都会申请MDL锁,DML操作需要MDL读锁,DDL操作需要MDL写锁(MDL加锁过程是系统自动控制,无法直接干预,读读共享,读写互斥,写写互斥),申请MDL锁的操作会形成一个队列,队列中写锁获取优先级高于读锁。一旦出现写锁等待,不但当前操作会被阻塞,同时还会阻塞后续该表的所有操作。事务一旦申请到MDL锁后,直到事务执行完才会将锁释放。(这里有种特殊情况如果事务中包含DDL操作,mysql会在DDL操作语句执行前,隐式提交commit,以保证该DDL语句操作作为一个单独的事务存在,同时也保证元数据排他锁的释放)。
[3.3] 意向锁
· 意向锁分为意向共享锁和意向排它锁。
为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB 还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁:
· 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
· 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
假设表T中有一行加了行级排它锁,现在想要对表T加排它锁,那么数据库就要遍历表T中的每一行,看看有没有行级锁和它即将要加的排它锁冲突,这样效率低下。所以提出意向锁来解决这个问题。
锁模式的兼容情况:
· 在加行锁之前,由InnoDB存储引擎加上表的IS或IX锁
· 意向锁之间都是兼容的,不会产生冲突,主要是为了辅助其他的在获取表锁的时候加快效率
· 意向锁存在的意义是为了更高效的获取表锁(表格中的X和S指的是表锁,不是行锁!)
· 意向锁是表级锁,协调表锁和行锁的共存关系。主要目的是显示事务正在锁定某行或者试图锁定某行。
举例:分析事务1获取行X锁和事务2获取表S锁:
首先事务1需要给表的第10行数据加X锁,于是InnoDB存储引擎自动给整张表加上了IX锁。当事务2再想获取整张表的S锁时,看到这张表已经有别的事务获取了IX锁了,就说明这张表肯定有某些数据被加上了X锁,这就导致事务2不能给整张表加S锁了。此时事务2只能等待,无法成功获取表S锁。
[4] 行级锁
· 开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高;
· 最大程度的支持并发,同时也带来了最大的锁开销;
· 在 InnoDB 中,除单个 SQL 组成的事务外,锁是逐步获得的,这就决定了在 InnoDB 中发生死锁是可能的;
· 行级锁只在存储引擎层实现,而Mysql服务器层没有实现。 行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。
行级锁分为行锁、间隙锁、临键锁。
[4.1] 行锁
· 行锁分为共享锁和排它锁。
与表锁一样,只不过一个针对表、一个针对行。
[4.2] 间隙锁
间隙锁是一个在索引记录之间的间隙上的锁。保证某个间隙内的数据在锁定情况下不会发生任何变化。比如mysql默认隔离级别下的可重复读(RR)。
当使用唯一索引来搜索唯一行的语句时,不需要间隙锁定。如下面语句的id列有唯一索引,此时只会对id值为10的行使用记录锁。
select * from t where id = 10 for update;// 注意:普通查询是快照读,不需要加锁
如果,上面语句中id列没有建立索引或者是非唯一索引时,则语句会产生间隙锁。
如果,搜索条件里有多个查询条件(即使每个列都有唯一索引),也是会有间隙锁的。
需要注意的是,当id列上没有索引时,SQL会走聚簇索引的全表扫描进行过滤,由于过滤是在MySQL Server层面进行的。因此每条记录(无论是否满足条件)都会被加上X锁。但是,为了效率考量,MySQL做了优化,对于不满足条件的记录,会在判断后放锁,最终持有的,是满足条件的记录上的锁。但是不满足条件的记录上的加锁/放锁动作是不会省略的。所以在没有索引时,不满足条件的数据行会有加锁又放锁的耗时过程。
[4.3] 临键锁
临键锁可以理解为锁住的是索引本身以及索引之前的间隙,是一个左开右闭的区间。当 SQL 执行按照非唯一索引进行数据的检索时,会给匹配到行上加上临键锁。
它可以看作行锁和间隙锁的组合。