Cannot rollback user-defined variables in MySQL

Question

First, I set John to the user-defined variable @name as shown below:

SET @name = 'John';

Then, I set David to @name in a transaction, then rollbacked as shown below:

BEGIN;
SET @name = 'David';
ROLLBACK;

But, @name was not rollbacked to John as shown below:

mysql> SELECT @name;
+-------+
| @name |
+-------+
| David |
+-------+

I read the doc about transaction and user-defined variables but I could not find any relevant information.

So, how can I rollback user-defined variables?

Transactions effects only the tables whose engine is transactional. None other objects, including user-defined variables, are saved by transactional savepoints. — Akina
– Akina, Commented Dec 15, 2023 at 6:19

nbk · Accepted Answer · 2023-12-14 23:22:32Z

0

A user-defined variable is as long active as long the session is online and disappear, when you disconnect. It has a session scope

A transaction scope don't touch that and can't influence it, besides setting the value and it will not reverse it as it is putside of its scope

answered Dec 14, 2023 at 23:22

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1