This article is recommended for those unfamiliar with transaction ACID and transaction isolation levelsBlog.csdn.net/weixin_4401…

1. Briefly introduce the isolation level role of transactions

In mysql, InnoDB engine, transaction support: RU (uncommitted, dirty read exists) 2.RC (committed) The dirty read is solved, but the read is not repeatable. The data read is inconsistent, that is, the logic of this transaction will be affected by other transactions. 4.SERIALIZABLE meets the complete definition of transaction ACID, which is similar to synchronized. Read means add a table lock and block other transactions from reading the tableCopy the code

2. A brief description of the question we will be testing

Business requirement: add one field of data in concurrent caseCopy the code

3. Solution:

0. Error model, the concurrent problems (error) demonstration open for repeatable read isolation level (mysql default transaction isolation level) transaction, read the original data, will read the data on the updated after covering the original data features: concurrent, different threads, the first to start the update operation began after the update operation may cover, write covering problem 1. Enable the transaction with serialization isolation level, read the original data, and update and cover the original data after the read data increases. Advantages: 1. Ensure transactions run one after another to ensure data consistency Disadvantage 1. Lock the entire table, affecting the read operation of other threads 2. Deadlock will be found when trying to acquire locks in multi-threaded environment. Low transaction execution success rate and low execution efficiency 3. Generally, 2 is not recommended. SQL: Update account set money = money + #{money} where ID = #{id} Transaction 2 is not required for simple logic. High execution efficiency disadvantages: 1. It only applies to simple logic, such as not being oversold or not. 3. Do not open transaction, use cas plus loop, constantly try to update advantages: 1. Ensure data consistency to a certain extent 2. Disadvantages of high execution efficiency under low concurrency: 1. Manual write retry logic 2 is required. With high concurrency, it is difficult to hit correct changes and the execution efficiency is low, which decreases greatly with the increase of concurrency. Retries with low hit ratio lead to the waste of database resources 3. Complex business logic requires the database to add the Version field, 4. Cas 4 can only be used when a transaction is added because of rollback requirements in complex logic. Enable repeatable read transactions and manually add mutex (select * from table for UPDATE) to read original data. The read data is automatically increased and then updated to cover the original data. Advantages: 1. Data consistency is guaranteed by placing exclusive locks on read data 2. Locking a single piece of data does not affect other read operations that do not design this piece of information. 3. High efficiency, in each concurrency has stable performance disadvantages: 1. Locks must be manually added to read operations. The contents to be locked are determined by the program. In complex services, it is necessary to manually confirm which read operations to be lockedCopy the code

To test the code (Springboot), visit Github:Github.com/nainai-wdd/…