MySQL transaction traps and art, mysql transaction traps

MySQL transaction traps and art, mysql transaction traps

In the previous article "MySQL transactions and why cannot I imitate transactions in PHP", the author elaborated on the advantages of transactions and introduced some simple SQL commands to make the application more robust. But nothing in the web programmer's life journey seems as simple as it is .....

Statement that cannot be rolled back (Statements you can't ROLLBACK)

We are sorry to inform you that not all Database Operations Support ROLLBACK ). If you change the database/table schema, all the current transactions will be committed, and the upgrade (alteration) will run in its unique transactions (not any client transactions ). These statements include:

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• RENAME TABLE
• TRUNCATE TABLE
• CREATE INDEX
• DROP INDEX
• CREATE EVENT
• DROP EVENT
• CREATE FUNCTION
• DROP FUNCTION
• CREATE PROCEDURE
• DROP PROCEDURE

We cannot cancel fundamental changes to the database, such:

Start transaction; drop table MyImportantData; -- all transactions are forcibly committed, and existing (empty) transaction is COMMIT-ed -- the table is permanently deleted (TABLE is dropped permanently) ROLLBACK; -- no chance. You are no longer needed for data. no chance, mate-your data's gone

Tip: TEMPORARY table (TEMPORARY)

Creating, upgrading, and deleting (CREATE, ALTER, and DROP) temporary tables do not cause implicit COMMIT (implicit COMMIT .). Of course, these operations cannot be rolled back.

Savepoint)

We love to take responsibility for exceptions, so let's take a look at another beautiful part of the design. A Savepoint is a valid namespace in a transaction. You can roll back to a save point without affecting the SQL update before the change point... It's a bit like the history panel in Photoshop.

The simplest method is as follows:

Start transaction; -- add the tableA record insert into tableA VALUES (, 3); -- create the storage point tableAupdatedSAVEPOINT tableAupdated; -- add the tableB record insert into tableB VALUES (, 6 ); -- if something unpleasant happens, cancel the update to tableB... rollback to tableAupdated; -- COMMIT at this time, only tableA is updated with COMMIT;

Of course, you can also set multiple SAVEPOINT identifiers and roll back to any place in the transaction.

You can also delete a save point. The syntax is as follows:

RELEASE SAVEPOINT savepointName;

As long as the transaction is committed or rolled back, all the storage points will be deleted.

The use of transactions and storage points is very simple and can effectively protect important data in InnoDB. Why do you insist on using MyISAM? Now MyISAM is not as efficient as InnoDB.

Note: Do you want to subscribe to more information?

You can subscribe to Tech geek newsletter for tech Times every week.

GitHub: https://github.com/cncounter/translation/blob/master/tiemao_2015/17_MySQL_Savepoint/MySQL_Savepoint.md

Link: http://www.sitepoint.com/mysql-transaction-gotchas-good-parts/

Author: Tie an http://blog.csdn.net/renfufei.

Date: January 1, June 29, 2015