mysql中的锁都有哪些

MySQL 中有哪些锁？

数据库中锁的设计初衷处理并发问题，作为多用户共享资源，当出现并发访问的时候，数据库需要合理控制资源访问规则。锁就是实现这些访问规则中的重要数据。

锁的分类

根据加锁范围，MySQL 里面的锁可以分成 全局锁 、 表级锁 、 行锁 三类。

全局锁

全局锁，就是对整个数据库实例加锁，MySQL 提供了一个加全局读锁的方法，命令是:

Flush tables with read lock (FTWRL)

当需要整个库只读状态的时候，可以使用这个命令，之后其他线程的：数据更新语句（增删改），数据定义语句（建表，修改表结构）和更新事务的提交语句将会被阻塞。

全局锁的使用场景

全局锁的定型使用场景，做 全库逻辑备份 。也就是把整个库每个表都 Select 出来，然后存成文本。

如何整个库都只读，会有什么问题？ 如果你在主库上备份，那么在备份期间都不能执行更想，业务就基本上停摆。 如果在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog ,会导致从延迟。 既然要全库只读， 为什么不使用set global readonly=true的方式呢?

readonly 方式也可以让全库进入只读状态,但我还是会建议你用FTWRL方式， 主要有两个原因：

一是， 在有些系统中， readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改global变量的方式影响面更大， 我不建议你使用。 二是， 在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开， 那么MySQL会自动释放这个全局锁， 整个库回到可以正常更新的状态。而将整个库设置为readonly之后， 如果客户端发生异常， 则数据库就会一直保持readonly状态， 这样会导致整个库长时间处于不可写状态， 风险较高 表级别锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lok, MDL）。表锁的语法是 ：

lock tables ... read/write

与 FTWRL 类似,可以使用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意的是，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的 *** 作对象。

MDL 表级锁

MDL 不需要显示使用，在访问一个表的时候自动加上， MDL 保证读写的正确性，也就是说在查询数据时，不允许有其他线程对这个表结构做变更。

什么 *** 作会加 MDL 锁？

在MySQL 5.5版本中引入了MDL， 当对一个表做增删改查 *** 作的时候，加 MDL读锁 ；当要对表做结构变更 *** 作的时候，加 MDL写锁 。

读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。 读写之间、写锁之间是互斥的，用来保证变更表结构 *** 作的安全性，如果有两个线程要同时给一个表加字段，其中一个要等另外一个执行完才能执行。 更改表结构要注意哪些？

给一个表加字段， 或者修改字段， 或者加索引， 需要扫描全表的数据。在对大表 *** 作的时候， 你肯定会特别小心， 以免对线上服务造成影响。而实际上， 即使是小表， *** 作不慎也会出问题，导致整个库的线程爆满。

举个例子

我们来看一下下面的 *** 作序列， 假设表t是一个小表。

image

session A先启动， 这时候会对表t加一个 MDL读锁 。由于session B需要的也是 MDL读锁 ， 因此可以正常执行。 session C会被blocked， 是因为session A的MDL读锁还没有释放， 而session C需要MDL写锁， 因此只能被阻塞，读写锁互斥。 如果只有session C自己被阻塞还没什么关系， 但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。前面我们说了，所有对表的增删改查 *** 作都需要先申请MDL读锁， 就都被锁住， 等于这个表现在完全不可读写了。

如果某个表上的查询语句频繁， 而且客户端有重试机制，也就是说超时后会再起一个新session 再请求的话， 这个 库的线程很快就会爆满 。事务中的MDL锁， 在语句执行开始时申请， 但是语句结束后并不会马上释放， 而会等到整个事务提交后再释放。

怎么解决这个 更改表结构问题

比较理想的机制是， 在alter table语句里面设定等待时间， 如果在这个指定的等待时间里面能够拿到MDL写锁最好， 拿不到也不要阻塞后面的业务语句， 先放弃。

ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...

对表的增删改查，都需要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锁

在我们系统中有一张表它的查询概率非常高。 最近有个需求，需要对这个表增加一个字段 ，然而在增加字段的时候发现系统中有多个业务出现了超时 *** 作，那么这个是什么原因导致的呢？经过查阅资料发现是数据库的 MDL锁+事务导致 的。

MDL锁属于表级别的元数据锁。 表级别锁分为数据锁和元数据锁，通常我们说的加锁一般指的是加的数据锁。跟数据锁一样，元数据锁也分读锁和读写锁。

MDL不需要显示使用，在进行表 *** 作时会自动加上 。当对表进行增删改查时，会自动加上MDL读锁；当要对表进行加减字段的结构修改时，会自动加上MDL写锁。

MDL锁的存在，其实是为了保证数据的一致性。 想象一下，假如没有MDL锁，一个查询在遍历表数据的过程中，另外一个线程执行了ALTER TABLE t DELETE COLUMN 'col_1'把col_1这一列删掉了，那查询结果就乱了，结果中是否应该有这一列数据？

：表示正常往下执行

：表示卡住了，即无法往下执行。

解释：

步骤 1 2 正常执行。执行步骤2 时，会申请表customer的MDL的 SHARED_READ 锁。

步骤3 会卡住， 因为此时会申请表customer的MDL的EXCLUSIVE锁，但是事物一的事物没有提交，此时是无法申请到EXCLUSIVE锁，因为它们是互斥的。

步骤4 也会卡住，因为EXCLUSIVE锁和SHARE_READ锁是互斥的， 且EXCLUSIVE锁的优先级更高 ，所以步骤4 也会卡住。

步骤5 事物提交，释放表的SHARE_READ锁，之后就可以执行6 和7 的 *** 作了。

如果先执行事务二，在执行事务三，则是可以成功的，因为alter数据ddl语句，和事物无关。

因此在我们开发的过程中，需要避免大的事务 *** 作，防止占有锁的时间过长。

---