事务和隔离级别:

sql语句使用事务:

start transaction;
select * from xxx where id = 1;
update xxx set name = test where id = 1;
update xxx set sex = male where id = 1;
…
commit;

事务四大特性:

原子性(atomicity): 一个事务必须被视为不可分割的最小单元, 这个事务内所有操作要么全部成功, 要么全部失败。
一致性(consistency): 数据库总是从一个一致性状态到另外一个一致性状态, 上述例子，即使某一步出错, 整个事务最终没有提交, 事务中的修改也不会保存到数据库中的.
隔离性(isolation): 一个事务所做的修改, 在最终提交之前, 对其他事务是不可见的。
持久性(durability): 一个事务提交, 其所做的修改, 会永久保存, 即使系统崩溃.

数据库几个并发问题:

脏读

一个事务读取另外一个事务未提交的数据。

事务1 事务2
select age from table1 where id = 1
— 结果是 20
update table1 set age = 30 where id = 1
— 这里没有提交
select age from table1 where id = 1
— 结果是 30
事务2回滚

这里的事务1就读取了一条脏数据。 — 脏读
例子:

  1. 原本: national_standard_code = 110101, remark = '对比一致' 
  2. 事务1:
  3. start transaction;
  4. update region_info_bak set remark = '吧啦巴拉巴拉'
  5. where national_standard_code = '110101';
  6. -- 第一步, 执行上述sql
  7. -- 第三步, 回滚事务1 执行rollback;
  8. 事务2:
  9. start transaction;
  10. select * from region_info_bak
  11. where national_standard_code = '110101';
  12. commit;
  13. -- 第二步, 执行事务2
  14. -----------------
  15. 不同隔离级别: 亲测有效
  16. Read Uncommitted: 会出现这种问题
  17. Read Committed: 不存在这种问题
  18. ...后续隔离级别也不存在

不可重复读

一个事务从开始到提交之前, 所做的任何事情对其他事务都是不可见的。比如: 事务1先去查询id = 1
的数据, 但此时事务2而并不知道事务1在查询id = 1的数据, 对id = 1 的数据进行修改并提交事务. 同理, 事务2在修改
id = 1的信息的时候, 事务1并不知道, 再次查询时候, 得到的结果和第一次不一致.

事务1 事务2
select age from table1 where id = 1
— 20
update table1 set age = 30 where id = 1
— 这里提交了
select age from table1 where id = 1
— 30
— 这里才commit		 
  1. 事务1:
  2. start transaction;
  3. select * from region_info_bak
  4. where national_standard_code = '110101';
  5. -- 第一步: 先执行上述sql
  7. select * from region_info_bak
  8. where national_standard_code = '110101';
  9. commit;
  10. -- 第三步: 执行上述查询sql,并提交事务
  12. 事务2:
  13. start transaction;
  14. update region_info_bak set remark = '吧啦巴拉巴拉'
  15. where national_standard_code = '110101';
  16. commit;
  17. -- 第二步: 执行事务2
  18. 结论: 两次查询到的数据不一致.
  20. -----------------
  21. 不同隔离级别: 亲测有效
  22. Read Uncommitted: 两次查询数据不一致
  23. Read Committed: 两次查询数据不一致
  24. Repeatable Read: 两次查询数据一致
  25. ...后续隔离级别也一致

幻读

幻读是不可重复读的一种特殊情况, 在同一个事务范围内, 查询两次结果得到条数不一样。
事务 (RR级别下, 标准的sql规范是存在幻读的, mysql通过间隙锁的技术避免幻读)

