Talk about transaction isolation and its application in development

preface

For relational database transactions, the previous understanding is still relatively shallow, basically still stay in those and the most basic operations, such as a transaction can execute a pair of SQL, once encountered exceptions will be all rolled back, no dirty data. This is the atomicity, consistency, and persistence of transactions. Isolation has been largely unused in previous development, and the default isolation level of the database that has been used has not been updated. Until recently, I encountered a small problem in development, which prompted me to have a new understanding and understanding of isolation. This article will talk about the isolation of transactions.

Problems encountered

I won’t elaborate on the definition of transaction isolation here, but you can see a couple of blog posts in my Resources that do a good job.

There is now a set of business logic in development:

// 1. Check whether a record exists in the database
// 2. If it does not exist, invoke another service via HTTP, perform a series of operations, and insert the data
// 3. Check again. If yes, proceed with the next operation
@Transactional
public void foo(a){
    // # 1st query
    EntityA a = aService.findOneBy();
    if(a == null) {// call other service and insert data
        httpUtils.doPost("http://ip:port/createA");
        // # 2nd querya = aService.findOneBy(); }}Copy the code

Call createA(), which returns the data in the JSON file, without having to query the database repeatedly. CreateA () does not return any data by default.

At first glance the code seems fine and the logic is simple. In fact, the results of both queries are the same, and we cannot get new data after successfully executing createA(). Why is that? The specific reason is affected by the database isolation level.

Transactional isolation and @transactional

Isolation is one of the ACID characteristics of relational database transactions, meaning that if multiple transactions are executing concurrently, the changes made by each transaction must be isolated from other transactions. This means that operations between transactions are not visible to each other. In order to balance database performance and reliability, the SQL standard gives centralized isolation levels (different databases implement different ways) READ UNCOMMITED, READ COMMITED, REPEATABLE READ and SERIALIZABLE.

• RAED UNCOMMITED: Use query statements that do not lock and may Read uncommitted rows (Dirty Read);
• READ COMMITED: Record lock is only applied to records, but no gap lock is added between records, so new records are allowed to be inserted near the locked records, so different results (non-repeatable Read) may be obtained when the query statement is used repeatedly;
• REPEATABLE READ: Multiple reads of the same range will return a snapshot of the first query, not different rows, but Phantom Read may occur;
• SERIALIZABLEInnoDB implicitly adds a shared lock to all queries to solve the illusion problem;

The default transaction isolation level in MySQL isREPEATABLE READ, but it also solves the phantom problem to some extent with the next-key lock.

MySQL isolation level can be viewed using the following SQL:

use performance_schema;
select * from global_variables where variable_name = 'tx_isolation';
Copy the code

Take a look at the use of declarative transaction annotations in Spring. The following parameters can be configured in @Transactional:

The property name	instructions
name	When you have multiple transactionmanagers in a configuration file, you can use this property to specify which TransactionManager to select.
propagation	The propagation behavior of the transaction, which defaults to REQUIRED.
isolation	The isolation degree of a transaction is DEFAULT.
timeout	Timeout period for a transaction. Default value is -1. If the time limit is exceeded but the transaction has not completed, the transaction is automatically rolled back.
read-only	Specifies whether the transaction is read-only. The default value is false. To ignore methods that do not require transactions, such as reading data, you can set read-only to true.
rollback-for	Specifies the type of exception that can trigger transaction rollback. If more than one exception type needs to be specified, separate the exception types by commas.
no-rollback- for	Throw the exception type specified by no-rollback-for without rolling back the transaction.

Isolation = DEFAULT means that Spring adopts the isolation level configured in the data by DEFAULT, namely REPEATABLE READ, and MySQL also handles phantom reads. As a result, the records in both query operations are null. This is obviously a normal result for a transaction, but it is problematic for our business logic. In the code above, we simply want to “read” changes made by other transactions.

Make transactions illusory

It doesn’t matter when it’s clear that some data is unrepeatable and illusory. In order for the above code to work properly, we can make these changes.

1. Directly modifyingfoo()Transaction annotation configuration on

@Transactional(isolation = Isolation.READ_COMMITTED)
public void foo(){}
Copy the code

This is equivalent to lowering the REPEATABLE READ isolation level of MySQL database by one level. At READ_COMMITTED level, unrepeatable reads and phantom reads will occur, which means that we can READ the changes made by other transactions to the database, and the problem is solved.

2. willaService.findOneBy()Methods run nontransactionally or as a separate transaction

@Transactional(propagation = Propagation.NOT_SUPPORTED)
public findOneBy(){}
Copy the code

Run non-transactionally to read the updated data of other transactions.

@Transactional(propagation = Propagation.REQUIRES_NEW)
public findOneBy(){}
Copy the code

Propagation.REQUIRES_NEW Means to create a new transaction and suspend the current transaction if it exists. 1st query –> createA –> 2nd Query = 1st query –> createA –> 2nd Query = 1st query –> createA –> 2nd Query = 1st query –> createA –> 2nd Query = 1st query –> createA

conclusion

In most scenarios, simply adding @transactional to a Service layer method allows the method to run as a transaction. This is a good way to rollback in case of an exception and prevent dirty data. However, in some special scenarios, it is necessary to manually control the isolation level and propagation behavior of transactions in fine granularity, such as the one described in this article.

The higher the isolation level, the more consistent the database, but the worse the performance. This needs to be coordinated in the development, the common problems of transaction dirty read, unrepeatable read and phantom read, in addition to dirty read is more fatal, the other two individuals feel that as long as it is controllable, it is not a problem.

The SQL standard defines that transactions are isolated, so that the results of other transactions cannot be read from within the transaction. But if T1 reads a certain piece of data, and then T2 changes and commits, then t1’s results will not be all wrong. What’s the point of being unrepeatable? What happens if you can read updates to external transactions?

The resources

• “Shallow in shallow out” MySQL and InnoDB
• “Shallow in deep out” implementation of MySQL transactions
• Wikipedia – Transaction isolation
• The relationship between transaction isolation level and locks in Innodb
• Understand the use of @Transactional in Spring
• The @Transactional annotation of Spring is used in detail