Detailed explanation of the implementation principle of MySQL transactions and Spring isolation levels

1. Transactions have ACID characteristics

• Atomicity: A transaction is the smallest unit of work that cannot be divided by a transaction. Either all of it is committed, or all of it fails and is rolled back.
• Consistency: The database always moves from a consistent state to another consistent state, which only contains the results of successful transaction commits.
• Isolation: The changes made by a transaction are submitted together at the end and are not visible to other transactions.
• Durability: Once a transaction is committed, the changes it makes are permanently saved to the database.
  

2. Transaction isolation level

1) Definition and issues of isolation level

• READ UNCOMMITTED: Modifications made by a transaction, even if not committed, are visible to other transactions. Transactions can read uncommitted data, a situation known as a dirty read.
• READ COMMITTED: Transactions read committed data, the default isolation level for most databases. When a transaction is being executed, the data is modified by another transaction, causing the information read before and after this transaction to be different. This situation is called non-repeatable read.
• PEPEATABLE READ (repeatable read): This level is the default isolation level of MySQL. It solves the problem of dirty reads and ensures that multiple reads of the same record by the same transaction are consistent. However, phantom reads may still occur at this level. Phantom read means that when a transaction A reads a range of data, another transaction B inserts rows in this range. When transaction A reads the data in this range again, phantom rows are generated. Special note: InnoDB and XtraDB storage engines solve the phantom read problem through multiversion concurrency control (MVCC). It uses gap locks (next-key locking) to lock the gaps in the rows and indexes involved in the query to prevent the insertion of phantom rows.
• SERIALIZABLE: This transaction is the highest isolation level, which forces transactions to be executed serially, avoiding phantom read problems. In short, SERIALIZABLE will lock each row of data read, so it may cause a lot of timeouts and lock contention.

2) If you view the modification and isolation level of MySQL

show variables like 'tx_isolation'; # View the isolation level, before MySQL8show variables like 'transaction_isolation'; # View the isolation level, before MySQL8

set global transaction_isolation='READ-COMMITTED'; // Set the isolation level, valve domain READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

The transaction isolation level can be at the Session level. We can set different levels for different Sessions:

set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;

3) Spring transaction isolation level

Spring transactions use the database isolation level by default. You can adjust the Session isolation level by annotating the isolation parameter in @Transactional. The isolation level is at the session level, and the JDBC java.sql.Connection interface supports the setting of the isolation level.

When Spring starts a transaction (DataSourceTransactionManager.doBegin), it sets the isolation level of the Connection according to the annotation configuration:

MySQL driver com.mysql.cj.jdbc.ConnectionImpl executes SQL statements to adjust the session-level isolation level

3. Deadlock

Deadlock occurs when two or more transactions occupy the same resource and request to lock the resources occupied by each other, resulting in a vicious cycle. Deadlock example:

# Transaction 1 start transaction;
update account set money=10 where id=1;
update account set money=20 where id=2;
commit;

# Transaction 2 start transaction;
update account set money=10 where id=2;
update account set money=20 where id=1;
commit;

Suppose by chance, transaction 1 and transaction 2 finish executing the first update statement at the same time, and then prepare to execute the second update statement, but find that the record has been locked by the other party. Then the two transactions wait for the other party to release resources while holding the lock required by the other party, which will cause an infinite loop.

To avoid deadlock problems, the database implements various deadlock detection and deadlock over-length mechanisms. InnoDB handles deadlock by rolling back the transaction that holds the least row-level exclusive lock.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.