Is it possible to Detect and handeling lock wait timeout inside stored
mysql procedure
The Question
purely for academic reasons, I'm wondering if you could add a handler to a
mysql stored procedure that is able to recover from a lock wait timeout
error if one of its queries locks up (such as a SELECT ... FOR UPDATE or
UPDATE) query.
The Example
This is assuming an innoDB database, set to issolation level Repeatable
read, with an empty users table defined.
1. Example Procedure:
DROP PROCEDURE IF EXISTS `lock_test`;
DELIMITER ;;
CREATE PROCEDURE `lock_test`(OUT status ENUM('success','timeout'))
MODIFIES SQL DATA
BEGIN
START TRANSACTION;
SELECT * FROM `users` FOR UPDATE;
SET status := 'success';
COMMIT;
END;;
DELIMITER ;
2. Run code in mysql terminal 1:
START TRANSACTION;
SELECT * FROM `users` FOR UPDATE;
the contents of users will be displayed, but the transaction will remain
open.
3. Run code in mysql terminal 2:
CALL `lock_test`(@out);
SELECT @out;
the transaction will run until it times out (default value of
innodb_lock_wait_timeout is 50 seconds)
Is it possible to add a handler inside the lock_test() procedure, so that
we can have @out hold 'timeout'?
No comments:
Post a Comment