MySQL transaction control flow and ACID characteristics

1. ACID Characteristics

Transaction processing is a management mechanism for MySQL operations that must be executed in batches. During a transaction, unless the entire batch of operations is executed correctly, if any operation in the middle fails, it will be回滾(Rollback) to the original safe state to ensure that no incorrect changes are made to the system data.

After MySQL 5.5, the default storage engine was replaced from MyLSAM to InnoDB. One of the important reasons for this is that InnoDB supports transactions. Let's use SHOW ENGINES to look at the description of various storage engines in MySQL.

The four most important properties of transactions are often referred to as ACID properties:
A - Atomicity 原子性: A transaction is an indivisible minimum unit. All operations in the transaction either succeed or fail, with no intermediate state. Atomicity is mainly achieved through the rollback log (undo log) in the transaction log. When a transaction modifies the database, InnoDB will generate an undo log for the opposite operation based on the operation. For example, for an insert operation, a delete record will be generated. If the transaction fails or rollback is called, it will be restored to the state before execution based on the content of the undo log.

C - Consistency 一致性: The data is in a legal and consistent state before and after the transaction is executed. Even if an exception occurs, the integrity constraints of the database, such as uniqueness constraints, will not be destroyed due to the exception.

I - Isolation 隔離性: Each transaction is independent of each other and will not be affected by the execution of other transactions. Transactions are not visible to other transactions before they are committed. Isolation is defined by the transaction isolation level, and a locking mechanism is used to ensure the isolation of write operations, and MVCC is used to ensure the isolation of read operations, which will be described in detail below.

D - Durability 持久性: The changes to the data after the transaction is committed are durable and will not be lost even if the database crashes. This is ensured by the redo log in the transaction log. Before a transaction is modified, the change information will be pre-written to the redo log. If the database crashes, the records in the redo log will be read to restore the data after recovery.

Transaction Control Syntax

MySQL transaction control has several important nodes, namely transaction start, commit, rollback and save point.

Starting a transaction means that the transaction begins execution, and the statement is START TRANSACTION or BEGIN . Committing a transaction means that all updates in the transaction are written to the physical database on disk. The transaction ends normally with COMMIT . If an exception occurs and a rollback is required, the statement is ROLLBACK . It should be noted that once a transaction has been committed, it cannot be rolled back. Therefore, when an exception is caught during code execution, you need to directly execute rollback instead of commit.

For example, A transfers 100 yuan to B:

// Execute normally and submit BEGIN; # Start transaction UPDATE account_balance SET balance = balance - 100.00 WHERE account_name = 'A';
UPDATE account_balance SET balance = balance + 100.00 WHERE account_name = 'B';
COMMIT; # Commit transaction // Exception occurred, rollback BEGIN; # Start transaction UPDATE account_balance SET balance = balance - 100.00 WHERE account_name = 'A';
UPDATE account_balance SET balance = balance + 100.00 WHERE account_name = 'B';
ROLLBACK; # Transaction rollback

In complex scenarios, sometimes we do not need to roll back the entire operation, but execute it in batches and roll it back to a certain node. This is equivalent to nesting several sub-transactions under a large transaction. In MySQL, SAVEPOINT can be used to achieve this.

BEGIN;
insert into user_tbl (id) values ​​(1);
insert into user_tbl (id) values ​​(2);
ROLLBACK; # 1,2 do not write BEGIN;
insert into user_tbl (id) values ​​(1);
SAVEPOINT s1;
insert into user_tbl (id) values ​​(2);
ROLLBACK TO s1; # Roll back to the retention point s1, so 1 is successfully written, 2 is rolled back, and the final result is 1

RELEASE SAVEPOINT s1; # Release the save point

By the way, there are two types of transactions: implicit transactions (automatically committed) and explicit transactions (must be committed manually). MySQL defaults to implicit transactions, which will be automatically committed and controlled by the autocommit parameter.

# View variables SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
# Enable auto-commit (default)
SET autocommit = 1;
# Turn off autocommit SET autocommit = 0;

In the automatic commit state, if no transaction is explicitly opened, each DML statement is a transaction, and the system automatically executes a commit operation on each SQL statement. After you start a transaction using BEGIN or START TRANSACTION, autocommit remains disabled until you end the transaction using COMMIT or ROLLBACK, at which point the autocommit mode is restored to its previous state.

