Stored procedures, handlers and deadlock retry

A stored procedure is a named program stored in the database and run with CALL. In MySQL its body is a BEGIN ... END block of SQL plus variables, IF, CASE, WHILE, LOOP, cursors and error handlers. (In the mysql command-line client you change the statement DELIMITER while defining one, so the semicolons inside the body do not end the CREATE PROCEDURE; that is a client feature, not part of SQL.)

Parameters and SIGNAL

Parameters are IN (the default), OUT or INOUT. SIGNAL raises an error of your own; SQLSTATE '45000' means "unhandled user-defined exception", and MySQL reports it as error 1644 unless you set MYSQL_ERRNO.

1CREATE TABLE stock (sku VARCHAR(5) PRIMARY KEY, qty INT NOT NULL);2INSERT INTO stock VALUES ('A', 10), ('B', 1);3CREATE PROCEDURE reserve(IN p_sku VARCHAR(5), IN p_qty INT, OUT p_left INT)4BEGIN5  IF p_qty <= 0 THEN6    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'quantity must be positive';7  END IF;8  UPDATE stock SET qty = qty - p_qty WHERE sku = p_sku AND qty >= p_qty;9  IF ROW_COUNT() = 0 THEN10    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'not enough stock';11  END IF;12  SELECT qty INTO p_left FROM stock WHERE sku = p_sku;13END;14-- A helper that runs a statement and records the error instead of stopping.15CREATE TABLE attempts (n INT AUTO_INCREMENT PRIMARY KEY, statement TEXT, outcome TEXT);16CREATE PROCEDURE try_sql(IN stmt TEXT)17BEGIN18  DECLARE EXIT HANDLER FOR SQLEXCEPTION19  BEGIN20    GET DIAGNOSTICS CONDITION 1 @err_no = MYSQL_ERRNO, @err_msg = MESSAGE_TEXT;21    INSERT INTO attempts (statement, outcome)22    VALUES (stmt, CONCAT('error ', @err_no, ': ', REPLACE(@err_msg, CONCAT(DATABASE(), '.'), '')));23  END;24  SET @sql = stmt;25  PREPARE s FROM @sql;26  EXECUTE s;27  DEALLOCATE PREPARE s;28  INSERT INTO attempts (statement, outcome) VALUES (stmt, 'ok');29END;

1CALL try_sql('CALL reserve(''A'', 3, @left)');2CALL try_sql('CALL reserve(''B'', 5, @left_b)');3CALL try_sql('CALL reserve(''A'', 0, @left_zero)');4 5SELECT a.n, a.statement, a.outcome,6       IF(a.n = 1, @left, NULL) AS out_left7FROM attempts a8ORDER BY a.n;

The first call reserved 3 units and returned the remainder, 7, through the OUT parameter. The other two stopped at a SIGNAL with a message the application can show. Because the stock check is part of the UPDATE (WHERE qty >= p_qty) and ROW_COUNT() tells whether a row matched, two sessions cannot both take the last units.

Handlers

DECLARE CONTINUE HANDLER FOR <condition> runs its statement and carries on after the failing one; EXIT HANDLER runs it and leaves the enclosing BEGIN ... END. The condition can be SQLEXCEPTION, SQLWARNING, NOT FOUND, a SQLSTATE or a MySQL error number such as 1213. Inside a handler, GET DIAGNOSTICS CONDITION 1 ... = MESSAGE_TEXT reads the error. The transactions lesson used an EXIT HANDLER FOR SQLEXCEPTION ROLLBACK to keep a failed transfer from committing half its work.

Retrying a deadlock victim

Error 1213 means InnoDB rolled your transaction back to break a deadlock. The right response is to run the whole transaction again, a bounded number of times. One session cannot produce a real deadlock, so this example simulates one: the first two attempts raise error 1213 with SIGNAL, the third succeeds.

1CREATE TABLE stock (sku VARCHAR(5) PRIMARY KEY, qty INT NOT NULL);2INSERT INTO stock VALUES ('A', 10);3CREATE TABLE retry_log (n INT AUTO_INCREMENT PRIMARY KEY, attempt INT, outcome VARCHAR(60));4-- One attempt at the transaction. Attempts 1 and 2 pretend to lose a deadlock.5CREATE PROCEDURE reserve_once(IN p_sku VARCHAR(5), IN p_qty INT, IN p_attempt INT)6BEGIN7  START TRANSACTION;8  UPDATE stock SET qty = qty - p_qty WHERE sku = p_sku;9  IF p_attempt < 3 THEN10    SIGNAL SQLSTATE '40001' SET MYSQL_ERRNO = 1213, MESSAGE_TEXT = 'Deadlock (simulated)';11  END IF;12  COMMIT;13END;