事务1 事务2
select * from table1 where id between 1 and 10
— 10条记录
insert into table1 values(9,’asd’,29)
— 提交
select * from table1 where id between 1 and 10
— 11条记录		 
  1. 事务1:
  2. start transaction;
  3. select * from region_info_bak
  4. where national_standard_code in ('110101','11010101');
  5. -- 第一步: 查询code是二者的, 查询结果一个。
  6. select * from region_info_bak
  7. where national_standard_code in ('110101','11010101');
  8. commit;
  9. -- 第三步: 查询code是二者的, 查询结果两个。
  11. 事务2:
  12. start transaction;
  13. INSERT INTO `spring`.`region_info_bak` (`national_standard_code`, `national_standard_name`, `dmall_code`, `dmall_name`, `remark`)
  14. VALUES ('11010101', '北京首都', '11010101', '北京首都', '对比一致');
  15. commit;
  16. -- 第二步: 执行事务2
  18. ------------------
  19. 不同隔离级别: 亲测有效
  20. Read Uncommitted: 两次查询数据数量不一致
  21. Read Committed: 两次查询数据数量不一致
  22. Repeatable Read: 两次查询数据数量一致 按常理来说,是无法解决幻读的。?? 疑问点???? 后续解答
  23. Serializable 可以解决幻读.

数据库事务的隔离级别

数据库查询隔离级别: select @@global.transaction_isolation;
数据库修改隔离级别:
READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

未提交读(Read uncommitted)

事务读不阻塞其他事务读和写, 事务写阻塞其他事务写但是不阻塞读。通过对写操作加持续X锁，对读操作不加锁实现。
产生现象:
事务1在读取的时候, 事务2也可以进行读取以及修改。(事务在读取的时候是不加锁的)
事务1在这条记录进行修改时候, 事务2是不允许修改(避免脏写, 事务写操作加X锁)。但是事务2是可以读取的(出现脏读问题) 

  1. 事务2
  2. start transaction;
  3. update region_info_bak set remark = '吧啦巴拉巴拉'
  4. where national_standard_code = '110101';
  5. -- 步骤1: 执行上述sql
  6. -- 步骤3: 提交事务1, commit;
  8. 事务2
  9. start transaction;
  10. update region_info_bak set remark = '淅沥淅沥下'
  11. where national_standard_code = '110101';
  12. commit;
  13. -- 步骤二: 执行事务2
  14. -- 结果: 事务2获取锁超时...

提交读(Read commited)

事务读不会阻塞其他事务读，事务写会阻塞其他事务读和写；通过对写操作加 “持续X锁”，对读操作加 “瞬时S锁” 实现(瞬时S锁: 读数据时候加锁, 数据读取完马上释放锁)；避免脏读；
产生现象:
事务1在读取的时候, 事务2也可以读取。(事务1在加共享锁同时, 事务2在读取的时候,同样可以进行添加共享锁)
事务1在更新时候, 事务2不可以进行读取(事务1在更新的时候添加了排他锁, 直到事务结束后才释放锁,但是此时事务2读取的时候, 是需要加S锁, 所以会阻塞)
image.png image.png

可重复读(Repeatable reads) (与RC区别:共享锁释放时间)

事务读会阻塞其他事务事务写，事务写会阻塞其他事务读和写；通过对写操作加 “持续X锁”，对读操作加 “持续S锁”(当数据读取完成并不立刻释放 S 锁，而是等到事务结束后再释放)。
产生现象:
事务1在读取的时候, 事务2也可以读取。(事务1在加共享锁同时, 事务2在读取的时候,同样可以进行添加共享锁)
事务1在读取某行的时候, 事务2不可以进行修改。只有事务1结束 共享锁释放，事务2才可以进行更新。(这里有别于RC) — 解决不可重复读。

可序列化(Serializable)

1.在读取数据的时候, 必须添加表级共享锁, 直到事务释放。
2.在更新数据的时候, 必须添加表级排他锁, 直到事务释放。
产生现象:
事务1读取表1数据, 事务2也可以读取表1数据,但是不可以进行新增 删除 修改操作。(事务1新增了表级共享锁, 其他事务可以增加共享锁,可以进行读取)
事务2更新表1数据, 事务2不能读取任何操作。(事务1新增了表级排他锁, 其他事务不可以新增任何锁，不可以进行任何操作) — 避免了幻读

MySQL中的隔离级别实现

读未提交和可串行化的实现没什么好说的，一个是啥也不干，一个是直接无脑加锁避开并行化 让你啥也干不成。重头戏就是读已提交和可重复读是如何实现的。

MVCC

多版本并发控制(Multi-Version Concurrency Control, MVCC)是MySQL中基于乐观锁理论实现隔离级别的方式。undo 版本链机制以及read view快照读机制，这两个机制相互配合是实现MVCC的核心。

