CODEWITHSUNDEEP
mysql
Bharanikumar
Bharanikumar

Reputation: 25733

Rollback not working in MySQL

I have a user table, and I have 5 records.

I deleted two records, then executed the rollback command, it executed successfully.

But that deleted two records not recovered.

The user table engine is InnoDB.

Upvotes: 19

Views: 33916

Answers (9)

nguyen tran
nguyen tran

Reputation: 1

If you're using MySQL Workbench you can turn off Auto-commit transactions in 'Query' tab. And don't forget to use "COMMIT;" after the Query which you need to rollback.

Upvotes: 0

PraveenKumar Lalasangi
PraveenKumar Lalasangi

Reputation: 3523

Even for me rollback was not working for InnoDB engine.
But adding begin; statement after start transaction; made the fix.

This worked for me 
SET autocommit = 0;
start transaction;
begin;
-- DML query goes here
rollback;
SET autocommit = 1;

But

This doesn't worked for me 
SET autocommit = 0;
start transaction;
-- DML query goes here
rollback;
SET autocommit = 1;

Really i don't know the reason, If anyone knows please comment here.

Upvotes: -1

George G
George G

Reputation: 7695

I think there is one important thing to mention: to re-enable autocommit after transaction is done.

SET autocommit = 0;
START TRANSACTION;
    INSERT INTO ..
    UPDATE <table> ..
COMMIT;
SET autocommit = 1;

Otherwise anything you do after disabling autocommit even without transaction will require you to explicitly commit.

Upvotes: 0

nickbodnar
nickbodnar

Reputation: 21

I don't know if you were locking tables, but I ran into the same problem where it seemed like rollback wasn't working.

Within my transaction I called "LOCK TABLES...", which

implicitly commits any active transaction before attempting to lock the tables

(http://dev.mysql.com/doc/refman/5.0/en/lock-tables-and-transactions.html)

I ended up locking outside of (before) the transaction, as that was the first thing I was doing in the transaction anyway. Alternatively, you can do as the docs suggest: 

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

Upvotes: 2

Ayyappan Sekar
Ayyappan Sekar

Reputation: 11475

You should be able to rollback your transaction as the table engine is InnoDB. enter image description here

Anyways here is the correct way to do transactions,

SET autocommit=0;
START TRANSACTION; 
Your Query here.
ROLLBACK;

and make sure that you are not using COMMIT after the Query which you need to rollback. Refer Table Engines and Transaction. And When a DB connection is created, it is in auto-commit mode by default. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. So if you need to do transactions yourself, you must turn off the autocommit mode by AUTOCOMMIT = 0. Refer this link for more info.

Upvotes: 27

Alfredo Iv&#225;n Palomino Calli
Alfredo Iv&#225;n Palomino Calli

Reputation: 21

I have the same problem, but i has checked the innodb support

First: Verify if the Engine INNODB is Available: with this:

mysql> show engines;

if INNODB is disabled: then

Second: go to the file "my.ini", in my case is in C:\AppServ\mysql in this file removes the "#" of the line 

#default-storage-engine=INNODB -> default-storage-engine=INNODB

and the line skip-innodb add "#"

skip-innodb -> #skip-innodb

Third: save and restart mysql service, and the problem was solved.

Upvotes: 1

Emil Vikstr&#246;m
Emil Vikstr&#246;m

Reputation: 91902

SET autocommit=0;
BEGIN;
.
.
.
ROLLBACK;

START TRANSACTION, COMMIT, and ROLLBACK Syntax

Upvotes: 2

YOU
YOU

Reputation: 123791

By default, MySQL starts the session
for each new connection with
autocommit enabled,

You could set autocommit mode disable before running your query

SET autocommit=0;

ref:

Link 1

Link 2

Upvotes: 6

user336253
user336253

Reputation: 31

Make sure you already do command

start transaction;

before the query delete.

Upvotes: 2

Related Questions