加锁情况与死锁原因分析
为方便大家复现,完整表结构和数据如下：
+c1+ int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (+c1+),
) ENGINE=InnoDB
死锁日志如下：
INSERT INTENTION LOCK
但是插入意向锁是客观存在的,我们可以在官方手册中查到,不可忽略：
Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.
当插入一条记录时,会去检查当前插入位置的下一条记录上是否存在锁对象,如果下一条记录上存在锁对象,就需要判断该锁对象是否锁住了 gap.如果 gap 被锁住了,则插入意向锁与之冲突,进入等待状态（插入意向锁之间并不互斥）.最后提醒一下大家这把锁的属性：
① 它不会阻塞其他任何锁；
在学习 MySQL 过程中,一般只有在它被阻塞的时候才能观察到,所以这也是它常常被忽略的原因吧...
GAP LOCK
在此例中,另外一个重要的点就是 gap lock,通常情况下我们说到 gap lock 都只会联想到 REPEATABLE-READ 隔离级别利用其解决幻读.但实际上在 READ-COMMITTED 隔离级别,也会存在 gap lock ,只发生在：唯一约束检查到有唯一冲突的时候,会加 S Next-key Lock,即对记录以及与和上一条记录之间的间隙加共享锁.
通过下面这个例子就能验证：
有个困惑很久的疑问：出现唯一冲突需要加 S Next-Key Lock 是事实,但是加锁的意义是什么?还是说是通过 S Next-Key Lock 来实现的唯一约束检查,但是这样意味着在插入没有遇到唯一冲突的时候,这个锁会立刻释放,这不符合二阶段锁原则.这点希望能与大家一起讨论得到好的解释.
如果是在 REPEATABLE-READ,除以上所说的唯一约束冲突外,gap lock 的存在是这样的：
对于 gap lock,相信 DBA 们的心情是一样一样的,所以我的建议是：
① 在绝大部分的业务场景下,都可以把 MySQL 的隔离界别设置为 READ-COMMITTED；
锁冲突矩阵
前面我们说的 GAP LOCK 其实是锁的属性,另外我们知道 InnoDB 常规锁模式有：S 和 X,即共享锁和排他锁.锁模式和锁属性是可以随意组合的,组合之后的冲突矩阵如下,这对我们分析死锁很有帮助：
对表的增删改查,都需要MDL锁,无所不在
MDL读锁之间不互斥,但MDL读写锁互斥
#举个栗子
假设t是一张大表
session1对t执行一个查询（SR）
事务中的MDL锁在语句开始时申请,但并不会在语句结束后就马上释放,而是会等到事务结束时才进行释放
忙时对大表DDL会产生的灾难性的结果就是：如果后续对该表有查询操作,而且web端又有重试机制的话,那么会有一个新的session再次发起读请求,反复如此,线程池就会在短时间内爆炸
eg.
session1
select * from cpf where payid'xxx'
union
#执行结果
#变种1
如果session1在执行select之前,添加一句start transaction
也就是证实了在事务中的MDL锁,在语句查询完之后并不会释放,而是会随着事务的释放而释放
MySQL是server-engine结构,MDL锁是server层的锁
UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME='wait/lock/metadata/sql/mdl';
[mysqld]
performance-schema-instrument = 'wait/lock/metadata/sql/mdl=ON'
global：全局级（FTWRL）
schema：库级（drop database）
table：表级（lock table read/write）
commit：提交级
关于global对象,主要作用是防止DDL和写操作的过程中,执行set golbal_read_only = on或flush tables with read lock.
关于commit对象锁,主要作用是执行flush tables with read lock后,防止已经开始在执行的写事务提交.insert/update/delete在提交时都会上（COMMIT,MDL_EXPLICIT,MDL_INTENTION_EXCLUSIVE）锁
IX与大部分锁都是兼容的,除了S,当然了X肯定是不兼容的；但IX与IX之间是兼容的,比如下图
flush table with read lock会持有这个锁（在global级别和commit级别）
FTWRL在全局级和事务级上分别加上了S锁
IX与S是不兼容的
所以DML和DDL都会与FTWRL产生阻塞
逻辑备份第一句：flush table with read lock（S锁）
大表DML（IX锁）
先执行的阻塞后执行的,逻辑备份之前需要检查是否有在线DDL（X锁）以及DML（IX锁）,否则逻辑备份产生等待；尽量不要在忙时进行逻辑备份,否则阻碍忙时DML
DML并不是只有IX锁,DML和select .. for update在执行中持有的锁实际是SW锁（DML需要找一个大一点的表来验证,目前只验证了select .. for update）,IX只是DML初期需要获得的锁
但如果我们是先执行的FTWRL再执行的select for update,那么画风就不是像上图那样了
如下图所示,在先执行FTWRL的情况下,select for update压根没有获得SW锁,而是在获取IX锁的过程中就受挫了,一直处于pending状态.（如果这个S锁不释放,那么后面的IX会一直等待,直到超时）
S锁除了逻辑备份时的FTWRL以外,createa table as也会持有这个锁
目前已知的是desc操作会持有这个SH锁
SH锁与绝大部分锁都兼容,除开X锁
也就是说在做rename一类的操作的时候,你是无法去执行desc的
前面提到的start transaction,以及所有的非当前读都需要持有这个锁
非当前读的意思就是快照读,也就是普通的select
研发有时候会很困惑的问我,"我这个表只有几十行数据,select查不出来?"? 这时候就需要检查MDL锁了
当前读需要持有此锁,常见的DML和select for update都对应此锁,但不包括DDL
看到一种说法是这个锁仅对MyISAM引擎生效,冲突范围与SW锁类似
部分alter语句会持有该锁.该锁可能会升级成SNW,SNRW,X；而X锁也有可能逐步降级到SU锁
SU锁和SU,SNW,SNRW,X锁互斥
表面看起来DML的SW锁和SU锁不互斥（DML和DDL）,但实际上因为SU锁存在升级的属性,SU锁会升级到SNW锁,从而和SW产生互斥
如下图,SU并没有被SW锁阻塞,但升级到SNW之后,SNW被SW阻塞,一直处于pending状态
SU锁的兼容性如下
查看改过源码的例子,在执行alter的时候,SU会升级到X,之后X降级到SU,然后SU再升级到X
先SU,再SW,SW被SU阻塞
先SW,再SU,SU并未被SW阻塞,但是SU向上升级的过程中产生的SNW被SW阻塞；于是将SW的会话commit,之后SNW向下降级成SU,并成功获得锁；
所以虽然看起来SW和SU不是一个双向阻塞,但实际效果就是双向阻塞,无论DML和DDL谁在前面,都必然会发生相互的阻塞
不兼容的有点多,先贴一个兼容性
SU升级X的过程中会升级成SNW
SU升级成X的过程中,有一个copy的过程,这个过程就是SNW,在这个copy的过程中,允许DML但是不允许select（SR）
copy是一个非常耗时的过程
lock tables read的语句会持有这个锁
SRO阻塞SW,SNRW,X
兼容性如图
lock tables write的语句会持有这个锁
阻塞的锁非常多,除开SH和S以外,其他的都阻塞,连SR都阻塞了
兼容性如下
换句话说flush tables with read lock; （S）会堵塞lock table write; （SNRW）
但是flush tables with read lock;（S）却不会堵塞lock table read （SRO）
阻塞一切
各种DDL均属于这个范畴
create,drop,rename? （alter table add column也属于这个范畴）
SW锁阻塞X锁,（X锁是为了去执行一个drop）
X锁阻塞SH
只有1行的select被堵住
SELECT
locked_schema,
locked_table,
locked_type,
waiting_processlist_id,
waiting_age,
waiting_query,
waiting_state,
blocking_processlist_id,
blocking_age,
substring_index(sql_text,"transaction_begin;" ,-1)ASblocking_query,
sql_kill_blocking_connection
FROM
(
b.OWNER_THREAD_IDASgranted_thread_id,
a.OBJECT_SCHEMAASlocked_schema,
a.OBJECT_NAMEASlocked_table,
"Metadata Lock"ASlocked_type,
c.PROCESSLIST_IDASwaiting_processlist_id,
c.PROCESSLIST_TIMEASwaiting_age,
c.PROCESSLIST_INFOASwaiting_query,
c.PROCESSLIST_STATEASwaiting_state,
d.PROCESSLIST_IDASblocking_processlist_id,
d.PROCESSLIST_TIMEASblocking_age,
d.PROCESSLIST_INFOASblocking_query,
concat('KILL', d.PROCESSLIST_ID)ASsql_kill_blocking_connection
performance_schema.metadata_locks a
JOINperformance_schema.metadata_locks bONa.OBJECT_SCHEMA=b.OBJECT_SCHEMA
ANDa.OBJECT_NAME=b.OBJECT_NAME
ANDa.lock_status='PENDING'
ANDb.lock_status='GRANTED'
ANDa.OWNER_THREAD_IDb.OWNER_THREAD_ID
ANDa.lock_type='EXCLUSIVE'
JOINperformance_schema.threads cONa.OWNER_THREAD_ID=c.THREAD_ID
JOINperformance_schema.threads dONb.OWNER_THREAD_ID=d.THREAD_ID
) t1,
thread_id,
group_concat(CASEWHENEVENT_NAME='statement/sql/begin'THEN"transaction_begin"ELSEsql_textENDORDERBYevent_id SEPARATOR ";" )ASsql_text
performance_schema.events_statements_history
GROUPBYthread_id
WHERE
MDL锁处理
MDL元数据锁
快速处理MDL锁
修改方法
systemd unit file 里支持的资源隔离配置项,如常见的：
CPUQuota=value
该参数表示服务可以获取的最大 CPU 时间,value 为百分数形式,高于 100% 表示可使用?1 核以上的?CPU.与 cgroup cpu 控制器?cpu.cfs_quota_us?配置项对应.
MemoryLimit=value
该参数表示服务可以使用的最大内存量,value 可以使用 K, M, G, T 等后缀表示值的大小.与 cgroup?memory 控制器?memory.limit_in_bytes?配置项对应.
READ UNCOMMITTED ? ? ? 未提交读,可以读取未提交的数据.
READ COMMITTED ? ? ? ? 已提交读,对于锁定读(select with for update 或者 for share)、update 和 delete 语句,InnoDB 仅锁定索引记录,而不锁定它们之间的间隙,所以呢允许在锁定的记录旁边自由插入新记录. ? ? ? ? ? ? ? ? ? ?
Gap locking 仅用于外键约束检查和重复键检查.
REPEATABLE READ ? ? ? ?可重复读,事务中的一致性读取读取的是事务第一次读取所建立的快照.
数据范围全集组成
SQL 语句根据条件判断不需要扫描的数据范围（不加锁）；
SQL 语句根据条件扫描到的可能需要加锁的数据范围；
以单个数据范围为例,数据范围全集包含：（数据范围不一定是连续的值,也可能是间隔的值组成）
全局锁就是对整个数据库实例加锁,加锁后整个实例就处于只读状态,后续的DML的写语句,DDL语句,已经更新操作的事务提交语句都将阻塞.其典型的使用场景就是做全库的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的完整性.但是对数据库加全局锁是有弊端的,如在主库上备份,那么在备份期间都不能执行更新,业务会受影响,第二如果是在从库上备份,那么在备份期间从库不能执行主库同步过来的二进制日志,会导致主从延迟.
解决办法是在innodb引擎中,备份时加上--single-transaction参数来完成不加锁的一致性数据备份.
添加全局锁: flush tables with read lock; 解锁 unlock tables.
表级锁,每次操作会锁住整张表.锁定粒度大,发送锁冲突的概率最高,并发读最低,应用在myisam、innodb、BOB等存储引擎中.表级锁分为: 表锁、元数据锁(meta data lock, MDL)和意向锁.
表锁又分为: 表共享读锁 read lock、表独占写锁write lock
语法: 1、加锁 lock tables 表名 ... read/write
元数据锁,在加锁过程中是系统自动控制的,无需显示使用,在访问一张表的时候会自动加上,MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候,不可以对元数据进行写入操作.为了避免DML和DDL冲突,保证读写的正确性.
查看元数据锁:
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;
意向锁,为了避免DML在执行时,加的行锁与表锁的冲突,在innodb中引入了意向锁,使得表锁不用检查每行数据是否加锁,使用意向锁来减少表锁的检查.意向锁分为,意向共享锁is由语句select ... lock in share mode添加.意向排他锁ix,由insert,update,delete,select...for update 添加.
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock;
行级锁,每次操作锁住对应的行数据,锁定粒度最小,发生锁冲突的概率最高,并发读最高,应用在innodb存储引擎中.
innodb的数据是基于索引组织的,行锁是通过对索引上的索引项加锁来实现的,而不是对记录加的锁,对于行级锁,主要分为以下三类:
①.、行锁或者叫record lock记录锁,锁定单个行记录的锁,防止其他事物对次行进行update和delete操作,在RC,RR隔离级别下都支持.
innodb实现了以下两种类型的行锁
①.、共享锁 S: 允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁.
insert 语句 排他锁 自动添加的
update语句 排他锁 自动添加
delete 语句 排他锁 自动添加
select 正常查询语句 不加锁 ...
select ...lock in share mode 共享锁 需要手动在select 之后加lock in share mode
select ...for update 排他锁 需要手动在select之后添加for update
默认情况下,innodb在repeatable read事务隔离级别运行,innodb使用next-key锁进行搜索和索引扫描,以防止幻读.
间隙锁唯一目的是防止其它事务插入间隙,间隙锁可以共存,一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用的间隙锁.
如果在transaction1(Tr1)进行一个