MySQL锁<一>

对表的增删改查，都需要MDL锁，无所不在

MDL读锁之间不互斥，但MDL读写锁互斥

#举个栗子

假设t是一张大表

session1对t执行一个查询（SR）

session2对t执行一个DDL（SU，可能升级到X）

session3对t执行一个查询（SR）

可知session1持有t表的MDL读锁（SR），session1的查询还没有结束的时候，去执行session2的DDL（SU），此时session2需要MDL写锁（SU升级到X，需要X锁），由于MDL读写锁互斥，因此session2需要等待session1释放MDL读锁（SR阻塞X）；同时session2对后面的所有MDL读锁互斥（X阻塞SR），因此session2又继续阻塞了session3...

#注释：一开始的DDL能看到的状态是SU，但如果SU的某个阶段被阻塞，会被升级到X，从而引发SR阻塞X，达到实验的效果。但实际测试中，DDL是分阶段的，如果没有满足一定的要求，就不会引发阻塞，看到的结果就是SR和SU并没有互相阻塞。这个过程需要具体的去查看源码，此处不展开。

事务中的MDL锁在语句开始时申请，但并不会在语句结束后就马上释放，而是会等到事务结束时才进行释放

忙时对大表DDL会产生的灾难性的结果就是：如果后续对该表有查询 *** 作，而且web端又有重试机制的话，那么会有一个新的session再次发起读请求，反复如此，线程池就会在短时间内爆炸

在线执行DDL的时候，需要检查一下information_schema.innodb_trx表中有没有当前 *** 作表对应的事务，此外还可以使用ALTER TABLE tbl_name NOWAIT...进行 *** 作（MySQL8.0新特性）

eg.

session1

select * from cpf where payid<>'xxx'

union

select * from cpf where payid<>'xxx'

union (union重复50次，确保查询时间几十秒以上)

session2

alter table cpf modify payer_userid varchar(500)

session3

select * from cpf where payer_userid='18051512003600300034'

#执行结果

session1执行了31秒，当session1完成的时候session2和session3相继完成

在session4中执行show processlist，结果如下

#变种1

如果session1在执行select之前，添加一句start transaction

会发现session1什么时候执行完commit，sesssion2和session3什么时候完成

也就是证实了在事务中的MDL锁，在语句查询完之后并不会释放，而是会随着事务的释放而释放

#变种2

session1和session3在执行select之前，添加一句start transaction，然后session1,2,3依次按顺序执行

会发现session1阻塞了session2，而session3在执行完start transaction之后就被阻塞，根本没有办法去执行后面的select

当session1执行commit释放之后，session2仍然处于阻塞状态，session3亦是如此

直到session2或者session3当中任意一个执行了停止（navicat客户端 *** 作，类似于rollback）后，另一个才能完成执行

单纯从变种2的结果来看，MDL锁并没有按照执行时间的先后来进行分配，当session1的锁释放之后，session3先获得了读锁

MySQL是server-engine结构，MDL锁是server层的锁

通过show processlist可以发现waiting for table metadata lock，但这还远远不够，需要在performance_schema库中进行设置（MySQL8.0默认开启）

5.7临时开启

UPDATE performance_schema.setup_instruments SET ENABLED='YES', TIMED='YES' WHERE NAME='wait/lock/metadata/sql/mdl'

5.7永久开启（修改cnf配置）

