Rollback not working in MySQL

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

I deleted two records, then execut开发者_JAVA技巧ed the rollback command, it executed successfully.

But that deleted two records not recovered.

The user table engine is InnoDB.

You should be able to rollback your transaction as the table engine is InnoDB.

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.

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

Make sure you already do command

start transaction;  

before the query delete.

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

START TRANSACTION, COMMIT, and ROLLBACK Syntax

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;

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.

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.

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.

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

SET autocommit = 0;
start transaction;
begin;
-- DML query goes here
rollback;
SET autocommit = 1;

But

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.