专栏首页aoho求索MySQL探秘(五):InnoDB锁的类型和状态查询

MySQL探秘(五):InnoDB锁的类型和状态查询

 锁是数据库系统区分于文件系统的一个关键特性。数据库使用锁来支持对共享资源进行并发访问,提供数据的完整性和一致性。此外,数据库事务的隔离性也是通过锁实现的。InnoDB在此方面一直优于其他数据库引擎。InnoDB会在行级别上对表数据上锁,而MyISAM只能在表级别上锁,二者性能差异可想而知。

InnoDB存储引擎中的锁

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

  • 共享锁(S Lock),允许事务读取一行
  • 排他锁(X Lock),允许事务删除或更新一行数据

  如果一个事务T1已经获取了行r的共享锁,那么另外一个事务T2可以立刻获得行r的共享锁,因为读取并不会改变数据,可以进行并发的读取操作;但若其他的事务T3想要获取行r的排他锁,则必须等待事务T1和T2释放行r上的共享锁之后才能继续,因为获取排他锁一般是为了改变数据,所以不能同时进行读取或则其他写入操作。

X

S

X

不兼容

不兼容

S

不兼容

兼容

 InnoDB存储引擎支持多粒度锁定,这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作,InnoDB存储引擎支持一种称为意向锁的锁方式。意向锁是将锁定的对象分为多个层次,意向锁意味着事务希望在更细粒度上进行加锁。

 InnoDB存储引擎的意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁:

  • 意向共享锁(IS Lock),事务想要获得一张表中某几行的共享锁
  • 意向排他锁(IX Lock),事务想要获得一张表中某几行的排他锁

 需要注意的是意向锁是表级别的锁,它不会和行级的X,S锁发生冲突。只会和表级的X,S发生冲突。故表级别的意向锁和表级别的锁的兼容性如下表所示。

IS

IX

S

X

IS

兼容

兼容

兼容

不兼容

IX

兼容

兼容

不兼容

不兼容

S

兼容

不兼容

兼容

不兼容

X

不兼容

不兼容

不兼容

不兼容

 向一个表添加表级X锁的时候(执行ALTER TABLE, DROP TABLE, LOCK TABLES等操作),如果没有意向锁的话,则需要遍历所有整个表判断是否有行锁的存在,以免发生冲突。如果有了意向锁,只需要判断该意向锁与即将添加的表级锁是否兼容即可。因为意向锁的存在代表了,有行级锁的存在或者即将有行级锁的存在,因而无需遍历整个表,即可获取结果。

层次结构.jpg

 如果将上锁的对象看成一棵树,那么对最下层的对象上锁,也就是对最细粒度的对象进行上锁,那么首先需要对粗粒度的对象上锁。如上图所示,如果需要对表1的记录m行上X锁,那么需要先对表1加意向IX锁,然后对记录m上X锁。如果其中任何一个部分导致等待,那么该操作需要等待粗粒度锁的完成。

InnoDB锁相关状态查询

 用户可以使用INFOMATION_SCHEMA库下的INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS表来监控当前事务并分析可能出现的锁问题。INNODB_TRX的定义如下表所示,其由8个字段组成。

  • trx_id:InnoDB存储引擎内部唯一的事务ID
  • trx_state:当前事务的状态
  • trx_started:事务的开始时间
  • trx_request_lock_id:等待事务的锁ID。如果trx_state的状态为LOCK WAIT,那么该字段代表当前事务等待之前事务占用的锁资源ID
  • trx_wait_started:事务等待的时间
  • trx_weight:事务的权重,反映了一个事务修改和锁住的行数,当发生死锁需要回滚时,会选择该数值最小的进行回滚
  • trx_mysql_thread_id:线程ID,SHOW PROCESSLIST 显示的结果
  • trx_query:事务运行的SQL语句
mysql> SELECT * FROM information_schema.INNODB_TRX\G;
************** 1.row ***********************
trx_id:  7311F4
trx_state: LOCK WAIT
trx_started: 2010-01-04 10:49:33
trx_requested_lock_id: 7311F4:96:3:2
trx_wait_started: 2010-01-04 10:49:33
trx_weight: 2
trx_mysql_thread_id: 471719
trx_query: select * from parent lock in share mode

 INNODB_TRX表只能显示当前运行的InnoDB事务,并不能直接判断锁的一些情况。如果需要查看锁,则还需要访问表INNODB_LOCKS,该表的字段组成如下表所示。

  • lock_id:锁的ID
  • lock_trx_id:事务的ID
  • lock_mode:锁的模式
  • lock_type:锁的类型,表锁还是行锁
  • lock_table:要加锁的表
  • lock_index:锁住的索引
  • lock_space:锁住的space id
  • lock_page:事务锁定页的数量,若是表锁,则该值为NULL
  • lock_rec:事务锁定行的数量,如果是表锁,则该值为NULL
  • lock_data:
