preface

We all know the concept of doing a transaction, that is, a complete operation is either executed or not executed, which is an indivisible unit of work. ACID is also the four characteristics of a transaction. So what exactly is ACID?

The ACID is introduced

Atomicity

A transaction must be considered as an indivisible, minimal unit of work. Even if the entire transaction contains several steps, all operations will either be committed successfully or all operations will fail to be rolled back. For a transaction, it is impossible to perform only part of the operations, such is the atomicity of the transaction.

Consistency

Consistency means that a transaction must move the database from one consistent state to another, that is, a transaction must be in a consistent state both before and after execution.

As long as the new transaction T is not committed, the result is the same whether the result is retrieved from the database at some point before the transaction T is initiated or during the operation.

Isolation

Isolation is when changes made by one transaction are not visible to other transactions until they are finally committed.

InnoDB supports the following isolation levels:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

The default isolation level for InnoDB is REPEATABLE READ.

“Durability”

Once a transaction commits, its changes are permanently stored in the database. Even if the system crashes, the modified data is not lost. (Of course, there are different levels of persistence.)

Isolation Level Introduction

READ UNCOMMITTED

At the READ UNCOMMITTED level, changes in a transaction, even if they are not committed, are visible to other transactions. Transactions can Read uncommitted data, which is called Dirty reads.

time	A transaction	Transaction B
T1	Start the transaction	Start the transaction
T2		Change the balance of account user1 from 100 to 200
T3	Select * from user1 where user1 = 200;
T4		Operation error, transaction rollback
T5	Withdraw 50 from the balance and the balance is changed to 150 (200-50)
T6	Commit the transaction

Note: According to the correct logic, the account balance should be 50.

This level leads to some less reliable results and is not much better than other levels in terms of performance. Use this isolation level with caution and note that the results may be inconsistent or not reproducible, depending on what other transactions are doing at the same time. Typically, transactions with this isolation level only perform queries and do not perform insert, update, or delete operations.

READ COMMITTED

This level is sometimes called unrepeatable reads. Only after a transaction is committed can it be read. However, there may be inconsistent values between the two phases, such that one value is read before another transaction is committed and another value is read after another transaction is committed. This is called Nonrepeatable Read.

time	A transaction	Transaction B
T1	Start the transaction
T2	On the first query, user1 has a balance of 100
T3		Start the transaction
T4	Other operating
T5		Change the balance of account user1 from 100 to 200
T6		Commit the transaction
T7	Second query, user1 balance is 200
T8	Continue with other operations

Note: Correct logic dictates that transaction A should read the same values as before and after transaction A.

At the Read commit isolation level, in addition to the “unrepeatable Read” condition, Phantom Read can also occur.

time	A transaction	Transaction B
T1	Start the transaction
T2	On the first query, the total amount of data is 100
T3		Start the transaction
T4	Other operating
T5		Insert 100 new data
T6		Commit the transaction
T7	Second query, total number of entries is 200
T8	Continue with other operations

Note: According to the correct logic, the total amount of data read before and after transaction A should be the same.

REPEATABLE READ

InnoDB’s default isolation level is repeatable. This level of isolation ensures consistent results when the same data is read multiple times in the same transaction.

Repeatable reads solve the problem of unrepeatable reads. But it still doesn’t solve the illusory case.

InnoDB addresses the illusion problem through Multiversion Concurrency Control (MVCC).

What’s the difference between unrepeatable and illusory?

1. Non-repeatable reads are reads of data changed by other transactions, for UPDATE operations

Workaround: Use A row-level lock that locks the row and releases the lock only after transaction A has read multiple times, allowing other transactions to change the previous data.

1. Phantom reads are data added by other transactions, for INSERT and DELETE operations

Solution: Use A table-level lock that locks the entire table and releases the lock after transaction A reads the total number of times, allowing other transactions to add/delete data.

SERIALIZABLE

This is the highest isolation level. By forcing each step of the transaction to be executed sequentially, locks are placed on each row of data that is read, which can result in numerous timeouts and lock grabs.

This isolation level is also rarely used in practice and is only considered when there is a strong need to ensure data consistency and no concurrency is acceptable.

The above isolation levels from low to high can solve problems such as dirty reads, unrepeatable reads, and phantom reads one by one.

Isolation level	Dirty read	Unrepeatable read	Phantom read	Read lock
Read uncommitted	Yes	Yes	Yes	No
Read the submission	No	Yes	Yes	No
Repeatable read	No	No	Yes	No
serializable	No	No	No	Yes

MVCC is introduced

concept

MVCC is called the Multiversion Concurrency Control. It allows you to save multiple historical versions of a single record, The historical version information is stored in the System tablespaces or undo tablespaces, which are collectively called rollback segments. This information is used to support rollback operations and consistent reads (repeatable reads) of things.

The MVCC read operation has two concepts: snapshot read and current read.

• Snapshot read: The implementation of snapshot read is based on MVCC. The data it reads may be historical data.
• Current read: The current read reads the latest data and locks the read record to ensure that other transactions cannot modify the current record.

The core concepts of MVCC are as follows:

• Three hidden fields are added to each row: DB_TRX_ID, DB_ROLL_PTR, and DB_ROW_ID.
• Undo logs
• Read View

MVCC is designed to allow read-write conflicts to be unlocked to improve performance (the read is a snapshot read, not the current read). It mainly solves the problem of unrepeatable reading, and also solves the problem of unreal reading.

