mysql问题-死锁问题

发表于   |   分类于   |     |   阅读次数

本文只给出现象和解决方法，并没有深度分析原因(mysql功力不够。)

一、表结构

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `app_client_value_minute_stat` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `app_id` bigint(20) NOT NULL COMMENT '应用id',
  `collect_time` bigint(20) NOT NULL COMMENT '统计时间:格式yyyyMMddHHmm00',
  `update_time` datetime NOT NULL COMMENT '创建时间',
  `command` varchar(20) NOT NULL COMMENT '命令',
  `distribute_type` tinyint(4) NOT NULL COMMENT '值分布类型',
  `count` int(11) NOT NULL COMMENT '调用次数',
  PRIMARY KEY (`id`),
  UNIQUE KEY `app_collect_command_dis` (`app_id`,`collect_time`,`command`,`distribute_type`),
  KEY `idx_collect_time` (`collect_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户端每分钟值分布上报数据统计';
注意：id是primary key, 四个列组成了unique_key

二、操作

批量写(利用mybatis拼接一个大sql)，自动提交

1
2
3
4
5
6
7
8
9
<insert id="batchSave">
	insert into app_client_value_minute_stat(<include refid="columns" />)
	values
	<foreach collection="appClientValueDistriStatTotalList" item="valueDistri" separator=",">
    	(#{valueDistri.appId},#{valueDistri.collectTime},#{valueDistri.updateTime},#{valueDistri.command},#{valueDistri.distributeType},#{valueDistri.count})
    </foreach>
    on duplicate key update
    count = count + #{valueDistri.count}, update_time=now()
</insert>

三、问题

1
2
3
4
5
6
7
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
org.springframework.dao.DeadlockLoserDataAccessException: 
Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
The error may involve defaultParameterMap
The error occurred while setting parameters
SQL: insert into app_client_value_minute_stat(    app_id,collect_time,update_time,command,distribute_type,count   )   values               (?,?,?,?,?,?)

四、解决方法：

1.经过大神的分析，是因为如下原因造成了死锁

  • id是主键，四个列是unique key
  • 批量写入，包含on duplicate key操作
  • 多个线程并发
    会造成死锁问题，具体和mysql insert_buffer好像有什么关系(没细看)

2.dba给的意见是：

  • 把唯一键改成主键，这样理论上能降低发生死锁的可能性，程度无法估计；
  • 调整程序逻辑，避免在写入过程中的间隙锁导致死锁。这种锁其实和唯一索引的关系都不大，任何索引都有这种间隙锁，并发高的情况下都会发生
  • 把事务隔离级别改成 READ COMMITTED，这种改动和现在相比来说会导致一些不可重复读，这得看你们业务方面能不能接受这种情况

3.最终解决：

采用第一种方案，上线后死锁仍然存在，但是明显减少。具体原因会后续分析，先记录一下。

PRIMARY KEY (`app_id`,`collect_time`,`command`,`distribute_type`),