undo log版本链

undo log: 保证了未提交读的ACID, 在事务未提交之前, 数据发生变更, 变更信息存储在undo log中。此外会有两个隐藏字段：row_trx_id和roll_pointer，row_trx_id表示更新本条记录的全局事务id, roll_pointer是回滚指针，指向当前记录的前一个undo log版本，如果是第一个版本则roll_pointer指向nil。这样如果有多个事务对同一条记录进行了多次改动，则会在undo log中以链(版本链)的形式存储改动过程。
image.png
操作类型是insert时候, undo log存储的新数据, 事务发生回滚, undo log日志会删除该数据。
操作类型是update的时候, undo log存储的新数据, 并指向旧数据, 事务发生回滚, undo log日志会恢复旧数据。

read view(快照读)

create_trx_id: 创建readView的事务id。
m_idx: 创建readview的时候, 活跃的事务id集合
min_trx_id: 创建readview的时候, 活跃的事务的id最小值
max_trx_id: 当前系统中已经创建过的最新事务的id+1的值
当一个事务读取某条记录时会追溯undo log版本链，找到第一个可以访问的版本，而该记录的某一个版本是否能被这个事务读取到遵循如下规则:
row_trx_id(当前记录行) < min_trx_id: 可以访问, 记录行在该事务开启之前创建, 可以访问。
row_trx_id >= max_trx_id: 不可以访问, 记录行创建晚于活跃的最大事务. 不可以访问。
row_trx_id <= min_trx_id < max_trx_id
row_trx_id存在于m_idx里面, 创建记录行的事务是当前活跃的事务, 不允许访问。(如果创建记录行的事务和创建readview的事务是同一个, 那么也是可以访问的)
row_trx_id不存在于m_idx里面, 记录行是其他事务再当前事务开启之前, 其他事务已经提交的undo 版本, 可以访问。

读已提交:每次执行sql, 都会创建一个read view.

image.png
事务A第一次查询创建的read view：
create_trx_id = 101｜ m_idx = [101, 102]｜min_trx_id = 101｜max_trx_id = 103
事务B的read view：
create_trx_id = 102｜ m_idx = [101, 102]｜min_trx_id = 101｜max_trx_id = 103
事务A第二次查询创建的read view：
create_trx_id = 101｜ m_idx = [101]｜min_trx_id = 101｜max_trx_id = 103

image.png
在读已提交隔离级别下，每次执行SQL都会创建最新的read view，所以在A事务做第二次查询的时候, 新建的readview里m_idx数组中没有102, 事务A在追溯undo log版本链的时候，最新版本记录的trx_id = 102，102不在A事务的m_idx数组中，且101 = min_trx_id <= 102 < max_trx_id = 103，因此可以访问到B事务的提交结果。出现了不可重复读的问题。（这里B不提交的话A如果就进行了第二次查询，则102不会从A事务的read view移除，则A事务依旧访问不到B事务未提交的修改，因此脏读还是可以避免的！）

可重复读: 当事务sql执行的时候，会生成一个read view快照，且在本事务周期内一直使用这个read view

image.png
事务A的read view：
create_trx_id = 101｜ m_idx = [101, 102]｜min_trx_id = 101｜max_trx_id = 103
事务B的read view：
create_trx_id = 102｜ m_idx = [101, 102]｜min_trx_id = 101｜max_trx_id = 103

image.png
在可重复读隔离级别下，只有事务开启的时候新建readview，所以在A事务做第二次查询的时候, 使用的readview还是第一次新建的,m_idx数组中包含[101,102], 事务A在追溯undo log版本链的时候，最新版本记录的trx_id = 102，102在A事务的m_idx数组中，且不等于事务A的事务id[101]，因此访问不到B事务的提交结果。接着向前回溯, 这里找到trx_id = 100的记录版本(小于A事务read view的min_trx_id属性，因此可以访问到). 故A事务第二次查询依旧得到a = 0，而不是B事务修改的a = 1。解决不可重复读的问题。

2. 锁相关:

image.png

2.1表锁 VS 行锁(粒度细分角度)

