MySQL transaction traps and art, mysql transaction traps

Source: Internet
Author: User
Tags savepoint

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.