1. The data structure of the reconciliation table is as follows
create table t_cgw_ckjnl
(
CNL_CODE varchar(10) default ' ' not null comment 'channel coding',
CNL_PLT_CD varchar(32) default ' ' not null comment 'channel platform number',
CNL_TYP varchar(10) default ' ' not null comment 'channel type',
CHK_BAT_NO varchar(32) default ' ' not null comment 'Reconciliation batch number',
BAT_NO varchar(32) default ' ' not null comment 'Transaction batch number',
SEQ_NO varchar(8) default ' ' not null comment 'batch serial number',
CHK_ORD_NO varchar(64) default ' ' not null comment 'Reconciliation order number',
CHK_TYP varchar(10) default ' ' not null comment 'Reconciliation type',
CHK_MOD varchar(2) default ' ' not null comment 'Reconciliation method (reserved: channel order number, transaction batch number + channel order number, transaction batch number + batch serial number)',
CHK_DT varchar(8) default ' ' not null comment 'Reconciliation date',
CHK_TM varchar(6) default ' ' not null comment 'Reconciliation time',
CHK_STS varchar(1) default '0' not null comment 'Reconciliation status',
REQ_DT varchar(8) default '0' not null comment 'Transaction request date',
IIF_TYP varchar(10) default '0' not null comment 'interface type',
ORD_NO varchar(32) default '0' not null comment 'Transaction order number',
CGW_STS varchar(2) default ' ' not null comment 'Transaction status',
TXN_AMT decimal(18,2) not null comment 'Transaction amount',
FEE_AMT decimal(18,2) default '0.00' not null comment 'handling fee',
BAT_FLG varchar(2) default ' ' not null comment 'Batch identification (B-batch, S-single)',
FIELD varchar(64) null comment 'alternate field',
TM_SMP varchar(26) default ' ' not null comment 'timestamp',
NOD_ID varchar(32) null comment 'Transaction Source',
primary key (CHK_BAT_NO, CHK_ORD_NO)
)
comment 'Reconciliation flow temporary table' engine=InnoDB;
2. Phenomenon
When two reconciliation transactions occur at the same time, the following delete operations are performed on this table. When two delete statements occur at the same time, a deadlock occurs.
sql:
delete from T_CGW_CKJNL where chk_typ=#{chk_typ} and cnl_code=#{cnl_code} and cnl_plt_cd=#{cnl_plt_cd}
Transaction 1 exception:
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] -> Reconciliation execution exception,
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:
<strong>com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction</strong>
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)
Transaction 2 exception:
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] -> Reconciliation execution exception, java.lang.reflect.UndeclaredThrowableException ... Nested Exception: <strong>com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction</strong> ...
3. Solution
MySQL’s InnoDB storage engine supports row-level locks. InnoDB’s row locks are implemented by locking index items. This means that InnoDB uses row locks only when data is retrieved through index conditions, otherwise table locks are used.
The fields chk_typ, cnl_code, and cnl_plt_cd involved in the above data update statement have no indexes, so the table is locked during concurrency.
The solution is to add indexes to the fields chk_typ, cnl_code, cnl_plt_cd, and reduce the granularity of the data lock range to row-level locks, which can better support concurrent operations without causing deadlocks.
4. Comparison of execution plans
EXPLAIN delete from T_CGW_CKJNL where chk_typ='Pay' and cnl_plt_cd='Z20275330000161' and cnl_code='EPCC'
Without index:
After adding the index:
Among them, rows represents the estimated number of rows that MySQL needs to read to find the required records based on table statistics and index selection. It can be seen that all rows are read without the index (a total of 33 records in the table), but only a specific row is read after adding the index.
ref: https://www.cnblogs.com/zmduan/p/5033047.html