How to use a transaction in a stored procedure?

Question

I have an SQL script that I need to convert to a parameterized stored procedure. I've only written simple functions in the past, never a complex transactional query with parameters.

Any help is greatly appreciated - simplified queries below. This script could really be anything containing a transaction and some user inputs.

-- transaction ensures i can clean up a mess, if one happens
begin;

-- parameters for the script; currently set manually before execution
set @parent_id := 123;
set @identifier := 'someid';

-- insert some row with user-specified values
insert into users (field1, field2) values (@parent_id, @identifier);

-- get the new id
set @user_id := last_insert_id();

-- do another insert
insert into usersmeta (user_id, field1, field2) values (@user_id, 1, 2);

-- if no errors happened yet, commit transaction
commit;

-- "return value"; some select query (could be 1 or many rows)
select users.id userid, usersmeta metaid
from users
join usersmeta on usersmeta.user_id = users.id;

I started with but then I pretty much got stuck. I'm especially concerned with ensuring errors, in the event they occur, are somehow made visible to the calling code

delimiter ;; 
CREATE PROCEDURE mytask(IN parent_id INT(11), IN identifier VARCHAR(200)) 
BEGIN 
        SET @query = ??? 
        PREPARE q FROM @query; 
        EXECUTE q; 
        DEALLOCATE PREPARE q; 
END;; 
delimiter ;

If an error occurs, an exception is thrown. You can catch it in your procedure and set an output parameter, reraise it and/or you can let the caller catch the exception. IF you want to use a transaction in a procedure, you should catch errors to be able to rollback. Be careful with transactions though, because they don't nest; if e.g. the caller uses a transaction too, that "outside" transaction will be commited/rolled back too. See eg [How can I use transactions in my MySQL stored procedure?](https://stackoverflow.com/questions/18817148/how-can-i-use-transactions-in-my-mysql-stored-procedure) — Solarflare, Jul 11 '17 at 09:04
@Solarflare thank you for your comment. I was able to use the resource you linked to arrive at my solution (posted below). I really appreciate it. — Mulan, Jul 11 '17 at 16:01

score 5 · Answer 1 · answered Jul 11 '17 at 16:00

It took a good amount of research, trial, and error, but I think I arrived at a pretty good solution.

DELIMITER //

CREATE PROCEDURE my_procedure (IN parent_id int, IN identifier varchar(255), OUT out_user_id int)
BEGIN

  -- local variable, set later after user is created
  DECLARE user_id int;

  -- rollback transaction and bubble up errors if something bad happens
  DECLARE exit handler FOR SQLEXCEPTION, SQLWARNING
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;

  -- insert some row with user-specified values
  INSERT INTO users (field1, field2) values (parent_id, identifier);

  -- get the new id
  SET user_id = last_insert_id();

  -- do another insert
  INSERT INTO usersmeta (user_id, field1, field2) values (user_id, 1, 2);

  -- if no errors happened yet, commit transaction
  COMMIT;

  -- return
  SELECT user_id INTO out_user_id;

END //

DELIMITER ;

I can use it like this

-- run the procedure
CALL my_procedure(123, 'some_id', @user_id);

-- get the "return" value
SELECT @user_id as user_id;

This is definitely the most complex stored procedure I've written. If anyone sees an area for improvement, I'd be happy to learn how I can make it better.

Here be dragons: `INSERT INTO usersmeta (user_id, field1, field2) values (user_id, 1, 2);` The `user_id` in `VALUES()` is ambiguous, because you have a column name and a program variable of the same name. Don't do this. The parser can assume you mean one when you intend the other, or you can end up recycling the uninitialized default value for the column. Always make program variables distinct from column names. My convention is a leading underscore for variables, e.g. `_user_id`. See this example for problematic behavior: https://dba.stackexchange.com/q/56957/11651 — Michael - sqlbot, Jul 12 '17 at 01:20
@NathanDrake Nicely done, but a little warning (no pun intended): if you resignal a warning, it will stay exactly that, a warning. A warning will usually not stop execution in the caller, unless you catch it there too and handle it as "the function rolled back". If you don't, you could alternatively signal an error (so turn warnings into errors by `signal ...` instead of `resignal`), don't rollback on warnings, or add an additional marker that you can check to know if the procedure was (un)successful (e.g. setting the output parameter to `null` in case that is otherwise not possible). — Solarflare, Jul 12 '17 at 06:10

How to use a transaction in a stored procedure?

1 Answers1