Shulou(Shulou.com)06/01 Report--

This article mainly explains how to achieve MySQL transactions and Spring isolation level, the content is clear, interested friends can learn, I believe it will be helpful after reading.

1. The transaction has ACID characteristics.

Atomicity (atomicity): the smallest unit of work in which a transaction is indivisible by a transaction, either all committed or all failed rollback. Consistency (consistency): a database always moves from a consistency state to another consistency state, and it contains only the result isolation of successful transaction commits (isolation): changes made by the transaction are finally committed together and are invisible to other transactions (durability): once the transaction commits, its changes are permanently saved to the database.

2. Isolation level of the transaction

1) definition and problems of isolation level

READ UNCOMMITTED (read uncommitted): changes to a transaction, even if not committed, are visible to other transactions. Transactions can read uncommitted data, which is called dirty reading. READ COMMITTED (read committed): transactions read committed data, the default isolation level for most databases. When a transaction is executed, the data is modified by another transaction, resulting in different information read before and after the transaction, which is called unrepeatable reading. PEPEATABLE READ (repeatable readable): this level is the default isolation level of MySQL. It solves the problem of dirty reads and ensures that multiple reads of the same records by the same transaction are consistent, but phantom reads still occur at this level. Illusory reading means that when one transaction A reads a certain range of data, another transaction B inserts a row in this range, and when A transaction reads this range of data again, a magic row will occur. Special note: InnoDB and XtraDB storage engines solve the problem of phantom reading through multi-version concurrency control (MVCC,Multiversion Concurrency Control), which uses gap locks (next-key locking) to lock gaps in rows and indexes involved in queries to prevent phantom rows from being inserted. SERIALIZABLE (serializable): this transaction is the highest isolation level and forces the transaction to execute serially, avoiding the problem of phantom reading. To put it simply, SERIALIZABLE locks every row of data read, so it can lead to a large number of timeouts and lock competition, isolation level, dirty reading possibility, unrepeatable reading possibility, phantom reading possibility and locking READ UNCONMITEDYesYesYesNoRED COMMITEDNoYesYesNoREPEATABLE READNoNoYesNoSERIALIZABLENoNoNoYes.

2) if you view the isolation level of modifications and MySQL

Show variables like 'tx_isolation'; # View isolation level, previous MySQL8 show variables like' transaction_isolation'; # View isolation level, MySQL8set global transaction_isolation='READ-COMMITTED'; / / set isolation level, valve domain READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

The isolation level of a transaction can be Session-tier, and we can set different levels for different Session:

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 isolation level of the database by default, and you can adjust the isolation level of Session by annotating the isolation parameter in @ Transactional. The isolation level is session level, and the java.sql.Connection interface of JDBC supports the setting of the isolation level.

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

MySQL drives com.mysql.cj.jdbc.ConnectionImpl to execute SQL statements to adjust the isolation level at the session level

3. Deadlock

Deadlock means that two or more transactions occupy each other on the same resource and request to lock the resources occupied by the other party, resulting in a vicious circle. Example of deadlock:

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

Suppose that transaction one and transaction two execute the first update statement at the same time, and then prepare to execute the second update statement, only to find that the record is locked by the other party, and then both transactions wait for the other party to release resources while holding the lock needed by the other party, so there will be an endless loop.

In order to avoid deadlock problems, the database implements various deadlock detection and deadlock length mechanisms. InnoDB handles deadlocks by rolling back transactions that hold the least row-level exclusive locks.

After reading the above content, do you have a better understanding of how to achieve MySQL transactions and Spring isolation level? if you want to learn more, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.