1CREATE PROCEDURE reserve_with_retry(IN p_sku VARCHAR(5), IN p_qty INT)2BEGIN3  DECLARE attempt INT DEFAULT 0;4  DECLARE done BOOL DEFAULT FALSE;5  WHILE NOT done AND attempt < 5 DO6    SET attempt = attempt + 1;7    BEGIN8      DECLARE EXIT HANDLER FOR 12139      BEGIN10        ROLLBACK;11        INSERT INTO retry_log (attempt, outcome) VALUES (attempt, 'deadlock: rolled back, retrying');12      END;13      CALL reserve_once(p_sku, p_qty, attempt);14      SET done = TRUE;15      INSERT INTO retry_log (attempt, outcome) VALUES (attempt, 'committed');16    END;17  END WHILE;18END;19 20CALL reserve_with_retry('A', 4);21 22SELECT r.attempt, r.outcome, (SELECT qty FROM stock WHERE sku = 'A') AS qty_now23FROM retry_log r24ORDER BY r.n;

Each failed attempt was rolled back by the handler and logged; the third committed, and the stock dropped by 4 once, not three times. One difference from the real thing: after a genuine deadlock InnoDB has already rolled the transaction back when 1213 arrives, while a SIGNAL undoes nothing, so here the handler's ROLLBACK does that work (after a real deadlock it is simply harmless). The inner BEGIN ... END matters: an EXIT handler leaves only that block, so the WHILE loop goes round again.

Cursors

A cursor walks a query's result one row at a time, which you need when each row's outcome depends on the previous ones:

DECLARE done BOOL DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT ... ORDER BY id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
  FETCH cur INTO v_a, v_b;
  IF done THEN LEAVE read_loop; END IF;
  -- work on one row
END LOOP;
CLOSE cur;

Declarations must come in this order: variables, then cursors, then handlers. Prefer a single set-based statement whenever the rows are independent; the exercise is a case where they are not.

A routine keeps the sql_mode it was created with

1CREATE TABLE t (n TINYINT);2CREATE TABLE results (n INT AUTO_INCREMENT PRIMARY KEY, how VARCHAR(30), outcome VARCHAR(100));3-- Created while strict mode is on (the default).4CREATE PROCEDURE add_n(IN v INT)5BEGIN6  DECLARE EXIT HANDLER FOR SQLEXCEPTION7  BEGIN8    GET DIAGNOSTICS CONDITION 1 @m = MESSAGE_TEXT;9    INSERT INTO results (how, outcome) VALUES ('procedure', CONCAT('error: ', @m));10  END;11  INSERT INTO t VALUES (v);12  INSERT INTO results (how, outcome) VALUES ('procedure', 'ok');13END;

1SET SESSION sql_mode = '';2 3INSERT INTO t VALUES (300);4INSERT INTO results (how, outcome)5SELECT 'direct INSERT', CONCAT('ok, stored ', n) FROM t;6 7CALL add_n(300);8 9SELECT r.how, r.outcome,10       IF(r.how = 'procedure',11          (SELECT FIND_IN_SET('STRICT_TRANS_TABLES', SQL_MODE) > 012           FROM information_schema.ROUTINES13           WHERE ROUTINE_SCHEMA = DATABASE() AND ROUTINE_NAME = 'add_n'),14          @@SESSION.sql_mode LIKE '%STRICT%') AS strict_in_effect15FROM results r16ORDER BY r.n;

The session turned strict mode off, and its own INSERT of 300 into a TINYINT was clamped to 127. The procedure still failed: MySQL stores the sql_mode in force when a routine is created and runs it under that mode, whatever the caller has set. Recreate the routine to change it.

Common mistakes

• Retrying only the failed statement after 1213. The whole transaction was rolled back; rerun all of it.
• A CONTINUE handler for SQLEXCEPTION around a transaction. It hides the error and lets COMMIT run, as in the transactions lesson.
• Checking stock with a SELECT, then updating. Two sessions can both pass the check; put the condition in the UPDATE and test ROW_COUNT().
• Expecting SET sql_mode to change how an existing procedure behaves. It runs with its creation-time mode.

What interviewers ask

• How do you raise a custom error in MySQL? SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '...', optionally with MYSQL_ERRNO.
• What should an application do on error 1213? Treat the transaction as rolled back and retry it from the start, a limited number of times.