4
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.

sennett
  • 8,014
  • 9
  • 46
  • 69
  • All I can find are error handlers for use in stored procedures (http://stackoverflow.com/a/19908197/614523), which is not what I am looking for. Is there a way I can check the last error of the query in the script? – sennett Apr 24 '15 at 10:07
  • I'm not clear what you want to do. However, check the following for if it is useful: [13.6.7.3 GET DIAGNOSTICS Syntax](http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html), [13.6.7.4 RESIGNAL Syntax](http://dev.mysql.com/doc/refman/5.6/en/resignal.html) and [13.6.7.5 SIGNAL Syntax](http://dev.mysql.com/doc/refman/5.6/en/signal.html). – wchiquito Apr 24 '15 at 14:11

2 Answers2

2

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;
Bartłomiej Wach
  • 1,968
  • 1
  • 11
  • 17
  • @wchiquito: Nice quote, but link after has wrong domain – Mark K Cowan Jul 10 '16 at 15:19
  • 6
    Thanks @MarkKCowan. Link updated. The documentation also says: `To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement. With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.` - [13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax](https://dev.mysql.com/doc/refman/5.6/en/commit.html). – wchiquito Jul 11 '16 at 07:45
1

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.

Crater
  • 349
  • 4
  • 5