Thursday, 5 September 2013

Is it possible to Detect and handeling lock wait timeout inside stored mysql procedure

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