How to wrap a mysql stored function in a transaction

Question

``` DROP FUNCTION IF EXISTS my_func; DELIMITER || CREATE FUNCTION my_func(arg_id INT) RETURNS VARCHAR(200) BEGIN DECLARE local_id INT; DECLARE local_name VARCHAR(200); START TRANSACTION; /* <-- this is allowed */ SELECT id, name INTO local_id, local_name FROM table WHERE id = arg_id FOR UPDATE; SELECT my_other_function(local_name) INTO local_name; UPDATE table SET name = local_name; COMMIT; /* <-- this is not allowed and yields an "Explicit or implicit commit is not allowed..." error */ RETURN local_name; END|| DELIMITER ; ``` Are return statements implicit commits? How are you supposed to ensure that a certain function runs as a transaction? Wrap it in a transaction? ``` START TRANSACTION; SELECT my_func(33); /* <-- no transaction statements inside the function */ COMMIT; ``` Start the transaction inside the function and commit? ``` SELECT my_func(33); /* <-- contains only the START TRANSACTION line */ COMMIT; ``` Also i've researched the issue and found some info that said something…

Accepted Answer

I had to use this before and it worked for me:

Option 1

```
SET autocommit = {0 | 1}
```

After you have completed what you want to do, you can re-enable it or call an explicit commit.

Option 2 To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement:

```
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
```

I got this from the mysql website itself - MySQL

For your situation, I would think that setting autocommit to false before the function would do the trick (starting a transaction is the same thing if you look at the description of it above). That way, during the transaction, your function, it doesn't autcommit.

How to wrap a mysql stored function in a transaction

Solution

Problem