Let’s talk in depth about transaction features and implementation principles in MySQL

This article will talk about the transaction features in MySQL and introduce the implementation principle of multi-version concurrency control MVCC. I hope it will be helpful to everyone!

1. Concept

Transactiongenerally refers to a logical set of operations , or as a series of operations executed by a single logical unit. All operations in a transaction will be encapsulated into an indivisible execution unit. All operations in this unit will either all execute successfully or all fail, as long as any one of the operations is executed. If it fails, the entire transaction will be rolled back.

2. Introduction to transaction characteristics and types

2.1 Transaction characteristics

Atomicity(atomicity)

The atomicity of a transaction means that all operations that make up the transaction either all execute successfully or all fail.

Consistency (consistency)

The consistency of a transaction means that the data is always in a consistent state before and after the transaction is executed.

Isolation

The isolation of a transaction refers to the non-interference between two concurrently executed transactions, that is to say, the execution of a transaction During the process, you cannot see the intermediate status of other transaction running processes.

?Note:MySQLensures the isolation of transactions by locking aMVCCmechanism.

Persistence (duration)

The durability of a transaction refers to the change operation of this transaction to the data once the transaction is committed. is persisted to the database and will not be rolled back.

2.2 Introduction to two transaction types

• Local transaction
• Distributed transaction

## Local transactions

Transactions usually controlled based on relational databases can be called traditional transactions or local transactions.

Local transaction execution process

• Before the client starts the transaction operation, it needs to be enabled A connection reply;

• After starting the reply, the client initiates an instruction to start the transaction;

• After the transaction starts, the client sends various SQL Statement processing data;

• Under normal circumstances, the client will initiate a transaction commit command. If an exception occurs, the client will initiate a rollback transaction command;

• After the above process is completed, close the session.

#✔Local transactions are managed locally by the resource manager.

The disadvantage of local transactions is:

Does not have the ability to process distributed transactions
• One transaction process can only connect A database that supports transactions cannot be used with multiple transactional databases.

3. What problems will concurrent transactions bring?

Update loss (dirty write)

For example:

That is to say: the later submitted transaction 2 overwrites the update operation of transaction 1, this It is the so-called update loss. Update loss (dirty write) is essentially a conflict of write operations. However, the way to solve dirty writes is to execute each transaction serially to ensure that transactions execute write operations in a certain order.

Dirty reading

One transaction reads uncommitted data from another transaction. For example: Transaction 1 is adding 100 yuan to Zhang San's balance. Before this transaction is submitted, another transaction 2 reads the data being modified. If there is no transaction under the control, the second transaction will read it. The dirty data that has not been committed will be detected, and the next step of processing the dirty data cluster will generate a dependency on the uncommitted data. This phenomenon is usually calledDirty Reading, that is to say:Dirty reading is when one transaction reads data that has not been submitted by another transaction.

?Dirty reading is essentially a conflict between read and write operations. The solution is to write first and then read, that is, read after writing.

Non-repeatable read

A transaction reads certain data. After a period of time, the transaction reads the previously read data again. When it is found that the read data has changed, or some of the data records have been deleted, this phenomenon is called: non-repeatable read,means the same transaction, using the same query statement, is read at different times The results obtained are inconsistent.

Non-repeatable read is essentially a conflict between read and write operations. The solution is to read first and then write, that is, write after reading.

Phantom reading

A transaction re-reads the previously read data according to the same query conditions. At this time, it is found that other transactions have inserted data that satisfies the current query conditions. With new data, the data result set becomes larger. This phenomenon is called phantom reading, that is, a transaction reads a range of data records twice, and the results read twice are different.

Phantom reading is essentially a conflict between read and write operations. The solution is to read first and then write, that is, write after reading.

At this point, many friends have questions. The same essence is a conflict between read and write operations. The solution is to read first and then write. What is the difference between non-repeatable reading and phantom reading?, I See below for a brief explanation:

• 1️⃣ The focus of non-repeatable reading is update and delete operations, while the focus of phantom reading is insertion operations.

• 2️⃣ When MySQL uses the lock mechanism to implement transaction isolation levels, in the repeatable read isolation level, after the first SQL statement reads data, the corresponding data will be locked. , so that other transactions cannot modify and delete these data, and repeatable reading is achieved through locking. However, this method cannot lock the insertion of new data. If transaction 1 reads the data, or modifies or deletes the data, transaction 2 can also perform an insertion operation, causing transaction 1 to inexplicably add an additional piece of data that was not available before. This It's phantom reading.

• 3️⃣ Therefore, phantom reads cannot be avoided through the lock mechanism. A serialized transaction isolation level needs to be used, but this transaction isolation level will greatly reduce the concurrency capability of the database.

✔MySQL uses the MVCC (Multiple Version Concurrency Control) mechanism to avoid non-repeatable reads and phantom reads.

4. MySQL transaction isolation level

Use the following command to query global-level and session-level transactions Isolation level:

# 默认数据库的事务隔离级别为：可重复读（REPEATABLE-READ） SELECT @@global.tx_isolation; SELECT @@session.tx_isolation; SELECT @@tx_isolation;

#5. Multi-Version Concurrency Control MVCC

##Multi-Version Concurrency Control

Multi-version concurrency control,MVCCis a concurrency control method, generally used in database management systems to achieve concurrent access to the database.

You can think of

as a compromise of row-level locks, which avoids Instead of using locks, it can provide smaller overhead. Depending on the implementation, it can allow non-blocking reads, locking only necessary records while writes occur.

MVCC的实现原理

-- 事务A操作 START TRANSACTION; SELECT * FROM account WHERE id = 1; //在事务B提交之前执行 SELECT * FROM account WHERE id = 1; //在事务B提交之后执行 COMMIT;

-- 事务B操作 START TRANSACTION; UPDATE account SET balance = balance+100 WHERE id = 1; COMMIT;

INSERT INTO account(id, name, balance) values(1001, 'austin', 100);

UPDATE account SET balance = balance+100 WHERE id = 1001;

DELETE FROM account WHERE id = 1001;