通过12334说说InnoDB里面的锁

前段时间我们在生产遇到MySQL阻塞的问题,后面经过排查发现是锁竞争导致的长时间阻塞,导致应用服务出现异常.这两周通过相关书籍与官方文档对InnoDB引擎做了深入了解,掌握了锁的概念,输出本篇文章,旨在说清楚锁相关的概念,并总结好相关知识,为以后问题排查留一个底,方便以后即使查阅复盘

一、锁的概念

指的是InnoDB为了在并发访问情况下对数据进行保护,防止数据错误而制作出来的一套数据保护机制.类似Java里面读写锁,Java里面读写锁保护的是内存共享变量.而在InnoDB这里,锁保护的是数据的库、表、页、行记录.

注意:本文只讨论InnoDB,MyISAM引擎只有表锁的概念.

二、12334

综合InnoDB里面锁的知识点,作者摘选了重要的部分,将用这几个数字来表达.
1指的是1个语句
2指的是2种读方式
3指的是3种锁算法
3指的是3张阻塞定位查询表
4指的是4中锁类型
先记住这几个数字,下面会按重点排序进行介绍

1、4把锁

InnoDB中实现了4把锁,就像Java中读锁、写锁的概念差不多.

先说说行级别的锁,就是说以下的这两个锁都是在行这个级别对数据进行锁定.
S:共享锁,Share Lock,在事务使用 加锁读 读数据的时候会申请这把锁,得到锁之后,会锁住目标数据.可以类比Java中的读锁.
X:独占锁,Exclusive Lock,在事务对目标数据进行更新或删除的时候,会申请这把锁,得到锁之后,会锁住目标数据,可以类比Java中的写锁.

正如他们的名字含义一致,共享锁,可以同一时间被多个事务(锁的拥有者是事务)所占有,而独占锁是排他的,同一时间只有一个事务所拥有,下面这个表是当两个事务对同一数据申请锁的时候会发生的情况.兼容表示可以正常申请到,冲突表示此时会引起阻塞.

锁/兼容或冲突	S	X
S	兼容	冲突
X	冲突	冲突

除了上面两个重点的行锁之外,InnoDB中提供了意向锁的概念.再说说意向锁.
就如上面看到两个锁都是行级别的,但是一个数据库并不仅仅只有行,还有表数据.
所以InnoDB为了支持多种级别粒度的锁定,提出了意向锁这个概念,比如说一个语句想要锁定一个表(lock tables...),那么就会在这个上面加上一个意向锁,然后到了具体的某些数据行,再加上对应的X/S,以这样的方式让InnoDB能真正做到锁表,而不仅仅在锁行,可以暂时理解(实际是多粒度的,可以锁库等)为意向锁就是InnoDB的表锁.

注意,InnoDB对意向锁是比较简练的,两种意向锁其实是不会阻塞除全表扫以外的任何请求,后面会细说.OK,然后就说下意向锁里面的两把锁

IS:意向共享锁,表示事务想要对某个表里面的某些数据加共享锁
IX:意向独占锁,表示事务想要对某个表里面的某些数据加排他锁

意向锁的使用协议如下所示:
如果一个事务想要申请一个S锁,那么必须先申请一个IS
若果一个事务想要申请一个X锁,那么必须先申请一个IX

刚说过两种意向锁其实是不会阻塞除全表扫以外的任何请求,我们通过下表看下,IX/IS的兼容性

锁/兼容或冲突	IX	IS	X	S
IX	兼容	兼容	冲突	冲突
IS	兼容	兼容	冲突	兼容
X	冲突	冲突	冲突	冲突
S	冲突	兼容	冲突	兼容

2、1个语句3张表

上面说了说之前有冲突,就会表现锁阻塞的现象,那么我们可以通过1个语句、3张表来抓到因为锁导致的阻塞问题

1个语句

show engine innodb status;//获取引擎状态

拿到数据后,搜索 lock 关键字,我们大致可以拿到类似下面的结果(取自官网)

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

作者尝试过,还是要有一点运气,当锁住的时候,可以拿到锁的一些相关信息.
3个表
表1.阻塞的信息查询,字段内容见注解或直接搜索官方文档

select * from information_schema.INNODB_LOCK_WAITS;

CREATE TEMPORARY TABLE `INNODB_LOCK_WAITS` (
  `requesting_trx_id` varchar(18) NOT NULL DEFAULT '',//请求所的事务ID
  `requested_lock_id` varchar(81) NOT NULL DEFAULT '',//申请的锁ID
  `blocking_trx_id` varchar(18) NOT NULL DEFAULT '',//拥有锁的事务ID
  `blocking_lock_id` varchar(81) NOT NULL DEFAULT ''//拥有的锁ID
) ENGINE=MEMORY DEFAULT CHARSET=utf8

表2.事务表

select * from information_schema.INNODB_TRX;

