mysql 死锁分析

Posted on By 投稿用户 技术

表结构：

CREATE TABLE `se_exclude_keyword` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`se_code` VARCHAR(200) NOT NULL COMMENT 'SE编码' COLLATE 'utf8_bin',
`se_account_id` BIGINT(20) NOT NULL COMMENT 'SE账户ID',
`campaign_id` BIGINT(20) NULL DEFAULT NULL COMMENT '活动主键ID',
`adgroup_id` BIGINT(20) NULL DEFAULT NULL COMMENT '广告组主键ID',
`keyword_name` VARCHAR(300) NULL DEFAULT NULL COMMENT '关键词' COLLATE 'utf8_bin',
`exclude_type` BIGINT(20) NULL DEFAULT NULL COMMENT '否定类型：0非精确,1广泛、2短语、3精确',
`exclude_range` VARCHAR(200) NULL DEFAULT NULL COMMENT '否定范围： campaign 活动、adgroup 单元' COLLATE 'utf8_bin',
`create_time` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` DATETIME NULL DEFAULT NULL COMMENT '更新时间',
`create_user` VARCHAR(300) NULL DEFAULT NULL COMMENT '创建人' COLLATE 'utf8_bin',
`update_user` VARCHAR(300) NULL DEFAULT NULL COMMENT '更新人' COLLATE 'utf8_bin',
`del` INT(11) NULL DEFAULT NULL COMMENT '逻辑删除标记 1 已删除 0 正常',
`operate_code` BIGINT(20) NOT NULL COMMENT '操作类型:   1 update 2 create 3 delete',
`submited` INT(11) NULL DEFAULT NULL COMMENT '操作是否提交 1 是 0 否',
`project_id` BIGINT(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `idx_sek_se_account_id` (`se_account_id`),
INDEX `idx_sek_campaign_id` (`campaign_id`),
INDEX `idx_sek_project_id` (`project_id`),
INDEX `idx_sek_adgroup_id` (`adgroup_id`)
)
COMMENT=' 活动、单元的否定关键词；同一条记录活动ID和广告组ID不能同时存在'
COLLATE='utf8_bin'
ENGINE=InnoDB

出现死锁的情况应该属于以下情况：
相同索引键来访问即使是不同行记录，出现锁冲突。

————————
LATEST DETECTED DEADLOCK
————————
2014-04-25 06:30:06 7efd9f7af700
*** (1) TRANSACTION:
TRANSACTION 659049795, ACTIVE 0 sec starting index read
mysql tables in use 4, locked 4
LOCK WAIT 5 lock struct(s), heap size 1248, 4 row lock(s)
MySQL thread id 887239, OS thread handle 0x7efd9fc41700, query id 749922319 192.168.4.195 ttt Sending data
delete sek from se_exclude_keyword sek where se_code = ‘baidu’ and se_account_id = 1343 and project_id = 277 and exists (select 1 from se_campaign where id = sek.campaign_id and se_campaign_id=14726849) and sek.exclude_range = ‘campaign’ and sek.submited = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 348 page no 7849 n bits 280 index `idx_sek_project_id` of table `sem3_prd_new`.`se_exclude_keyword` trx id 659049795 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000000000000115; asc ;;
1: len 8; hex 8000000000001018; asc ;;

*** (2) TRANSACTION:
TRANSACTION 659049793, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 2043
mysql tables in use 4, locked 4
147 lock struct(s), heap size 31160, 15940 row lock(s)
MySQL thread id 887225, OS thread handle 0x7efd9f7af700, query id 749922318 192.168.4.195 ttt Sending data
delete sek from se_exclude_keyword sek where se_code = ‘baidu’ and se_account_id = 20463 and project_id = 277 and exists (select 1 from se_campaign where id = sek.campaign_id and se_campaign_id=13824923) and sek.exclude_range = ‘campaign’ and sek.submited = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 348 page no 7849 n bits 280 index `idx_sek_project_id` of table `sem3_prd_new`.`se_exclude_keyword` trx id 659049793 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000000000000115; asc ;;
1: len 8; hex 8000000000001018; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 348 page no 3156 n bits 192 index `PRIMARY` of table `se_exclude_keyword` trx id 659049793 lock_mode X locks rec but not gap waiting
Record lock, heap no 39 PHYSICAL RECORD: n_fields 18; compact format; info bits 0
0: len 8; hex 800000000002b69a; asc ;;
1: len 6; hex 0000256ba525; asc %k %;;
2: len 7; hex ad000002f80110; asc ;;
3: len 5; hex 6261696475; asc baidu;;
4: len 8; hex 800000000000053f; asc ?;;
5: len 8; hex 8000000000ce1d63; asc c;;
6: SQL NULL;
7: len 6; hex e9a291e98193; asc ;;
8: len 8; hex 8000000000000000; asc ;;
9: len 8; hex 63616d706169676e; asc campaign;;
10: len 8; hex 800012514ec325ee; asc QN % ;;
11: len 8; hex 800012514ec325ee; asc QN % ;;
12: len 5; hex 5345415049; asc SEAPI;;
13: len 5; hex 5345415049; asc SEAPI;;
14: len 4; hex 80000000; asc ;;
15: len 8; hex 8000000000000000; asc ;;
16: len 4; hex 80000001; asc ;;
17: len 8; hex 8000000000000115; asc ;;

*** WE ROLL BACK TRANSACTION (1)

 

Innodb的行锁是根据检索的索引项进行加锁（注意不要理解成对索引加锁），具体的说是对所有根据索引检索到的记录进行加锁（由于Innodb使用主键为聚簇索引的方式，也可以理解为对主键进行加锁，Innodb总会有聚簇索引的，即使没有也会隐式生成一个），这样就决定了Innodb的行锁会有下面一些性质：

  • 只有通过索引检索，才能使用行锁，否则使用表锁
  • 使用相同索引键来访问即使是不同行记录，也会出现锁冲突。（这里指的是需要锁的情况下。在非一致性锁定读情况下，即使读取的行已经被使用Select … For Update，也是可以读的（因为读的是快照））
  • 间隙锁，范围检索以及对不存在记录相等条件的检索都会使用，所以应该在设计上尽量避免，避免导致严重的锁等待

以下情况会使用表锁：

  • 没有使用索引进行检索
  • 判断不同执行计划的代价，如果全表扫描效率更高，也会使用表锁
  • 数据类型转化而导致表锁。