1. What are transactions
A transaction is a logical (non-divisible) unit in a database management system (DBMS) execution that consists of a finite sequence of database operations (multiple DML statements, select statements that do not contain transactions) that either all succeed or all fail.
For example, if A transfers money to B, A’s account will be -1000 yuan, and B’s account will be +1000 yuan. The two update statements must be executed as A whole, otherwise A will deduct money, and B will not add money. The transaction then guarantees that all changes to account A and account B will occur at once, or none at all.
2. Transaction features
Transactions have 4 attributes: Atomicity, Consistency, Isolation, Durability. These four properties are commonly referred to as ACID properties.
2.1 atomic
A transaction must be treated as an indivisible, minimal unit in which all operations in the entire transaction either commit successfully or fail. For a transaction, only a few operations cannot be performed.
For example: A transfers money to B, A deducts 500 yuan and B adds 500 yuan
The operation of the whole transaction is either all successful or all failed, so A cannot deduct money, but B does not increase. If atomicity is not guaranteed, consistency problems naturally arise.
2.2 consistency
Consistency means that a transaction transitions the database from one consistency state to another without compromising the integrity of the data in the database before and after the transaction.
If A transfers money to B, A deducts 500 yuan and B adds 500 yuan, the sum of the deducted money (-500) and the added money (+500) should add up to 0.
2.3 isolation,
The execution of a transaction cannot be interfered with by other transactions. That is, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.
What problems can arise if isolation is not guaranteed?
If A has 1000 at the beginning and B has no money, then A transfers money to B twice, 500 for the first time and 500 for the second time. If we call these two transfer operations T1 and T2 respectively, then under normal circumstances, the transfer should be done one by one, but there may be alternation in the database. Such as:
| T1 | T2 |
|---|---|
| Read: A = 1000 | |
| Read: A = 1000 | |
| A – 500 = 500 | |
| B + 500 = 500 | |
| Read: B = 500 | |
| A – 500 = 500 | |
| B + 500 = 1000 |
So if we do this, A has 500 left, and B is already 1000, so we have 500 more.
2.4 persistence
Once a transaction commits, its changes are permanently stored in the database. Even if the system crashes, the submitted modification data will not be lost.
3. Transaction concurrency
As we know, MySQL is a client/server architecture software. For the same server, there can be several clients connected to it. After each client is connected to the server, it can be called a Session. Each client can issue a request statement to the server in its own session, and a request statement can be part of a transaction, meaning that it is possible for the server to process multiple transactions simultaneously.
As mentioned above, transactions have a property called isolation. In theory, when a transaction accesses a data, other transactions should queue up, and when the transaction commits, other transactions can continue to access the data. In this way, the execution of concurrent transactions becomes serial execution.
We want to maintain some isolation of transactions while allowing the server to handle as many transactions as possible accessing the same data. What kind of data problems might arise when we abandon isolation?
3.1 Isolation Level
MySQL has four transaction isolation levels, increasing in intensity. High isolation limits the number of transactions that can be executed in parallel, so some applications reduce isolation levels in exchange for greater throughput. Different service scenarios use different database transaction isolation. Some key services use high isolation levels to ensure data correctness.
MySQL > create transaction isolation levels
- Read Uncommitted: A transaction can Read data that is not committed by different transactions. In practical applications, dirty reads are generated. A transaction has Read data that is not committed by other transactions, but the data is rolled back.
- Read Committed: Transaction read other things have submitted data, read the latest data, so it will appear in the same transaction is not consistent the select read data, can appear not repeatable read, not repeatable read problem is we are in the same transaction execution exactly the same when the select statement may see different results.
- Repeatable Read: Mysql default isolation level, read many times the same data in the same transaction result is the same, solve the problem not repeatable read, read this level will be illusions, namely when the user read a range of data lines, another transaction and insert a new row in the scale, when the user read the scope of the data row again, will find that there is a new line.
- Serializable: The highest level of transaction isolation. Serializable forces transaction sequencing to block, avoids transaction conflicts, and solves all the problems mentioned above. It uses shared locks, which is inefficient in execution and can lead to a large number of timeout and lock switching competitions.
Note: MySQL’s default transaction isolation level is Repeatable Read.
To view the default isolation level:
SHOW VARIABLES LIKE 'transaction_isolation'; Or SELECT @ @ transaction_isolation;Copy the codeHow do I set the isolation level for a transaction
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
Copy the codeThere are four optional level values:
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
When setting the isolation level of a transaction, you can place the GLOBAL keyword, SESSION keyword, or nothing after the SET keyword. This will have different effects on different scopes of transactions as follows:
Use the GLOBAL keyword (globally affected) :
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Copy the codeOnly the session generated after the statement is executed is valid. The existing session is invalid.
Use the SESSION keyword (affected at the SESSION scope) :
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Copy the codeValid for all subsequent transactions of the current session.
This statement can be executed in the middle of an already opened transaction, but does not affect the currently executing transaction. If executed between transactions, it is valid for subsequent transactions.
Neither keyword is used (only affects the next transaction after the statement is executed) :
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Copy the codeOnly valid for the next transaction to be started in the current session. After the next transaction completes, subsequent transactions revert to the previous isolation level. This statement cannot be executed in the middle of an already opened transaction, and an error will be reported.
If we want to change the default transaction isolation level when the server is started, we can change the value of the startup parameter transaction-ISOLATION. For example, we specify — transaction-Isolation =SERIALIZABLE when the server is started. The default isolation level of the transaction is changed from REPEATABLE READ to SERIALIZABLE.
3.2 Basic transaction syntax
| instructions | operation |
|---|---|
| The transaction start | begin |
| Transaction rollback | rollback |
| Transaction commit | commit |
Create a new table to illustrate the problems caused by different isolation levels:
CREATE TABLE 'account' (' id 'bigint NOT NULL COMMENT' id ', 'name' varchar(30) DEFAULT NULL COMMENT 'iD ', 'money' decimal(12,2) DEFAULT NULL COMMENT 'account balance ', PRIMARY KEY (' id')) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;Copy the codeInitial data is as follows:
3.3 dirty read
When a transaction reads data that another transaction has modified but not committed, it is called a dirty read.
Open two session sessions and set the isolation level to read uncommitted:
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Copy the code1. Start A transaction T1 and read the current amount of A.
2, open another transaction T2, increase A’s amount by 500, but do not commit.
3. The query data in T1 is 1500.
If T2 is rolled back, the final value is 1000. If T1 is 1500 and performs operations before T2 is rolled back, dirty reads will occur.
| T1 | T2 |
|---|---|
| BEGIN | |
| SELECT money FROM account WHERE id = 1 | BEGIN |
| UPDATE account SET money = money + 500 WHERE id = 1 | |
| SELECT money FROM account WHERE id = 1 | |
| ROLLBACK |
3.4 Unrepeatable Read
When the same record in a transaction is retrieved twice and the results obtained are different, this phenomenon is called non-repeatable reads.
Open two session sessions and set the isolation level to read committed:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Copy the code1. Start A transaction T1 and read the current amount of A.
2, open another transaction T2, increase the amount of A by 500, commit.
3. For T1, the amount of A is 1500, and the two query results are different. T2 made changes to the record and committed successfully, which means that the modified record is visible to other transactions, so T1 reads money differently in both cases.
| T1 | T2 |
|---|---|
| BEGIN | |
| SELECT money FROM account WHERE id = 1 | BEGIN |
| UPDATE account SET money = money + 500 WHERE id = 1 | |
| COMMIT | |
| SELECT money FROM account WHERE id = 1 | |
| COMMIT |
3.5 phantom read
General explanation:
Transaction T1 performs the same select operation twice to obtain different data sets. This is not a magic read, it is a type of non-repeatable read (same as delete), and only occurs at r-U and R-C levels, which is not allowed in MySQL’s default RR isolation level.
In fact:
This is not to say that the result sets obtained by the two reads are different. The phantom read focuses on the fact that the data state represented by the result obtained by one SELECT operation cannot support subsequent business operations. Select whether a record exists, does not exist, and attempts to insert the record, but the record already exists and cannot be inserted.
Open two sessions and set the isolation level to repeatable read:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Copy the code1. Start transaction T1 and query data whose name is A.
2. Open another transaction T2, add A new data named A and commit it.
3. If the same data is added to T1, an error will be reported. No data can be found in T1, but it is informed that the data already exists, resulting in a phantom read phenomenon.
Other scenarios:
Bank A opened A transaction window to check whether there was A “ayue” user in the current system, but found there was not. Bank B also opened A transaction window to check whether there was A “ayue” user in the current system. Bank A first created the “ayue” user and submitted it. Bank B must ensure data consistency in a transaction, so “AYue” could not be queried. As a result, the window of bank B thought that “AYue” had not been registered, so it created “ayue” user to register “Ayue” user, only to find that the system indicated that “ayue” user had been registered. But “ayue” is not found in this transaction, just like an illusion.
4. To summarize
MySQL isolation levels
| Isolation level | Dirty read | Unrepeatable read | Phantom read |
|---|---|---|---|
| READ UNCOMMITTED | May occur | May occur | May occur |
| READ COMMITTED | To solve | May occur | May occur |
| REPEATABLE READ | To solve | To solve | May occur |
| SERIALIZABLE | To solve | To solve | To solve |