MySQL 锁问题（脏读、锁阻塞、死锁）
1 锁问题
通过锁机制可以实现事务的隔离性要求，使得事务可以并发地工作。锁提高了并发，但是也有有潜在的问题。不过好在因为事务隔离性的要求，锁只会带来三种问题，如果可以防止这三种情况的发生，将不会产生并发异常。
1.1 脏读
先了解脏数据，脏页，脏读。
脏页 指的是在缓冲池中已近被修改的页，但是还没有刷新到磁盘中,即数据库实例内存中的页和磁盘中的页数据是不一致的，当然在刷新到磁盘之前，日志都已经被写入到了重做（undo）日志文件中。
脏数据：是指事务对缓冲池中行记录的修改，并且还没有被提交。
对于脏页的读取，是非常正常的。脏页是因为数据库实例内存和磁盘的异步造成的，这并不影响数据的一致性（或者说两者最终会达到一致性，即当脏页都刷到磁盘）。并且因为脏页的刷新是异步的，不影响数据库的可用性，因此可以带来性能的提高。
脏数据是指未提交的数据，如果读到脏数据，即一个事务可以读到另外一个事务中未提交的数据，则显然违反了数据库的隔离性。
脏读：指的就是在不同的事务下，当前事务可以读到另外事务未提交的数据，简单来说就是可以读到脏数据。
脏读示例：
create table t (a int primary key); insert into t values (1);
事务的隔离级别
- Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
- Read committed (读已提交)：可避免脏读的发生。
- Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
- Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
会话A 并没有主动提交2这条插入事务 ， 但是在会话B 读取到了， 这就是脏读。
1.2 不可重复读
不可重读是在一个事务内读取同一数据集合。在这个事务还没有结束时，另外一个事务也访问同一数据集合，并做了一些DML操作。因此在第一个事务中的两次读取数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的情况，这种情况称为不可重复读。
不可重复读和脏读的区别是：脏读示读到未提交的数据，而不可重复读读到确实已近提交的数据。
1.3 丢失更新
虽然数据库能阻止更新问题的产生，但是在生产应用还有另一个逻辑意义丢失更新问题，而导致该问题的并不是因为数据库本身的问题。实际上，在所有多用户计算机系统环境下都有可能产生这个问题。比如下面的情况：
比如一个用户账号中有10000元，他用两个网上银行的客户端分别进行转账操作，第一次转账9000人民币，因为网络和数据的关系，这时需要等待。但是这时用户操作另一个网上银行客户端，转账1元，如果最终两笔操作都成功了，用户账号的余款是9999 元，第一次转的9000人民币并没有得到更新，但是在转账的另一个账号却会收到这9000元，这导致了结果就是钱变多，而账不平。但是银行了也很聪明啊，个人网银绑定usb key的，不会发生这种情况的。是的，通过usb key 登录也许可以解决这个问题。但是更重要的是在数据库层解决这个问题，避免任何可能发生丢失更新的情况。
要避免丢失更新发生 ，需要让事务在这种情况下的操作变成串行化，而不是并行的操作。
2 锁阻塞
因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。阻塞并不是一件坏事，其实为了确保事务可以并发正常地运行。
在 InnoDB 存储引擎中，参数innodb_lock_wait_timeout用来控制等待的时间(默认是50秒)，innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作（默认是off，不回滚）。参数innodb_lock_wait_timeout可以在mysql 数据库运行时进行调整：
在默认情况下 InnoDB 存储引擎不会回滚超时引发的错误异常。其实InnoDB 存储引擎在大部分情况下都不会对异常进行回滚。
查看锁阻塞的信息：
select * from information_schema.innodb_trx\G; # 查看当前的事务信息 select * from information_schema.innodb_locks\G; # 查看当前的锁信息 select * from information_schema.innodb_lock_waits\G; # 查看当前的锁等待信息 可以联表查，查找自己想要的结果。 select * from sys.innodb_lock_waits\G; # 查看当前的锁等待信息 show engine innodb status\G; 还可以通过当前执行了执行了什么语句 select * from performance_schema.events_statements_current\G; show full processlist;
3 死锁
死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。
3.1 数据库层面解决死锁的两种方式
1、解决死锁的问题最简单的方式是不要有等待,将任何的等待都转化为回滚，并且事务重新开始。
这种没有死锁问题的产生。在线上环境中，可能导致并发性能的下降，甚至任何一个事务都不能进行。而这锁带来的问题远比死锁问题更为严重，而这锁带来的问题原题远比死锁问题更为严重，因为这很难被发现并且浪费资源。
2、解决死锁的问题最简单的一种方
法时超时，即当两个事务互相等待是，当一个等待时超过设置的某一阈值是，其中一个事务进行回滚，另一个等待的事务就能继续进行。用innodb_lock_wait_timeout用来设置超时的时间。
超时机制虽然简单，仅通过超时后对事务进行回滚的方式来处理，或者说其根据FIFO的顺序选择回滚对象。但若超时的事务所占权重比较大，如事务操作更新很多行(比如某程序猿用死循环来执行一些事务)，占用了较多的undo log，这是采用FIFO 的方式，就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会更多。
在mysql 5.7.x 和 mysql 5.6.x 对死锁采用的方式：
mysql 5.6.x 是用锁等待（超时）的方式来解决， 没有自动解决死锁的问题：
mysql 5.7.x 默认开启了死锁保护机制：
3.2 死锁演示
如果程序是串行的，那么不可能发生死锁。死锁只存在于并发的情况，而数据库本身就是一个并发运行的程序，因此可能会发生死锁。
死锁示例：
a :创建表 create table temp(id int primary key ,name varchar(10)); insert into temp values(1,'a'),(2,'b'),(3,'c'); 此时表里只有3条数据 执行步骤根据数据顺序来： 1. 事务1: start transaction; update temp set name='aa' where id=1; 2. 事务2: start transaction; update temp set name='bb' where id=2; 3. 事务1:update temp set name='aaa' where id=2; 这时候3的步骤会有锁等待， 立马执行4，就会马上产生死锁 4. 事务2: update temp set name='bbb' where id=1;
3.3 避免死锁发生的方法
在事务性数据库中，死锁是个经典的问题，但只要发生的频率不高则死锁问题不需要太过担心死锁应该非常少发生，若经常发生，则系统是不可用。
查看死锁的方法有两种：
通过show engine innodb status命令可以查看最后一个死锁的情况
通过innodb_print_all_deadlocks参数配置可以将所有死锁的信息都打印到MySQL的错误日志中
减少死锁发生的方法：
- 尽可能的保持事务小型化，减少事务执行的时间可以减少发生影响的概率
- 及时执行 commit 或者 rollback，来尽快的释放锁
- 当要访问多个表数据或者要访问相同表的不同行集合时，尽可能的保证每次访问的顺序是相同的。比如可以将多个语句封装在存储过程中，通过调用同一个存储过程的方法可以减少死锁的发生
- 增加合适的索引以便语句执行所扫描的数据范围足够小
- 尽可能的少使用锁，比如如果可以承担幻读的情况，则直接使用 select 语句，而不要使用 select…for update 语句
- 如果没有其他更好的选择，则可以通过施加表级锁将事务执行串行化，最大限度的限制死锁发生
背景
数据库的锁是在多线程高并发的情况下用来保证数据稳定性和一致性的一种机制。MySQL 根据底层存储引擎的不同，锁的支持粒度和实现机制也不同。MyISAM 只支持表锁，InnoDB 支持行锁和表锁。目前 MySQL 默认的存储引擎是 InnoDB，这里主要介绍 InnoDB 的锁。
InnoDB 存储引擎
使用 InnoDB 的两大优点：一是支持事务；二是支持行锁。
MySQL 的事务
在高并发的情况下事务的并发处理会带来几个问题
- 脏读：指在事务 A 处理过程里读取到了事务 B 未提交的事务中的数据。比如在转账的例子中：小 A 开了一个事务给小 B 转了1000 块，还没提交事务的时候就跟小 B 说，钱已经到账了。这个时候小 B 去看了一下余额，发现果真到账了（然后就开开心心刷抖音去了），这个时候小 A 回滚了事务，把 1000 块又搞回去了。小 B 刷完抖音再去看下余额，发现钱又不见了。
- 不可重复读：指在一个事务执行的过程中多次查询某一数据的时候结果不一致的现象，由于在执行的过程中被另一个事务修改了这个数据并提交了事务。比如：事务 A 第一次读小明的年龄是 18 岁，此时事务 B 将小明的年龄改成了 20 并提交了，这个时候事务 A 再次读取小明的年龄发现是 20，这就是同一条数据不可重复读。
- 幻读：幻读通常指的是对一批数据的操作完成后，有其他事务又插入了满足条件的数据导致的现象。比如：事务 A 将数据库性别为男的状态都改成1 表示有钱人，这个时候事务 B 又插入了一条状态为 0 没钱人的记录，这个时候，用户再查看刚刚修改的数据时就会发现还有一行没有修改，这就出现了幻读。幻读往往针对 insert 操作，脏读和不可重复读针对 select 操作。
由于高并发事务带来这几个问题，所以就产生了事务的隔离级别
- Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
- Read committed (读已提交)：可避免脏读的发生。
- Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
- Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
InnoDB 常见的几种锁机制
- 共享锁和独占锁（Shared and Exclusive Locks），InnoDB 通过共享锁和独占所两种方式实现了标准的行锁。共享锁（S 锁）：允许事务获得锁后去读数据，独占锁（X 锁）：允许事务获得锁后去更新或删除数据。一个事务获取的共享锁 S 后，允许其他事务获取 S 锁，此时两个事务都持有共享锁 S，但是不允许其他事务获取 X 锁。如果一个事务获取的独占锁（X），则不允许其他事务获取 S 或者 X 锁，必须等到该事务释放锁后才可以获取到。大家可以通过下面的 SQL 感受下。
START TRANSACTION WITH CONSISTENT SNAPSHOT; SELECT * FROM category WHERE category_no = 2 lock in SHARE mode; //共享锁 SELECT * FROM category WHERE category_no = 2 for UPDATE; //独占锁 COMMIT; START TRANSACTION WITH CONSISTENT SNAPSHOT; SELECT * FROM category WHERE category_no = 2 lock in SHARE mode; //共享锁 UPDATE category set category_name = '动漫' WHERE category_no = 2; //独占锁 COMMIT;
- 意向锁（Intention Locks），上面说过 InnoDB 支持行锁和表锁，意向锁是一种表级锁，用来指示接下来的一个事务将要获取的是什么类型的锁（共享还是独占）。意向锁分为意向共享锁（IS）和意向独占锁（IX），依次表示接下来一个事务将会获得共享锁或者独占锁。意向锁不需要显示的获取，在我们获取共享锁或者独占锁的时候会自动的获取，意思也就是说，如果要获取共享锁或者独占锁，则一定是先获取到了意向共享锁或者意向独占锁。 意向锁不会锁住任何东西，除非有进行全表请求的操作，否则不会锁住任何数据。存在的意义只是用来表示有事务正在锁某一行的数据，或者将要锁某一行的数据。
“ 原文：Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.
- 记录锁（record Locks），锁住某一行，如果表存在索引，那么记录锁是锁在索引上的，如果表没有索引，那么 InnoDB 会创建一个隐藏的聚簇索引加锁。所以我们在进行查询的时候尽量采用索引进行查询，这样可以降低锁的冲突。
- 间隙锁（Gap Locks），间隙锁是一种记录行与记录行之间存在空隙或在第一行记录之前或最后一行记录之后产生的锁。间隙锁可能占据的单行，多行或者是空记录。通常的情况是我们采用范围查找的时候，比如在学生成绩管理系统中，如果此时有学生成绩 60，72，80，95，一个老师要查下成绩大于 72 的所有同学的信息，采用的语句是
select * from student where grade > 72 for update，这个时候 InnoDB 锁住的不仅是 80，95，而是所有在 72-80，80-95，以及 95 以上的所有记录。为什么会 这样呢？实际上是因为如果不锁住这些行，那么如果另一个事务在此时插入了一条分数大于 72 的记录，那会导致第一次的事务两次查询的结果不一样，出现了幻读。所以为了在满足事务隔离级别的情况下需要锁住所有满足条件的行。 - Next-Key Locks，NK 是一种记录锁和间隙锁的组合锁。是 3 和 4 的组合形式，既锁住行也锁住间隙。并且采用的左开右闭的原则。InnoDB 对于查询都是采用这种锁的。
举个例子
CREATE TABLE `a` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `uid` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_uid` (`uid`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO `a`(uid) VALUES (1); INSERT INTO `a`(uid) VALUES (2); INSERT INTO `a`(uid) VALUES (3); INSERT INTO `a`(uid) VALUES (6); INSERT INTO `a`(uid) VALUES (10); # T1 START TRANSACTION WITH CONSISTENT SNAPSHOT; //1 SELECT * FROM a WHERE uid = 6 for UPDATE; //2 COMMIT; //5 # T2 START TRANSACTION WITH CONSISTENT SNAPSHOT; //3 INSERT INto a(uid) VALUES(11); INSERT INto a(uid) VALUES(5); //4 INSERT INto a(uid) VALUES(7); INSERT INto a(uid) VALUES(8); INSERT INto a(uid) VALUES(9); SELECT * FROM a WHERE uid = 6 for UPDATE; COMMIT; ROLLBACK;
按照上面 1，2，3，4 的顺序执行会发现第 4 步被阻塞了，必须执行完第 5 步后才能插入成功。这里我们会很奇怪明明锁住的是uid=6 的这一行，为什么不能插入 5 呢？原因就是这里采用了 next-key 的算法，锁住的是（3,10）整个区间。感兴趣的可以试一下。
原文1：https://zhuanlan.zhihu.com/p/443214547
原文2：https://zhuanlan.zhihu.com/p/442941919