There is another parameter completion_type， the default value is 0 (NO_CHAIN)

# View variables SHOW VARIABLES LIKE 'completion_type';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| completion_type | NO_CHAIN ​​|
+-----------------+----------+

completion_type = 0: The default value. A new transaction will not be automatically started after commit is executed.
completion_type = 1: When executing commit, it is equivalent to executing COMMIT AND CHAIN, and automatically starts a transaction with the same isolation level.
completion_type = 2: When executing commit, it is equivalent to executing COMMIT AND RELEASE. The server connection is automatically disconnected after the transaction is committed.

3. Transaction Concurrency Exception

In an actual production line environment, there may be large-scale concurrent requests. If the transaction isolation level is not properly set, some abnormal situations may occur. The most common abnormalities are臟讀(Dirty Read)、幻讀(Phantom Read) and不可重復讀(Unrepeatable Read)。

1. Dirty read

Dirty read means that a transaction accesses uncommitted data of another transaction, as follows:

• Assume that the value of a is 1, and transaction 2 changes a to 2. At this time, the transaction has not been committed.
• At this time, transaction 1 reads a and the value of a is 2. Transaction 1 completes the read.
• As a result, transaction 2 rolls back the modification to a (or does not commit), so the value of a changes back to 1.
• This results in the fact that the value of a is 1, but the result obtained by transaction 1 is 2, so transaction 1 reads dirty data and a dirty read occurs.

2. Non-repeatable read

Non-repeatable read refers to the situation where the data value content changes during a transaction reading the same data multiple times, resulting in the inability to read the same value. It describes the phenomenon of update/deletion of the same data , as shown in the following process:

• Transaction 1 reads a, and a = 1
• At this time, transaction 2 modifies a to 2 and is successfully submitted, and the change takes effect.
• Transaction 1 reads a again, and now a = 2
• Transaction 1: The same value is read twice in the same transaction, but the data value content has changed, resulting in a non-repeatable read.

3. Phantom reading

Phantom read refers to the situation where the number of data items changes during a transaction reading the same data multiple times, as if an illusion has occurred. It describes the phenomenon of insert/delete for the entire table, as shown in the following process:

• Transaction 1 reads the quantity for the first time and obtains 10 data items
• At this time, transaction 2 inserts a piece of data and is successfully submitted. The change takes effect and the data becomes 11.
• Transaction 1 reads the number again and gets 11 pieces of data. For transaction 1, there is one more piece of data for no apparent reason, as if it were an illusion. A phantom read occurs.

4. Transaction Isolation Level

Serialized transaction processing is of course the safest method, but serialization cannot meet the needs of high-concurrency access to the database. As a compromise, sometimes we have to lower the isolation standard of the database in exchange for the concurrency of transactions, sacrificing correctness within a controllable range in exchange for improved efficiency. This trade-off is achieved through the isolation level of the transaction.

The database has four transaction isolation levels, from low to high:讀未提交Read Uncommitted ,讀已提交Read Committed ,可重復讀Repeatable Read , and串行化Serializable .

(1) Read Uncommitted
Allows reading of uncommitted content. Queries at this level will not lock, so dirty reads, non-repeatable reads, and phantom reads may occur.
(2) Read Committed
Only committed content is allowed to be read. Dirty reads will not occur in queries at this level because dirty data is uncommitted data and will not be read. However, non-repeatable reads and phantom reads may still occur.
(3) Repeatable Read (MySQL's default isolation level)
Using row-level locks to ensure that the data results obtained by two queries under the same query conditions are consistent can avoid dirty reads and non-repeatable reads, but there is no way to avoid phantom reads.
(4) Serializable
Using table-level locks to ensure the serialization of all transactions can prevent all abnormal situations, but it sacrifices the concurrency of the system.

The tolerance of the above three abnormal situations in the four isolation levels is as follows ( Representatives allow, Represents prohibition):

The command to view the isolation level is:

SHOW VARIABLES LIKE 'transaction_isolation';
# or SELECT @@global.tx_isolation, @@tx_isolation;

The second way is to view the global and current session isolation levels.

The command to set the isolation level is :

# Set the isolation level of the current session to read uncommitted SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
# Set the global isolation level to read uncommitted SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

This is the end of this article about the detailed explanation of MySQL transactions. For more relevant MySQL content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!