Jump to content
David Beroff

[CLOSED] Atomic transactions in MySQL using InnoDB

Recommended Posts

I've now read at least three tutorials, plus the manual page itself, and still can't get a straight answer.  I'd think that my case would be somewhat common, and yet I can't seem to find an answer, so I'd appreciate your help.

 

I have a set of MySQL statements that I'd like to have either all or none occur.  Specifically, there's one statement that inserts a set of rows which will sometimes represent an attempt to insert a duplicate key.  If (at least) one duplicate key is attempted, I want none of the rows to be inserted within that one statement.  More generally, if any error occurs, I want none of the statements to occur; the entire transaction should be rolled back.

 

I'd rather not have to explicitly sense for an error condition and then manually ROLLBACK; I thought the entire point of transactions was to have that happen automatically.

 

Here's sample code.  My goal is to only have three rows at the end, no more.

CREATE TABLE AllUnique (OnlyColumn VARCHAR(20) PRIMARY KEY) ENGINE = InnoDB;
INSERT INTO AllUnique (OnlyColumn) VALUES ("apple"), ("banana"), ("carrot");

START TRANSACTION;
SET autocommit = 0;        -- Does this even do anything here?
INSERT INTO AllUnique (OnlyColumn) VALUES ("first insert");
INSERT INTO AllUnique (OnlyColumn) VALUES ("tomato"), ("banana");
INSERT INTO AllUnique (OnlyColumn) VALUES ("last insert");
SET autocommit = 1;        -- Does this even do anything here?
COMMIT;

SELECT * FROM AllUnique;
DROP TABLE AllUnique;

Thanks in advance!

Share this post


Link to post
Share on other sites

SEOPress WordPress SEO plugin

Found it! :D

 

The DECLARE EXIT HANDLER line below delivers my expected behavior.  Hat tip to Hammad Khan!

DELIMITER |

CREATE PROCEDURE AllOrNone()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;
INSERT INTO AllUnique (OnlyColumn) VALUES ("first insert");
INSERT INTO AllUnique (OnlyColumn) VALUES ("tomato", "banana");
INSERT INTO AllUnique (OnlyColumn) VALUES ("last insert");
COMMIT;
END |

DELIMITER ;

CREATE TABLE AllUnique (OnlyColumn VARCHAR(20) PRIMARY KEY) ENGINE = InnoDB;
INSERT INTO AllUnique (OnlyColumn) VALUES ("apple"), ("banana"), ("carrot");

CALL AllOrNone();

SELECT * FROM AllUnique;
DROP PROCEDURE AllOrNone;
DROP TABLE AllUnique;

Share this post


Link to post
Share on other sites

I'm going to be honest, I know you can do all kinds of wizardry with MySQL, but everything I've ever personally worked with uses SQL exclusively as a data storage medium and almost all logic is expressed in the software and not in the database.

Share this post


Link to post
Share on other sites

I'm going to be honest, I know you can do all kinds of wizardry with MySQL, but everything I've ever personally worked with uses SQL exclusively as a data storage medium and almost all logic is expressed in the software and not in the database.

 

And that's fine.  I (obviously) lean in the opposite direction.  I'm not going to argue as to who has the "better" favorite color.

 

All I'll say is that it feels better to me to put the investment in up front, (and it isn't even that much work, once you get used to it), and then it seems that everything simply "flows" more easily when the database server is doing most of the heavy lifting.  One quick example might be when I find an ad hoc need to "hop in" with phpMyAdmin or MySQL Workbench, (instead of the website code), and all of the business rules are still in force.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.