先看异常日志
代码:
@Override @Transactional(propagation = Propagation.REQUIRED, readOnly = false, rollbackFor = RuntimeException.class) public void updateAuthStatus(Long userId, Integer authType, Integer authStatus) { retryNum++; try { creAuthStatusDao.updateAuthStatus(userId, authType, authStatus); int creditNum = creAuthStatusDao.countAuthedCreditNum(userId);
.... } catch (Exception e) { log.error("updateAuthStatus: exception = {}", e); } }
|
在一个事物中既有update,又有select for update(意向排它锁)
数据库中,有user_id的索引和user_id与auth_type的联合索引
session 1 | session 2 |
begin; | |
begin; | |
update cre_auth_status SET user_id='29507138388754432',auth_type=4,auth_status=2,update_time='2018-11-21 09:32:35.414' WHERE (user_id='29507138388754432' and auth_type=2) 排它锁,因为有单独索引,将锁user_id='29507138388754432'的相关数据表单锁定。 | |
update cre_auth_status SET user_id='29507138388754432',auth_type=9,auth_status=2,update_time='2018-11-21 09:32:35.414' WHERE (user_id='29507138388754432' and auth_type=9) 排它锁pending | |
select id, user_id, basic_type, auth_type, auth_status, task_id, auth_time, expire_time, create_time, update_time from cre_auth_status where user_id = '29507138388754432' AND basic_type =3 AND auth_status =2 FOR UPDATE 需要等待user_id='29507138388754432'的数据释放,锁也是处于pending状态,和session2争抢锁资源,进入死锁。 | |
Select时,user_id的索引需要锁住了user_id=’ 29507138388754432’的数据,等待session2中的update释放,但是因为session2中的排它锁是pending状态,未commit,锁等待中,进入死锁 | select id, user_id, basic_type, auth_type, auth_status, task_id, auth_time, expire_time, create_time, update_time from cre_auth_status where user_id = '29507138388754432' AND basic_type =3 AND auth_status =2 FOR UPDATE |
死锁出现异常,事物回滚 | 拿到锁,正常流程往下走 |
解决方式:
- 将事物放在update里,等待update结束之后,再进行select;
- 去掉事物,事物在这里属于画蛇添足。
@Override public void updateAuthStatus(Long userId, Integer authType, Integer authStatus, String operatorGid, int retryNum) { retryNum++; try { creAuthStatusDao.updateAuthStatus(userId, authType, authStatus); int creditNum = creAuthStatusDao.countAuthedCreditNum(userId);
.... } catch (Exception e) { log.error("updateAuthStatus: exception = {}", e); } }
|