MySQL provides two transactional storage engines: InnoDB and NDB Cluster. There are also third-party storage engines that support transactions
1. Database transactions
A database transaction is a set of data operations within which all operations either succeed or fail.
For example, in the transfer process, zhang SAN transfers 2000 to Li Si. In the first step, 2000 is deducted from the bill account, and in the second step, 2000 is added to Li Si’s account. These two steps can be regarded as one transaction. If both steps succeed, the transfer succeeds. If either step fails, the transfer operation is withdrawn
Transactions have Atomicity, Consistency, Isolation and Durability (ACID for short).
The main thing here is isolation
2. Possible problems of transaction concurrency
Why should there be an isolation level, mainly to prevent a series of problems in transaction concurrency
Suppose our table structure and data are as follows:
create table movie (
id int unsigned not null auto_increment primary key,
name varchar(30) not null comment 'Movie Title',
price int not null comment 'tickets'
)ENGINE=InnoDB comment='movie';
INSERT INTO movie (id, name, price) VALUES (1.Don 'agent 3'.70);
INSERT INTO movie (id, name, price) VALUES (2.'Hello, Li Huanying'.60);
Copy the code2.1 dirty read
Dirty read refers to reading data that has not been committed by another transaction. Uncommitted data in a transaction may be temporary data or may need to be rolled back and may not be stored in the database itself, which is called dirty reads.
Before the business started, the price of Tang Tan 3 was 70 yuan.
Transaction 1 starts the transaction first, and then transaction 2 starts the transaction. Because transaction 2 processes the data faster, the price is changed to 55 yuan first. At this time, the transaction has not committed or rolled back, so transaction 1 needs to read this data
| Time, business | A transaction | Transaction 2 |
|---|---|---|
| 1 | begin | |
| 2 | begin | |
| 3 | update movie set price = 55 where id = 1; | |
| 4 | select price from movie where id = 1; If you read 55 here you get a dirty read | |
| 5 | commit; | |
| 6 | rollback; |
2.2 Unrepeatable read
Not repeatable read means within the same transaction, different time to read the same data may not be the same, may be affected by other transactions, such as in the read data and second data for the first time, among other issues also to modify the data, lead to the same transaction on the same data inconsistent reads. This usually happens with data UPDATE operations.
| Time, business | A transaction | Transaction 2 |
|---|---|---|
| 1 | begin | |
| 2 | begin | |
| 4 | select price from movie where id = 1; At this time of 70 | |
| 3 | update movie set price = 55 where id = 1; | |
| 4 | select price from movie where id = 1; If 55 is read here it is unrepeatable | |
| 5 | commit; | |
| 6 | rollback; |
2.3 phantom read
Phantom reading is when a transaction queries the same range twice before and after, and the latter query sees rows not seen by the previous query.
| Time, business | A transaction | Transaction 2 |
|---|---|---|
| 1 | begin | |
| 2 | begin | |
| 4 | select name from movie where id <= 70; At this time, two pieces of data should be found: 1. Tang Tan 3, 2. Hello, Li Huanying | |
| 3 | INSERT INTO movie (id, name, price) VALUES (3, ‘movie ‘, 50); | |
| 4 | select name from movie where id <= 70; At this time, three pieces of data are found as 1. Tang Tan 3, 2. Hello, Li Huanying 3. Assassinate novelist | |
| 5 | commit; | |
| 6 | rollback; |
3. Transaction isolation level
The SQL standard defines four isolation levels, all of which are supported by MySQL. The four isolation levels are:
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
Each isolation level addresses the corresponding problem
| Isolation level | problems | Dirty read | Unrepeatable read | Phantom read |
|---|---|---|---|
| READ UNCOMMITTED | Not solved | Not solved | Not solved |
| READ COMMITTED | To solve | Not solved | Not solved |
| REPEATABLE READ | To solve | To solve | Not solved |
| SERIALIZABLE | To solve | To solve | To solve |
You can see from this table that the higher the isolation level, the more problems are solved, and the higher the level, the greater the impact on concurrency performance
READ UNCOMMITTED, nothing is resolved, and I’m not talking about that isolation level here, okay
3.1 How does READ COMMITTED work
Read commit resolves the dirty read problem
The first 6 steps are the same as those in the isolation level of the commit statement: However, since the data has been found to have been modified in transaction 2, the query can not be performed until the completion of transaction 2 (commit or rollback). Therefore, the steps 4 and 5 of transaction 1 can not be performed until the completion of transaction 6 in transaction 2
| Time, business | A transaction | Transaction 2 |
|---|---|---|
| 1 | begin | |
| 2 | begin | |
| 3 | update movie set price = 55 where id = 1; | |
| 4 | select price from movie where id = 1; | |
| 5 | commit; | |
| 6 | rollback; | |
| 5 | The SQL in Step 4 will wait until here | |
| 6 | The SQL in Step 5 will wait until here |
3.2 REPEATABLE READ
Repeatable reads solve the problem of dirty and unrepeatable reads
| Time, business | A transaction | Transaction 2 |
|---|---|---|
| 1 | begin | |
| 2 | begin | |
| 4 | select price from movie where id = 1; At this time of 70 | |
| 3 | update movie set price = 55 where id = 1; | |
| 4 | select price from movie where id = 1; I’m still reading 70 | |
| 5 | commit; | |
| 6 | commit; | |
| 7 | select price from movie where id = 1; It now reads 55 |
Update (id = 1); update (id = 1);
A: Because InnoDB has MVCC (multi-version concurrency control), you can use snapshot reads without blocking.
3.3 SERIALIZABLE
This pattern is relatively simple, it requires serialized transaction execution, the transaction can only be executed one after another, not concurrently, to solve the dirty read, unrepeatable read and phantom read, of course, the worst performance.
4. Multi-version Concurrency Control (MVCC)
Most of MySQL’s transactional storage engines are not simple row-level locks. They generally implement multiple versions of concurrency control MVCC at the same time for promotion and release purposes.
4.1 the InnoDB MVCC
InnoDB’s MVCC is implemented by storing two hidden columns at the end of each row. These two columns hold the creation time of the row and the expiration (or deletion) time of the row, but not the actual time, but the system version number. Each time a transaction is started, the system version number increases automatically. The version number of the transaction start time is used as the version number of the current transaction, which is compared with the version number of each row of records queried.
The specific operation of REPEATABLE READ isolation level is as follows:
-
SELECT
InnoDB checks each row based on two criteria:
A. InnoDB only queries rows whose version is earlier than the current transaction version (i.e., rows whose system version number is less than or equal to the transaction’s system version number). This ensures that the transaction reads rows that either existed before the transaction started or were inserted or modified by the transaction itself. B. The deleted version of the row is either undefined or greater than the current transaction version number. This ensures that rows read by the transaction are not deleted before the transaction begins.
Only records that meet the preceding two conditions can be returned as the query result
-
INSERT
InnoDB stores the current system version number as the row version number for each newly inserted row
-
DELETE
InnoDB stores the current system version number as a row deletion identifier for each row that is deleted
-
UPDATE InnoDB Inserts a new row, saves the current system version number as the row version number, and saves the current system version number to the original row as the row delete mark
Two additional system version numbers are kept, enabling most read operations to be unlocked. This design makes reading data simple, performs well, and guarantees that only rows that meet the criteria will be read
5. Transaction issues under hybrid storage engines
MySQL does not manage transactions at the service layer and transactions are implemented by the underlying storage engine, so it is not reliable to process the same transaction in different storage engines. For example: Table_A uses innoDB engine, table_B uses MyISAM engine (does not support transactions) suppose the following operations are in a transaction:
insert table_a ... # 1
insert table_b ... # 2
update table_a ... # 3
Copy the codeIf an exception occurs during execution #3, the transaction will be rolled back because Table_B does not support transactions. This will cause table_B’s changes to be unable to be rolled back, which will violate the consistency and atomicity of the transaction
6. View and set the isolation level
6.1 Viewing the Isolation Level
View the current isolation level
One - way
show variables like '%tx_isolation%';
Two - way
select @@tx_isolation;
Copy the codeView the isolation level of the session
SELECT @@session.tx_isolation;
Copy the codeView global isolation levels
SELECT @@global.tx_isolation;
Copy the codeIn MySQL 8.0.3, tx_ISOLATION was replaced with transaction_isolation, and tx_isolation was replaced with transaction_isolation
6.2 Changing the Isolation Level
MySQL provides the SET TRANSACTION statement, which can change the TRANSACTION isolation level for a single session or globally. The syntax is as follows:
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
The SESSION and GLOBAL keywords are used to specify the scope of the modified transaction isolation level:
- SESSION: indicates that the modified transaction isolation level will be applied to all transactions in the current SESSION (current CMD window);
- GLOBAL: indicates that the modified transaction isolation level will be applied to all transactions in all sessions (globally) and existing sessions will not be affected.
- If SESSION and GLOBAL are omitted, the modified transaction isolation level will apply to the next transaction within the current SESSION that has not yet started.
Any user can change the transaction isolation level of the session, but only users with SUPER permission can change the global transaction isolation level.
6.3 Changing the isolation level of the current connection for JDBC
SetTransactionIsolation () is provided in Connection to change the isolation level of the current connection as follows:
connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
Copy the code