Mysql数据库管理-mysql锁管理

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

一、全局锁

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

命令是

Flush tables with read lock。

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

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

但是让整个库都只读，可能出现以下问题：

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的binlog，会导致主从延迟
在可重复读隔离级别下开启一个事务能够拿到一致性视图

逻辑备份工具是mysqldump

当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。single-transaction只适用于所有的表使用事务引擎的库

1.既然要全库只读，为什么不使用set global readonly=true的方式？

在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此修改global变量的方式影响面更大

在异常处理机制上有差异。如果执行Flush tables with read lock命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高

二、表级锁

MySQL里面表级别的锁有两种：

一种是表锁，

一种是元数据锁（meta data lock，MDL）

2.1 表级锁表锁

锁的语法是lock tables … read/write。可以用unlock tables主动释放锁，也可以在客户端断开的时 候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象

如果在某个线程A中执行

lock tables t1 read,t2 wirte;  ----实际进行测试

这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许

2.2 表级的锁是MDL。

I have two tables in my scenario

table1, which has about 20 tuples

table2, which has about 3 million tuples

table2 has a foreign key referencing table1 "ID" column.

When I try to execute the following query:

ALTER TABLE table1 MODIFY vccolumn VARCHAR(1000);

It takes forever. Why is it taking that long? I have read that it should not, because it only has 20 tuples.

Is there any way to speed it up without having server downtime? Because the query is locking the table, also.

解决方案

I would guess the ALTER TABLE is waiting on a metadata lock, and it has not actually starting altering anything.

What is a metadata lock?

When you run any query like SELECT/INSERT/UPDATE/DELETE against a table, it must acquire a metadata lock. Those queries do not block each other. Any number of queries of that type can have a metadata lock.

But a DDL statement like CREATE/ALTER/DROP/TRUNCATE/RENAME or event CREATE TRIGGER or LOCK TABLES, must acquire an exclusive metadata lock. If any transaction still holds a metadata lock, the DDL statement waits.

You can demonstrate this. Open two terminal windows and open the mysql client in each window.

Window 1: CREATE TABLE foo ( id int primary key );

Window 1: START TRANSACTION;

Window 1: SELECT * FROM foo; -- it doesn't matter that the table has no data

Window 2: DROP TABLE foo; -- notice it waits

Window 1: SHOW PROCESSLIST;

+-----+------+-----------+------+---------+------+---------------------------------+------------------+-----------+---------------+

| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |

+-----+------+-----------+------+---------+------+---------------------------------+------------------+-----------+---------------+

| 679 | root | localhost | test | Query | 0 | starting | show processlist | 0 | 0 |

| 680 | root | localhost | test | Query | 4 | Waiting for table metadata lock | drop table foo | 0 | 0 |

+-----+------+-----------+------+---------+------+---------------------------------+------------------+-----------+---------------+

You can see the drop table waiting for the table metadata lock. Just waiting. How long will it wait? Until the transaction in window 1 completes. Eventually it will time out after lock_wait_timeout seconds (by default, this is set to 1 year).

Window 1: COMMIT;

Window 2: Notice it stops waiting, and it immediately drops the

table.

So what can you do? Make sure there are no long-running transactions blocking your ALTER TABLE. Even a transaction that ran a quick SELECT against your table earlier will hold its metadata lock until the transaction commits.

MDL不需要显式使用，在访问一个表的时候会被自动加上。

MDL的作用是，保证读写的正确性。如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做了变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定不行

在MySQL5.5版本引入了MDL，

1. 当对一个表做增删改查操作的时候，加MDL读锁；
2. 当要对表做结构变更操作的时候，加MDL写锁

1. 读锁之间不互斥，因此可以有多个线程同时对一张表增删改查
2. 读写锁之间、
3. 写锁之间是互斥的，

用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行
给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，需要特别小心，以免对线上服务造成影响

 session A先启动，这时候会对表t加一个MDL读锁。

由于session B需要的也是MDL读锁，因此可以正常执行。

之后sesession C会被blocked，是因为session A的MDL读锁还没有释放，而session C需要MDL写锁，因此只能被阻塞。

如果只有session C自己被阻塞还没什么关系，但是之后所有要在表t上新申请MDL读锁的请求也会被session C阻塞。所有对表的增删改查操作都需要先申请MDL读锁，就都被锁住，等于这个表现在完全不可读写了

事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放

2.3 如果安全地给小表加字段？

首先要解决长事务，事务不提交，就会一直占着MDL锁。

