A local variable is not rollbacked in a MySQL procedure [closed]

Question

I'm trying to rollback the local variable v1 from 5 to 2 with ROLLBACK statement in my_proc() procedure but v1 is not rollbacked keeping 5 as shown below:

DELIMITER $$

CREATE PROCEDURE my_proc() 
BEGIN
  DECLARE v1 INT DEFAULT 2; -- v1 is 2
  START TRANSACTION;
    SELECT 5 INTO v1;       -- v1 is 5
    SELECT v1; -- v1 is 5
    ROLLBACK;  -- Here
    SELECT v1; -- v1 is 5
END$$ 

DELIMITER ;

This below is the actuall result which v1 is not rollbacked keeping 5:

mysql> CALL my_proc();
+------+
| v1   |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

+------+
| v1   |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

So, how can I rollback the local variable?

Decani

This is almost a duplicate of the question you asked 2 days ago: #77663986 – Flowerdeluce

Answer 1

Rollback rolls back a database transaction, cancelling out any changes it did to the data in the tables. Changing a variable's value is not saved in a mysql table, therefore rollback does no roll changes back to variables.

The only workaround is to keep the variable's original value and restore it if certain conditions are met.

Edgebone