[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）锁

DML和DDL在执行之前都会申请IX锁，DML会在global级别上加，而DDL会在global和schema这2个级别上都加IX（也就是2把锁）

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

如下图，前面2行是FTWRL持有的S锁，第3行是一个update语句，IX直接被阻塞，处于pending的锁等待状态；同时由于S锁的持有时间为EXPLICIT，表明FTWRL需要一个显示的释放（unlock tables）

DML并不是只有IX锁，DML和select .. for update在执行中持有的锁实际是SW锁（DML需要找一个大一点的表来验证，目前只验证了select .. for update），IX只是DML初期需要获得的锁

如下图是一个select for update语句，start transaction对应的是第2行的SR锁，而语句本身对应的是SW锁

如果在此时执行一个FTWRL，我们会发现2个会话并不会相互阻塞（因为S锁与SR和SW都是兼容的），如下图

但如果我们是先执行的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

与SR锁有冲突的有2个，一个是X，另一个是SNRW

研发有时候会很困惑的问我，“我这个表只有几十行数据，select查不出来？？？”  这时候就需要检查MDL锁了

当前读需要持有此锁，常见的DML和select for update都对应此锁，但不包括DDL

与SW锁有冲突的有4个，SU,SRO,SNRW,X

看到一种说法是这个锁仅对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

thread104在做一个create table as的表复制 *** 作，在表里面并没有发现X锁的信息，在thread95上对新表做一个desc *** 作，可以看到SH锁处于等待状态，然而这里阻碍SH的并不是X锁

只有1行的select被堵住

thread95做一个start transaction之后不提交，thread107对95的表做出一个rename *** 作，X锁被前面的SR锁阻塞，这时候thread108对该表发起一个limit仅仅为1的查询，但被X锁阻塞。由于lock_wait_timeout这个参数通常是1年，所以一连串查询被堵死

alter开头的几个SQL，无论是modify还是add，查询出来都是SU锁，但DDL是一个过程，其中的有一部分如果发生了阻塞，可能会发现是X锁阻塞；拿SR阻塞X锁的实验来说，SR阻塞X的过程非常短暂，如果没有刚好卡到那个点，看到的结果可能就是SR和SU互不干涉，但如果卡到那个点，就会观测到X被SR所阻塞。具体的需要读源码，这里不展开

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

(

SELECT

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

FROM

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_ID<>b.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,

(

SELECT

thread_id,

group_concat(CASEWHENEVENT_NAME='statement/sql/begin'THEN"transaction_begin"ELSEsql_textENDORDERBYevent_id SEPARATOR "" )ASsql_text

FROM

performance_schema.events_statements_history

GROUPBYthread_id

) t2

WHERE

t1.granted_thread_id=t2.thread_id

MDL锁处理

MDL元数据锁

快速处理MDL锁

MySQL 中原数据锁是系统自动控制添加的，对于用户来说无需显示调用，当我们使用一张表的时候就会加上原数据锁。

原数据锁的作用是为了保护表原数据的一致性，如果在表上有活动事务的时候，不可以对元数据进行写入 *** 作。也就是为了避免DML 和DDL 之间的冲突，保证读写的正确性。

说白了就是， 在对数据表进行读写 *** 作的时候，不能进行修改表结构的 *** 作 。

如上图所示，在执行select *** 作的时候，MySQL 会自动加上shared_read 锁，在insert，update， delete 以及 select for update *** 作的时候会加上shared_write 锁，这两类锁是兼容的。

在执行alter table *** 作的时候，会加上 exclusive 锁，这个锁与shared_read 和 shared_write 锁 是互斥的，换句话说在 做查询和更新表数据的时候，是不能够修改表结构 的。

来看个例子

首先开启事务，使用select 语句会针对表加上shared_read的共享锁

begin

select * from course

此时查看原数据锁的信息

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks

通过上图我们可以发现，course 表加上了shared_read锁。

接着，开启另外一个事务，记住刚才的事务不要commit

begin

update course set name = 'Jason' where id =2

如上图所示，此时的update 语句可以执行成功，并没有被阻塞。说明select 和update 是不冲突的，他们的锁是兼容的。

再次查看原数据锁

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks

从上面的截图可以看出，此时原数据锁的表中记录了两条记录分别是针对course 表的shared_read 和 shared_write 锁，也刚好对应我们执行的select 和update *** 作。

最后，我们再启动第三个客户端，并且启动 第三个事务，执行alter语句,在course 表中加入一个字段hello 如下 。

begin

alter table course add column hello int

由于之前的事务没有提交所以修改表的 *** 作会被阻塞， 因为shared_read 以及 shared_write 这两个锁 与 exclusive之间是互斥的，所以会阻塞 。

此时，回到最开始的两个客户端，对两个事务进行commit *** 作，再返回到第三个事务执行的alter 语句出，发现语句顺利执行。

多线程开启事务处理。每个事务有多个update *** 作和一个insert *** 作（都在同一张表）。

默认隔离级别：Repeatable Read

只有hotel_id=2和hotel_id=11111的数据

逻辑删除原有数据

插入新的数据

根据现有数据情况，update的时候没有数据被更新

报了非常多一样的错

发现居然有死锁。

根据常识考虑，我每个线程（事务）更新的数据都不冲突，为什么会产生死锁？

带着这个问题，打印mysql最近一次的死锁信息

show engine innodb status

显示如下

发现事务1在等待一个锁

事务2也在等待一个锁

而且事物2持有了事物1需要的锁

关于锁的描述，出现了 lock_mode ， gap before rec ， insert intention 等字眼，看不懂说明了什么？说明我关于mysql的锁相关的知识储备还不够。那就开始调查mysql的锁相关知识。

通过搜索引擎，

锁的持有兼容程度如下表

那么再回到死锁日志，可以知道 :

事务1正在获取插入意向锁

事务2正在获取插入意向锁，持有排他gap锁

再看我们上面的锁兼容表格，可以知道， gap lock和insert intention lock是不兼容的

那么就可以推断出： 事务1持有gap lock，等待事务2的insert intention lock释放；事务2持有gap lock，等待事务1的insert intention lock释放，从而导致死锁。

那么新的问题就来了，事务1的intention lock 为什么会和事务2的gap lock 有交集，或者说，事务1要插入的数据的位置为什么会被事务2给锁住？

让我回顾一下gap lock的定义：

间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况

那为什么是gap lock，gap lock到底是基于什么逻辑锁的记录？发现自己相关的知识储备还不够。那就开始调查。

调查后发现，当当前索引是一个 普通索引 的时候，会加一个gap lock来防止幻读， 此gap lock 会锁住一个左开右闭的区间。 假设索引为xx_idx（xx_id），数据分布为1,4,6,8,12，当更新xx_id=9的时候，这个时候gap lock的锁定记录区间就是(8，12]，也就是锁住了xxid in (9,10,11,12)的数据，当有其他事务要插入xxid in (9,10,11,12)的数据时，就会处于等待获取锁的状态。

ps：当前索引不是普通索引，而且是唯一索引等其他情况，请参考下面资料

MySQL 加锁处理分析

回到我自己的案例中，重新屡一下事务1的执行过程：

因为普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的关系 这段sql会获取一个gap lock，范围(2,11111]

这段sql会获取一个insert intention lock （waiting）

再看事务2的执行过程

因为普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的关系 这段sql也会获取一个gap lock，范围也是(2,11111]（根据前面的知识，gap lock之间会互相兼容，可以一起持有锁的）

这段sql也会获取一个insert intention lock （waiting）

看到这里，基本也就破案了。因为普通索引的关系，事务1和事务2的gap lock的覆盖范围太广，导致其他事务无法插入数据。

重新梳理一下：

所以从结果来看，一堆事务被回滚，只有10007数据被更新成功

gap lock 导致了并发处理的死锁

在mysql默认的事务隔离级别（repeatable read）下，无法避免这种情况。只能把并发处理改成同步处理。或者从业务层面做处理。

共享锁、排他锁、意向共享、意向排他

record lock、gap lock、next key lock、insert intention lock

show engine innodb status

---