在MySQL的information_schema库的innodb_trx表中，可以查到当前执行的事务。如果要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务

2.如果要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而又不得不加个字段，该怎么做？

在alter table语句里面设定等待时间lock_wait_timeout ，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后再通过重试命令重复这个过程

4、事务执行超时设置。

前面提到的的socket timeout、statement timeout，都是限制单个sql的最大执行超时。在事务的情况下，可能需要执行多个sql，

我们想针对整个事务设置一个最大的超时时间。

例如，我们在采用spring配置事务管理器的时候，可以指定一个defaultTimeout属性，单位是秒，指定所有事务的默认超时时间。

在 spring 中，通过在 @Transactional 注解上针对某个事务，指定超时时间，如：

@Transactional(timeout = 3)

transaction timeout的实现原理可以用以下流程进行描述，假设事务超时为5秒，需要执行3个sql：

 start transaction  
 #事务超时为5秒
 
 sql1  
 #statement timeout设置为5秒
 #执行耗时1s，那么整个事务超时还剩4秒
 
 sql2
 #设置statement timeout设置为4秒
 #执行耗时2秒，整个事务超时还是2秒
 
 sql3  
 #设置statement timeout设置为2秒
 #假设执行耗时超过2s，那么整个事务超时，抛出异常  

三、行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁

行锁就是针对数据表中行记录的锁。

比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新

1、两阶段锁协议

事务A持有的两个记录的行锁都是在commit的时候才释放的，事务B的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行

在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放

假设要实现一个电影票在线交易业务，顾客A要在影院B购买电影票。业务需要涉及到以下操作：

1.从顾客A账户余额中扣除电影票价

2.给影院B的账户余额增加这张电影票价

3.记录一条交易日志

为了保证交易的原子性，要把这三个操作放在一个事务中。如何安排这三个语句在事务中的顺序呢？

如果同时有另外一个顾客C要在影院B买票，那么这两个事务冲突的部分就是语句2了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。根据两阶段锁协议，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果把语句2安排在最后，比如按照3、1、2这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度

2、死锁和死锁检测

在并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁

  事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，

3    死锁解决有两种策略：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑
在InnoDB中，innodb_lock_wait_timeout的默认值是50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的

正常情况下还是要采用主动死锁检查策略，而且innodb_deadlock_detect的默认值本身就是on。主动死锁监测在发生死锁的时候，是能够快速发现并进行处理的，但是它有额外负担的。每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁

如果所有事务都要更新同一行的场景，每个新来的被堵住的线程都要判断会不会由于自己的加入导致死锁，这是一个时间复杂度是O(n)的操作

4 怎么解决由这种热点行更新导致的性能问题？

1.如果确保这个业务一定不会出现死锁，可以临时把死锁检测关掉

2.控制并发度

3.将一行改成逻辑上的多行来减少锁冲突。以影院账户为例，可以考虑放在多条记录上，比如10个记录，影院的账户总额等于这10个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成员原来的1/10，可以减少锁等待个数，也就减少了死锁检测的CPU消耗

5 为什么我只查一行的语句，也执行这么慢？

构造一个表，这个表有两个字段id和c，并且在里面插入了10万行记录

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE DEFINER=`root`@`%` PROCEDURE `idata`()
BEGIN
    declare i int;
  set i=1;
  while(i<=100000) do
    insert into t values(i,i);
    set i=i+1;
  end while;
END

1、第一类：查询长时间不返回
select * from t3 where id=1;
1 查询结果长时间不返回，使用show processlist命令，查看当前语句处于什么状态

 1）等MDL锁 （如何正常处理MDL锁等待）

如下图所示，使用show processlist;命令查看Waiting for table metadata lock的示意图

这个状态表示现在有一个线程正在表t上请求或者持有MDL写锁，把select语句堵住了

场景复现：

 sessionA通过lock table命令持有表t的MDL写锁，

而sessionB的查询需要获取MDL读锁。

所以，sessionB进入等待状态

1) 杀会话

这类问题的处理方式，就是找到谁持有MDL写锁，然后把它kill掉。但是由于show processlist的结果里，sessionA的Command列是Sleep，导致查找起来很不方便，可以通过查询sys.schema_table_lock_waits这张表直接找出造成阻塞的process id，把这个连接kill命令断开即可（MySQL启动时需要设置performance_schema=on，相比于设置为off会有10%左右的性能损失）

select blocking_pid from sys.schema_table_lock_waits;
 

2）等flush

在表t上执行如下的SQL语句：

