随着应用对事务完整性和并发性要求的不断提高，MySQL才开始开发基于事务的存储引擎
mysql 锁
只改一行语句，锁这么多？
👉导读
这篇文章我想来聊聊 MySQL 的锁是怎么加上的，为啥想聊这个呢？主要是因为业务中我们或多或少都会使用到锁，毕竟锁是保障我们数据安全性的关键法宝。但是由于不了解原理，往往可能导致我们在”刻意“或者”无意“的使用场景下，带来潜在的性能问题，轻则导致处理能力降低，重则可能会拖垮我们的 DB，因此需要对锁的原理以及使用场景有比较全面的了解，才能更好地驾驭，避免给我们带来不必要的业务隐患。👉目录
1 啥时候加2 如何加3 什么时候该加什么时候不该加我们主要从三个方面来讨论这个问题：▶︎ 啥时候加？▶︎ 如何加？▶︎ 什么时候该加什么时候不该加？
01
啥时候加
1.1 显示锁
MySQL 的加锁可以分为显示加锁和隐式加锁，显示加锁我们比较好识别的，因为他往往直接体现在 SQL 中，常见的显示加锁语句主要有：▶︎ select ... for update；▶︎ select ... in share mode；
两者的区别在于前者加的是排它锁，后者加的是共享锁。加了排他锁之后，后续对该范围数据的写和读操作都将被阻塞，另外一个共享锁不会阻塞读取，而是阻塞写入，但是这往往会带来一些问题，比如电商场景下更新库存时候，我们为了保障数据的一致性更新往往需要先将该商品数据锁住，如果此时两个线程并发更新库存，就可能会导致数据更新出现异常。
所以我们在业务上往往会使用 select ... for update 对数据进行加锁。另外还有些咱们比较不常用的加锁方式，比如：▶︎ 全局锁：Flush tables with read lock，主要在进行逻辑备份的时候会用到▶︎ 表锁：lock tables … read/write
1.2 隐式锁
隐式锁是我们需要特别关注的，很多的“坑”就是因为隐式锁的存在导致的，无形往往最为致命。
表级锁除了表锁以外，还有元数据锁：▶︎ 在进行增删改查的时候会加 MDL 读锁；▶︎ 在对表结构进行变更的时候，会加 MDL 写锁；
这个会带来的问题就是当我们想给表添加索引或者修改表结构的时候，由于加了 MDL 写锁，会阻塞我们线上正常的读写请求，这个时候可能会触发上游的失败重试机制，那很可能就会出现请求雪崩导致 DB 被打挂。
另外的就是与我们日常业务息息相关的行锁以及间隙锁，当我们在进行增删改的时候，会根据当前的隔离级别加上行锁或者间隙锁，那么这时候需要注意是否会影响正常业务的读写性能，另外带来的风险就是可能出现加锁范围过大阻塞请求，并触发上游重试，导致服务雪崩，DB 打挂。
1.3 会不会加锁呢？
谈到这里有的同学可能有疑问，你这增删改都加锁了，那我读的时候岂不是性能很差，特别是在读多写多的业务场景下，我的读请求一上来的话，DB 不是分分钟被我查挂了？其实这里 innodb 引擎用到了一个 mvcc 的技术即多版本并发控制，其原理就是在数据更新的同时在 undolog 中记录更新的事务 id 以及相应的数据，并且维护一个 Readview 的活跃事务 id，这样当一个事务执行的时候，很容易能知道自己能看见什么数据，不能看见什么数据，这时候读取数据自然也就不会受到锁的影响能够正常地读取啦。
02
怎么加
这里讨论怎么加其实就是了解加锁的类型以及范围，即用了什么锁且加在哪里了？在讨论这个问题之前我们先来看看事务隔离级别：▶︎ 读未提交；▶︎ 读已提交；▶︎ 可重复读；▶︎ 串行化；
为啥要说这个呢？因为隔离级别也影响着咱们的加锁，读已提交解决了脏读的问题，但是未解决幻读问题；可重复读通过引入间隙锁解决了幻读问题，因此意味着不同的隔离级别用到的锁还不一样，但是有一点明确的是，越高隔离级别锁的使用更加严格。可重复读是默认的事务隔离级别，但是线上设置的隔离级别往往都是读已提交，主要是因为这个级别够用并且能够有更好的并发性能。接下来我们讨论的范围也主要是在读已提交（RC）和可重复读（RR）。
这里根据相应规则来具体分析：▶︎ 原则1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。▶︎ 原则2：查找过程中访问到的对象才会加锁。▶︎ 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。▶︎ 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。▶︎ 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
另外有两点需要注意的是：▶︎ 锁是加在索引上的；▶︎ gap锁是共享的而非独占的。
2.1 RC
接下来分别进行讨论，可能有些冗长，需要你耐心看完。
首先是 RC 级别，这个级别下的加锁规则是比较简单的，因为只涉及到行锁，首先我们先设计一张表
CREATE TABLE `t_db_lock` (`id` int(11) NOT NULL,`a` int(11) DEFAULT NULL,`b` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `a` (`a`)) ENGINE=InnoDB;insert into t_db_lock values(0,0,0),(5,5,5),(10,10,10);
2.2 主建等值存在
| sessionA | sessionB | sessionC |
| begin;update t_db_lock set id=id+1 where id = 0; | ||
| insert into t_db_lock values(1,1,1) [block] | ||
| update t_db_lock set id=id+1 where id = 5;[success] |
▶︎ 可以看到此时 sessionA 在做主键上的数据更新，将当前的记录的主键值更新为1，此时 db 会在 id=1 和 0 上加上行锁，即此时针对该id的更新会被阻塞；▶︎ 因此当 sessionB 想插入 id=1 的记录时会被阻塞住；▶︎ 但是由于 sessionC 更新的是 id=5 的记录，因此可以执行成功。
2.3 非唯一等值
| sessionA | sessionB | sessionC |
| begin;update t_db_lock set b=b+1 where a = 0; | ||
| update t_db_lock set b=b+1 where id = 0; [block] | ||
| update t_db_lock set b=b+1 where b = 0;[block] |
▶︎ sessionA 根据普通索引的判断条件更新数据，由于行锁是加在索引上，因此这时候 a 列相关索引数据上了锁；▶︎ 但是为啥这时候我更新 id=0 的数据也被阻塞了呢？因为这时除了加 a 上的索引，还有回表更新的操作，此时访问到的主键上的索引也会被加锁，因为是同一行，所以此时更新同样被阻塞住；▶︎ 同样的道理，当我们去更新的 b=0 的数据对应的主键索引上也是同一条数据，所以此时更新也被阻塞，但是如果我们此时是更新 b=5 的这条数据的话就能更新成功。
2.4 主键等值不存在
| sessionA | sessionB | sessionC |
| begin;select * from t_db_lock where id = 2 for update; | ||
| update t_db_lock set b=b+1 where a = 0; [success] | ||
| update t_db_lock set b=b+1 where b = 0;[success] |
▶︎ sessionA 加了一个 id 为2的锁，此时这行记录不存在，行锁没有加成功，因此不会阻塞其他 session 的请求；▶︎ sessionB 执行成功；▶︎ sessionC 执行成功。
2.5 无索引等值不存在
| sessionA | sessionB | sessionC |
| begin;select * from t_db_lock where b=3 for update; | ||
| update t_db_lock set b=b+1 where a = 0; [success] | ||
| update t_db_lock set b=b+1 where b = 5;[success] |
▶︎ 这种情况和主键等值不存在一致，由于未找到对应的加锁记录，则后续的更新操作都能够执行成功。
2.6 主键范围
| sessionA | sessionB | sessionC |
| begin;select * from t_db_lock where id >= 0 and id <= 5 for update; | ||
| update t_db_lock set b=b+1 where a = 0; [block] | ||
| insert into t_db_lock values(1,1,1) [success] |
▶︎ sessionA 根据范围加锁，锁了 id=0 和 5 这两行数据；▶︎ sessionB 由于更新 id=0 这行已经上锁的数据，所以被阻塞住；▶︎ sessionC 由于之前 id=1 这行记录并不存在，所以可以正常插入，这个场景是不是有点熟悉，就是咱们所说的幻读，如果这时候在 sessionA 中再执行 select * from t_db_lock where id >= 0 and id <= 5 就会发现多了一条数据；
2.7 RR
这里可重复读级别下主要是讨论间隙锁的加锁场景，这种加锁情况会比读已提交的隔离级别复杂的多；set session transaction isolation level repeatable read。
2.8 主键等值存在
| sessionA | sessionB | sessionC |
| begin;select * from t_db_lock where id = 5 for update; | ||
| insert into t_db_lock values(2,2,2); [success] | ||
| insert into t_db_lock values(6,6,6); [success] |
▶︎ sessionA 在已经存在的 id=5 这行加锁，根据加锁规则，唯一索引会退化为行锁，因此仅在 id=5 这行加锁；其实这也好理解，既然已经是唯一索引了，那么就不会会出现幻读的情况，因此幻读仅仅取决于这行是否存在，因此我只要给该行加锁保证不再写入即可；
▶︎ sessionB 和 sessionC 均不在锁范围内则插入成功.
2.9 非唯一等值
| sessionA | sessionB | sessionC |
| begin;select * from t_db_lock where a = 5 for update; | ||
| insert into t_db_lock values(6,11,6); [block] | ||
| insert into t_db_lock values(11,10,6); [success] |
▶︎ sessionA 在已经存在的 a=5 这行记录上加锁，由于是非唯一索引，根据加锁规则，首先扫描 a 索引加上 next-key lock (0,5] ,接着向右遍历到第一个不满足条件的(根据规则五，唯一索引上的范围查询会访问到不满足条件的第一个值为止)，并退化为间隙锁，因此加锁范围为（5,10），总体加锁范围为（0,10）；并且 for update，也会对应在主键的索引范围内加上锁，即（0，10）；
▶︎ sessionB 在主键索引的锁范围内，因此被阻塞；
▶︎ sessionC 此时不在普通索引和主键索引的范围上，因此执行成功；
这里可以看到，对于非唯一等值查询的情况下，加锁的范围要比主键等值存在更大，因此我们在对非唯一索引加锁的时候需要注意这个范围。
2.10 主键等值不存在
| sessionA | sessionB | sessionC |
| begin;select * from t_db_lock where id = 3 for update; | ||
| insert into t_db_lock values(2,2,2); [block] | ||
| insert into t_db_lock values(6,6,6); [success] |
▶︎ sessionA 此时对 id=3 的记录加上了行锁，但是由于此时3这行的记录不存在，会对此范围加锁，按照加锁原则，向右遍历且最后一个值不满足等值条件，next-key lock 退化为间隙锁，此时加锁范围为（0,5）；
▶︎ sessionB 属于加锁范围内，因此被阻塞；
▶︎ sessionC 不在此加锁范围内，加锁成功。
为啥这里要加的是范围锁呢，其实主要解决的是幻读问题，假设这里没有在此范围内加锁，那么 T1 时刻 sessionB 执行成功，T2 时刻再次执行 select * from t_db_lock where id = 3 的话，就会发现原先查询不到的结果现在竟然可以查询到了，就像出现幻觉一样；为了避免出现这种幻读的情况，需要在此范围内加锁。
2.11 非唯一等值不存在
| sessionA | sessionB | sessionC |
| begin;select * from t_db_lock where a = 3 for update; | ||
| insert into t_db_lock values(3,5,5); [block] | ||
| insert into t_db_lock values(6,5,5); [success] |
▶︎ sessionA 在 a=3 这行上加锁的，由于 db 中不存在该行，所以同样会加next-key lock,并且因为锁都是加在索引上的，因此会在 a 索引上加上（0,5）的范围锁。但是这里有个奇怪的现象，当 a=5 时，如果 id<5 会阻塞，如果 id>5 则会成功，从结果看来，此时 a 上的锁似乎是有偏向性的，并不是严格意义上的 a=5 时就会锁住相应的插入记录
2.12 主键范围
| sessionA | sessionB | sessionC |
| select * from t_db_lock where id >= 5 and id < 6 for update; | ||
| insert into t_db_lock values(3,3,3); [success] | ||
| insert into t_db_lock values(10,10,10); [block] |
▶︎ sessionA 进行范围查询加锁，在语义上等价于 select * from t_db_lock where id = 5 for update，但是实际加锁情况还是有很大的区别，首先 id >= 5 根据等值查询查询到id=5这行加锁为（0,5]，由于是唯一索引，退化为行锁，因此在 id=5 这行上加了锁，接着向右查询，找到第一个不满足条件的值，即 id=10 这行，所以加 next-key lock(5,10]，这里因为并不是等值查询，不会有退化为间隙锁的过程，所以整体加锁范围[5,10]；
▶︎ sessionB 不在锁范围内，插入成功；
▶︎ sessionC 在锁中，插入失败，注意这里是被阻塞住，而不是报主键冲突。
2.13 非唯一范围
| sessionA | sessionB | sessionC |
| begin;select * from t_db_lock where a >= 5 and a < 6 for update; | ||
| insert into t_db_lock values(3,3,3); [block] | ||
| insert into t_db_lock values(10,10,10); [block] |
▶︎ sessionA 加锁范围区别于主键索引主要是在(0, 5]这个范围下并未退化为行锁，因此总体加锁范围为(0, 10]
2.14 无索引等值不存在
| sessionA | sessionB | sessionC |
| begin;select * from t_db_lock where b = 6 for update; | ||
| insert into t_db_lock values(3,3,3); [block] | ||
| insert into t_db_lock values(10,10,10); [block] |
▶︎ sessionA 中加锁记录为 b=6 这行，由于 b 未创建索引，因此会将所有 b 索引上的记录都加锁，由于是 for update 加锁，认为还回去主表上更新，因此主表的相关记录也都被上了锁，这就会导致加锁期间处于锁表的状态，任何的更新操作都没办法成功，这在线上会是非常危险的操作，可能会导致 db 被打垮。
03
什么时候该加什么时候不该加
通过上述的分析我们应该对锁的类型以及语句中加锁的范围有一个大致的了解，可以知道悲观锁是需要我们谨慎使用的，因为很可能简单的 SQL 就会拖垮 db 的性能，影响线上服务的质量，那么什么时候该加什么时候不该加呢？
我认为对于 db 的并发场景，我们可以这么去考虑：
▶︎ 尽可能优先考虑使用乐观锁的方式解决；
▶︎ 如果需要用到悲观锁，则务必在加锁的键上加索引；
▶︎ 确认 db 的隔离级别，分析 SQL 中可能存在导致冲突或者死锁的原因，避免 SQL 被长时间阻塞；
其实对于 db 的互斥方案并没有银弹，要根据具体的业务场景去针对性的制定解决方案，只是在可能出现的一些坑中，我们能够提前识别到，避免低级错误，并且有能力去优化他，这就是能让自己不断进步提升的好方法啦。
只改一行语句，锁这么多？ https://mp.weixin.qq.com/s/0GDk_4b4JUPBmKWDdEbp1A
MYSQL 解锁与锁表 - 专注it - 博客园 https://www.cnblogs.com/wanghuaijun/p/5949934.html
2016-10-11 16:50
MYSQL 解锁与锁表
MySQL锁概述
相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
MySQL这3种锁的特性可大致归纳如下。
开销、加锁速度、死锁、粒度、并发性能
l 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
l 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
l 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
MyISAM表锁
MyISAM存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。随着应用对事务完整性和并发性要求的不断提高，MySQL才开始开发基于事务的存储引擎，后来慢慢出现了支持页锁的BDB存储引擎和支持行锁的InnoDB存储引擎（实际 InnoDB是单独的一个公司，现在已经被Oracle公司收购）。但是MyISAM的表锁依然是使用最为广泛的锁类型。本节将详细介绍MyISAM表锁的使用。
查询表级锁争用情况
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 2979 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec))
如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。
获取InnoDB行锁争用情况
可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0 |
| InnoDB_row_lock_time | 0 |
| InnoDB_row_lock_time_avg | 0 |
| InnoDB_row_lock_time_max | 0 |
| InnoDB_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.01 sec)
如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过
案例
Variable_name Value
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 16174736
Innodb_row_lock_time_avg 719
Innodb_row_lock_time_max 51923
Innodb_row_lock_waits 22491
解锁
第一种
show processlist;
找到锁进程，kill id ;
第二种
mysql>UNLOCK TABLES;
锁表
锁定数据表，避免在备份过程中，表被更新
mysql>LOCK TABLES tbl_name READ;
为表增加一个写锁定：
mysql>LOCK TABLES tbl_name WRITE;