mysql> SELECT * FROM information_schema.INNODB_LOCKS\G;
***************** 1.row ***********************
lock_id: 7311F4:96:3:2
lock_trx_id: 7311F4
lock_mode: S
lock_type: RECORD
lock_type: 'mytest'.'parent'
lock_index: 'PRIMARY'
lock_space: 96
lock_page: 3
lock_rec: 2
lock_data: 1

通过表INNODB_LOCKS查看每张表上锁的情况后,用户就可以来判断由此引发的等待情况。当时当事务量非常大,其中锁和等待也时常发生,这个时候就不那么容易判断。但是通过表INNODB_LOCK_WAITS,可以很直观的反应当前事务的等待。表INNODB_LOCK_WAITS由四个字段组成,如下表所示。

  • requesting_trx_id:申请锁资源的事务ID
  • requesting_lock_id:申请的锁的ID
  • blocking_trx_id:阻塞的事务ID
  • blocking_lock_id:阻塞的锁的ID
mysql> SELECT * FROM information_schema.INNODB_LOCK_WAITS\G;
******************1.row**************************
requesting_trx_id: 7311F4
requesting_lock_id: 7311F4:96:3:2
blocking_trx_id: 730FEE
blocking_lock_id: 730FEE:96:3:2

通过上述的SQL语句,用户可以清楚直观地看到哪个事务阻塞了另一个事务,然后使用上述的事务ID和锁ID,去INNODB_TRX和INNDOB_LOCKS表中查看更加详细的信息。

后记

 我们后续还会学习InnoDB的一致性非锁定读相关的知识,请大家持续关注。

本文分享自微信公众号 - aoho求索(aohoBlog)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-11-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL探秘(六):InnoDB一致性非锁定读

     一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过多版本控制(MVVC)读取当前数据库中行数据的方式。如果读取...

    aoho求索
  • Go 1.14 正式发布,重要更新有哪些

    Go 在 2019 年发布了Go 1.12与Go 1.13。Go 1.13 的大部分变化在于工具链、运行时和库的实现。时隔半年,Go 1.14 正式发布。

    aoho求索
  • Eureka Server之间的注册表信息同步

    前言 Eureka 作为一个服务注册中心,Eureka Server必然是可以通过集群的方式进行部署,但是分布式系统中一个很关键的点就是数据的一致性,多节点部署...

    aoho求索
  • MySQL探秘(五):InnoDB锁的类型和状态查询

     锁是数据库系统区分于文件系统的一个关键特性。数据库使用锁来支持对共享资源进行并发访问,提供数据的完整性和一致性。此外,数据库事务的隔离性也是通过锁实现的。In...

    程序员历小冰
  • android 休眠唤醒机制分析(一) — wake_lock【转】

    Android的休眠唤醒主要基于wake_lock机制,只要系统中存在任一有效的wake_lock,系统就不能进入深度休眠,但可以进行设备的浅度休眠操作。wak...

    233333
  • Python3学习笔记 | 七、Python的类型与运算-字典

    字典在Python里是无序集合对象类型,字典的值都有独立的唯一的键(Key),用相应的键来取值。

    TeamsSix
  • 【自考】数据结构第六章查找,期末不挂科指南,第10篇

    查找表 是由同一类型的数据元素 构成的集合,它是一种以查找为“核心”,同时包括其他运算的非常灵活的数据结构。

    梦想橡皮擦
  • go cannot find package "golang.org/x/crypto/ssh/terminal" 解决方案

    莫斯
  • 学界 | 在深度学习时代用 HowNet 搞事情

    2017 年 12 月底,清华大学张钹院士做了一场题为《AI 科学突破的前夜,教授们应当看到什么?》的精彩特邀报告。他认为,处理知识是人类所擅长的,而处理数据是...

    AI科技评论
  • 专栏 | 清华大学刘知远:在深度学习时代用HowNet搞事情

    机器之心

扫码关注云+社区

领取腾讯云代金券