select * from information_schema.processlist where id=1;

查出来某个线程状态为Waiting for table flush

这个状态表示的是，现在有一个线程正要对表t做flush操作。MySQL里面对表做flush操作的用法，一般有以下两个：

flush tables t with read lock;

flush tables with read lock;

这两个flush语句，如果指定表t的话，代表的是只关闭表t；如果没有指定具体的表名，则表示关闭MySQL里所有打开的表

但是正常情况下这两个语句执行起来都很快，除非它们被别的线程堵住了

所以，出现Waiting for table flush状态的可能情况是：有一个flush tables命令被别的语句堵住了，然后它有堵住了select语句

场景复现：

 sessionA中，每行调用一次sleep(1)，这样这个语句默认要执行10万秒，在这期间表t一直是被sessionA打开着。然后，sessionB的flush tables t再去关闭表t，就需要等sessionA的查询结束。这样sessionC要再次查询的话，就会被flush命令堵住了

6 等行锁

select * from t where id=1 lock in share mode; 
1
由于访问id=1这个记录时要加读锁，如果这时候已经有一个事务在这行记录上持有一个写锁，select语句就会被堵住

场景复现：

  sessionA启动了事务，占有写锁，还不提交，是导致sessionB被堵住的原因

2、查询慢

sessionA先用start transaction with consistent snapshot命令开启一个事务，建立事务的一致性读（又称为快照读。使用的是MVCC机制读取undo log中的已经提交的数据。所以它的读取是非阻塞的），之后sessionB执行update语句

sessionB执行完100万次update语句后，生成100万个回滚日志

带lock in share mode的语句是当前读，因此会直接读到1000001这个结果，速度很快；而select * from t where id=1这个语句是一致性读，因此需要从1000001开始，依次执行undo log，执行了100万次以后，才将1这个结果返回

五、间隙锁

建表和初始化语句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
 

这个表除了主键id外，还有一个索引c

为了解决幻读问题，InnoDB引入了间隙锁，锁的就是两个值之间的空隙

 当执行select * from t where d=5 for update的时候，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录

行锁分成读锁和写锁

 跟间隙锁存在冲突关系的是往这个间隙中插入一个记录这个操作。间隙锁之间不存在冲突关系

 这里sessionB并不会被堵住。因为表t里面并没有c=7会这个记录，因此sessionA加的是间隙锁(5,10)。而sessionB也是在这个间隙加的间隙锁。它们用共同的目标，保护这个间隙，不允许插入值。但它们之间是不冲突的

