CODEWITHSUNDEEP
mysqltransactionsrollback
sennett
sennett

Reputation: 8424

How can I rollback a transaction on error in MySQL?

update my_table set limit_id = 2 where id='176846';

start transaction;
update my_table set limit_id = 1 where id='176846';
update my_table set limit_id = 4 where id='176846';  --  <- this one fails
commit;

select limit_id from my_table where id='176846';

I would like to roll this back automatically - I want the script to output 2, not 1. I have no access to the connection policy in use.

Upvotes: 4

Views: 8010

Answers (2)

Crater
Crater

Reputation: 349

It depends on why a limit_id value of 4 causes an error, but MySql does not always roll back the entire transaction. See: http://dev.mysql.com/doc/refman/5.7/en/innodb-error-handling.html for more information, but in several cases, MySql will only implicitly rollback the last statement, then continue with the transaction.

Upvotes: 1

Bartłomiej Wach
Bartłomiej Wach

Reputation: 1986

reading here:

http://dev.mysql.com/doc/refman/5.5/en/commit.html

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. The change cannot be rolled back.

try something like 

SET autocommit = 0;
start transaction;
(...)
commit;

Upvotes: 2

Related Questions