阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL 加锁和死锁解析

207次阅读
没有评论

共计 2324 个字符,预计需要花费 6 分钟才能阅读完成。

产生死锁的必要条件

  • 多个并发事务(2 个或者以上)
  • 每个事物都持有了锁(或者是已经在等待锁)
  • 每个事务都需要再继续持有锁(为了完成事务逻辑,还必须更新更多的行)
  • 事物之间产生加锁的循环等待,形成死锁

    常规锁模式

  • LOCK_S(读锁,共享锁)
  • LOCK_X(写锁,排它锁)

    锁的属性

  • LOCK _REC_NOT_GAP(锁记录)
  • LOCK_GAP(锁记录前的 GAP)
  • LOCK_ORDINARY(同时锁记录 + 记录前的 GAP,Next key 锁)
  • LOCK_INSERT_INTETION(插入意向锁)

    锁组合(属性 + 模式)

    可以任意组合

    锁冲突矩阵

    MySQL 加锁和死锁解析

锁是加在那里的?

  • 根据主键查找 - 锁加在主键上
    如 begin;select * from tt_copy where id=4 for update;
    加锁情况

    index PRIMARY of table test.tt_copy trx id 1101588 lock_mode X locks rec but not gap

  • 根据普通索引查找 - 锁加在普通索引和主键上
    如 begin;select * from tt_copy force index(idx_a) where a=4 for update;
    加锁情况

    index idx_a of table test.tt_copy trx id 1101590 lock_mode X locks rec but not gap
    index PRIMARY of table test.tt_copy trx id 1101590 lock_mode X locks rec but not gap

操作与加锁的对照关系

以下没特殊说明都为 RC 隔离级别

Insert

  • 无 Unique key,插入后:无论 RC 或 RR 隔离级别都是对主键加 LOCK_X+LOCK_REC_NOT_GAP
  • 有 Unique key

    插入前,唯一约束检查:LOCK_S+LOCK_ORDINARY
    插入前,插入的位置有 GAP 锁:LOCK_INSERT_INTETION
    插入后,新数据插入:LOCK_X+LOCK_REC_NOT_GAP

Delete

满足删除条件的所有记录:LOCK_X+LOCK_REC_NOT_GAP

Update

Update 操作分解

  • Step 1:定位到 下一条 满足查询条件的记录(查询过程,类似于 Select/Delete)
  • Step 2:删除当前定位到的记录(标记为删除状态)
  • Step 3:拼装更新后项,根据更新后项定位到 新的插入位置
  • Step 4:在新的插入位置,判断是否存在 Unique 冲突(存在 Unique Key 时
  • Step 5:插入更新后项(不存在 Unique 冲突时)
  • Step 6:重复 Step 1 到 Step 5 的操作,直至扫描完整个查询范围

Update 操作分析

  • Step 1,Step 2:Delete
  • Step 3,Step 4,Step 5:Insert

Update

  • 无 Unique key:

    • 查询范围中的所有记录,LOCK_X + LOCK_REC_NOT_GAP
  • 有 Unique key:

    • 查找满足条件的记录:查询范围内的所有记录,LOCK_X + LOCK_REC_NOT_GAP
    • 更新后项存在唯一性冲突:冲突项上的加锁,LOCK_S + LOCK_ORDINARY
    • 更新后项不存在唯一性冲突:更新位置后项加锁,LOCK_S + LOCK_GAP(省略)
    • 实际更新操作:可看做插入了一条新纪录,LOCK_X + LOCK_REC_NOT_GAP

GAP 锁

那些操作会加 GAP 锁?

  • Read Committed(RC)):Unique Key 唯一约束检查;Purge 操作;
  • Repeatable Read(RC):RC 的基础上,所有需要加锁的索引范围扫描和索引查找(Update/Delete…)
  • 还有一种会加 GAP 锁:RR 隔离级别下,对有唯一索引的表执行 insert on duplicate update 操作,除了会对新插入的记录加 x not gap 外,还会对相邻记录加 x gap

如何去掉 GAP 锁?

change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated)

什么时候加 next-key lock?

By default, InnoDB operates in REPEATABLE READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows

Insert Intention Lock

An insert intention lock is a type of gap lock set by INSERT operations prior to(在 … 之前)row insertion.

总结

• 原则之一

  • 要分析一个死锁,必须深入业务,了解整个事务的逻辑(闭门无法造车)

• 原则之二`

  • GAP 锁很复杂,为了减少 GAP 锁,减少 GAP 导致的死锁,尽量选择 Read Committed 隔离级别(RC + row based binlog,基本上能够解决所有问题,无需使用 Repeatable Read)
  • 适当的 减少 Unique 索引,能够减少 GAP 锁导致的死锁(根据业务情况而定)

• 原则之三

  • 在 MySQL 中,以不同索引的过滤条件,来操作相同的记录(Update/Delete),很容易产生死
    锁。

• 原则之四

  • RC 隔离级别下,如果死锁中出现 Next Key(Gap 锁),说明表中一定存在 unique 索引
  • 多语句事务产生的死锁,确保每条语句操作记录的顺序性,能够极大减少死锁

本文大多数都整理自《死锁 - 何登成 – 管中窥豹——MySQL(InnoDB)死锁分析之道》

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计2324字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中