I wish to run an update query against a production database so that as good little developer I'm attempting to make it as being safe as you possibly can. I'm searching to complete the next

BEGIN TRANSACTION
    UPDATE table_x SET col_y = 'some_value'
    .
    .
    .
IF (@@error <> 0)
BEGIN
    ROLLBACK
END
ELSE
BEGIN
    COMMIT
END

The above mentioned should operate in SQL Server however i need this to operate against a MySQL database.

EDIT: Sorry, there's a lot more than 1 statement to complete. Yes I know of not requiring to wrap just one query inside a transaction.

CREATE PROCEDURE prc_test()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
  END;
  START TRANSACTION;
    INSERT
    INTO t_test VALUES ('test', 'test');
    INSERT
    INTO no_such_table
    VALUES ('no');
  COMMIT;
END;

CALL prc_test();

SELECT *
FROM t_test;

0 rows fetched.

I do not think this really is necessary as there's the idea of implicit commit/rollback.

From MySQL docs:

Automatically, MySQL begins the session for every new reference to autocommit mode enabled, so MySQL does a commit after each SQL statement if that statement didn't return an error. If your statement returns an error, the commit or rollback behavior is dependent around the error. See Section 13.6.13, “InnoDB Error Handling”.

BEGIN;
UPDATE foo SET bar = 3;
UPDATE bar SET thing = 5;
COMMIT;

If the error happens, the whole transaction is going to be folded back instantly. You actually only have to execute ROLLBACK if something inside your application signifies the necessity to rollback.


You'll be able to handle errors clearly within methods or compound claims in MySQL, however i wouldn't recommend heading down this route. Check this out how-to article and also the docs for DECLARE HANDLER. You'll also need to find the specific error code you want to handle, or make use of the general SQLEXCEPTION condition. You'll should also review compound statements and defining stored programs.

Anyway, in line with the paperwork, you could do this something similar to the query below, however it honestly wouldn't do anything whatsoever in a different way than these answer above. It might also enable you to get very strange looks from anybody who uses MySQL (including myself).

BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
  START TRANSACTION;
    UPDATE foo SET bar = 3;
    UPDATE bar SET thing = 5;
  COMMIT;
END;


Old answer:

If you are performing just one query, this does not seem sensible. Simply execute the query if the error happens, there is little happen - your transaction will instantly be folded back.

This is because, automatically, all single queries are covered with a "hidden" transaction, known as "autocommit" mode in MySQL. The normal alternative would be to clearly use transactions - when you execute "BEGIN" you've began a transaction. When you either COMMIT or ROLLBACK you will be in autocommit mode.

So, the only real reason to utilize a transaction in MySQL is if you wish to rollback to particular condition when a mistake (as well as other exterior event) happens. In MySQL, a transaction is definitely aborted if the error happens.

Lastly, you'll be able to turn this behavior off entirely, and you must use transactions clearly whatsoever occasions. In my opinion "BEGIN" is implied from the time you last committed or folded back, however, you must either COMMIT or ROLLBACK questions you take.

See The InnoDB Transaction Model within the MySQL manual for more information.