河源市东源县建设局网站,曲麻菜公司网站建设,国外优秀网站建设,dw网站模板免费本篇主要介绍MySQL跟加锁相关的一些概念、MySQL执行插入Insert时的加锁过程、唯一索引下批量插入可能导致的死锁情况#xff0c;以及分别从业务角度和MySQL配置角度介绍提升批量插入的效率的方法#xff1b;MySQL跟加锁相关的一些概念在介绍MySQL执行插入的加锁过程之前…本篇主要介绍MySQL跟加锁相关的一些概念、MySQL执行插入Insert时的加锁过程、唯一索引下批量插入可能导致的死锁情况以及分别从业务角度和MySQL配置角度介绍提升批量插入的效率的方法MySQL跟加锁相关的一些概念在介绍MySQL执行插入的加锁过程之前先复习下几种跟锁相关的概念快照读InnoDB 利用了“所有数据都有多个版本”的这个特性实现了“秒级创建快照”的能力快照是整库级别数据版本与事务ID一一对应各个数据版本通过当前数据与期间的undo log作用得到判断数据是否可见的规则——对于当前事务如果一个数据版本是在其启动之前生成的就认如果是我启动以后才生成的就不认我必须要找到它的上一个版本如果是这个事务自己更新的数据它自己还是要认的一致性视图生成的时机即视图数组确定的时机并非事务的begin命令而是事务内的第一个select语句trx_id生成的时机是事务开启后的第一个当前读DML语句因此可能存在trx_id小于当前事务A但未提交的事务A_pre和trx_id大于当前事务A但未提交的事务A_behind对于innoDB默认的可重复读级别下查询只承认在一致性视图创建前就已经提交完成的数据对于读提交查询只承认在语句执行前就已经提交完成的数据不管哪个事务隔离级别写操作都是当前读当前读总是读取已经提交完成的最新版本当前读新数据都是先读后写的而这个读只能读当前最新的值称为“当前读”currentread行锁是在事务执行更新语句时加上的要等到事务结束时才释放除了update 之类的写操作语句外select语句也可以加行锁也是当前读 select ... lock in share mode 对数据行加读锁S锁共享锁 select ... for update对数据行加写锁X锁排他锁S锁S锁英文为Shared Lock共享锁也称之为读锁即Read LockS锁之间是共享的或者说是互不阻塞的执行select ... lock in share mode读取一条记录时需要先获取该记录的S锁S锁规则——事务T1对记录R1加上了S锁那么当前事务T1可以读取R1这一行记录但是不能修改R1其他事务T2可以继续对R1添加S锁但是不能添加X锁只有当R1上面的S锁释放了才能加上X锁X锁X锁英文为Exclusive Lock排他锁也称之为写锁即Write Lock如同它的名字X锁是具有排他性的即一个写锁会阻塞其他的X锁和S锁当事务需要修改一条记录时或执行 select ... for update需要先获取该记录的X锁当然更新数据之前肯定是需要读到最新数据的因此获取X锁不影响当前事务读取最新数据幻读事务执行更新的期间若允许插入了新的数据导致事更新到了新插入的数据相当于发生了幻读——事务前后看到的数据不一样当前读读到了多出来的一些数据幻读破坏了加锁的语义——满足更新语句条件的记录在事务中途增加了没锁住此外幻读影响了binlog与主库数据的一致性——binlog在事务commit时生成若事务A先执行更新同时事务B执行插入后commit然后A再提交则会导致从库执行binlog时更新到了预期之外的行导致主从数据不一致间隙锁gap lockgap lock锁的是插入行为gap lock锁之间其实并不互斥为了解决幻读问题InnoDB引入间隙锁 (Gap Lock)锁的就是两个值之间的空隙可以是主键也可以是二级索引next-key lock为解决幻读问题引入了间隙锁再加上更新数据时的行锁合在一起称为next-key lock把间隙锁记为开区间因此每个next-key lock是前开后闭区间间隙锁和next-key lock的引入解决了幻读的问题但同时在并发情况下可能导致死锁发生原因是间隙锁获取不冲突导致多个线程都持有同一个间隙锁但是执行插入时会冲突插入意向锁插入意向锁准确的说它不是锁而是插入行为插入意向锁与间隙锁gap lock冲突配合上面的间隙锁gap lock一起防止了幻读如果插入的记录有唯一索引还会进行Duplicate Key判断因此需要当前读如果存在相同Key且该Key被加了互斥锁则会加S锁共享锁然后等待因为这个相同的Key之后有可能会回滚删除这里非常容易死锁直到等到成功插入后会在这条记录上加X锁排他锁最后在当前事务提交/回滚时释放X锁MySQL执行插入Insert时的加锁过程MySQL官方文档对Insert加锁情况的描述INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.解读1. INSERT操作会对新插入的记录加行锁写锁排它锁X锁注意区别于执行update执行insert时这里加的是X锁并非next-key lock因此不会阻塞其他的事务对gap区间的插入操作2. 在插入记录前会向插入记录所在位置申请意向插入意向锁Insertion Intention Gap Lock只要不是插入的是同一行记录多个事务对相同gap区间的插入操作不会冲突3. 对于唯一索引发生唯一键冲突时当前事务会先尝试在这条记录上加读锁S锁加S锁的机制可能会导致死锁即A线程占用记录的X锁B、C阻塞它们需要先获取S锁再获取X锁执行更新当A释放X锁时B、C同时拿到S锁共享锁但互相都无法继续获取X锁S锁与X锁互斥导致死锁下面通过几个case来验证和说明上面的insert的加锁过程简化的表结构如下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)
);case1并发事务先后插入同一行在记录不存在的情况下两个同样顺序的批量insert同时执行第二个会进行锁等待状态现象如下解释insert加的是插入意向锁它是隐式锁隐式锁的意思就是没有锁但是可能因为一些触发规则被升级成行锁在t1插入记录时是不加锁的这个时候事务t1还未提交的情况下事务t2尝试插入的时候发现有这条记录根据官方文档描述t2会尝试获取S锁它会判定记录上的事务id是否活跃存在正在执行还未提交的事务如果活跃的话说明正在修改记录的事务未结束会帮t1把它的隐式锁提升为显式锁X锁因此t1持有X锁而t2尝试获得S锁X锁与S锁冲突t2阻塞case2批量插入顺序不一致的导致的死锁根据case1的结论当多个事务线程批量插入数据的插入顺序不一致时会导致死锁现象及解释如下解决方法在业务侧对插入的数据做好排序保证并发执行时不会产生死锁case3delete提交但未purge导致的并发insert死锁关于purge的相关知识可参考我的MySQL——关于删除/purge/删除加锁/删除大量数据/truncatedelete这篇文章begin;
delete from t1 where a 25
commit;begin;
INSERT ignore INTO t1 (a, b) VALUES(25,1)
commit;这块代码在多个线程同时调用的时候非常容易死锁注意这块代码里面有2个事务解释执行delete事务提交成功若还没来得及 purge被执行purge的对象包括UNIQUE KEY(a,b)这时后面的事务执行这条记录的insert时会先尝试对这条记录加S锁并发情况下多个insert事务获取S锁成功但是在执行含唯一索引insert时需要获取X锁此时并发的线程会出现——同时拿到S锁共享锁但互相都无法继续获取X锁S锁与X锁互斥导致死锁case43个 insert ignore一个回滚造成的死锁这个case就跟官方文档中表述的一致——If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.当互斥锁释放时并发的insert线程会由于先尝试获取的S锁而导致后序获取X锁时产生死锁实验如下本地MySQL 5.7版本开3个链接窗口关闭自动提交按照如下顺序操作结果如预期一样出现死锁通过 SHOW ENGINE INNODB STATUS; 命令查看日志如下
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死锁日志、MySQL死锁日志的查看和分析MySQL insert加锁流程重要1. 首先对插入的间隙加插入意向锁Insert Intension Locks注意插入意向锁可以理解为只是个插入动作并非真的加锁它只跟GAP锁间隙锁锁的插入区间冲突如果该间隙已被加上了GAP锁间隙锁或 Next-Key 锁含GAP 锁则加锁失败等待间隙释放如果没有则加插入意向锁成功表示可以插入2. [可选 唯一键]然后判断插入记录是否有唯一键如果有则需要进行唯一性约束检查如果不存在相同键值则准备插入如果存在相同键值则判断该键值是否加锁如果没有锁 判断该记录是否被标记为删除如果标记为删除说明删除事务已经提交数据页上的记录还没来得及 purge这时尝试加S锁如果没有标记删除说明数据已存在本次插入会导致唯一键冲突则报 1062 duplicate key 错误如果有锁说明该记录正在被处理正在被其他事务新增、删除或更新且其他事务还未提交则当前事务尝试加S锁后会进入阻塞等待其他事务释放记录X锁3. 插入记录并对记录加X锁当前事务提交或回滚时释放X锁 insert加锁的小结关于隐式锁一般使用自增主键且不含唯一键的insert语句并发时不会有冲突因为插入意向锁是隐式锁实际代表的是一个准备插入的行为这个行为只和间隙锁GAP锁冲突关于锁升级当插入的数据含唯一键出现并发插入时如事务A和事务B事务A先执行insert但还未提交事务后执行插入的事务B会使用当前读如果发现这条记录已存在还未提交则会先尝试获取S锁此时事务A的插入意向锁隐式锁会被提升为显式锁 X 锁导致事务B阻塞关于插入死锁正是MySQL这种唯一键冲突时的insert插入获取锁的规则导致了在[case2: 并发插入事务中的顺序顺序不一致]、[case3: 删除提交但还未purge]、或[case4: 并发插入事务中单条回滚]的case下会出现并行的事务先拿到S锁共享锁但在执行insert加X锁时发现阻塞构成死锁这个问题在MySQL官方文档也提到了建议针对case2保证各个事务线程执行插入时插入数据的顺序相同避免死锁针对case3和case4保证各个事务线程执行含唯一键的数据插入前先对数据加X锁即跳过insert加锁机制的先尝试获取S锁这一阶段如select ... for update如果查到了则跳过插入有哪些提升MySQL插入性能的方法今天有个同事问我有个业务场景需要提升MySQL插入的性能问我有哪些思路我的第一反应是大部分的互联网场景都是读多写少很少见写入密集型的业务场景高并发写本就不适合用MySQL大量的写操作会产生大量日志消耗IO与带宽资源同时会一定程度影响查询性能此外在读写分离的架构下会导致主从延迟升高从库难以追上主库导致一些查询场景查到的数据一直是是旧的引起业务问题不过还是从MySQL的角度提供了几点优化思路1. 使用批量插入替代逐条插入批量插入意味着多条插入语句放入了一个事务里而单条插入时每条语句都会生成一条事务优点是通过合并批量插入语句到单个事务可以减少创建事务的消耗缺点也很明显这个事务的会随着批次bitchSize的增加变成一个长事务会导致较大的回滚成本以及主从延迟增加因此需要权衡这个bitchSize的大小建议不要超过1002. 修改SQL语句的写法将事务内多条insert values语句合并起来如下INSERT INTO t (datetime, uid, content, type) VALUES (0, userid_0, content_0, 0);
INSERT INTO t (datetime, uid, content, type) VALUES (1, userid_1, content_1, 1); 改为INSERT INTO t (datetime, uid, content, type) VALUES(0, userid_0, content_0, 0),(1, userid_1, content_1, 1); 这种方法优化的效果理论上还是有的执行SQL产生的binlog大小减少了降低日志刷盘的数据量和频率并且通过合并SQL语句减小了SQL语句的传输大小减少网络传输的IO同时也能减少SQL语句解析的次数3. 尽量保证数据有序插入如果插入的表上存在索引可以在插入前尽量让插入的数据按索引排序如在业务代码内做一下手动排序如果有多个索引则选择索引树较大的那个索引对齐字段做排序在InnoDB中表都是根据主键顺序以索引的形式存放的这种存储方式的表称为索引组织表每一个索引在InnoDB里面对应一棵B树所有叶子节点构成一个有序链表也就是说是有序的数据库插入时需要维护索引数据而无序的记录会增大维护索引的成本试想如果每次插入记录都在索引的最后面索引的定位效率很高并且对索引调整较小如果插入的记录在索引中间插入记录的索引定位效率会下降并且数据插到叶子节点时可能引起页分裂/页合并数据量较大时会有频繁的IO操作会消耗比较多CPU和IO资源4. 修改innodb_flush_log_at_trx_commit参数InnoDB提供了innodb_flush_log_at_trx_commit参数来控制redolog的写入策略设置为0表示每次事务提交时都只是把redolog留在redolog buffer中设置为1表示每次事务提交时都将redolog直接持久化到磁盘设置为2表示每次事务提交时都只是把redolog写到page cacheInnoDB有一个后台线程默认每隔1秒就会把redolog buffer中的日志调用write写到文件系统的page cache然后调用fsync持久化到磁盘为了缓解IO压力可以让redolog的落盘不要那么频繁完全依赖后台线程则可以将innodb_flush_log_at_trx_commit修改为0缺点也很明显但Sever断电时有丢失数据风险会丢失这个落盘任务执行间隔之间变更的数据一般作为业务侧是用不到这个方法的也是不推荐的这里提一下问题本身一个处理流程中包含一次insert和一次RPC调用而insert的性能瓶颈成为了这个流程执行效率的瓶颈同事一开始的思路是优化插入操作的性能但在我询问了这条insert与RPC是否有数据强一致性关联时他捋了捋发现并没有所以解决问题的方案很简单——将insert从处理流程中去掉就行了是不是很简单提前用任务插入或者流程中异步插入控制插入执行的速率如通过MQ哪怕真的有数据强一致性关联也应该使用其他写性能很高的存储引擎如redis所以解决问题的思路要视野宏观一段切勿一叶障目不过也借此机会梳理了下MySQL插入相关的知识点本篇参考我的文章——幻读/间隙锁/Next-Key LockS锁与X锁 / 当前读与快照读读MySQL源码再看INSERT加锁流程MySQL插入死锁套路MySQL 死锁套路一次诡异的批量插入死锁问题分析修改MySQL配置来提高mysql插入速度的方法MySQL批量SQL插入性能优化解决死锁之路 - 常见 SQL 语句的加锁分析
