buguge - Keep it simple,stupid

知识就是力量,但更重要的,是运用知识的能力why buguge?

导航

< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

统计

Mysql InnoDB 数据更新/删除导致锁表

 

回到顶部

一. 如下 对账表 数据结构 

复制代码
create table t_cgw_ckjnl
(
    CNL_CODE varchar(10) default ' ' not null comment '通道编码',
    CNL_PLT_CD varchar(32) default ' ' not null comment '通道平台号',
    CNL_TYP varchar(10) default ' ' not null comment '通道类型',
    CHK_BAT_NO varchar(32) default ' ' not null comment '对账批次号',
    BAT_NO varchar(32) default ' ' not null comment '交易批次号',
    SEQ_NO varchar(8) default ' ' not null comment '批次序列号',
    CHK_ORD_NO varchar(64) default ' ' not null comment '对账订单号',
    CHK_TYP varchar(10) default ' ' not null comment '对账类型',
    CHK_MOD varchar(2) default ' ' not null comment '对账方式(预留:通道订单号、交易批次号+通道订单号、交易批次号+批次序列号)',
    CHK_DT varchar(8) default ' ' not null comment '对账日期',
    CHK_TM varchar(6) default ' ' not null comment '对账时间',
    CHK_STS varchar(1) default '0' not null comment '对账状态',
    REQ_DT varchar(8) default '0' not null comment '交易请求日期',
    IIF_TYP varchar(10) default '0' not null comment '接口类型',
    ORD_NO varchar(32) default '0' not null comment '交易订单号',
    CGW_STS varchar(2) default ' ' not null comment '交易状态',
    TXN_AMT decimal(18,2) not null comment '交易金额',
    FEE_AMT decimal(18,2) default '0.00' not null comment '手续费',
    BAT_FLG varchar(2) default ' ' not null comment '批量标识(B-批量,S-单笔)',
    FIELD varchar(64) null comment '备用字段',
    TM_SMP varchar(26) default ' ' not null comment '时间戳',
    NOD_ID varchar(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

posted on   buguge  阅读(13806)  评论(0编辑  收藏  举报

编辑推荐:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
阅读排行:
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 推荐几款开源且免费的 .NET MAUI 组件库
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· 【全网最全教程】使用最强DeepSeekR1+联网的火山引擎,没有生成长度限制,DeepSeek本体
点击右上角即可分享
微信分享提示