关于MySQL的锁机制详解

妖狐艹你老母 2021-10-01 07:32 325阅读 0赞

#### 锁概述 ####

MySQL的锁机制，就是数据库为了保证数据的一致性而设计的面对并发场景的一种规则。

最显著的特点是不同的存储引擎支持不同的锁机制，InnoDB支持行锁和表锁，MyISAM支持表锁。

表锁就是把整张表锁起来，特点是加锁快，开销小，不会出现死锁，锁粒度大，发生锁冲突的概率高，并发相对较低。  
　　行锁就是以行为单位把数据锁起来，特点是加锁慢，开销大，会出现死锁，锁粒度小，发生锁冲突的概率低，并发度也相对表锁较高。

#### MyISAM锁 ####

#### MyISAM的锁调度 ####

在MyISAM引擎中，读锁和写锁是互斥的，读写操作是串行的，锁设计方案如下：

对于写操作：如果表上没有锁，则在上面加一把写锁，否则，把请求放到写锁队列中。  
　　对于读操作：如果表上没有锁，则在上面加一把读锁，否则，把请求方到读锁队列中。

这是什么意思呢？

意思就是说MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行更新语句(增删改操作)前，会自动给涉及的表加写锁，这个过程并不需要用户干预。

当一个锁被释放时，锁定权会先被写锁队列中的线程得到，当写锁队列中的请求都跑完后，才轮到读锁队列中的请求。（即使读请求先到锁等待队列中，写请求后到，写请求也会插入到读请求之前！这就是MySQL认为写请求一般比读请求重要）

这就意味着，如果一个表上有很多更新操作，那么select语句将等待直到别的更新都结束后才能查到东西。这也就是为什么MyISAM表不适合大量更新操作应用的原因，因为大量更新操作可能导致查询操作很难获得读锁，从而长久阻塞，致使程序响应超时。

#### 也许你需要显式加锁 ####

表锁语句有如下三条（MyISAM和InnoDB都一样）：

LOCK TABLES tb\_name READ; 加读锁，其他会话可读，但不能更新。  
　　LOCK TABLES tb\_name WRITE; 加写错，其他会话不可读，不可写。  
　　UNLOCK TABLES; 释放锁

当有连续多表更新的时候，可能会出现频繁的表锁竞争，更新数据的速度反而会下降，并且更新这个表的时候另一个表的数据可能被别的线程更新了（MyISAM是没有事务的），这个时候，我们就需要锁住多张表，再进行更新。

这里示例，同时上锁更新两个表，给id为1的用户余额加1：

LOCK TABLES tb\_1 WRITE,tb\_2 WRITE;  
　　UPDATE tb\_1 SET balance=balance+1 WHERE user\_id=1;  
　　UPDATE tb\_2 SET balance=balance+1 WHERE user\_id=1;  
　　UNLOCK TABLES;

特别注意：显式加锁的时候，必须同时取得所有涉及表的锁，并且，只能访问显式加锁的这些表，不能访问未加锁的表。

（MyISAM的内容就这一章，接下来的章节都是InnDB的了，特此说明哈。）

#### InnoDB锁类型 ####

#### 共享锁（S锁、读锁） ####

SELECT \* FROM tb\_name LOCK IN SHARE MODE;

一个事务获取了一个数据行的读锁，允许其他事务也来获取读锁，但是不允许其他事务来获取写锁。也就是说，我上了读锁之后，其他事务也可以来读，但是不能增删改。

#### 排他锁（X锁、写锁） ####

SELECT \* FROM tb\_name FOR UPDATE;

一个事务获取了一个数据行的写锁，其他事务就不能再跑来获取任何锁了，所有请求都会被阻塞，直到当前的写锁被释放。

#### 意向锁与MDL锁 ####

意向共享锁（IS）：事务在给一个数据行加共享锁之前必须先取得该表的IS锁。  
　　意向排他锁（IX）：事务在给一个数据行加共享锁之前必须先取得该表的IX锁。  
　　MDL锁：在事务中，InnoDB会给涉及的所有表加上一个MDL锁，其他事务就不可以执行任何DDL语句的操作。（亲测只要在事务中，不管是查询语句还是更新语句，涉及到的表都会被加上MDL锁）

这三种锁，是InnoDB内部使用的锁，是自动实现的，不需要用户干预。

#### 几种行锁技术 ####

