一次Mysql 死锁事故

故障描述：

简单描述一下需求：我们写的一个计步器的客户端软件，用户通过手机客户端将用户的运动计步信息传到服务器。

服务器侧记录每个用户每次上传明细，同时每个用户有一个汇总值，参与全省排名。

1.加入明细记录方法

List<EmployeeRecord> list = mEmployeeRecordService.uploadRecord(usercode, token, uploadStr, movement_id);

2.更新统计值（上面明细添加成功后，将实体返回，更新统计值）

this.mEmployeeReportService.updateUserReport(usercode, list);

3.更新统计值中有一个方法用来更新用户在本活动中的排名信息，具体方法如下：

/**
     * 更新所有人的排名
     * 
     * @param movementId
     *            ：活动编号
     * @param orderType
     *            增加排名方式@2013.12.11
     */
    @SuppressWarnings("unchecked")
    private void updateRanks(String movementId, String orderType) {
String updateField = orderType.equals("0") ? "all_sport_steps"
                : "all_sport_distence";
        String sql = "update tb_c_employee_report  as rpt set rpt.current_rank="
                + "(select count(1)+1 from (select movement_id,all_sport_steps from tb_c_employee_report) as b where b.movement_id='"
                + movementId
                + "' and b."
                + updateField
                + ">rpt."
                + updateField
                + ")" + "where movement_id='" + movementId + "'";
        this.jdbcTemplate.execute(sql);

这个巧妙的方法可以用一条sql语句更新用户的排名。

但问题也出在这个语句上：mysql的update语句或insert语句中如果包含select（如insert into select from），将会为select 表加锁（可能是表锁或行锁），而且mysql的行锁是加在索引上的。

这个方法上线后，一开始运行正常。但随着并发数的增加，经常报一个如下的错误：

 nested exception is org.hibernate.exception.LockAcquisitionException: Could not execute JDBC batch update

通过mysql的show innodb status查看，产生一个死锁。

 

解决方案：

网上有很多解决办法。我这里更新排名没有办法了。只能拿出来遍历了。方法虽然傻了点，但每个分公司是一个活动，活动内用户最多也就1000多人。不会有太大效率上的问题。先解决问题。明天再想想有没有别的办法解决。

/**
     * 更新所有人的排名
     * 
     * @param movementId
     *            ：活动编号
     * @param orderType
     *            增加排名方式@2013.12.11
     */
    @SuppressWarnings("unchecked")
    private void updateRanks(String movementId, String orderType) {
//耗效率一点。反正一个活动里的人也不是太多。
String updateField = orderType.equals("0") ? "all_sport_steps"
                : "all_sport_distence";
String sql = "SELECT employee_report_id FROM tb_c_employee_report WHERE movement_id=? ORDER BY " + updateField + " DESC";
        List<Map<String,Object>> list = this.jdbcTemplate.queryForList(sql, movementId);
        int i = 1;
        for(Map<String,Object> map : list)
        {
            this.jdbcTemplate.execute("UPDATE tb_c_employee_report SET current_rank='"+i+"'" +
                    " WHERE employee_report_id='"+map.get("employee_report_id").toString()+"'");
            i++;
        }

 

附：

http://tech110.blog.51cto.com/438717/268954

这篇文章推荐如果有insert 和 select同时操作时使用MyISAM 表。明天有空试一下。

 

http://xu20cn.blog.51cto.com/274020/72574

这里有详细的介绍innodb锁机制。