MySQL affairs in a detailed

MySQL transaction is mainly used to deal with large-scale and high-complexity data. For example, in a banking system, to add an account, you need to add a basic account information, but also to add information related to the account, such as phone calls, etc., these database operation statements constitute a transaction.

First, the concept and characteristics of business

• In MySQL, only InnoDB is used.
  The Database Engine database or table does not support transactions. (MySQL5.7 uses the InnoDB storage engine by default).
• 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.
• At the default settings of the MySQL command line, transactions are automatically committed, that is, a commit is executed immediately after the SQL statement is executed. Therefore, to open a transaction, you must use the command BEGIN or execute the command SET autocommit=0 to prohibit the use of autocommit for the current session.

In general, a transaction must meet 4 conditions (ACID): atomicity (atomicity, or indivisibility), consistency (consistency), isolation (isolation, also known as independence)

(1) Atomicity: All operations in a transaction (transaction) are either completed or not completed, and do not end up in the middle of a link. 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.

(2) 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.

(3) Isolation: The database allows multiple concurrent transactions to read and write their data at the same time and the ability to modify, isolation can prevent multiple transactions concurrent execution due to cross-execution caused inconsistent data. Transaction isolation is divided into different levels, including read UNCOMMITTED, Read Committed, REPEATABLE READ (repeatable Read), and serialization (Serializable).

(4) Persistence: After transaction processing, the data modification is permanent, even if the system failure is not lost. Once a transaction is committed, the result of the transaction is permanently persisted to the database.

Second, the transaction control statement:

Begin: Open a transaction, followed by a number of database operation statement execution;

Commit: Commits the transaction and makes all modifications made to the database permanent;

ROLLBACK: 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: Used to set the isolation level of a 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

Iv. Transaction Testing

1. Creating databases and Tables

2. Insert 2 data in data table info, start the transaction with begin and commit.

(1) Without committing commits, the data can be queried in the current transaction, but the data will not appear in the data table.

mysql> begin;   #开始事务Query OK, 0 rows affected (0.00 sec)mysql> insert into info (id) values(3);Query OK, 1 row affected (0.01 sec)mysql> insert into info (id) values(4);Query OK, 1 row affected (0.00 sec)mysql> select * from info;     #在当前事务中能查询到数据+------+| id   |+------+|    3 ||    4 |+------+2 rows in set (0.00 sec)

mysql> exit;        #退出mysql连接Bye[[email protected] ~]# mysql -u root -p  #重新进mysql数据Enter password: ..........       #省略部分信息mysql> use test;Database changedmysql> select * from info;  

(2) The data appears in the data table after commit commits the transaction

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> insert into info (id) values(3);Query OK, 1 row affected (0.00 sec)mysql> insert into info (id) values(4);Query OK, 1 row affected (0.00 sec)mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from info;   +------+| id   |+------+|    3 ||    4 |+------+2 rows in set (0.00 sec)

mysql> exit;        #退出mysql连接Bye[[email protected] ~]# mysql -u root -p  #重新进mysql数据Enter password: ..........       #省略部分信息mysql> use test;Database changedmysql> select * from info;     #查看commit提交的数据表+------+| id   |+------+|    3 ||    

3. After inserting a piece of data, use rollback to revert to the state of the pre-insert data.

mysql> begin;            #开始事务Query OK, 0 rows affected (0.00 sec)mysql> insert into info (id) values (5);  #插入一条数据Query OK, 1 row affected (0.00 sec)mysql> rollback;                        #回滚Query OK, 0 rows affected (0.00 sec)mysql> select * from info;       #因为回滚所以数据没有插入+------+| id   |+------+|    3 ||    

4. Insert a piece of data, use SavePoint to define the rollback point, and when the error occurs, rollback can decide where to roll back to, and then commit with the commit command

mysql> begin;     #开始事务Query OK, 0 rows affected (0.00 sec)mysql> insert into info (id) values (5);Query OK, 1 row affected (0.01 sec)mysql> savepoint s1;           #定义回滚点s1Query OK, 0 rows affected (0.00 sec)mysql> insert into info (id) values (6);  #误操作Query OK, 1 row affected (0.00 sec)mysql> savepoint s2;           #定义回滚点s2Query OK, 0 rows affected (0.00 sec)mysql> rollback to savepoint s1;    #回滚到s1Query OK, 0 rows affected (0.00 sec)mysql> select * from info;+------+| id   |+------+|    3 ||    4 ||    

SET autocommit=0 is equivalent to BEGIN, and the test method is the same as above.

MySQL affairs in a detailed