#### 记录锁（record lock） ####

这是一个索引记录锁，它是建立在索引记录上的锁（主键和唯一索引都算），很多时候，锁定一条数据，由于无索引，往往会导致整个表被锁住，建立合适的索引可以防止扫描整个表。

如：开两个会话，两个事务，并且都不commit，该表有主键，两个会话修改同一条数据，第一个会话update执行后，第二个会话的update是无法执行成功的，会进入等待状态，但是如果update别的数据行就可以成功。

再例如：开两个会话，两个事务，并且都不commit，并且该表无主键无索引，那么第二个会话不管改什么都会进入等待状态。因为无索引的话，整个表的数据都被第一个会话锁定了。

#### 间隙锁（gap lock） ####

MySQL默认隔离级别是可重复读，这个隔离级别为了避免幻读现象，引入了这个间隙锁，对索引项之间的间隙上锁。

示例：

(会话1)  
　　START TRANSACTION;  
　　SELECT \* FROM tb\_name WHERE id>10 LOCK IN SHARE MODE;  
　　（会话2）  
　　START TRANSACTION;  
　　INSERT INTO tb\_name(id,name) VLUES(11,"张三")

结果怎样？会话2会进入执行等待状态，直至会话1的锁释放或者锁超时。

#### next-key锁（记录所和间隙锁的组合） ####

当InnoDB扫描索引记录时，会先对选中的索引记录加上记录锁（record Lock），再对索引记录两遍的间隙加上间隙锁（gap lock）。

还是以间隙锁的例子说，假如表中没有id=10的这行数据，会话2添加的id该为10，会成功吗？

答案是不会，因为它不止锁了id>10的间隙，连id=10也一起锁了。

#### 表锁 ####

在InnoDB中绝大部分都应该使用行锁，因为事务和行锁往往是我们选择InnoDB表的理由，但是在个别特殊事务中，也可以考虑使用表锁。

情况1：事务需要更新大部分或者全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高事务的执行速度。

情况2：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚，这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁，减少数据库因事务回滚带来的开销。

当然，这两种情况不能太多，否则就应该从业务和程序设计上进行拆分处理，而不是由数据库来承担这个事情。

例子如下：

LOCK TABLES tb_name WRITE;
    　　UNLOCK TABLES;

注意：在事务中锁表时，在事务结束前不要释放锁，因为unlock tables会隐含提交事务，所以正确的做法是结束事务后再释放锁。

#### 锁等待和死锁 ####

锁等待是指一个事务过程中产生的锁，其他事务需要等待上一个事务释放它的锁，才能占用该资源，如果该事务一直不释放，就需要继续等待下去，直到超过了锁等待时间，会报一个超时错误。

查看锁等待允许时间：

SHOW VARIABLES LIKE "innodb_lock_wait_timeout"

死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，就是所谓的死循环。

典型的实验过程就是两个事务并发，互相修改自己的一条数据，紧接着又修改对方的锁定的那条数据，都要等待对方的锁，死锁就产生了。

出现死锁的问题并不可怕，解决死锁通常有如下办法：

1.不要把无关的操作放到事务里，小事务发生冲突的概率较低。  
　　2.如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样事务就会形成定义良好的查询并且没有死锁。  
　　3.尽量按照索引去查数据，范围查找增加了锁冲突的可能性。  
　　4.对于非常容易产生死锁的业务部分，可以尝试升级锁粒度，通过表锁定来减少死锁产生的概率。

#### 锁监控 ####

#### 表锁监控 ####

获取表锁争用情况：

SHOW STATUS LIKE "table%"

查了很多资料，确实是这个获取方法，但是我自己没测出来它的用处，试了两台数据库都不行，很奇怪。

查询哪些表正在被锁定：

SHOW OPEN TABLES WHERE In\_use > 0;

这个命令监控的是被表锁锁住的表，亲测如果用行锁，这个命令是没有反应的，真的得自己动手实践才能发现真相。

#### 行锁监控 ####

获取行锁争用情况：

SHOW STATUS LIKE "innodb_row_lock%"

下面介绍几张表，可以帮助我们监控当前的事务并分析可能存在的锁问题。

select \* from information\_schema.innodb\_trx;

![0fb74759571048cb1a716024df01e986.png][]

主要字段如下：

