MySQL 加锁原理大揭秘：避免死锁，提升效率的终极指南

说起MySQL这个数据库大师家里热闹得跟火车站似的，里面的锁定机制可不就是站里的规矩。今天咱们就来聊聊MySQL插入数据时是怎么上锁的，还有怎么避免让人头疼的死锁问题。当然，我还会告诉你几个提升批量插入速度的小妙招。你准备好了吗？那咱们就一起去看看MySQL锁背后的秘密！

1.锁的概念复习：不只是简单的锁门

你猜怎么着?MySQL里面的锁可不是随便关门那么简单的!它还有个厉害的武器，就是间隙锁，用来避免误读。此外，如果要改动数据的话，还需要用行锁。这两样合在一起，就成了next-keylock。这样一来，数据库里的数据就像穿上了防护服，前后都能保持一致。

你往数据库里插入新数据时，如果存在唯一索引，MySQL就得看看这东西是不是重复了。这时候，要用到“当前读”功能。啊找到相同关键字了？还被互斥锁锁定着？那MySQL就给你加个S锁（也就是共享锁），耐心等待。这就好比你想去人多的游乐园，得等到别人玩完了才能进去。

2.插入操作的加锁过程：不仅仅是插入那么简单

把新的数据丢进数据库时，可不能随便乱塞。要是翻到的那条记录有唯一索引，那么MySQL马上给你上X锁，好比给你的路上设置个单行车道，这样你的车就能畅快通过，不用担心别人的车会撞上来。

但是，如果这个Key已经被设置了互斥锁的话，MySQL就得先挂个锁，等待另一方解除锁定才能进行操作。这其实就像是两辆车在十字路口撞到了，大家都动弹不得，直接卡死了。

3.唯一索引下的死锁：不仅仅是等待那么简单

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(5),
  `b` varchar(5),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`a`,`b`)
);

说起唯一索引里的唯一键冲突，就像是两辆车在十字路口相撞，每个人都想要抢着加锁，结果就是车没法动了。

想解决这个问题吗？那就让新数据先排排队，这样并发的时候就能避开死锁！这就好像我们事先规划好交通路线，确保车子都能顺畅通行，避免堵车一样。

4.提升批量插入效率：不仅仅是速度那么简单

想快点插数据？咱们可以在business和database两个角度下手。先说说business，把数据排个队，这样并发执行也不怕卡！

在调整MySQL时，咱们可以试试看把innodb_flush_log_at_trx_commit设成0，这样就能减少IO的压力，redolog的写入也就不会太勤快，全都交给后台去搞定。这就好比是优化路口的红绿灯，让车子跑得更快，等待的时间也会变短！

5.实验验证：不仅仅是理论那么简单

begin;
delete from t1 where a = '25'
commit;
begin;
INSERT ignore INTO `t1` (`a`, `b`) VALUES('25','1')
commit;

试试看新学会的插入加锁法，我们做个小游戏来看成果。先在电脑上用MySQL5.7开三个窗口，别忘了关掉自动提交功能。按照下面的步骤来，你会发现就像堵车一样，卡住了！这就是实践检验真理的过程~

6.隐式锁和锁升级：不仅仅是加锁那么简单

说到隐式锁，它是用INSERT命令的时候，不用担心重复问题。因为这种锁定叫“插入意向锁”，也就是说你正打算把东西插进去！

简单来说，你插入数据到锁里面，如果存在重复，后面的交易只会用最新的那个信息，而这个信息可能已经被占用了（就是没提交成功嘛）。这时候它就开始找S锁。

7.实际操作中的注意事项：不仅仅是理论那么简单

日常工作中得注意一些小窍门！比如给已经有索引的表格加新的内容，最好先按索引排序，就跟我们平时收拾东西一样。

搞定IO压力大的问题，简单！咱们调整下redolog的存储量，让它在后台默默工作就好。然后，把innodb_flush_log_at_trx_commit设置为0，也没啥影响。

8.总结：不仅仅是加锁那么简单

=====================================
2023-03-08 15:15:13 0x7fa4 INNODB MONITOR OUTPUT
=====================================
略
------------------------
LATEST DETECTED DEADLOCK ## 最新的死锁
------------------------
2023-03-08 15:10:12 0x7fa4 ## 这里显示了最近一次发生死锁的日期和时间
*** (1) TRANSACTION: ## 事务1
TRANSACTION 220709, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 16, OS thread handle 25992, query id 158597 localhost 127.0.0.1 root update
INSERT ignore INTO `user` (`id`,`username`, `age`, `birthday`, `sex`, `address`, `update_time`) VALUES (55,'小明', 5, '2019-09-17 19:12:37', '男', '杭州', '2022-09-01 16:03:25')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:  
## 解释：事务1在等X锁（主键索引space id 302 page no 3 n bits 88 index PRIMARY）
RECORD LOCKS space id 302 page no 3 n bits 88 index PRIMARY of table `mybatis_01`.`user` trx id 220709 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 19 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
略
*** (2) TRANSACTION: ## 事务1
TRANSACTION 220708, ACTIVE 25 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 7, OS thread handle 32676, query id 158584 localhost 127.0.0.1 root update
INSERT ignore INTO `user` (`id`,`username`, `age`, `birthday`, `sex`, `address`, `update_time`) VALUES (55,'小明', 5, '2019-09-17 19:12:37', '男', '杭州', '2022-09-01 16:03:25')
*** (2) HOLDS THE LOCK(S): 
## 解释：事务2持有S锁（主键索引space id 302 page no 3 n bits 88 index PRIMARY，跟上面事务1等的X锁为同一行，X锁与S锁互斥，因此事务1得等事务2释放行锁）
RECORD LOCKS space id 302 page no 3 n bits 88 index PRIMARY of table `mybatis_01`.`user` trx id 220708 lock mode S locks gap before rec
Record lock, heap no 19 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
略
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: 
## 解释：事务2在等X锁（主键索引space id 302 page no 3 n bits 88 index PRIMARY，跟上面事务1等的X锁为同一把锁，而事务1在等事务2持有的S锁释放，因此死锁）
RECORD LOCKS space id 302 page no 3 n bits 88 index PRIMARY of table `mybatis_01`.`user` trx id 220708 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 19 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
略
*** WE ROLL BACK TRANSACTION (2)
## 解释：根据事务回滚成本，选择事务2回滚，释放S锁
------------

咱们说了这么半天得出个结论来，那就是弄懂MySQL锁这事儿没那么容易！你看，光是INSERT锁就让人头大，再加上唯一索引下可能出现的死锁问题和大批量插入如何更顺利，这些都得咱们深入研究并精心设计。只要咱们用心点儿，就能让那个忙得团团转的MySQL跑得飞快，不堵车，效率还高！

末尾再问下大伙儿，你们有没碰到过上班时MySQL卡住死机的烦心事儿？咋解决的呀？来评论区跟我们说说呗！交流学习，一块儿进步！觉得这篇文章有点用处就点个赞，分享出去，让更多人受益！