Question 304964 in Mysql Question posted in
The official MySQL documentation can be found here.

Cannot rollback user-defined variables in MySQL

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?

Tags:

2

Answers


  1. 0

    In MySQL, only DML statements in certain storage engines (chiefly InnoDB) support transactions.

    Using SET to set variables, whether they are user-defined variables or builtin session or global variables, cannot be rolled back. These statements are not related to any specific storage engine, and they don’t support transactions.

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search