CODEWITHSUNDEEP
mysqltransactionssql-updateinnodbsql-insert
Leandro Bardelli
Leandro Bardelli

Reputation: 11578

MySQL which is the method to rollback a transaction effective?

I saw in multiple answers here and on google that rollback a transaction implies only the rollback of the last command, and i read also that implies ALL commands. (neither both documented or referenced by)

That I need to do is create a store procedure that insert/update on table A, get the last ID of A, insert that ID into B, get the last id of B, insert it into C, etc, etc, etc.

I want to know which is the method to commit or rollback all commands in the transaction, in order to start the transaction and if something fails, get back everything as the original.

SQL code with IF error and last_id will be preciated, because also I saw a LOT of differents ways to get the last id and I don't know which is better.

By the way, all tables are InnoDB

Kind regards,

Upvotes: 0

Views: 189

Answers (2)

tadman
tadman

Reputation: 211540

If you BEGIN a transaction then nothing will get applied until you COMMIT it. Dropping your connection or issuing a ROLLBACK is the same as never committing it.

This is, of course, presuming you have autocommit set on, which is usually the case.

You can roll-back individual commands if you wrap them as transactions as well.

More information is available in the documentation.

Keep in mind that MyISAM and other engines do not support transactions, where InnoDB does. Further, only INSERT, UPDATE, DELETE and REPLACE statements are able to be rolled back. Other things, like alterations to the schema, are not.

Upvotes: 1

eggyal
eggyal

Reputation: 125835

As documented under START TRANSACTION, COMMIT, and ROLLBACK Syntax:

These statements provide control over use of transactions:

[ deletia ]

  • ROLLBACK rolls back the current transaction, canceling its changes.

Upvotes: 1

Related Questions