间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。表t初始化以后，如果用select * from t for update要把整个表所有记录锁起来，就形成了7个next-key lock，分别是(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。因为+∞是开区间，在实现上，InnoDB给每个索引加了一个不存在的最大值supremum，这样才符合都是前开后闭区间

间隙锁和next-key lock的引入，解决了幻读的问题，但同时也带来了一些困扰

间隙锁导致的死锁：

1.sessionA执行select … for update语句，由于id=9这一行并不存在，因此会加上间隙锁(5,10)

2.sessionB执行select … for update语句，同样会加上间隙锁(5,10)，间隙锁之间不会冲突

3.sessionB试图插入一行(9,9,9)，被sessionA的间隙锁挡住了，只好进入等待

4.sessionA试图插入一行(9,9,9)，被sessionB的间隙锁挡住了

两个session进入互相等待状态，形成了死锁

间隙锁的引入可能会导致同样的语句锁住更大的范围，这其实是影响并发度的

在读提交隔离级别下，不存在间隙锁

六、next-key lock

表t的建表语句和初始化语句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

1、next-key lock加锁规则

原则1：加锁的基本单位是next-key lock，next-key lock是前开后闭区间
原则2：查找过程中访问到的对象才会加锁
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁
一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止
这个规则只限于MySQL5.x系列<=5.7.24，8.0系列<=8.0.13

2、案例一：等值查询间隙锁

1.由于表t中没有id=7的记录，根据原则1，加锁单位是next-key lock，sessionA加锁范围就是(5,10]

2.根据优化2，这是一个等值查询(id=7)，而id=10不满足查询条件，next-key lock退化成间隙锁，因此最终加锁的范围是(5,10)

所以，sessionB要往这个间隙里面插入id=8的记录会被锁住，但是sessionC修改id=10这行是可以的

3、案例二：非唯一索引等值锁

1.根据原则1，加锁单位是next-key lock，因此会给(0,5]加上next-key lock

2.c是普通索引，因此访问c=5这一条记录是不能马上停下来的，需要向右遍历，查到c=10才放弃。根据原则2，访问到的都要加锁，因此要给(5,10]加next-key lock

3.根据优化2，等值判断，向右遍历，最后一个值不满足c=5这个等值条件，因此退化成间隙锁(5,10)

4.根据原则2，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有任何锁，这就是为什么sessionB的update语句可以执行完成

锁是加在索引上的，在这个例子中，lock in share mode只锁覆盖索引，但是如果是for update，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁，这样的话sessionB的update语句会被阻塞住。如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段

4、案例三：主键索引范围锁

1.开始执行的时候，要找到第一个id=10的行，因此本该是next-key lock(5,10]。根据优化1，主键id上的等值条件，退化成行锁，只加了id=10这一行的行锁

2.范围查询就往后继续找，找到id=15这一行停下来，因此需要加next-key lock(10,15]

所以，sessionA这时候锁的范围就是主键索引上，行锁id=10和next-key lock(10,15]

5、案例四：非唯一索引范围锁

这次sessionA用字段c来判断，加锁规则跟案例三唯一的不同是：在第一次用c=10定位记录的时候，索引c上加上(5,10]这个next-key lock后，由于索引c是非唯一索引，没有优化规则，因此最终sessionA加的锁是索引c上的(5,10]和(10,15]这两个next-key lock

6、案例五：唯一索引范围锁bug

sessionA是一个范围查询，按照原则1的话，应该是索引id上只加(10,15]这个next-key lock，并且因为id是唯一键，所以循环判断到id=15这一行就应该停止了

但是实现上，InnoDB会扫描到第一个不满足条件的行为止，也就是id=20。而且由于这是个范围扫描，因此索引id上的(15,20]这个next-key lock也会被锁上

所以，sessionB要更新id=20这一行是会被锁住的。同样地，sessionC要插入id=16的一行，也会被锁住

7、案例六：非唯一索引上存在等值的例子

insert into t values(30,10,30);

新插入的这一行c=10，现在表里有两个c=10的行。虽然有两个c=10，但是它们的主键值id是不同的，因此这两个c=10的记录之间也是有间隙的

 

sessionA在遍历的时候，先访问第一个c=10的记录。根据原则1，这里加的是(c=5,id=5)到(c=10,id=10)这个next-key lock。然后sessionA向右查找，直到碰到(c=15,id=15)这一行，循环才结束。根据优化2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成(c=10,id=10)到(c=15,id=15)的间隙锁

也就是说，这个delete语句在索引c上的加锁范围，就是下图中蓝色区域覆盖的部分，这个蓝色区域左右两边都是虚线，表示开区间

8、案例七：limit语句加锁

加了limit 2的限制，因此在遍历到(c=10,id=30)这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引c上的加锁范围就变成了从(c=5,id=5)到(c=10,id=30)这个前开后闭区间，如下图所示：

再删除数据的时候尽量加limit，这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围

9、案例八：一个死锁的例子

1.sessionA启动事务后执行查询语句加lock in share mode，在索引c上加了next-key lock(5,10]和间隙锁(10,15)

2.sessionB的update语句也要在索引c上加next-key lock(5,10]，进入锁等待

3.然后sessionA要再插入(8,8,8)这一行，被sessionB的间隙锁锁住。由于出现了死锁，InnoDB让sessionB回滚

sessionB的加next-key lock(5,10]操作，实际上分成了两步，先是加(5,10)间隙锁，加锁成功；然后加c=10的行锁，这时候才被锁住的

七、用动态的观点看加锁

表t的建表语句和初始化语句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

1、不等号条件里的等值查询
begin;
select * from t where id>9 and id<12 order by id desc for update;

利用上面的加锁规则，这个语句的加锁范围是主键索引上的(0,5]、(5,10]和(10,15)。加锁单位是next-key lock，这里用到了优化2，即索引上的等值查询，向右遍历的时候id=15不满足条件，所以next-key lock退化为了间隙锁(10,15)

1.首先这个查询语句的语义是order by id desc，要拿到满足条件的所有行，优化器必须先找到第一个id<12的值

2.这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到id=12的这个值，只是最终没找到，但找到了(10,15)这个间隙

3.然后根据order by id desc，再向左遍历，在遍历过程中，就不是等值查询了，会扫描到id=5这一行，所以会加一个next-key lock (0,5]

在执行过程中，通过树搜索的方式定位记录的时候，用的是等值查询的方法

2、等值查询的过程
begin;
select id from t where c in(5,20,10) lock in share mode;

这条in语句使用了索引c并且rows=3，说明这三个值都是通过B+树搜索定位的

在查找c=5的时候，先锁住了(0,5]。但是因为c不是唯一索引，为了确认还有没有别的记录c=5，就要向右遍历，找到c=10确认没有了，这个过程满足优化2，所以加了间隙锁(5,10)。执行c=10会这个逻辑的时候，加锁的范围是(5,10]和(10,15)，执行c=20这个逻辑的时候，加锁的范围是(15,20]和(20,25)

这条语句在索引c上加的三个记录锁的顺序是：先加c=5的记录锁，再加c=10的记录锁，最后加c=20的记录锁

select id from t where c in(5,20,10) order by c desc for update;
1
由于语句里面是order by c desc，这三个记录锁的加锁顺序是先锁c=20，然后c=10，最后是c=5。这两条语句要加锁相同的资源，但是加锁顺序相反。当这两条语句并发执行的时候，就可能出现死锁

八、insert语句的锁为什么这么多？

1、insert … select语句

表t和t2的表结构、初始化数据语句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t;

在可重复读隔离级别下，binlog_format=statement时执行下面这个语句时，需要对表t的所有行和间隙加锁

insert into t2(c,d) select c,d from t;
1

2、insert循环写入

要往表t2中插入一行数据，这一行的c值是表t中c值的最大值加1，SQL语句如下：

insert into t2(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);
1
这个语句的加锁范围，就是表t索引c上的(3,4]和(4,supermum]这两个next-key lock，以及主键索引上id=4这一行

执行流程是从表t中按照索引c倒序吗，扫描第一行，拿到结果写入到表t2中，因此整条语句的扫描行数是1

但如果要把这一行的数据插入到表t中的话：

insert into t(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);
 

explain结果中的Extra字段中Using temporary字段，表示这个语句用到了临时表

执行流程如下：

1.创建临时表，表里有两个字段c和d

2.按照索引c扫描表t，依次取c=4、3、2、1，然后回表，读到c和d的值写入临时表

3.由于语义里面有limit 1，所以只取了临时表的第一行，再插入到表t中

这个语句会导致在表t上做全表扫描，并且会给索引c上的所有间隙都加上共享的next-key lock。所以，这个语句执行期间，其他事务不能在这个表上插入数据

需要临时表是因为这类一边遍历数据，一边更新数据的情况，如果读出来的数据直接写回原表，就可能在遍历过程中，读到刚刚插入的记录，新插入的记录如果参与计算逻辑，就跟语义不符

3、insert唯一键冲突

sessionA执行的insert语句，发生唯一键冲突的时候，并不只是简单地报错返回，还在冲突的索引上加了锁，sessionA持有索引c上的(5,10]共享next-key lock（读锁）

在sessionA执行rollback语句回滚的时候，sessionC几乎同时发现死锁并返回

1.在T1时刻，启动sessionA，并执行insert语句，此时在索引c的c=5上加了记录锁。这个索引是唯一索引，因此退化为记录锁

2.在T2时刻，sessionA回滚。这时候，sessionB和sessionC都试图继续执行插入操作，都要加上写锁。两个session都要等待对方的行锁，所以就出现了死锁

4、insert into … on duplicate key update

上面这个例子是主键冲突后直接报错，如果改写成

insert into t values(11,10,10) on duplicate key update d=100; 
1
就会给索引c上(5,10]加一个排他的next-key lock（写锁）

insert into … on duplicate key update的语义逻辑是，插入一行数据，如果碰到唯一键约束，就继续执行后面的更新语句。如果有多个列违反了唯一性索引，就会按照索引的顺序，修改跟第一个索引冲突的行

表t里面已经有了(1,1,1)和(2,2,2)这两行，执行这个语句效果如下：

主键id是先判断的，MySQL认为这个语句跟id=2这一行冲突，所以修改的是id=2的行

思考题：
1、如果要删除一个表里面的前10000行数据，有以下三种方法可以做到：

第一种，直接执行delete from T limit 10000;
第二种，在一个连接中循环执行20次delete from T limit 500；
第三种，在20个连接中同时执行delete from T limit 500；
选择哪一种方式比较好？

参考答案：

第一种方式，单个语句占用时间长，锁的时间也比较长，而且大事务还会导致主从延迟

第三种方式，会人为造成锁冲突

第二种方式相对较好
————————————————
版权声明：本文为CSDN博主「邋遢的流浪剑客」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_40378034/article/details/90904573