表锁: 对一整张表加锁，一般是 DDL 处理时使用。
行锁: innerdb引入了行锁的概念, 行锁是锁定某一行数据或某几行, 或行和行之间的间隙。是通过给索引上的索引记录加锁来实现的, 如果未击中索引, 那么加的是表锁。数据库操作使用主键索引时，InnoDB会锁住主键索引；使用非主键索引时，InnoDB会先锁住非主键索引，再锁定主键索引。
image.png

2.1.1 行锁:

1. mysql加锁流程:(举例说明)

update students set score = 100 where name = ‘Tom’; name是二级索引, 会在二级索引上加锁, 同时会在二级索引定位到的主键索引上加X锁。
image.png
对于同时更新多条记录, 会读取where条件的第一条满足条件的记录, 然后加锁, 在发起update操作,更新该数据. 依次处理所有数据。
image.png

2. 写锁 VS 读锁 (使用方式划分)

读锁: 也称共享锁(S锁)。允许获得该锁的事务读取数据行，同时允许其他事务获得该数据行上的共享锁，并且阻止其他事务获得数据行上的排他锁。
写锁, 也叫排他锁(X锁)。允许获得该锁的事务更新或删除数据行，同时阻止其他事务取得该数据行上的共享锁和排他锁。
例子: 事务A对id为1的数据行加共享锁, 使用select … for share 语句。
事务B对id为1的数据行加排它锁, 使用select … for update语句。
image.png
image.png

3. 行锁三种实现算法(记录锁,间隙锁,Next-key锁)

image.png
image.png
记录锁(Record Lock), 封锁的是该行的索引记录, 如果表中没有定义索引, InnoDB 默认为表创建一个隐藏的聚簇索引，并且使用该索引锁定记录。如果索引失效, 那么行级锁就会退化成表锁。记录锁是基于唯一索引的。
间隙锁(LOCK_GAP): 不同于 Record Lock是基于唯一索引的，Gap Lock 和 Next-Key Lock 都是基于非唯一索引的。不同于 Record Lock锁定的是某一个索引记录，Gap Lock 和 Next-Key Lock 锁定的都是一段范围内的索引记录。
Next-Key锁: 是结合了 Gap Lock 和 Record Lock 的一种锁定算法。
间隙锁和Next-Key锁只有在可重复读(RR)级别下才存在。其主要目的是为了解决幻读问题。

例子: 一个索引假设有10,11,13,20这几个值
记录锁: 会将10,11,13,20这四个索引锁住。
间隙锁: (-∞,10)，(10,11)，(11,13)，(13,20)，(20, +∞) 五个范围锁住。
Next-Key: (-∞,10]，(10,11]，(11,13]，(13,20]，(20, +∞)，即锁定了一个范围, 也会锁定索引本身。

4. 加锁规则(RR级别)

select xxx 快照读,无锁.
select … lock in share mode IS锁；S锁.
select … for update IX锁；X锁.(锁实现：记录锁；间隙锁；临键锁)
update … [等值 / 范围] / delete … [等值 / 范围] IX锁；X锁. (锁实现：记录锁；间隙锁；临键锁)
insert … 插入意向锁

行锁的默认算法是Next-Key Lock，是一个左开右闭的区间，锁住当前记录及其左区间。
在进行等值查询时, 若加锁的对象是唯一索引，则Next-Key Lock会退化为Record Lock。若查询条件没有命中行，则Next-Key Lock退化为Gap Lock。
在进行范围扫描时，行锁不退化。

5. 加锁实例

image.png
现有如下数据, id是主键, age是普通索引, username无索引。并且事务的隔离级别是RR。

使用唯一索引来进行等值查询, 如果该数据存在, 产生记录锁。

  1. -- 事务A
  2. -- 使用唯一索引来进行等值查询, 如果该数据存在, 产生记录锁
  3. begin;
  4. select * from test  where id = 5 for update;
  7. -- 事务2
  8. begin;
  9. select * from test  where id = 5 for update;
  10. commit;

image.png
原因: 同一条记录产生记录锁, 会发生阻塞。

