Description:
when accessing the database through multiple connections and there is a deadlock,
calls to a simple stored function fail after the deadlock. the stored function
declares a continue handler for the condition 02000, but after the deadlock
the stored function seem to loose this handler declaration for the function.
it keeps exiting with error code 02000.
after issuing a ROLLBACK(), the function works well again.
How to repeat:
USE test;
DROP FUNCTION IF EXISTS sp;
DELIMITER //
CREATE FUNCTION sp()
RETURNS INT
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN
DECLARE qty INT DEFAULT 0;
DECLARE x INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT i FROM t1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur;
myloop: LOOP
FETCH cur INTO x;
IF done THEN
LEAVE myloop;
END IF;
SET qty = qty + x;
END LOOP myloop;
CLOSE cur;
RETURN qty;
END //
DELIMITER ;
SET AUTOCOMMIT=0;
CREATE TABLE t1 (i INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
COMMIT;
mysql> SELECT sp();
+------+
| sp() |
+------+
| 15 |
+------+
1 row in set (0,00 sec)
open another connection to mysql:
mysql> USE test;
mysql> SET AUTOCOMMIT=0;
mysql> SELECT * FROM t1 WHERE i=3 FOR UPDATE;
+------+
| i |
+------+
| 3 |
+------+
1 row in set (0,00 sec)
this puts a lock on the appropriate row.
then, on the fist connection, try to issue an update command to get an
deadlock:
mysql> UPDATE t1 SET i=3 WHERE i=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
now, try to call the stored function again:
mysql> SELECT sp();
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
the funny part: after rollback, things work well again.
mysql> ROLLBACK;
Query OK, 0 rows affected (0,00 sec)
mysql> SELECT sp();
+------+
| sp() |
+------+
| 15 |
+------+
1 row in set (0,00 sec)
Description: when accessing the database through multiple connections and there is a deadlock, calls to a simple stored function fail after the deadlock. the stored function declares a continue handler for the condition 02000, but after the deadlock the stored function seem to loose this handler declaration for the function. it keeps exiting with error code 02000. after issuing a ROLLBACK(), the function works well again. How to repeat: USE test; DROP FUNCTION IF EXISTS sp; DELIMITER // CREATE FUNCTION sp() RETURNS INT SQL SECURITY INVOKER DETERMINISTIC BEGIN DECLARE qty INT DEFAULT 0; DECLARE x INT DEFAULT 0; DECLARE done INT DEFAULT 0; DECLARE cur CURSOR FOR SELECT i FROM t1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur; myloop: LOOP FETCH cur INTO x; IF done THEN LEAVE myloop; END IF; SET qty = qty + x; END LOOP myloop; CLOSE cur; RETURN qty; END // DELIMITER ; SET AUTOCOMMIT=0; CREATE TABLE t1 (i INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1),(2),(3),(4),(5); COMMIT; mysql> SELECT sp(); +------+ | sp() | +------+ | 15 | +------+ 1 row in set (0,00 sec) open another connection to mysql: mysql> USE test; mysql> SET AUTOCOMMIT=0; mysql> SELECT * FROM t1 WHERE i=3 FOR UPDATE; +------+ | i | +------+ | 3 | +------+ 1 row in set (0,00 sec) this puts a lock on the appropriate row. then, on the fist connection, try to issue an update command to get an deadlock: mysql> UPDATE t1 SET i=3 WHERE i=3; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction now, try to call the stored function again: mysql> SELECT sp(); ERROR 1329 (02000): No data - zero rows fetched, selected, or processed the funny part: after rollback, things work well again. mysql> ROLLBACK; Query OK, 0 rows affected (0,00 sec) mysql> SELECT sp(); +------+ | sp() | +------+ | 15 | +------+ 1 row in set (0,00 sec)