java - 从java调用mysql proc时获取锁等待超时

Question

我有以下过程:

CREATE PROCEDURE updatepath()
BEGIN
declare cnt, n int;
    update foo a set a.path=a.name where a.parent_id is null;
    select count(*) into cnt from foo where path is null;
    while cnt > 0 do
        update foo a, foo b set a.path = concat(b.path, '/', a.name)  where b.path is not null and a.parent_id = b.id;
        select row_count() into cnt;
    end while;
END;
;;

当我从 mysql 工作台调用此过程时，如下所示:

调用 updatePath();它在几毫秒内成功执行。但是，当我从 java 程序运行相同的代码时，需要花费大量时间，最终我必须终止 java 进程。调用proc的代码如下:

{
            jdbcTemplate.execute(
                    new CallableStatementCreator() {
                        public CallableStatement createCallableStatement(Connection con) throws SQLException {
                            CallableStatement cs = con.prepareCall("{call updatePath()}");  
                            return cs;
                        }
                    },
                    new CallableStatementCallback() {
                        public Object doInCallableStatement(CallableStatement cs) throws SQLException {
                            cs.execute();
                            return null;
                        }
                    }
            );        
        }

上述方法在单独的事务中运行。当我在一段时间后终止事务时，我看到它正在等待锁定。在执行显示引擎 innodb 状态时，我得到以下信息:

---TRANSACTION 4186138, ACTIVE 532 sec fetching rows mysql tables in use 2, locked 2 37 lock struct(s), heap size 6544, 2437 row lock(s), undo log entries 307157 MySQL thread id 14, OS thread handle 0x2b38, query id 461758 localhost 127.0.0.1 root Sending data update foo a, foo b set a.path = concat(b.path, '/', a.name) where b.path is not null and a.parent_id = b.id;

Answer 1

问题似乎是循环中更新后的“select row_count() into cnt”语句。这个row_count负责维护循环。就我而言， while 进入无限循环，因为更新语句在所有记录更新后始终为真。当我单独在 mysql 工作台上运行查询时。我看到修改了 0 行，找到了 6 行。从工作台运行 proc 时，row_count 似乎是从修改的行填充的，但是当我从 java 程序运行它时，它似乎正在拾取找到的行。通过将循环中的查询修改为以下内容，我可以从工作台和 java 应用程序运行它:

update foo a, foo b set a.path = concat(b.path, '/', a.name)  where b.path is not null and a.parent_id = b.id **and a.path is null** ;