使用唯一索引来进行等值查询, 该记录不存在, 产生间隙锁。

  1. -- 事务A
  2. -- 使用唯一索引来进行等值查询, 该记录不存在, 产生间隙锁。
  3. begin;
  4. select * from test  where id = 1 for update;
  7. -- 事务2
  8. begin;
  9. insert into test(id,username, age, class) values(4,'zs-1',10,'A');
  10. commit;

image.png
原因: 唯一索引等值查询id=1, 未查询到数据, 加了间隙锁(-无穷,5]。

使用唯一索引来范围查询的语句时, 对于满足查询条件但不存在的数据产生间隙(gap)锁，如果查询存在的记录就会产生记录锁，加在一起就是临键锁(next-key)锁。

  1. -- 事务A
  2. -- 使用唯一索引来范围查询的语句时, 对于满足查询条件但不存在的数据产生间隙(gap)锁，如果查询存在的记录就会产生记录锁，加在一起就是临键锁(next-key)锁。
  3. begin;
  4. select * from test  where id <= 3 for update;
  5. commit;
  8. -- 事务2
  9. begin;
  10. insert into test(id,username, age, class) values(3,'zs',10,'A');
  11. commit;

image.png
原因: 唯一索引范围查询 id<=3 会加(-无穷,3]间隙锁, 所以插入id=3的数据会阻塞。

使用普通索引不管是锁住单条，还是多条记录，都会产生间隙锁。

  1. -- 事务A
  2. # 使用普通索引不管是锁住单条，还是多条记录，都会产生间隙锁。
  3. begin;
  4. select * from test  where age = 4 for update;
  6. -- 事务2
  7. begin;
  8. insert into test(id,username, age, class) values(9,'zs',5,'A');
  9. commit;
  10. begin;
  11. update test set username = 'ls' where id = 7;
  12. commit;

image.png
原因: 普通索引 加 gap锁(5,7],(7,8],(8,10),插入或者修改都阻塞。

没有索引不管是锁住单条，还是多条记录，都会产生表锁。

  1. -- 事务A
  2. -- 没有索引不管是锁住单条，还是多条记录，都会产生表锁
  3. begin;
  4. select * from test  where username = '张三' for update;
  5. commit;
  8. -- 事务2
  9. begin;
  10. update test set username = 'ls' where id = 7;
  11. commit;
  12. -- 由于产生表锁, 发生阻塞。

2.2.1意向锁

例子: 事务A获取的一行数据的共享锁, 这时事务B申请修改表结构, 需要获取表锁(修改任意数据行)。存在冲突。常规情况下是需要遍历整张表, 看看是不是某条记录被加锁，如果有锁，则不允许加表锁，显然这是很低效的一种方法，为了方便检测表锁和行锁的冲突，从而引入了意向锁(表明某个事务正在或者即将锁定表中的数据行)。

意向共享锁（IS）<表级>

事务在给数据行加行级共享锁之前，必须先取得该表的 IS 锁。

意向排他锁（IX）<表级>

事务在给数据行加行级排他锁之前，必须先取得该表的 IX 锁。

image.png

插入意向锁(II GAP) <行级>

插入意向锁本质上可以看成是一个Gap Lock。
普通的Gap Lock 不允许 在 （上一条记录，本记录） 范围内插入数据
插入意向锁Gap Lock 允许 在 （上一条记录，本记录） 范围内插入数据
插入意向锁的作用是为了提高并发插入的性能， 多个事务 同时写入 不同数据 至同一索引范围（区间）内，并不需要等待其他事务完成，不会发生锁等待。

插入意向锁是间隙锁的一种，专门针对insert操作的。即多个事务在同一个索引、同一个范围区间内插入记录时，如果插入的位置不冲突，则不会阻塞彼此；可以提高并发插入。

下图为锁兼容情况, 对于插入意向锁而言, 兼容性和加锁顺序有关系, 如图所示, 如果前一个事务持有gap锁, 或者next-key锁, 那么后一个事务想要持有插入意向锁时候不兼容, 会出现锁等待。
image.png

2.2乐观锁 VS 悲观锁(思想上划分)

悲观锁，每次在拿数据的时候都会给数据加上锁(通过for update实现)，用这种方式来避免跟别人冲突，虽然很有效，但是可能会出现大量的锁冲突，导致性能低下。
乐观锁, 每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有改过这个数据，可以使用版本号等机制来判断。