CREATE TEMPORARY TABLE `INNODB_TRX` (
  `trx_id` varchar(18) NOT NULL DEFAULT '',//事务ID
  `trx_state` varchar(13) NOT NULL DEFAULT '',//事务状态
  `trx_started` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',//事务开始时间
  `trx_requested_lock_id` varchar(81) DEFAULT NULL,//请求的所ID
  `trx_wait_started` datetime DEFAULT NULL,//事务等待开始时间
  `trx_weight` bigint(21) unsigned NOT NULL DEFAULT '0',//权重、用于回滚
  `trx_mysql_thread_id` bigint(21) unsigned NOT NULL DEFAULT '0',//线程ID,可以用来杀进程哦
  `trx_query` varchar(1024) DEFAULT NULL,//查询语句
  `trx_operation_state` varchar(64) DEFAULT NULL,
  `trx_tables_in_use` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_tables_locked` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_lock_structs` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_lock_memory_bytes` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_rows_locked` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_rows_modified` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_concurrency_tickets` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_isolation_level` varchar(16) NOT NULL DEFAULT '',
  `trx_unique_checks` int(1) NOT NULL DEFAULT '0',
  `trx_foreign_key_checks` int(1) NOT NULL DEFAULT '0',
  `trx_last_foreign_key_error` varchar(256) DEFAULT NULL,
  `trx_adaptive_hash_latched` int(1) NOT NULL DEFAULT '0',
  `trx_adaptive_hash_timeout` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_is_read_only` int(1) NOT NULL DEFAULT '0',
  `trx_autocommit_non_locking` int(1) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8

表3.描述锁的表

select * from information_schema.INNODB_LOCKS;

CREATE TEMPORARY TABLE `INNODB_LOCKS` (
  `lock_id` varchar(81) NOT NULL DEFAULT '',//锁ID
  `lock_trx_id` varchar(18) NOT NULL DEFAULT '',//占有锁的事务ID
  `lock_mode` varchar(32) NOT NULL DEFAULT '',//锁的模式S or W
  `lock_type` varchar(32) NOT NULL DEFAULT '',//锁的类型、行锁 or 表锁 
  `lock_table` varchar(1024) NOT NULL DEFAULT '',//锁住的表
  `lock_index` varchar(1024) DEFAULT NULL,//锁所在的索引
  `lock_space` bigint(21) unsigned DEFAULT NULL,
  `lock_page` bigint(21) unsigned DEFAULT NULL,
  `lock_rec` bigint(21) unsigned DEFAULT NULL,
  `lock_data` varchar(8192) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8

我们可以结合上面三个表,通过阻塞表关联查询得到阻塞时的事务与运行语句,方便我们快速定位问题.

3、3个锁算法

其实这个这里用“算法”这个词并不准确,就是应该说是锁,到底锁住的内容是什么?因为书里用算法一词,那么作者就跟着用了.好,那我们来看看到底锁的是什么

1.记录锁:指的是索引上面每一行记录的锁.注意,锁一定是加在索引上面的,如果这个表没有索引,那么锁就是加在隐藏的那个聚集索引上面
如以下sql示例,这样将会对c1=10的记录加上锁

select c1 from t where c1 = 10 for update;//加写锁
select c1 from t where c1 = 10 in share mode;//加读锁

2.间隙锁:也就是常说的gap锁,指的是锁住一个范围,这个锁也是在索引上面加锁的.但要注意的是gap锁并不是一定会出现的,它跟事务隔离级别有关系.比如在读提交这个事务隔离级别下 它就不会锁范围了(只有检查唯一索引的时候,且唯一索引索引由多个字段组成,查询的时候只提供一个字段为查询条件才会加gap锁).
如一下sql示例,如果id这个字段并不是唯一索引,且此时的事务隔离级别是不可重复读那么数据库将锁住[x,100],(100,y]这两个范围,x,y表示是库中靠近100的值

select * from child where id = 100;

3.next-key锁:就是#1跟#2的组合

3、2种读

InnoDB中仅有这两种读取方式,理解这两种读取方式有助于我们判断sql语句是否会进行加锁.
一致性非锁定读:指的是当执行普通查询的时候,InnoDB读取的当时数据的一个快照版本.下面将通过三个方面进行介绍
1.原理
其原理是通过MVCC机制实现的.说白了就是读取undo文件里面的某一个版本的快照日志,然后直接返回,这样就不会阻塞InnoDB的其他操作.
2.读取的版本
读取的数据版本跟事务隔离级别有关:

• 在读提交下,每次查询的数据都是最新一个版本的快照数据.在一个事务中重复读取数据可能会不一样
• 在可重复读的情况下,读取的当前事务开始时那个时刻的数据,这样在这个事务中重复读取这份数据都是一样的了

3.性能问题:
这种模式不会消耗性能.因为MVCC机制是基于undo日志来实现的,undo日志又是可用来回滚的,所以undo日志必须存在,所以去读undo里面的日志不会带来额外消耗.

一致性锁定读:指的是在读取的时候顺便对记录或范围加锁,然后就回到上面的话题,不再细说.

select c1 from t where c1 = 10 for update;//加写锁
select c1 from t where c1 = 10 in share mode;//加读锁

三、其他相关但是不重要的知识

1、InnoDB死锁的两个点

1.死锁的发生
是因为几个不同事务对锁的竞争导致的死锁
2.InnoDB对死锁的解决
2.1 超时机制,通过全局变量控制超时时间

innodb_lock_wait_timeout

2.2 锁引用的关联关系图.
死锁的解决都是InnoDB自身的内部机制保障的,程序员不关心,不过如果一直通过慢查询日志看到有死锁现象的话,那么可以通过别的途径去避免死锁的发生,这里就不再拓展.

2、sql语句加锁一览表

SELECT ... FROM --一致性非锁定读,不加锁

-- 一致性锁定读
SELECT ... FOR UPDATE --加写锁
SELECT ... LOCK IN SHARE MODE --加读锁

--加写锁,根据条件决定是否加gap锁
UPDATE ... WHERE ...
DELETE FROM ... WHERE ...

--加写锁,只加记录锁
INSERT
INSERT ... ON DUPLICATE KEY UPDATE
REPLACE INSERT

总结一下遇到阻塞问题的解决方法
1.show engine innodb status看下是否有锁
2.通过INNODB_LOCK_WAITS,INNODB_TRX,INNODB_LOCKS3张表得到锁信息
3.分析sql+索引+代码,看下是哪里的问题导致锁竞争.需要留意唯一索引

InnoDB锁的12334你记住了吗?
参考:
《MySQL技术内幕》
官方文档:https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html