使用JDBC Template批量更新MariaDB遇锁超时问题求助

我有一段在Oracle数据库中运行正常的代码，在MariaDB执行时抛出锁等待超时异常：

SQL state [HY000]; error code [1205]; (conn=30) Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.BatchUpdateException: (conn=30) Lock wait timeout exceeded; try restarting transaction

已经尝试过增大innodb_lock_wait_timeout、修改事务隔离级别（@@GLOBAL.tx_isolation和@@tx_isolation），但问题依旧。对应的Java代码如下：

public void updateFailureData(final Long mId, final Set<String> errorVals)  {
    try {
        this.jdbcTemplate.batchUpdate("UPDATE MARKETS SET ERR_VALS = ? WHERE M_ID = ?", new BatchPreparedStatementSetter() {

            @Override
            public void setValues(final PreparedStatement preparedStatement, final int arg1) throws SQLException {
                int index = 1;
                preparedStatement.setString(index++, StringUtils.join(errorVals, ","));
                preparedStatement.setLong(index, mId);
            }

            @Override
            public int getBatchSize() {
                return 1;
            }
        });
    } catch (DataAccessException dataAccessException) {
        System.out.println("Updating errorVals.", dataAccessException);
    }
}

以下是可行的解决方向：

• 排查长事务与锁持有情况：用SHOW ENGINE INNODB STATUS;查看当前锁等待的详细信息，找到持有锁的事务ID，再通过SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_id = '事务ID';查看该事务的状态，确认是否有未提交的操作长时间占用行锁。另外检查这段更新代码是否被包裹在一个大事务中，尽量缩小事务范围，减少锁持有时间。
• 给M_ID字段添加索引：如果MARKETS表的M_ID没有索引，执行UPDATE时会触发全表扫描并加表锁，大幅提升锁冲突概率。执行以下语句创建索引：

CREATE INDEX idx_markets_mid ON MARKETS(M_ID);

创建后用EXPLAIN UPDATE MARKETS SET ERR_VALS = ? WHERE M_ID = ?;验证是否走索引。

• 移除无意义的批量更新：代码中batchUpdate的批量大小是1，完全没必要用批量操作，换成普通update方法即可，减少不必要的事务处理开销：

public void updateFailureData(final Long mId, final Set<String> errorVals)  {
    try {
        this.jdbcTemplate.update(
            "UPDATE MARKETS SET ERR_VALS = ? WHERE M_ID = ?",
            StringUtils.join(errorVals, ","),
            mId
        );
    } catch (DataAccessException dataAccessException) {
        System.out.println("Updating errorVals.", dataAccessException);
    }
}

• 引入乐观锁优化并发：如果是高并发场景下的锁冲突，给MARKETS表新增一个版本号字段VERSION（INT类型，默认值0），更新时带上版本判断，避免长时间行锁占用：

UPDATE MARKETS SET ERR_VALS = ?, VERSION = VERSION + 1 WHERE M_ID = ? AND VERSION = ?

Java代码中需要先查询当前版本号，再执行更新，若更新行数为0则说明数据已被其他线程修改，可根据业务逻辑重试或返回提示。

• 检查InnoDB事务隔离级别的实际生效情况：修改隔离级别后，要确认当前会话的隔离级别是否生效，执行SELECT @@tx_isolation;查看。另外，MariaDB 10.2+版本开始用transaction_isolation替代tx_isolation，如果是新版本，要修改对应的变量：

SET GLOBAL transaction_isolation = 'READ-COMMITTED';
SET SESSION transaction_isolation = 'READ-COMMITTED';

READ-COMMITTED级别相比REPEATABLE-READ能减少幻读和锁范围，降低锁冲突概率。

内容的提问来源于stack exchange，提问作者nee nee