乐观锁使用:

方式1: 通过版本号判断
步骤一: select * from OptimisticLock where id = 2 — 查询得到版本是 1
步骤二: update OptimisticLock
set status = ‘finish’,version = version + 1
where id = 2 and version = 1
方式2: 通过状态判断 库存举例
update xxx
set amount = amount - #{buyId}
where code = #{code} and amount > #{buyId} (amount > #{buyId} 就是一个状态机)
方式3:缓存实现乐观锁 CAS机制(Compare and Swap)

2.3 死锁

2.3.1 死锁产生条件

互斥条件：一段时间内某资源只由一个进程占用。
请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放。
不剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。
环路等待条件：存在一个进程-资源之间的环形链 ，环路中每个进程都在等待下一个进程所占有的资源

2.3.2 死锁模拟

共享排他锁死锁:(并发量插入相同记录情况下出现)

  1. -- 1. 事务A先执行, 获取id=10的排它锁
  2. begin;
  3. insert into test(id,username, age, class) values (10,'ww',15,'A');
  4. rollback;    -- 4. id=10排他锁释放。
  6. -- 2. 事务2执行, 需要进行PK校验，故需要先获取id=10的共享锁，阻塞
  7. begin;
  8. insert into test(id,username, age, class) values (10,'ww',15,'A');
  9. commit;
  11. -- 3. 事务3执行,需要进行PK校验，也要先获取id=10的共享锁，也阻塞
  12. begin;
  13. insert into test(id,username, age, class) values (10,'ww',15,'A');
  14. commit;
  16. -- 5. 事务2和事务3要想插入成功，必须获得id=7的排他锁，
  17. 但由于双方都已经获取到id=7的共享锁，它们都无法获取到彼此的排他锁，死锁就出现了。

image.png

并发间隙锁死锁
image.png

  1. -- 事务1
  2. begin;
  3. delete from test where id = 6;    
  4. -- 1. 执行delete, id = 6数据不存在, 获得(3,10)间隙共享锁
  5. insert into test(id,username, age, class) values (5,'www',36,'A');
  6. -- 3. 执行insert,希望获得(3,10)间隙排他锁, 但因为有共享锁了, 故阻塞。
  7. commit ;
  9. begin;
  10. delete from test where id = 7;
  11. -- 2. 执行delete, id = 7数据不存在, 获得(3,10)间隙共享锁
  12. insert into test(id,username, age, class) values (8,'www',37,'A');
  13. -- 4. 执行insert,也希望获得(3,10)间隙排他锁, 于是发生死锁。
  14. commit ;

image.png
image.png

2.3.3 死锁检测机制

InnoDB主要采用两种方式来预防死锁：超时获取+基于等待图的主动检测。
超时获取: 当获取锁的等待超过一定时间时，自动退出等待并回滚事务。超时获取的主要缺点在于时间阈值不好确定。通过(innodb_lock_wait_timeout参数设置)
基于等待图（wait-for graph）的主动检测:
根据事务所持有的锁、以及尝试获取锁的信息，绘制事务之间的等待图，若图中存在回路，则说明存在死锁。此时，InnoDB会主动回滚undo量最少的事务。(将参数 innodb_deadlock_detect 设置为 on)
等待图是一种主动检测策略，在每个事务请求锁并发生等待时，均会将其放入等待图中，并判断是否会产生回路。InnoDB采用深度优先算法对等待图进行回路检测。等待图的缺点在于会耗费较多的CPU资源。

