MySQL's transaction traps and art

The author explains the merits of the transaction in detail in the previous article, "MySQL transaction and why it can't be copied in PHP," and introduces some simple SQL commands to make the application more robust. But in the life of the Web programmer there is not many things that seem so simple ....

Cannot roll back the statement (statements you can ' t ROLLBACK)

It's a pity to inform you that not all database operations support rollback (ROLLBACK). If you change the database/table structure (schema), all current transactions are committed, and the upgrade (alteration) will run in its own unique transaction (not belonging to any client transaction). 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 undo fundamental changes to the database, such as:

START TRANSACTION; DROP TABLE MyImportantData;-- 所有事务会被强制提交, existing (empty) transaction is COMMIT-ed-- 然后该表就被永久地删除了(table is dropped permanently)ROLLBACK;-- 没机会了,数据已经不要你了. no chance, mate - your data‘s gone

Hint: Temp table (Temporary)

creating, upgrading, and deleting a temporary table (create, ALTER, and DROP) does not cause an implicit commit (implicit commit.). Of course, these operations cannot be rolled back.

Save Point (SavePoint)

We are deeply responsible for the anomaly, so let's take a look at another beautifully designed section. The save point (savepoint) is a valid named location in a transaction. You can roll back to a save point without affecting the SQL update before pity Dorado ... A bit like the history panel in Photoshop.

The simplest way, let's look at an example:

START TRANSACTION;-- 增加 tableA 的记录INSERT INTO tableA VALUES (1,2,3);-- 创建保存点 tableAupdatedSAVEPOINT tableAupdated;-- 增加 tableB 的记录INSERT INTO tableB VALUES (4,5,6);-- 反正发生了些什么不愉快的事,要取消对 tableB 所做的更新...ROLLBACK TO tableAupdated;-- 这时候提交,就只有 tableA 被更新了COMMIT;

Of course, you can also set multiple savepoint identifiers (SavePoint identifiers) and roll back to any place in a transaction.

You can also delete a savepoint with the following syntax:

RELEASE SAVEPOINT savepointName;

All savepoint are deleted as long as the transaction commits or (the whole) is rolled back.

The use of transactions and SavePoint is very simple and can effectively protect the important data in InnoDB. Do you have any reason to insist on using MyISAM, now MyISAM efficiency is not as InnoDB.

Note: Would you like to subscribe for more information?

You can subscribe to the Tech Times weekly Tech geek newsletter.

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

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

Author: Anchor Http://blog.csdn.net/renfufei

Date: June 29, 2015

MySQL's transaction traps and art