MySQL's transaction and isolation levels in a detailed

A transaction in MySQL

A transaction is an execution unit in a DBMS that is composed of a limited sequence of database operations. However, not all database operation sequences can become transactions. In general, a transaction has the following 4 characteristics (acid characteristics):

1. atomicity (automicity): Database operations caused by this attribute are "either all executed or not executed at all."

2. Consistency (consistency): This attribute indicates that the final state of the database operation before and after the operation is consistent. For example, two users, a, B, transfer money to each other, but the total amount of the final two users is constant.

3. Isolation (Isolation): When multiple transactions are executing concurrently, each transaction is executed independently, with minimal impact between the various transactions.

4. Persistence (Durability): Once the transaction commit execution succeeds, the system guarantees that the transaction will not cause inconsistencies under any failures.

The concept of dirty read, non-repeatable read, and phantom reading in MySQL

Dirty reads: In the current transaction under the Read-uncommitted isolation level, you can read data that has not yet been committed by other transactions.

Non-repeatable read (non-repeatable reads): In the current transaction, and not committed, and other transactions when modifying related data, the results of two queries are different, called non-repeatable read.

Phantom read: The result of the current transaction is not the same as after uncommitted and committed. It feels like a phantom.

The isolation level in MySQL is divided into 4 types:

1, read-uncommited: Read not submitted. The current transaction under this isolation level can see the execution results of other uncommitted transactions. Dirty reads occur at this level.

2, read-committed: Read submit. Resolves the problem of dirty reads. Under this isolation level, the current transaction can see only the execution results after the other transactions have been committed. Therefore, this isolation level supports non-repeatable reads. The default isolation level for most databases is read-committed, but MySQL is not.

3, Repeatable-read: repeat read. Resolves a non-repeatable read problem. At this level and the current transaction is not committed, the result of each query is the same regardless of how the other transaction modifies the data. Once the current transaction commits, the result of the query is the most recent result. Therefore, the phenomenon of phantom reading will occur. The results are different before and after the transaction is committed. At this level, the MVCC (multi-version concurrency control) mechanism is needed to improve concurrency capabilities. This is the default isolation level for MySQL.

4, Serializable: can be serialized. is a serial execution of a transaction followed by one, and only another transaction under the same instance can be executed after the end of the other transaction under the same instance. This solves the problem of Phantom reading.

Summary: The four default isolation levels for MySQL are read as follows:

1, read-uncommitted: There will be dirty reading, non-repeatable reading and other phenomena.

2, read-committed: There will be non-repeatable phenomenon.

3, Repeatable-read: There will be phantom reading phenomenon.

4, Serializable: There will be no dirty reading, non-repeatable reading, such as Phantom reading phenomenon.

Thus, the higher the isolation level, the less interference, the more resources consumed by the system. And most importantly, the worse their concurrency performance is.

MySQL handles transactions by 2 ways:

1. Perform the transaction manually

mysql>begin;   or mysql>start transaction; # #表示启动事务

mysql>rollback; # #撤销之前所做的修改 (rollback)

mysql>commit; # #提交事务

2, set the automatic commit mode of the transaction

Mysql>select @ @autocommit; # #查看当前事务是否是自动提交的. 0 or off to turn off autocommit, 1 or on to turn on auto-commit

Set Autocommit=0 # #关闭自动提交功能 (only for the current session, which is the modified session variable)

Set Autocommit=1 # #开启自动提交功能 (only for the current session, which is the modified session variable)

Settings for the isolation level in MySQL:

Mysql>select @ @tx_isolation; # #显示当前的隔离级别

Mysql>set tx_isolation= ' VALUE ' # #用来设定隔离级别

Execution status of a transaction: 5 states in total

1. Active: Indicates that the current transaction is in progress

2. Partially submitted: Indicates that the statement was executed only partially during execution, for some reason (such as downtime). Therefore, the transaction is in this state.

3. Failure: Indicates that the transaction did not execute successfully.

4. Termination: Because the transaction execution failed, the system terminates the transaction

5. Successful submission: After the commit, the transaction executes successfully.

This article is from the "Linux Learning path" blog, so be sure to keep this source http://xslwahaha.blog.51cto.com/4738972/1581558

MySQL's transaction and isolation levels in a detailed