【 使用环境 】生产环境
【 OB or 其他组件 】ob Java
【 使用版本 】5.7.25-OceanBase_CE-v4.2.1.2
【问题描述】使用存储过程处理SQLEXCEPTION时没有触发锁等待直接跳过
【复现路径】存储过程中有更新ieai_id表操作，我写了段程序先锁住这个表，随后执行存储过程，存储过程没有锁等待，而是直接返回结果。
【附件及日志】存储过程内容：
CREATE DEFINER = root@% PROCEDURE dgns1015.PROC_GET_NEXT_PK(IN AV_CLASSNAME VARCHAR(100), OUT AD_NEXT_ID NUMERIC(19,0))
MODIFIES SQL DATA
BEGIN
DECLARE LN_PK NUMERIC(19,0);
DECLARE LN_USED_PK NUMERIC(19,0);
DECLARE LV_COLNAME VARCHAR(60);
DECLARE LV_COLTYPE VARCHAR(40);
DECLARE LV_USER VARCHAR(40);
DECLARE LI_COUNT INTEGER;
DECLARE LS_SQL VARCHAR(500);
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE RETSQLCODE INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
SET RETSQLCODE = SQLCODE;
UPDATE IEAI_ID
SET IVALUE = IVALUE + 1
WHERE ICLASSNAME = AV_CLASSNAME;
SET LI_COUNT = ROW_COUNT();
-- insert into ieai_ymx values('update ieai_id',LI_COUNT);
IF LI_COUNT = 0 THEN
INSERT INTO IEAI_ID
(
ICLASSNAME,
IVALUE
)
VALUES (
AV_CLASSNAME,
1
);
COMMIT;
-- insert into ieai_ymx values('insert ieai_id',LI_COUNT);
END IF;
SELECT IVALUE
INTO LN_PK
FROM IEAI_ID
WHERE ICLASSNAME = AV_CLASSNAME;
-- insert into ieai_ymx values('SELECT ieai_id',LN_PK);
IF RETSQLCODE = 0 OR RETSQLCODE = 100 THEN
COMMIT WORK;
SET AD_NEXT_ID = LN_PK;
ELSE
ROLLBACK WORK;
SET AD_NEXT_ID = NULL;
END IF;
COMMIT;
END
代码内容：
public static void insert(){
// 组织数据插入语句
long start = System.currentTimeMillis();
Connection conn = null;
PreparedStatement itemStat = null;
long id = 894245;
try
{
BasicDataSource jdbcDataSource = new BasicDataSource();
jdbcDataSource.setDriverClassName("com.mysql.jdbc.Driver");
jdbcDataSource.setUrl("jdbc:mysql://192.168.4.239:2881/dgns1015?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&autoReconnect=true&failOverReadOnly=false");
jdbcDataSource.setUsername("root");
jdbcDataSource.setPassword("Ideal.123");
jdbcDataSource.setInitialSize(200);
jdbcDataSource.setMaxActive(1800);
jdbcDataSource.setMaxIdle(100);
jdbcDataSource.setMinIdle(50);
// jdbcDataSource.setMaxWait(30000);
conn = jdbcDataSource.getConnection();
conn.setAutoCommit(false);
// for (int i = 0; i < 10000; i++)
// {
// id ++;
itemStat = conn.prepareStatement(
“update ieai_id set ivalue = 333370 where ICLASSNAME = ‘com.ideal.ieai.server.repository.activity.RepActivityRuntime’”);
// itemStat.setLong(1, id);
// itemStat.setLong(2, 233);
// itemStat.setLong(3, 527478);
// itemStat.setLong(4, 521851);
// itemStat.setLong(5, 1727982);
// itemStat.setLong(6, 492922);
// itemStat.setLong(7, 1);
// itemStat.setLong(8, 1);
// itemStat.setLong(9, 1);
// itemStat.setLong(10, 0);
itemStat.executeUpdate();
Thread.sleep(3600000);
conn.commit();
// }
// conn.rollback();
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InterruptedException e) {
throw new RuntimeException(e);
} finally
{
try
{
if (null != itemStat)
{
itemStat.close();
itemStat = null;
}
if (null != conn && !conn.isClosed())
{
conn.close();
}
conn = null;
} catch (SQLException e)
{
e.printStackTrace();
}
}
}
执行存储过程代码：
– 定义输入参数和输出参数
SET @input_param = ‘com.ideal.ieai.server.repository.activity.RepActivityRuntime’;
SET @output_param = 0;
– 调用存储过程
CALL PROC_GET_NEXT_PK(@input_param, @output_param);
– 查看输出参数的值
SELECT @output_param;
我先执行代码，更新ieai_id表但不提交，睡眠一小时，随后手动执行存储过程，存储过程中的UPDATE IEAI_ID SET IVALUE = IVALUE + 1 WHERE ICLASSNAME = AV_CLASSNAME;并没有进行锁等待，而是直接返回了结果，请问各位大神，ob为什么会出现这种情况
很奇怪啊