一则由于索引导致的MySQL死锁分析

涉及死锁的 authorized_user 表的 DDL

CREATE TABLE `authorized_user` (
  `id`          INT(11) UNSIGNED NOT NULL AUTO_INCREMENT
  COMMENT '自增id',
  `username`    VARCHAR(50)      NOT NULL
  COMMENT '用户名',
  `password`    VARCHAR(200)     NOT NULL
  COMMENT '加密后的密码',
  `description` VARCHAR(500)     NOT NULL DEFAULT ''
  COMMENT '描述',
  `status`      TINYINT(4)       NOT NULL DEFAULT 0
  COMMENT '状态,0:已注册,1:已激活,3:已锁定,4:已注销',
  `phone_no`    VARCHAR(15)      NOT NULL DEFAULT ''
  COMMENT '手机号',
  `email`       VARCHAR(75)      NOT NULL DEFAULT ''
  COMMENT '电子邮件',
  `create_time` TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '创建时间',
  `update_time` TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP
  ON UPDATE CURRENT_TIMESTAMP
  COMMENT '最近修改时间',
  PRIMARY KEY (`id`),
  KEY `index_username` (`username`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COMMENT = '用户表';

死锁日志

2016-11-27 15:57:13 7fe166a92700InnoDB: transactions deadlock detected, dumping detailed information.
2016-11-27 15:57:13 7fe166a92700
*** (1) TRANSACTION:
TRANSACTION 80660631141, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 2936, 8 row lock(s)
MySQL thread id 126366554, OS thread handle 0x7fe18143e700, query id 2011631849 172.18.3.148 acc Searching rows for update
UPDATE authorized_user SET status = 1 WHERE username = 'wcy'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 669 page no 937 n bits 136 index `PRIMARY` of table `test`.`authorized_user` trx table locks 1 total table locks 2  trx id 80660631141 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0 
*** (2) TRANSACTION:
TRANSACTION 80660631143, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4999
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 126366547, OS thread handle 0x7fe166a92700, query id 2011631851 172.18.3.154 acc updating
UPDATE authorized_user SET username = 'wcy100' WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 669 page no 937 n bits 136 index `PRIMARY` of table `test`.`authorized_user` trx table locks 1 total table locks 2  trx id 80660631143 lock_mode X locks rec but not gap lock hold time 0 wait time before grant 0 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 669 page no 13359 n bits 1176 index `index_username` of table `test`.`authorized_user` trx table locks 1 total table locks 2  trx id 80660631143 lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant 0 
*** WE ROLL BACK TRANSACTION (2)

根据 MySQL 日志分析出来的涉及死锁的 SQL 语句分析

mysql> explain UPDATE authorized_user SET status = 1 WHERE username = 'wcy' \G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: authorized_user
   partitions: NULL
         type: range
possible_keys: index_username
          key: index_username
      key_len: 152
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set (0.00 sec)
mysql> explain UPDATE authorized_user SET username = 'wcy100' WHERE id = 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: authorized_user
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set (0.00 sec)

原因分析

InnoDB 引擎,更新操作默认会加行级锁,行级锁会对索引加锁。如果更新语句使用多个索引,行锁会先锁非聚簇索引,再锁聚簇索引。如果两个事务中的 SQL 用到了不同的非聚簇索引或者一个用了一个没有使用(即使用索引的情况不同),这样的话就会导致这两个事务加行锁的顺序不一致,形成了多个事务之间资源(行锁)的循环等待,构成了死锁的四个必要条件之一,而其他3个条件(互斥、请求与保持、不剥夺)已经满足,所以最终导致了死锁。

具体分析(以死锁日志1为例)

由日志分析可得:

事务1用到的 SQL 语句:
UPDATE authorized_user SET status = 1 WHERE username = 'wcy'
事务1获取的锁:X lock on (669,13359,1176) index_username
事务1等待的锁:X lock on (669,937,136) PRIMARY

事务2用到的 SQL 语句:
UPDATE authorized_user SET username = 'wcy100' WHERE id = 1
事务2获取的锁:X lock on (669,937,136) PRIMARY
事务2等待的锁:X lock on (669,13359,1176) index_username

导致这种情况的 SQL 执行流程说明:

事务1的 update 语句正常的执行步骤如下:
1.由于用到了非聚簇索引,首先需要获取 index_username 上的行级锁。
2.紧接着根据主键进行更新,所以需要获取 PRIMAEY 上的行级锁。
3.更新完毕后,提交,并释放所有锁。

但是,如果在步骤1和2之间突然插入事务2的这条 SQL 语句:
UPDATE authorized_user SET username = 'wcy100' WHERE id = 1, 这条语句会先锁住聚簇索引,然后由于其更新的字段 username 有非聚簇索引,所以这条语句需要锁住 index_username。

这时候的情况变成了:事务1获取了 index_username 上的锁,等待 PRIMARY 上的锁;事务2获取了 PRIMARY 上的锁,等待 index_username 上的锁,这样就出现了死锁。

解决方案

可以将原先的单条批量 update 语句分拆成如下步骤(此处以事务1的 SQL 语句为例):

1.先获取需要更新的记录的主键

select id from authorized_user WHERE username = 'wcy'

2.开启一个事务,逐条更新

for (Integer id : ids) {    
    updateStatusById(id,1);
}

这样每一次更新操作都是针对单条记录先获取 PRIMARY 上的锁,再获取 index_username 上的锁,避免了由于在获取行锁的时候,锁索引的顺序不一致造成的死锁。

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 155,958评论 4 358
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 66,179评论 1 286
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 105,792评论 0 237
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 43,409评论 0 202
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 51,723评论 3 285
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 40,174评论 1 204
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,543评论 2 306
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 30,270评论 0 194
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 33,899评论 1 236
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,235评论 2 240
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 31,771评论 1 255
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 28,139评论 2 249
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 32,688评论 3 228
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 25,936评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,646评论 0 192
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 35,194评论 2 265
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 35,145评论 2 258

推荐阅读更多精彩内容

  • 当一个系统访问量上来的时候,不只是数据库性能瓶颈问题了,数据库数据安全也会浮现,这时候合理使用数据库锁机制就显得异...
    初来的雨天阅读 3,497评论 0 22
  • 1背景1 1.1MVCC:Snapshot Read vs Current Read2 1.2Cluster In...
    简小鹿奔跑ing阅读 4,106评论 1 50
  • MySQL 加锁处理分析 转载2013年12月13日 16:43:55 7598 原文地址:http://hede...
    初来的雨天阅读 424评论 0 2
  • MySQL 的加锁处理分析 MySQL/InnoDB的加锁分析,一直是一个比较困难的话题。我在工作过程中,经常会有...
    meng_philip123阅读 785评论 0 12
  • 最近,常常做梦,梦里有他,有她,有他们。欣喜若狂的梦里行走自己的另一个故事,那种感觉真好。说不出,道不明。每...
    我是葉子阅读 736评论 4 1