Hidden fields

InnoDB’s MVCC is implemented by adding three fields to each row of records:

1. DB_TRX_ID. The ID (6 bytes) of the last transaction to store the modified (insert, update, and delete) row of data. In addition, the delete operation is internally treated as an update operation, marking the special bits in the line as deleted.
2. DB_ROLL_PTR. Stores a 7-byte undo log location pointer to the previous version of the data. Points to a rollback log in the rollback segment that contains information about how to restore a row if it has been updated.
3. DB_ROW_ID. The row ID (6 bytes) that monotonically increases as a new row is inserted. This hidden row ID is used to create a clustered index when a clustered index is automatically generated when no suitable index is available for the created table.

So when you insert a row, it actually looks like this in InnoDB:

id	name	age	DB_TRX_ID	DB_ROLL_PTR	DB_ROW_ID
1	Zhang SAN	15	5	344434	1

Undo logs

The basic concept

During data modification, not only redo but also the corresponding Undo is recorded, which can be used to roll back the transaction if it fails or is rolled back for some reason.

The undo log is a logical log, unlike the redo log. You can assume that when a record is deleted, the Undo log records a corresponding INSERT record, and vice versa, when a record is updated, it records a corresponding reverse update record.

When rollback is performed, the logical records in the Undo log can be read and rolled back. Sometimes, row versioning is also implemented through undo log: When a row is locked by another transaction, it can analyze the previous data of the row from the Undo log, so as to provide the version information of the row, so that users can achieve non-locked consistent read.

storage

Undo log segment rollback segment Undo log segment rollback segment Undo log segment rollback segment Rollback segments exist in system tablespace, undo tablespaces, and temporary tablespaces.

Innodb storage engine manages undo in segments. Rollback segments are called rollback segments. InnoDB supports a maximum of 128 rollback segments, of which 32 are allocated to temporary tablespaces. This leaves 96 rollback segments that can be allocated to transactions that modify data in regular tables. There are 1024 undo log segments in each rollback segment.

Recycling mechanism

The Undo log is divided into insert logs and update logs.

1. Insert log: This log is only required for rollback and is deleted soon after the transaction commits.
2. Update log: Used for consistent reads and deleted when no transactions are needed.

Insert log The log is deleted as soon as the transaction commits. The following describes the internal mechanism of update logs.

When the transaction is committed, InnoDB will not delete the undo log immediately, because the undo log may be used later. For example, when the isolation level is REPEATable Read, the transaction reads the latest version of the commit row when the transaction is started. As long as the transaction does not end, the version of the row cannot be deleted. That is, the undo log cannot be deleted.

However, when a transaction is committed, the undo log corresponding to the transaction will be put into the delete list, which will be deleted through purge in the future. In addition, when committing a transaction, it will determine whether the pages allocated by Undo Log can be reused. If they can be reused, they will be allocated to subsequent transactions to avoid wasting storage space and performance by allocating separate Undo log pages for each independent transaction.

Delete and update operations with undo log :(insert operation does not need analysis, just insert rows)

• The delete operation does not actually delete directly, but instead flags the DELETE object as deleted, and the purge thread has done the final deletion.
• Update is divided into two cases: whether the update column is a primary key column.
• • If it is not a primary key column, the undo log directly reverses how the update was done. That is, the update is done directly.
  • If it is a primary key column, the update is performed in two parts: delete the row and insert the target row.

⚠️ Note: Do not use long transactions because long transactions keep undo logs all the time, so log files get bigger and bigger.

Read view

The basic concept

A Read view is a point-in-time snapshot of the database that defines “what data can I see” during a transaction.

In MySQL, there are two concepts of views:

• One is a virtual table, created using the Create View.
• One is the consistency view InnoDB uses to implement MVCC, which supports the implementation of RC and RR isolation levels.

Note: view creation time \

1. The TRANSACTION is started with BEGIN or START TRANSACTION, and then created in the first SELECT statement. \
2. Use START TRANSACTION WITH CONSISTENT SNAPSHOT to START things directly and create snapshots.

Key noun concepts:

1. Trx_id: indicates the current transaction ID of the row.
2. Trx_ids: set of active (uncommitted) transaction version numbers for the current system.
3. Min_trx_id: The minimum transaction ID that was active when the current Read View was created.
4. Max_trx_id: The next transaction ID that the system should assign when the current read view is created.
5. Creator_trx_id: transaction version number for creating the current Read View.

Judgment logic

Thus, a data version of DB_TRX_ID has the following possibilities for the moment the transaction is started:

1. It falls in the green zone. Transaction ID < MIN_trx_id is displayed. Because if the data transaction ID is less than the minimum active transaction ID in the Read View, you can be sure that the data existed before the current transaction started and therefore can be displayed.
2. It falls in the red zone. Transaction ID > max_trx_id is not displayed. Because if the data transaction ID is greater than the maximum transaction ID of the current system in the Read View, the data was created after the current Read View was created, so the data is not displayed.
3. It falls in the yellow region. Min_trx_id < transaction ID < max_trx_id matches trx_IDS. There are three situations:

• • If the transaction ID is not in the collection, it indicates that the version was generated by a committed transaction.
  • If the transaction ID is in the set and the transaction ID is equal to creator_trx_id, the current transaction is self-generated and therefore visible.
  • If the transaction ID is in the collection, but the transaction ID is not creator_trx_id, the current transaction is not self-generated, and the transaction has not been committed, so it is not visible.