首页 > 数据库 >【MySQL】全局锁、表级锁、行级锁

【MySQL】全局锁、表级锁、行级锁

时间:2023-01-16 18:05:16浏览次数:51  
标签:行级 事务 加锁 MDL 索引 MySQL 表锁 数据 表级


[1] 前言

  索引列数据锁的设计主要用来解决并发带来的问题。当一个业务场景中出现多用户共享同一资源,当出现并发访问的时候,数据库需要合理的控制资源的访问规则,锁就是用来控制这些访问规则的。

  根据加锁的范围,MySQL里的锁大致可以划分为全局锁,表级锁和行锁三类,如下图:

【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中的每一行,看看有没有行级锁和它即将要加的排它锁冲突,这样效率低下。所以提出意向锁来解决这个问题。

  锁模式的兼容情况:

【MySQL】全局锁、表级锁、行级锁_排它锁_02

  · 在加行锁之前,由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 执行按照非唯一索引进行数据的检索时,会给匹配到行上加上临键锁。

  它可以看作行锁和间隙锁的组合。


标签:行级,事务,加锁,MDL,索引,MySQL,表锁,数据,表级
From: https://blog.51cto.com/u_15942590/6010663

相关文章

  • 【MySQL】索引不会被使用的7种情况
    [1]隐式类型转换  索引列出现了隐式类型转换(​​ImplicitTypeConversion​​​),则​​MySQL​​​不会使用索引。常见的情况是,如果在​​SQL​​​的​​WHERE​​​......
  • 【MySQL思考】触发器是否应该在生产环境中使用?
    [1]触发器简单介绍  MySQL触发器​​trigger​​​是一种存储程序,它和一个指定的表相关联,当该表中的数据发生变化(增加、更新、删除)时自动执行。这些修改数据行的操作......
  • MySQL 中的事务控制机制
    事务控制是MySQL的重要特性之一。在MySQL中,InnoDB和NDBCluster是常见的事务型存储引擎。1.自动提交默认情况下,MySQL是自动提交(autocommit)的。也就意味着:如果不......
  • mac虚拟环境Reason: tried: '/usr/local/lib/libmysqlclient.21.dylib' (no such file
    关于django链接数据库时,出现了找不到lib/libmysqlclient.21.dylib的问题。在网上百度了好久,终于用如下的命令解决了。版本信息虚拟环境python=3.7MYSQL=8.0.31mysqlc......
  • MySQL中的事务
    事务简介事务:是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。就比如:......
  • mysql主从同步失败 错误码1594
    起因某日mysql主从同步发生了错误,此时发现从库不再执行同步操作,于是在从库执行showslavestatus\G发现如下错误:上图中标红色的需要重点关注。解决办法之前出现其他常......
  • mysql8.0.30主从配置
    安装包下载地址:https://downloads.mysql.com/archives/community/1、解压介质包:#tarxfmysql-8.0.30-linux-glibc2.12-x86_64.tar.xz#mvmysql-8.0.30-linux-glib......
  • MySQL自定义排序ORDER BY FIELD
    在一些场景中,有场景A查询出一个已经排好顺序的id,需要到场景B中查询这些,使用mysql中的WHERE**IN(****),查询出来的结果并不是按照传入的list排序的.但是......
  • MySQL必知必会第十章-创建计算字段
    创建计算字段计算字段计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。字段(field)基本上与列(column)的意思相同,经常互换使用,不过数据库列一......
  • MySQL优化四,高性能优化
    一,查询优化器这个部分的整个过程是由MySQL的存储引擎来做的,优化器就会根据存储引擎来使用原来的开销,优化后的开销,哪个更好一点? 1.如果是查询语句(select语句),首先会查......