MySQL transaction from the Novice tutorial to speak of the clear

MySQL Transaction

MySQL transaction is mainly used to deal with large-scale and high-complexity data. For example, in the Personnel Management system, you delete a person, you need to delete the basic information of the person, but also to delete the information related to the person, such as mailbox, articles and so on, so that these database operation statements constitute a transaction!

• In MySQL, transactions are supported only by databases or tables that use the INNODB database engine.
• Transactions can be used to maintain the integrity of the database, to ensure that batches of SQL statements are either fully executed or not executed at all.
• Transactions are used to manage insert,update,delete statements

In general, transactions are required to meet 4 conditions (ACID): atomicity (Atomicity, or indivisibility), consistency (Consistency), Isolation (Isolation, Also known as independence), persistence (Durability).

• atomicity: All operations in a transaction (transaction) are either complete or not complete and do not end up in the middle of a session. When an error occurs during execution, the transaction is rolled back (Rollback) to the state before the transaction begins, as if the transaction had never been executed.

• Consistency: The integrity of the database is not compromised until the transaction begins and after the transaction has ended. This means that the data being written must fully conform to all of the preset rules, which include the accuracy of the data, the concatenation, and the subsequent database's ability to perform the scheduled work spontaneously.

• Isolation: The ability of a database to read and write and modify its data at the same time for multiple concurrent transactions, which prevents inconsistencies in data resulting from cross-execution when multiple transactions are executing concurrently. Transaction isolation is divided into different levels, including read UNCOMMITTED, Read Committed, REPEATABLE READ (repeatable Read), and serialization (Serializable).

• Persistence: After the transaction is finished, modifications to the data are permanent, even if the system failure is not lost.

at the default settings of the MySQL command line, transactions are automatically committed. The COMMIT operation is executed immediately after the SQL statement is executed. Therefore, to explicitly open a transaction, it is imperative to use the command BEGIN or START TRANSACTION, or execute command SET autocommit=0, to prohibit the use of autocommit for the current session.

Transaction control Statements:

• Begin or start TRANSACTION; explicitly open a transaction;

• Commit, or you can use commit work, but the two are equivalent. Commit commits a transaction and makes all modifications made to the database permanent;

• ROLLBACK; ROLLBACK work can be used, but the two are equivalent. Rollback will end the user's transaction and revoke any uncommitted modifications that are in progress;

• SavePoint Identifier;savepoint allows you to create a savepoint in a transaction that can have multiple savepoint in a transaction;

• RELEASE savepoint identifier; Deletes the savepoint of a transaction, and when there is no savepoint specified, the execution of the statement throws an exception;

• ROLLBACK to identifier; Roll the transaction back to the Mark Point;

• Set TRANSACTION; Sets the isolation level for the transaction. The InnoDB storage engine provides transaction isolation levels of READ UNCOMMITTED, read COMMITTED, Repeatable read, and serializable.

There are two main methods of MYSQL transaction processing:

1, with BEGIN, ROLLBACK, commit to achieve

• begin a transaction
• ROLLBACK Transaction rollback
• Commit Transaction acknowledgement

2, directly with SET to change the MySQL automatic submission mode:

• SET autocommit=0 prohibit auto-commit
• SET autocommit=1 turn on auto-commit

Transaction testing

Mysql>Use runoob;database changedmysql> CREATE TABLE runoob_transaction_test (IDint(5)) engine=InnoDB; # Create a data table query OK,0Rows Affected (0.04sec) MySQL>Select* fromrunoob_transaction_test; EmptySet(0.01sec) MySQL>begin; # Start transaction query OK,0Rows Affected (0.00sec) MySQL> INSERT into Runoob_transaction_test value (5); Query OK,1Rows Affected (0.01sec) MySQL> INSERT into Runoob_transaction_test value (6); Query OK,1Rows Affected (0.00sec) MySQL>Commit a transaction query OK0Rows Affected (0.01sec) MySQL>Select* fromrunoob_transaction_test;+------+| ID |+------+|5||6|+------+2Rowsinch Set(0.01sec) MySQL>begin; # Start transaction query OK,0Rows Affected (0.00sec) MySQL> INSERT into runoob_transaction_test values (7); Query OK,1Rows Affected (0.00sec) MySQL>rollback; # Roll back the query OK,0Rows Affected (0.00sec) MySQL>Select* fromrunoob_transaction_test; # because the rollback so the data is not inserted+------+| ID |+------+|5||6|+------+2Rowsinch Set(0.01Sec

MySQL transaction from the Novice tutorial to speak of the clear