jdbc mysql innodb 死锁 deadlock_Mysql InnoDB 数据更新/删除导致锁表

一. 如下 对账表 数据结构

create tablet_cgw_ckjnl

(

CNL_CODEvarchar(10) default ' ' not null comment '通道编码',

CNL_PLT_CDvarchar(32) default ' ' not null comment '通道平台号',

CNL_TYPvarchar(10) default ' ' not null comment '通道类型',

CHK_BAT_NOvarchar(32) default ' ' not null comment '对账批次号',

BAT_NOvarchar(32) default ' ' not null comment '交易批次号',

SEQ_NOvarchar(8) default ' ' not null comment '批次序列号',

CHK_ORD_NOvarchar(64) default ' ' not null comment '对账订单号',

CHK_TYPvarchar(10) default ' ' not null comment '对账类型',

CHK_MODvarchar(2) default ' ' not null comment '对账方式(预留:通道订单号、交易批次号+通道订单号、交易批次号+批次序列号)',

CHK_DTvarchar(8) default ' ' not null comment '对账日期',

CHK_TMvarchar(6) default ' ' not null comment '对账时间',

CHK_STSvarchar(1) default '0' not null comment '对账状态',

REQ_DTvarchar(8) default '0' not null comment '交易请求日期',

IIF_TYPvarchar(10) default '0' not null comment '接口类型',

ORD_NOvarchar(32) default '0' not null comment '交易订单号',

CGW_STSvarchar(2) default ' ' not null comment '交易状态',

TXN_AMTdecimal(18,2) not null comment '交易金额',

FEE_AMTdecimal(18,2) default '0.00' not null comment '手续费',

BAT_FLGvarchar(2) default ' ' not null comment '批量标识(B-批量,S-单笔)',

FIELDvarchar(64) null comment '备用字段',

TM_SMPvarchar(26) default ' ' not null comment '时间戳',

NOD_IDvarchar(32) null comment '交易来源',primary key(CHK_BAT_NO, CHK_ORD_NO)

)

comment'对账流水临时表' engine=InnoDB;

二. 现象

当两个对账交易同时发生时，因都对这个表执行如下delete操作，当2个delete语句同时发生时，产生死锁。

sql：

delete from T_CGW_CKJNL where chk_typ=#{chk_typ} and cnl_code=#{cnl_code} and cnl_plt_cd=#{cnl_plt_cd}

交易1异常：

INFO[11-02 13:58:01,697] -> update sql:[delete from T_CGW_CKJNL where chk_typ='SP' and cnl_code='EPCC' and cnl_plt_cd='Z2027533000016' ]

INFO[11-02 13:58:01,767] -> 对账执行异常,

java.lang.reflect.UndeclaredThrowableException

at com.sun.proxy.$Proxy256.deleteCkJnl(Unknown Source)

at com.murong.ecp.app.bpg.cgw.service.db.CgwCkJnlDBService.deleteCkJnl(CgwCkJnlDBService.java:29)

at com.murong.ecp.app.bpg.cgw.service.biz.CheckFlowService.check(CheckFlowService.java:352)

at com.murong.ecp.app.bpg.cgw.service.biz.CheckFlowService.checkExecute(CheckFlowService.java:116)

at com.murong.ecp.app.bpg.cgw.action.cgwchkbpc1.CheckFlowAction.doProcess(CheckFlowAction.java:61)

...

Nested Exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)

sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

java.lang.reflect.Constructor.newInstance(Constructor.java:423)

com.mysql.jdbc.Util.handleNewInstance(Util.java:377)

com.mysql.jdbc.Util.getInstance(Util.java:360)

com.mysql.jdbc.SQLError.createSQLException(SQLError.java:985)

com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)

com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)

com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)

com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)

com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)

com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)

com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2141)

com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2077)

com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2062)

org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)

org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)

...

java.lang.Thread.run(Thread.java:748)

交易2异常：

INFO[11-02 13:58:01,697] -> update sql:[delete from T_CGW_CKJNL where chk_typ='Refund' and cnl_code='EPCC' and cnl_plt_cd='Z2027533000016' ]

INFO[11-02 13:58:01,767] -> 对账执行异常,

java.lang.reflect.UndeclaredThrowableException

...

Nested Exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

...

三. 解决办法

MySQL的InnoDB存储引擎支持行级锁，InnoDB的行锁是通过给索引项加锁实现的。这就意味着只有通过索引条件检索数据时，InnoDB才使用行锁，否则使用表锁。

上面的数据更新语句涉及到的字段chk_typ,cnl_code,cnl_plt_cd上都没有索引，所以并发时导致表被锁。

解决办法就是为字段chk_typ,cnl_code,cnl_plt_cd添加索引，将数据锁定范围的颗粒度降低为行级锁，这样可以更好的支持并发操作而不产生死锁。

四. 执行计划对比

EXPLAIN delete from T_CGW_CKJNL where chk_typ='Pay' and cnl_plt_cd='Z20275330000161' and cnl_code='EPCC'

没有索引时：

添加索引后：

其中，rows表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。可见，没有索引时读取了所有行(表里共33条记录)，而添加索引后只读取特定的1行。

ref：https://www.cnblogs.com/zmduan/p/5033047.html