网站建设实训经验总结,搜索引擎seo如何优化,济宁市建设工程质量监督站网站,怎么做网站美工来源 | 程序员历小冰责编 | Carol封图 | CSDN 付费下载于视觉中国疫情期间在家工作时#xff0c;同事使用了 insert into on duplicate key update 语句进行插入去重#xff0c;但是在测试过程中发生了死锁现象:ERROR 1213(40001): Deadlock found when trying to getlock;
t… 来源 | 程序员历小冰责编 | Carol封图 | CSDN 付费下载于视觉中国疫情期间在家工作时同事使用了 insert into on duplicate key update 语句进行插入去重但是在测试过程中发生了死锁现象:ERROR 1213(40001): Deadlock found when trying to getlock;
try restarting transaction
由于开发任务紧急只是暂时规避了一下但是对触发死锁的原因和相关原理不甚了解于是这几天一直在查阅相关资料总结出一个系列文章供大家参考本篇主要介绍 MySQL 加锁原理和锁的不同模式或类型的基本知识。后续会讲解常见语句的加锁情况和通过 MySQL 死锁日志分析死锁原因。表锁和行锁我们首先来了解一下表锁和行锁表锁是指对一整张表加锁一般是 DDL 处理时使用而行锁则是锁定某一行或者某几行或者行与行之间的间隙。表锁由 MySQL Server 实现行锁则是存储引擎实现不同的引擎实现的不同。在 MySQL 的常用引擎中 InnoDB 支持行锁而 MyISAM 则只能使用 MySQL Server 提供的表锁。1、表锁表锁由 MySQL Server 实现一般在执行 DDL 语句时会对整个表进行加锁比如说 ALTER TABLE 等操作。在执行 SQL 语句时也可以明确指定对某个表进行加锁。mysql lock table user read(write);# 分为读锁和写锁Query OK, 0 rows affected (0.00 sec)mysql select* from user where id 100; # 成功
mysql select* from role where id 100; # 失败未提前获取该 role的读表锁
mysql update user set name Tomwhere id 100; # 失败未提前获得user的写表锁mysql unlock tables; # 显示释放表锁
Query OK, 0 rows affected (0.00 sec)
表锁使用的是一次性锁技术也就是说在会话开始的地方使用 lock 命令将后续需要用到的表都加上锁在表释放前只能访问这些加锁的表不能访问其他表直到最后通过 unlock tables 释放所有表锁。除了使用 unlock tables 显示释放锁之外会话持有其他表锁时执行lock table 语句会释放会话之前持有的锁会话持有其他表锁时执行 start transaction 或者 begin 开启事务时也会释放之前持有的锁。2、行锁不同存储引擎的行锁实现不同后续没有特别说明则行锁特指 InnoDB 实现的行锁。在了解 InnoDB 的加锁原理前需要对其存储结构有一定的了解。InnoDB 是聚簇索引也就是 B树的叶节点既存储了主键索引也存储了数据行。而 InnoDB 的二级索引的叶节点存储的则是主键值所以通过二级索引查询数据时还需要拿对应的主键去聚簇索引中再次进行查询。关于 InnoDB 和 MyISAM 的索引的详细知识可以阅读《Mysql探索(一):BTree索引》一文。下面以两条 SQL 的执行为例讲解一下 InnoDB 对于单行数据的加锁原理。update user set age 10where id 49;
update user set age 10where name Tom;
第一条 SQL 使用主键索引来查询则只需要在 id 49 这个主键索引上加上写锁第二条 SQL 则使用二级索引来查询则首先在 name Tom 这个索引上加写锁然后由于使用 InnoDB 二级索引还需再次根据主键索引查询所以还需要在 id 49 这个主键索引上加写锁如上图所示。也就是说使用主键索引需要加一把锁使用二级索引需要在二级索引和主键索引上各加一把锁。根据索引对单行数据进行更新的加锁原理了解了那如果更新操作涉及多个行呢比如下面 SQL 的执行场景。update user set age 10where id 49;
上述 SQL 的执行过程如下图所示。MySQL Server 会根据 WHERE 条件读取第一条满足条件的记录然后 InnoDB 引擎会将第一条记录返回并加锁接着 MySQL Server 发起更新改行记录的 UPDATE 请求更新这条记录。一条记录操作完成再读取下一条记录直至没有匹配的记录为止。这种场景下的锁的释放较为复杂有多种的优化方式我对这块暂时还没有了解还请知道的小伙伴在下方留言解释。下面主要依次介绍 InnoDB 中锁的模式和类型锁的类型是指锁的粒度或者锁具体加在什么地方而锁模式描述的是锁的兼容性也就是加的是什么锁比如写锁或者读锁。内容基本来自于 MySQL 的技术文档 innodb-lock 一章感兴趣的同学可以直接去阅读原文原文地址为见文章末尾。行锁的模式锁的模式有读意向锁写意向锁读锁写锁和自增锁(auto_inc)下面我们依次来看。1、读写锁读锁又称共享锁Share locks简称 S 锁加了读锁的记录所有的事务都可以读取但是不能修改并且可同时有多个事务对记录加读锁。写锁又称排他锁Exclusive locks简称 X 锁或独占锁对记录加了排他锁之后只有拥有该锁的事务可以读取和修改其他事务都不可以读取和修改并且同一时间只能有一个事务加写锁。2、读写意向锁由于表锁和行锁虽然锁定范围不同但是会相互冲突。所以当你要加表锁时势必要先遍历该表的所有记录判断是否加有排他锁。这种遍历检查的方式显然是一种低效的方式MySQL 引入了意向锁来检测表锁和行锁的冲突。意向锁也是表级锁也可分为读意向锁IS 锁和写意向锁IX 锁。当事务要在记录上加上读锁或写锁时要首先在表上加上意向锁。这样判断表中是否有记录加锁就很简单了只要看下表上是否有意向锁就行了。意向锁之间是不会产生冲突的也不和 AUTO_INC 表锁冲突它只会阻塞表级读锁或表级写锁另外意向锁也不会和行锁冲突行锁只会和行锁冲突。3、自增锁AUTOINC 锁又叫自增锁一般简写成 AI 锁是一种表锁当表中有自增列AUTOINCREMENT时出现。当插入表中有自增列时数据库需要自动生成自增值它会先为该表加 AUTOINC 表锁阻塞其他事务的插入操作这样保证生成的自增值肯定是唯一的。AUTOINC 锁具有如下特点AUTO_INC 锁互不兼容也就是说同一张表同时只允许有一个自增锁自增值一旦分配了就会 1如果事务回滚自增值也不会减回去所以自增值可能会出现中断的情况。显然AUTOINC 表锁会导致并发插入的效率降低为了提高插入的并发性MySQL 从 5.1.22 版本开始引入了一种可选的轻量级锁mutex机制来代替 AUTOINC 锁可以通过参数 innodbautoinclockmode 来灵活控制分配自增值时的并发策略。具体可以参考 MySQL 的 AUTOINCREMENT Handling in InnoDB 一文链接在文末。3、不同模式锁的兼容矩阵下面是各个表锁之间的兼容矩阵。总结起来有下面几点意向锁之间互不冲突S 锁只和 S/IS 锁兼容和其他锁都冲突X 锁和其他所有锁都冲突AI 锁只和意向锁兼容行锁的类型根据锁的粒度可以把锁细分为表锁和行锁行锁根据场景的不同又可以进一步细分依次为 Next-Key LockGap Lock 间隙锁Record Lock 记录锁和插入意向 GAP 锁。不同的锁锁定的位置是不同的比如说记录锁只锁住对应的记录而间隙锁锁住记录和记录之间的间隔Next-Key Lock 则所属记录和记录之前的间隙。不同类型锁的锁定范围大致如下图所示。下面我们来依次了解一下不同的类型的锁。1、记录锁记录锁是最简单的行锁并没有什么好说的。上边描述 InnoDB 加锁原理中的锁就是记录锁只锁住 id 49 或者 name Tom 这一条记录。当 SQL 语句无法使用索引时会进行全表扫描这个时候 MySQL 会给整张表的所有数据行加记录锁再由 MySQL Server 层进行过滤。但是在 MySQL Server 层进行过滤的时候如果发现不满足 WHERE 条件会释放对应记录的锁。这样做保证了最后只会持有满足条件记录上的锁但是每条记录的加锁操作还是不能省略的。所以更新操作必须要根据索引进行操作没有索引时不仅会消耗大量的锁资源增加数据库的开销还会极大的降低了数据库的并发性能。2、间隙锁还是最开始更新用户年龄的例子如果 id 49 这条记录不存在这个 SQL 语句还会加锁吗答案是可能有这取决于数据库的隔离级别。这种情况下在 RC 隔离级别不会加任何锁在 RR 隔离级别会在 id 49 前后两个索引之间加上间隙锁。间隙锁是一种加在两个索引之间的锁或者加在第一个索引之前或最后一个索引之后的间隙。这个间隙可以跨一个索引记录多个索引记录甚至是空的。使用间隙锁可以防止其他事务在这个范围内插入或修改记录保证两次读取这个范围内的记录不会变从而不会出现幻读现象。值得注意的是间隙锁和间隙锁之间是互不冲突的间隙锁唯一的作用就是为了防止其他事务的插入所以加间隙 S 锁和加间隙 X 锁没有任何区别。3、Next-Key 锁Next-key锁是记录锁和间隙锁的组合它指的是加在某条记录以及这条记录前面间隙上的锁。假设一个索引包含 15、18、20 304950 这几个值可能的 Next-key 锁如下(-∞, 15](15, 18](18, 20](20, 30](30, 49](49, 50](50, ∞)
通常我们都用这种左开右闭区间来表示 Next-key 锁其中圆括号表示不包含该记录方括号表示包含该记录。前面四个都是 Next-key 锁最后一个为间隙锁。和间隙锁一样在 RC 隔离级别下没有 Next-key 锁只有 RR 隔离级别才有。还是之前的例子如果 id 不是主键而是二级索引且不是唯一索引那么这个 SQL 在 RR 隔离级别下就会加如下的 Next-key 锁 (30, 49](49, 50)此时如果插入一条 id 31 的记录将会阻塞住。之所以要把 id 49 前后的间隙都锁住仍然是为了解决幻读问题因为 id 是非唯一索引所以 id 49 可能会有多条记录为了防止再插入一条 id 49 的记录。4、插入意向锁插入意向锁是一种特殊的间隙锁简写成 II GAP表示插入的意向只有在 INSERT 的时候才会有这个锁。注意这个锁虽然也叫意向锁但是和上面介绍的表级意向锁是两个完全不同的概念不要搞混了。插入意向锁和插入意向锁之间互不冲突所以可以在同一个间隙中有多个事务同时插入不同索引的记录。譬如在上面的例子中id 30 和 id 49 之间如果有两个事务要同时分别插入 id 32 和 id 33 是没问题的虽然两个事务都会在 id 30 和 id 50 之间加上插入意向锁但是不会冲突。插入意向锁只会和间隙锁或 Next-key 锁冲突正如上面所说间隙锁唯一的作用就是防止其他事务插入记录造成幻读正是由于在执行 INSERT 语句时需要加插入意向锁而插入意向锁和间隙锁冲突从而阻止了插入操作的执行。5、不同类型锁的兼容矩阵不同类型锁的兼容下如下图所示。其中第一行表示已有的锁第一列表示要加的锁。插入意向锁较为特殊所以我们先对插入意向锁做个总结如下插入意向锁不影响其他事务加其他任何锁。也就是说一个事务已经获取了插入意向锁对其他事务是没有任何影响的插入意向锁与间隙锁和 Next-key 锁冲突。也就是说一个事务想要获取插入意向锁如果有其他事务已经加了间隙锁或 Next-key 锁则会阻塞。其他类型的锁的规则较为简单间隙锁不和其他锁不包括插入意向锁冲突记录锁和记录锁冲突Next-key 锁和 Next-key 锁冲突记录锁和 Next-key 锁冲突 推荐阅读蚂蚁金服高要求的领域建模能力对研发来说到底指什么Redis 6.0 新特性多线程连环 13 问为你整理了一份 Mysql 的学习笔记建议收藏学习阿里技术专家告诉你如何画出优秀的架构图我画了35张图就是为了让你深入 AQS中国 App 出海“变形记”加拿大API平台如何做到30%为中国明星项目创业老兵这样说……真香朕在看了