2.3.4 死锁分析

  1. SHOW ENGINE INNODB STATUS查看死锁日志。并分析加锁情况。 ```java

LATEST DETECTED DEADLOCK

2022-06-14 22:05:06 0x70000e5a9000 * (1) TRANSACTION: 事务 480125 活跃36秒 TRANSACTION 480125, ACTIVE 36 sec inserting

事务正在使用1张表, 涉及到锁的表有一张 mysql tables in use 1, locked 1

这行表示在等待4把锁，占用内存1136字节，涉及2行记录。 LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2 MySQL thread id 63, OS thread handle 123145556033536, query id 9456 localhost 127.0.0.1 root update / ApplicationName=DataGrip 2018.1.4 / insert into test(username, age, class) values (‘王五’,15,’A’)

目前持有的锁 * (1) HOLDS THE LOCK(S): 持有的锁是一个record lock,空间id是62,页编号为5,大概位置在页的72位处。 锁发生在表spring.test的索引(test_age_index上),是一个Next-Key锁。 RECORD LOCKS space id 62 page no 5 n bits 72 index test_age_index of table spring.test trx id 480125 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;;

等待的锁 * (1) WAITING FOR THIS LOCK TO BE GRANTED: 等待的锁是一个record lock,空间id是62,页编号为5,大概位置在页的72位处。 锁发生在表spring.test的索引(test_age_index上),要加一个插入意向锁但是还在等待状态。 RECORD LOCKS space id 62 page no 5 n bits 72 index test_age_index of table spring.test trx id 480125 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;;

** (2) TRANSACTION: TRANSACTION 480126, ACTIVE 23 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2 MySQL thread id 72, OS thread handle 123145555427328, query id 9465 localhost 127.0.0.1 root update / ApplicationName=DataGrip 2018.1.4 */ insert into test(username, age, class) values (‘赵六’,30,’A’)

* (2) HOLDS THE LOCK(S): 持有的锁是一个record lock,空间id是62,页编号为5,大概位置在页的72位处。 锁发生在表spring.test的索引(test_age_index上),是一个Gap锁。 RECORD LOCKS space id 62 page no 5 n bits 72 index test_age_index of table spring.test trx id 480126 lock_mode X locks gap before rec Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000014; asc ;; 1: len 4; hex 80000002; asc ;;

* (2) WAITING FOR THIS LOCK TO BE GRANTED: 等待的锁是一个record lock,空间id是62,页编号为5,大概位置在页的72位处。 锁发生在表spring.test的索引(test_age_index上),要加一个插入意向锁但是还在等待状态。 RECORD LOCKS space id 62 page no 5 n bits 72 index test_age_index of table spring.test trx id 480126 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;

* WE ROLL BACK TRANSACTION (1) ``` 事务1: 拥有Next-Key锁, 等待插入意向锁.(Next-Key锁与插入意向锁是冲突的, 所以在拥有的Next-Key锁的情况下, 是获取不到插入意向锁的)
事务2: 拥有Gap锁, 等待插入意向锁.(Gap锁与插入意向锁是冲突的, 所以在拥有的Gap锁的情况下, 是获取不到插入意向锁的) 发生死锁。

2.3.5 预防死锁

  1. 不同的应用访问同一组表时, 应尽量约定以相同的顺序访问各组表。对一个表而言, 应尽量以固定的顺序存取表中的信息。

举例：好比有a,b两张表，如果事务1先a后b,事务2先b后a,那就可能存在相互等待产生死锁。那如果事务1和事务2都先a后b，那事务1先拿到a的锁，事务2再去拿a的锁，如果锁冲突那就会等待事务1释放锁，那自然事务2就不会拿到b的锁，那就不会堵塞事务1拿到b的锁，这样就避免死锁了。

  1. 在主键等值更新的时候，尽量先查询看数据库中有没有满足条件的数据，如果不存在就不用更新，存在才更新。为什么要这么做呢，因为如果去更新一条数据库不存在的数据，一样会产生间隙锁。

举例：如果表中只有id=1和id=5的数据，那么如果你更新id=3的sql，因为这条记录表中不存在，那就会产生一个(1,5)的间隙锁，但其实这个锁就是多余的，因为你去更新一个数据都不存在的数据没有任何意义。

  1. 尽量使用主键更新数据,因为主键是唯一索引，在等值查询能查到数据的情况下只会产生行锁，不会产生间隙锁，这样产生死锁的概率就减少了。当然如果是范围查询，一样会产生间隙锁。
  2. 避免长事务，小事务发送锁冲突的几率也小。
  3. 在允许幻读和不可重复度的情况下，尽量使用RC的隔离级别，避免gap lock造成的死锁，因为产生死锁经常都跟间隙锁有关.