Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about what transaction control statements there are in MySQL, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

Transaction SQL control statement

MySQL transactions comply with ACID:

Atomic (atomicity): all statements are executed successfully or cancelled as a unit.

Consistent (consistency): if the database is in a consistent state at the beginning of the transaction, the consistent state is retained during the execution of the transaction.

Isolated (isolation): transactions do not affect each other.

Durable (persistence): after the transaction completes successfully, all changes made are accurately recorded in the database. Changes made will not be lost

1) transaction SQL control statement

START TRANSACTION (or BEGIN): explicitly start a new transaction

SAVEPOINT: allocates a location during a transaction for future reference

COMMIT: permanently records changes made by the current transaction

ROLLBACK: cancels changes made by the current transaction

ROLLBACK TO SAVEPOINT: canceling changes made after savepoint

RELEASE SAVEPOINT: deleting savepoint identifiers

SET AUTOCOMMIT: disable or enable the default autocommit mode for the current connection

2) AUTOCOMMIT mode

How to set the AUTOCOMMIT mode determines how and when to start a new transaction. By default, AUTOCOMMIT is enabled globally, which means that each SQL statement is forced to start a new transaction implicitly. You can disable AUTOCOMMIT globally through an options file, or you can disable it per session by setting the autocommit variable. Enabling AUTOCOMMIT restricts each statement, which in turn affects the transaction table in its own transaction. This effectively prevents multiple statements from being executed in a transaction. This means that you will not be able to submit or roll back multiple statements as a unit through COMMIT or ROLLBACK. Sometimes this situation is mistaken for no transaction at all. However, this is not the case. When AUTOCOMMIT is enabled, each statement is still executed atomically. For example, by comparing the effect of violating constraint restrictions when inserting multiple rows, you can see the difference between enabling AUTOCOMMIT and having no transaction at all. In a non-transactional table, such as MyISAM, once an error occurs, the statement terminates and rows that have been inserted remain in the table. For the InnoDB table, all rows that have been inserted are deleted from the table without any actual impact.

AUTOCOMMIT determines how and when to start a new transaction; by default, AUTOCOMMIT mode is enabled: each statement is implicitly committed as a transaction

If the AUTOCOMMIT mode is set to 0 in my.cnf, or SET GLOBAL AUTOCOMMIT=0;SET SESSION AUTOCOMMIT=0;SET @ @ AUTOCOMMIT: = 0, AUTOCOMMIT is disabled, the transaction spans multiple statements, and you need to end the transaction using COMMIT or ROLLBACK

Use SELECT to check the AUTOCOMMIT settings:

SELECT @ @ AUTOCOMMIT

3) implicit submission

The COMMIT statement always explicitly commits the current transaction. Other transaction control statements, such as those listed in this slide, also have the effect of implicitly committing the current transaction. In addition to these transaction control statements, other types of statements may also have the effect of implicitly committing and terminating the current transaction. These statements behave as if COMMIT were issued before the actual statement was executed. In addition, these statements themselves are not transaction statements, that is, if successful, they cannot be rolled back. Typically, data definition statements, data access and user management statements, and locking statements have this effect.

Note: there are many exceptions, and not all of these statements can lead to implicit commit on all versions of the server. However, it is recommended that all non-DML statements be considered to result in implicit commit. For a complete list of statements that result in implicit submission, see the MySQL reference Manual: http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

An implicit commit terminates the current transaction. SQL statement for implicit submission:

L START TRANSACTION

L SET AUTOCOMMIT = 1

The non-transactional statement that caused the commit:

L data definition statements (ALTER, CREATE, and DROP)

L administrative statements (GRANT, REVOKE, and SET PASSWORD)

L lock statements (LOCK TABLES and UNLOCK TABLES)

Example of a statement that causes an implicit submission:

Mysql > TRUNCATE TABLE

Mysql > LOAD DATA INFILE

4) transaction storage engine

Use SHOW ENGINES to list engine characteristics:

Mysql > SHOW ENGINES\ G

* 2. Row *

Engine: InnoDB

Support: DEFAULT

Comment: Supports transactions, row-level locking

And foreign keys

Transactions: YES

XA: YES

Savepoints: YES

* 1. Row *

Engine: MyISAM

Support: YES

Comment: MyISAM storage engine

Transactions: NO

XA: NO

Savepoints: NO

After reading the above, do you have any further understanding of the transaction control statements in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.