trx\_id：唯一的事务id号  
　　trx\_state：当前事务的状态，lock wait锁等待状态，running执行中状态。  
　　trx\_started：事务开始时间  
　　trx\_wait\_started：事务开始等待时间  
　　trx\_mysql\_thread\_id：线程id  
　　trx\_query：事务运行的SQL语句

持有锁的对象：

select \* from information\_schema.innodb\_locks;

锁等待的对象：

select \* from information\_schema.innodb\_lock\_waits;

#### 解密 ####

#### 为什么锁一行数据，速度就变得这么慢？ ####

实验内容：两个会话两个事务，会话1锁，会话2改，目标是不同的行数据。

会话1的where条件必须是索引，才能锁住这一行，否则就会锁住整张表的数据，让会话2上不了锁。

会话2的where条件也必须是索引，才能锁住这一行，否则会试图去锁整张表的数据，而整张表的数据已经有一行被会话1锁了，所以会话2锁不上。

#### 为什么我要锁一行，MySQL给我锁全表？ ####

即使在条件中使用了索引，但是是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB也会对全表记录上锁（申明一点，行锁不会升级成表锁，它实际上是把所有行都上了锁）。

#### 事务中混合使用存储引擎会怎样？ ####

MySQL的服务层不管理事务，事务是由下层的存储引擎实现的（表锁是由MySQL的服务层实现的），所以在同一个事务中，使用多种存储引擎的表是有风险的。

比如在事务中同时操作innodb和myisam的表，正常提交不会有问题,但是如果要回滚，myisam的表是不会被回滚的。

因此，在一个事务中，最好不要使用不同存储引擎的表。

#### 先开事务再锁表？还是锁了表再开事务？ ####

答案是先开事务再锁表，因为START TRANSACTION语句会隐含了UNLOCK TABLES，一开事务就等于释放了之前的表锁。

#### 我就是开一个事务执行SQL,算不算上锁？ ####

InnoDB采用的是两阶段锁定协议。

在事务执行过程中，随时都可以执行锁定，锁只有在commit或者rollback的时候才会释放（这里说的是行锁哈^\_^，表锁是不在存储引擎这层的），并且所有的锁是在同一时刻释放。

innodb会根据隔离级别在需要的时候自动加锁，优先走隔离级别的规则，然后才是行锁，如果数据确实隔离了，那么是不会上锁的（不信小伙伴们可以亲测，开事务改数据会自动上锁，但是开事务查数据不会上锁）。

显式加锁语句是LOCK IN SHARE MODE 和 FOR UPDATE了。

（隔离级别的内容请往这里跳：[https://www.linuxidc.com/Linux/2018-11/155273.htm][https_www.linuxidc.com_Linux_2018-11_155273.htm]）

#### 怎么测试它到底有没有上锁呢？ ####

两种办法：

第一种，在事务中使用显式加锁语句，不在事务中使用你是感觉不到它上了锁的。

第二种，关闭自动提交模式

SET autocommit=0

关闭之后就可以不开事务直接显式上锁，直到你执行commit或者rollback它才会释放锁。

这其实就证明了一个很多人都不知道的事情：每一条SQL都是一个事务。只不过都是自动提交的，所以人们感觉不到事务的存在而已，当关闭了自动提交后，就必须手动提交事务才可以让SQL生效。

查询自动提交是否开启：

SHOW VARIABLES LIKE "autocommit"

（这里有一个我还没弄明白的问题：我只能确定每个更新语句是开了事务的，但我不知道每一个查询是不是开了事务，没办法去证明，也没想出来该怎么去证明，有知道的小伙伴可以交流一下哦）

文章同步发布： [https://www.geek-share.com/detail/2752692802.html][https_www.geek-share.com_detail_2752692802.html]

参考文章：

[MySQL锁原理浅谈][MySQL]  
[https://www.geek-share.com/detail/2751656002.html][MySQL]

转载于:https://www.cnblogs.com/xxcn/p/9941365.html

[0fb74759571048cb1a716024df01e986.png]: https://www.geek-share.com/Uploads/Images/Content/201811/0fb74759571048cb1a716024df01e986.png
[https_www.linuxidc.com_Linux_2018-11_155273.htm]: https://www.geek-share.com/detail/2752692801.html
[https_www.geek-share.com_detail_2752692802.html]: https://www.geek-share.com/detail/2752692802.html
[MySQL]: https://www.geek-share.com/detail/2751656002.html