getting lock wait timout when calling mysql proc from java

Question

I have this below proc:

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;
;;

when I call this proc from mysql workbench like below:

call updatePath(); It executes successfully in few milliseconds. But when I run the same code from a java program it takes enormous amount of time and ultimately I have to kill the java process. COde for calling proc is as below:

{
            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;
                        }
                    }
            );        
        }

above method is running in a separate transaction.When I kill transaction after sometime,I see that it was waiting for the lock. On Doing show engine innodb status, I get below:

---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;

Rips Rips · Accepted Answer · 2015-07-29T06:21:38

The problem seems to be the "select row_count() into cnt" statement after the update in the loop.This row_count is responsible for maintaining loop. in my case while was going in an infinite loop as update statement is always true after all the records are updated. When I run the query on the mysql work bench separately.I see that 0 rows modified,6 rows found.While running proc from the workbench,row_count seems to be populated from the modified rows but when I ran it from the java program ,it seems to be picking up the found rows. I got it running from both work bench and java app by modifying the query in loop to below:

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** ;

getting lock wait timout when calling mysql proc from java

1 Answers