CODEWITHSUNDEEP
mysqlmysql-workbench
IcedDante
IcedDante

Reputation: 6802

MySQL Workbench: Start Transaction seems to be committed before rollback

I wrote a bunch of delete statements and wrapped them in a transaction:

start transaction;
delete a...
delete b...
delete c...
rollback;

The idea is I would want the deletes to occur inside a transaction which would rollback upon completion. If a step failed along the way, I would want the successful steps to be rolled back as well.

To my chagrin delete a worked, removing a few thousand rows, delete b failed, but when I reran the statements all the records from a appeared to be gone.

Is this because the transaction is still open? I tried doing:

set session transaction isolation level read committed;
select a.*

and got back zero rows so I think that is not the case. Was the successful a delete committed? And if so, how do I prevent that from happening until I can guarantee a full working query?

Upvotes: 3

Views: 9014

Answers (4)

Jacob Akkerboom
Jacob Akkerboom

Reputation: 193

Yes, probably the transaction was still open. rollback is not executed if there is an error in a line above it. So long as the the transaction is not rolled back, it will seem (when using SELECT) that the data has been deleted, but since you have not committed the transaction, you can still use rollback.

Long version

To verify that this answer is correct, you can do the following

start transaction;
delete a...
SELECT @@non_existent_variableXYZ -- This gives an error
rollback;

Now run SELECT * FROM information_schema.INNODB_TRX. You can see the transaction in progress. Then run rollback (in a script that does not give an error, consider creating a new query tab in MySQL Workbench). You will see that the deletion is undone and by running using SELECT again you can see that the transaction is no longer in information_schema.INNODB_TRX.

About other answers

This answer shows that you don't need to set @@autocommit to 0 to be able to group multiple statements into a transaction and to be able to roll it back with rollback.

Upvotes: 1

Phillip
Phillip

Reputation: 31

Try setting the autocommit flag to false:

set @@autocommit = false;
start transaction;
delete a...
delete b...
delete c...
rollback;

Upvotes: 0

IcedDante
IcedDante

Reputation: 6802

Somebody turned me on to, what I think, is a better way to do this:

begin;
<sql transactions>
commit;

Upvotes: 1

Mike Lischke
Mike Lischke

Reputation: 53337

MySQL Workbench enables auto commit by default. In the SQL editor there is a toolbar button that can be used to toggle auto commit at will:

enter image description here

Upvotes: 8

Related Questions