High-performance MySQL-detailed explanation of transactions and isolation levels

This article mainly includes the following contents:
(1) The concept of transaction and ACID
(2) Transaction isolation level
(3) Transactions in MySQL

1. Transactions and ACID

Understanding transactions is the basis for other advanced concepts.

Transaction: A transaction is a set of atomic SQLqueries, or an independent execution unit. Either all succeed or all fail. If it fails, return Roll to the state before the transaction.

Let’s understand the concepts of ACID in the database: atomicity, consistency, isolation, and durability.
(1) Atomicity: The operations in a transaction are an indivisible overall unit, either all are done or none are done.

(2) Consistency: The database must be in a consistent state before and after transaction execution.

(3) Isolation: Generally speaking, modifications made by one thing are invisible to other transactions before final submission. This involves the issue of transaction isolation level.

(4) Durability: Once the transaction is submitted, the modification is permanent and will not be affected even if the server goes down.

In order to better understand ACID, let’s take a bank account transfer as an example:

-- 开始事务START TRANSACTION;
-- 查询支票账户余额+
SELECT balance FROM checking WHERE customer_id = 10233276;+
-- 将支票账户减去200UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
-- 将余额账户增加200UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
-- 提交事务更新COMMIT;

Atomicity: Either fully commit (the checking balance of 10233276 is reduced by 200, and the balance of savings is increased by 200) , or roll back completely (the balances of both tables will not change)

Consistency: The consistency of this example is reflected in the fact that 200 yuan will not be because the database system runs after the 3rd row and before the 4th row. Sometimes it crashes and disappears because the thing hasn't been committed yet.

Isolation: The operation statements in one transaction are allowed to be isolated from the statements of other transactions. For example, transaction A runs after line 3 and before line 4, and transaction B queries the checking balance at this time. , it can still see the 200 yuan that was subtracted in transaction A (the account money remains unchanged), because transactions A and B are isolated from each other. Before transaction A commits, transaction B cannot observe the data changes.

Persistence: This is easy to understand, that is, the modification is permanent after the transaction is submitted.

Transactions, like locks, require a lot of work, so you can decide whether you need transaction support and choose different storage engines based on your own needs.

2. Transaction isolation level

SQL defines four isolation levels to limit which data within a transaction is visible. It is obvious that low-level isolation levels have higher concurrency and smaller system overhead, but with them come data security issues.

Read Uncommitted (uncommitted read)

At this isolation level, all transactions can see the execution results of other uncommitted transactions. Reading uncommitted data is also called dirty read. This level is rarely used.

Read Committed

This is the default isolation level for most database systems (but not the MySQL default). It meets the simple definition of isolation: a transaction can only see changes made by committed transactions. In other words, it is invisible to other transactions before the transaction is committed. This isolation level also supports nonrepeatable read, because other instances of the same transaction may have new commits during the processing of this instance, so the same select query may return different results.

Repeatable Read

This is the default transaction isolation level of MySQL, It ensures that multiple instances of the same transaction can be processed concurrently When reading the data, you will see the same rows of data. However, in theory, this will lead to another thorny problem: phantom read (Phantom Read). Simply put, phantom reading means that when the user reads a certain range of data rows, another transaction inserts a new row in the range. When the user reads the data rows in the range again, he will find that there are new " Phantom” OK. InnoDB and Falcon storage engines solve this problem through the multiversion concurrency control (MVCC, Multiversion Concurrency Control) mechanism.

Serializable

This is the highest isolation level. It forces transactions to be executed serially, making it impossible for them to conflict with each other, thus Solve the phantom reading problem. In other words, it adds a shared lock on each data row read. At this level, a lot of timeouts and lock contention can result.

The problems that may arise from implementing these four isolation sectors in MySQL are as follows:

3. Transactions in MySQL

The default in MySQL is to adopt the automatic commit mode (AutoCommit), which means that as long as a transaction is not explicitly started, each query operation is treated as a transaction and executed as a commit operation.

We can enable or disable automatic submission mode by setting the AUTOCOMMIT variable.
Setting 1 means enabling AUTOCOMMIT, 0 means disabling AUTOCOMMIT.

This article mainly includes the following contents:
(1) The concept of transaction and ACID
(2) Transaction isolation level
(3) Transactions in MySQL

The above is the detailed content of High-performance MySQL-detailed explanation of transactions and isolation levels. For more information, please follow other related articles on the PHP Chinese website!