Deadlock handling in MySQL stored procedures

When you have your data logic stored in stored procedures on database site (why to do it?), you will have to face the problem of locking and unlocking resources in database. Due to the way, that MySQL’s InnoDB engine performs locks deadlocks may become an issue.

The algorithm of locking data in InnoDB is quite complicated. It involves two types of locks, “intentions” of acquiring each of the type and multiple levels of lock. You can find all details about it in MySQL documentation.

Fortunately, you don’t have to know everything about it. To understand, why deadlocks can happen and how to handle them in stored procedures it’s enough, that you will know just the following facts: InnoDB provides two basic types of locks – read lock, (that can be shared) and write lock, that is exclusive. Locks are held until the end of current transaction and there is no way to release them earlier. To make it even worse, there is something called “lock upgrade” – in a single transaction you can upgrade a read lock to write lock. But, as it was said, there is no way to release a lock inside transaction, so the upgrade is a single-step operation that doesn’t contain release of read lock. If the read lock was shared by two clients, each of them is going to upgrade his lock to non-shareable one and neither of them is able to release the lock, we have a nice scenario of deadlock. In such case InnoDB is rolling back shorter of those transactions and returning error 40001 – ER_LOCK_DEADLOCK.

Once we know and understand the reason of problems, let’s take a look at possible solutions. Of course you can always retry on the client-side, but there are scenarios, when it’s undesirable. The other way is to handle everything in a stored procedure:

CREATE TABLE Test (
   Id INTEGER PRIMARY KEY 
);

INSERT INTO Test (Id) VALUES (1), (2), (3), (4), (5); 

DELIMITER |

CREATE PROCEDURE DoAndHandleDeadlock ()
BEGIN
	DECLARE TrashVar INTEGER UNSIGNED;
	
	DECLARE EXIT HANDLER
		FOR SQLSTATE '40001' # (ER_LOCK_DEADLOCK) Retry when deadlock occured
		BEGIN
			ROLLBACK AND NO CHAIN; # or COMMIT AND NO CHAIN; 
			
			SELECT SLEEP(FLOOR(RAND() * 5)) INTO TrashVar;
					
			CALL DoAndHandleDeadlock ();
		END;

	SET autocommit := 0;
	START TRANSACTION;

        SELECT * FROM Test; # Read lock on all rows
        UPDATE Test SET Id = Id + 10; # Trying to upgrade to write lock

	COMMIT;
	SET autocommit := 1;
END|

DELIMITER ;

If you’ll try to CALL DoAndHandleDeadlock() multiple times from different connections, you will cause some deadlocks. The deadlocks, as they occur, will be caught by EXIT HANDLER. The transaction must be finished – rolled back or committed, depending on what you’re trying to do. The NO CHAIN part means, that new transaction will not be started immediately. Then our thread sleeps for random (up to five) number of seconds and retries.

There is one serious drawback in this solution – the retry part is being executed as a recurrence. As you know, this is not memory, nor performance optimal solution, but in MySQL there is yet one more problem related to this – the limit of stored procedures call stack size. It’s determined by max_sp_recursion_depth setting in mysqld config file. By default it’s set to 0, to the solution will not work before adding the following line to a config file:

max_sp_recursion_depth = 20

The maximum legal value is 255, but according to my my experience me 20 is enough.