mysql锁怎么设置,mysql锁有几种方式

MySQL锁

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

成都创新互联专业为企业提供白云网站建设、白云做网站、白云网站设计、白云网站制作等企业网站建设、网页设计与制作、白云企业网站模板建站服务，10多年白云做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。

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_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,

(

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读数据时怎么加写锁

加锁情况与死锁原因分析

为方便大家复现，完整表结构和数据如下：

CREATE TABLE `t3` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`),

UNIQUE KEY `c2` (`c2`)

) ENGINE=InnoDB

insert into t3 values(1,1),(15,15),(20,20);

在 session1 执行 commit 的瞬间，我们会看到 session2、session3 的其中一个报死锁。这个死锁是这样产生的：

1. session1 执行 delete  会在唯一索引 c2 的 c2 = 15 这一记录上加 X lock（也就是在MySQL 内部观测到的：X Lock but not gap）；

2. session2 和 session3 在执行 insert 的时候，由于唯一约束检测发生唯一冲突，会加 S Next-Key Lock，即对 (1,15] 这个区间加锁包括间隙，并且被 seesion1 的 X Lock 阻塞，进入等待；

3. session1 在执行 commit 后，会释放 X Lock，session2 和 session3 都获得 S Next-Key Lock；

4. session2 和 session3 继续执行插入操作，这个时候 INSERT INTENTION LOCK（插入意向锁）出现了，并且由于插入意向锁会被 gap 锁阻塞，所以 session2 和 session3 互相等待，造成死锁。

死锁日志如下：

请点击输入图片描述

INSERT INTENTION LOCK

在之前的死锁分析第四点，如果不分析插入意向锁，也是会造成死锁的，因为插入最终还是要对记录加 X Lock 的，session2 和 session3 还是会互相阻塞互相等待。

但是插入意向锁是客观存在的，我们可以在官方手册中查到，不可忽略：

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 lock，但是它不会阻塞其他锁。假设存在值为 4 和 7 的索引记录，尝试插入值 5 和 6 的两个事务在获取插入行上的排它锁之前使用插入意向锁锁定间隙，即在（4，7）上加 gap lock，但是这两个事务不会互相冲突等待。

当插入一条记录时，会去检查当前插入位置的下一条记录上是否存在锁对象，如果下一条记录上存在锁对象，就需要判断该锁对象是否锁住了 gap。如果 gap 被锁住了，则插入意向锁与之冲突，进入等待状态（插入意向锁之间并不互斥）。总结一下这把锁的属性：

1. 它不会阻塞其他任何锁；

2. 它本身仅会被 gap lock 阻塞。

在学习 MySQL 过程中，一般只有在它被阻塞的时候才能观察到，所以这也是它常常被忽略的原因吧...

GAP LOCK

在此例中，另外一个重要的点就是 gap lock，通常情况下我们说到 gap lock 都只会联想到 REPEATABLE-READ 隔离级别利用其解决幻读。但实际上在 READ-COMMITTED 隔离级别，也会存在 gap lock ，只发生在：唯一约束检查到有唯一冲突的时候，会加 S Next-key Lock，即对记录以及与和上一条记录之间的间隙加共享锁。

通过下面这个例子就能验证：

请点击输入图片描述

这里 session1 插入数据遇到唯一冲突，虽然报错，但是对 (15,20] 加的 S Next-Key Lock 并不会马上释放，所以 session2 被阻塞。另外一种情况就是本文开始的例子，当 session2 插入遇到唯一冲突但是因为被 X Lock 阻塞，并不会立刻报错 “Duplicate key”，但是依然要等待获取 S Next-Key Lock 。

有个困惑很久的疑问：出现唯一冲突需要加 S Next-Key Lock 是事实，但是加锁的意义是什么？还是说是通过 S Next-Key Lock 来实现的唯一约束检查，但是这样意味着在插入没有遇到唯一冲突的时候，这个锁会立刻释放，这不符合二阶段锁原则。这点希望能与大家一起讨论得到好的解释。

如果是在 REPEATABLE-READ，除以上所说的唯一约束冲突外，gap lock 的存在是这样的：

普通索引（非唯一索引）的S/X Lock，都带 gap 属性，会锁住记录以及前1条记录到后1条记录的左闭右开区间，比如有[4,6,8]记录，delete 6，则会锁住[4,8）整个区间。

对于 gap lock，相信 DBA 们的心情是一样一样的，所以我的建议是：

1. 在绝大部分的业务场景下，都可以把 MySQL 的隔离界别设置为 READ-COMMITTED；

2. 在业务方便控制字段值唯一的情况下，尽量减少表中唯一索引的数量。

锁冲突矩阵

前面我们说的 GAP LOCK 其实是锁的属性，另外我们知道 InnoDB 常规锁模式有：S 和 X，即共享锁和排他锁。锁模式和锁属性是可以随意组合的，组合之后的冲突矩阵如下，这对我们分析死锁很有帮助：

请点击输入图片描述

mysql怎么设置悲观锁

在mysql中可以使用select…for update实现悲观锁。这样那条数据就被我们锁定了，其它的事务必须等本次事务提交之后才能执行。从而保证数据不会被其他事务更改从而导致数据的异常。但是select…for update不会阻塞select的查询。

需要注意的是mysql在采用InnoDB时，默认为行锁，且只有明确额指定主键，MySQL 才会执行行锁，锁住对应的那条数据，否则MySQL 将会执行表锁(将整个数据表单给锁住)。