Wechat official account “Backend Advanced” focuses on back-end technology sharing: Java, Golang, WEB framework, distributed middleware, service governance and so on. The old driver give each other all the way, take you to advance, too late to explain quickly get on the car!
Some businesses of the company used pessimistic lock for update database, but some colleagues did not execute for Update in Spring transactions, and serious thread blocking occurred in concurrent scenarios. In order to get a thorough understanding of this problem, as the professional quality of the old driver, I decided to give my colleagues an explanation.
The scene
Recently, some of the company’s RPC calls between some of its Dubbo services resulted in a number of occasional serious timeouts, causing some modules to fail to service properly. Our database uses Oracle. After DBA investigation, we found that the execution time of some SQL was extremely long. By comparison, we found that these SQL with long execution time had pessimistic lock for Update, so relevant developers checked the corresponding business code of SQL. Mybatis commits for update (” for update “, “for update”, “for update”) The problem with concurrency should be that the corresponding resource is not locked and dirty data is generated rather than blocked. But after debugging the code, concurrent execution without Spring transactions does block.
Case analysis
Based on the problem of the crime scene, I specially wrote several case analysis test codes for the problem, “Talk is cheap, show you the code” :
Add Spring transaction execution but do not commit the transaction
public void forupdateByTransaction(a) throws Exception {
// The main thread gets the exclusive lock
reentrantLock.lock();
new Thread(() -> transactionTemplate.execute(transactionStatus -> {
// select * from forupdate where name = #{name} for update
this.forupdateMapper.findByName("testforupdate");
System.out.println("==========for update==========");
countDownLatch.countDown();
// block the transaction from committing
reentrantLock.lock();
return null;
})).start();
countDownLatch.await();
System.out.println("==========for update has countdown==========");
this.forupdateMapper.updateByName("testforupdate");
System.out.println("==========update success==========");
reentrantLock.unlock();
}
Copy the codeThe data transaction mechanism allows you to commit a SQL transaction only after a COMMIT or rollback operation is performed. For UPDATE is not released and the resource is locked until the commit transaction releases for UDpate. Therefore, the main thread will block to perform the update operation.
Concurrent execution without Spring transactions
public void forupdateByConcurrent(a) {
AtomicInteger atomicInteger = new AtomicInteger();
for (int i = 0; i < 100; i++) {
new Thread(() -> {
// select * from forupdate where name = #{name} for update
this.forupdateMapper.findByName("testforupdate");
System.out.println("========ok:"+ atomicInteger.getAndIncrement()); }).start(); }}Copy the codeFirst, we will increase the initialization size of the database connection pool a little so that the concurrent execution will obtain at least two connection objects with different IDS to execute for update. The following is the execution log of a certain time:
Mysql does not block if two or more connection objects with different IDS execute SQL. I will explain why Mysql does not block later.
The druid connection pool is true by default, so I set the druid connection pool autoCommit to false and run the test code again. With this test in mind, I will take you through a wave of source code to explain this phenomenon.
Smart you may think, the underlying source code of Mybatis not to us encapsulate some repetitive operations, such as when we execute a SQL statement, Mybatis automatically commit or rollback for us, this is also the basic requirements of JDBC framework, If Mybatis commits the update, it should be released. If you think of this question, it shows that you are a serious thinker, and we will keep this question in mind for later explanation.
Add Spring transactions to execute concurrently
private void forupdateByConcurrentAndTransaction(a) {
AtomicInteger atomicInteger = new AtomicInteger();
for (int i = 0; i < 100; i++) {
new Thread(() -> transactionTemplate.execute(transactionStatus -> {
// select * from forupdate where name = #{name} for update
this.forupdateMapper.findByName("testforupdate");
System.out.println("========ok:" + atomicInteger.getAndIncrement());
return null; })).start(); }}Copy the codeThe druid link pool autoCommit parameter is set to true and false. The druid link pool autoCommit parameter is set to true and false. The druid link pool autoCommit parameter is set to true and false. From the test results, it seems to have a lot to do with Spring transactions.
We now summarize the case study test results:
- Transaction not committed, for UPDATE pessimistic lock not released;
- If two or more connections with different ids execute for update, a block will occur. Mysql does not block.
- Execute for UPDATE statements concurrently without Spring transactions and druid connection pool autoCOMMIT =false without blocking;
- A Spring transaction executes the for UPDATE statement concurrently without blocking.
Post test code address: github.com/objcoding/t…
Source code to go a wave
Based on the above case analysis, we go through a wave of source code, from the perspective of the underlying source code to analyze why there is such a result.
Mybatis transaction manager
So far, I have been emphasizing Spring transactions. In fact, from a database perspective, SQL is a TRANSACTION as long as it is executed between START TRANSACTION and COMMIT or ROLLBACK. Mybatis also has its own transaction manager. Usually, we use Mybatis with Spring, and Spring integrates Mybatis. In the Mybatis -Spring package, Mybatis uses this class to manage the JDBC Connection lifecycle. This class is called SpringManagedTransaction. Although its name starts with Spring, it has nothing to do with Spring’s transaction manager.
Mybatis will create a SqlSession when executing SQL, about SqlSession, I sat next to the student Zhong asked me to create SqlSession mechanism, I specifically wrote an article, interested can have a look, here will not repeat the story:
“Zhong, this is for you!”
When SqlSession is created, a transaction manager is created accordingly:
Org. Mybatis. Spring. Transaction. SpringManagedTransactionFactory# newTransaction:
public Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level, boolean autoCommit) {
return new SpringManagedTransaction(dataSource);
}
Copy the codeWhen creating a Transaction, we find that the autoCommit passed in does not assign a value to SpringManagedTransaction at all.
Mybatis obtains a Connection object from the database connection pool from the transaction manager when executing the SQL:
Org. Mybatis. Spring. Transaction. SpringManagedTransaction# openConnection:
private void openConnection(a) throws SQLException {
this.connection = DataSourceUtils.getConnection(this.dataSource);
this.autoCommit = this.connection.getAutoCommit();
this.isConnectionTransactional = DataSourceUtils.isConnectionTransactional(this.connection, this.dataSource);
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(
"JDBC Connection ["
+ this.connection
+ "] will"
+ (this.isConnectionTransactional ? "" : " not ")
+ "be managed by Spring"); }}Copy the codeThe Connection object is retrieved from the database connection pool, and the autoCommit value in the Connection object is assigned to SpringManagedTransaction! In Mybatis, the autoCommit value is overwritten by the database connection pool. The JDBC connection object is not managed by Spring. Mybatis can manage the JDBC connection object by itself.
SQL > select * from Mybatis; select * from SqlSessionTemplate;
Org. Mybatis. Spring. SqlSessionTemplate. SqlSessionInterceptor:
if(! isSqlSessionTransactional(sqlSession, SqlSessionTemplate.this.sqlSessionFactory)) {
// force commit even on non-dirty sessions because some databases require
// a commit/rollback before calling close()
sqlSession.commit(true);
}
Copy the codeMybatis transaction manager commits to commit (Mybatis transaction manager)
Org. Mybatis. Spring. Transaction. SpringManagedTransaction# commit:
public void commit(a) throws SQLException {
if (this.connection ! =null&&!this.isConnectionTransactional && !this.autoCommit) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("Committing JDBC Connection [" + this.connection + "]");
}
this.connection.commit(); }}Copy the codeAs I mentioned earlier, the autoCommit of the druid database connection pool defaults to true. When the transaction manager obtains the connection object, it assigns the autoCommit value of the connection to the transaction manager. If autoCommit is true, Mybatis will consider the connection to have committed the transaction automatically. Mybatis will not commit the transaction if autoCommit is true.
If you set druid’s autoCommit to false, Mybaits will execute the following commit operations. Connection autocommit = true From the test results, it is clear that there is no COMMIT. The default value for autoCOMMIT is false, meaning that an explicit commit is required tocommit a transaction. This is why when Druid’s autoCommit=false, concurrent execution does not block, because Mybatis has already committed it for us.
When druid autoCommit=true, Mysql does not block. Mysql > enable log printing
set global general_log = 1;
Copy the code
Autocommit =1; autocommit=1; autocommit=1;
Spring transaction manager
In the above example, the concurrent execution of a Spring transaction does not block. It is obvious that the Spring transaction is doing something that cannot be described. There are several Spring transaction managers, and we use the database connection pool manager. Flexible call DataSourceTransactionManager, I here in order to control the transaction scope of fine-grained, use the declarative transaction, we went on a wave source, tracking all the way from the transaction entry to come in, found that the first step you need to call doBegin method:
. Org. Springframework. JDBC datasource. DataSourceTransactionManager# doBegin:
// Switch to manual commit if necessary. This is very expensive in some JDBC drivers,
// so we don't want to do it unnecessarily (for example if we've explicitly
// configured the connection pool to set it already).
if (con.getAutoCommit()) {
txObject.setMustRestoreAutoCommit(true);
if (logger.isDebugEnabled()) {
logger.debug("Switching JDBC Connection [" + con + "] to manual commit");
}
con.setAutoCommit(false);
}
Copy the codeIn the doBegin method, we discovered that it had secretly tampered with the value of the connection object autoCommit, setting it to false, and you can see how that works, Spring manages transactions by setting the current connection object to non-commit mode before SQL execution. Subsequent SQL execution will not commit automatically. When the transaction ends, Spring transaction manager will commit the transaction for us. This is why concurrent execution with Spring transactions does not block, as described in Mybatis above.
. Org. Springframework. JDBC datasource. DataSourceTransactionManager# doCleanupAfterCompletion:
// Reset connection.
Connection con = txObject.getConnectionHolder().getConnection();
try {
if (txObject.isMustRestoreAutoCommit()) {
con.setAutoCommit(true);
}
DataSourceUtils.resetConnectionAfterTransaction(con, txObject.getPreviousIsolationLevel());
}
catch (Throwable ex) {
logger.debug("Could not reset JDBC Connection after transaction", ex);
}
Copy the codeAfter the transaction completes, we also need to restore the Connection object. Because the connection exists in the connection pool, it is not really closed when it is closed, but is reclaimed from the connection pool. If we do not restore the connection object, Then, when the next session gets the Connection object, the autoCommit will still be the value of the previous session, causing some very subtle problems.
Write in the last
In fact, this problem from the application layer to analyze the good, directly lu source code is over, mainly this problem also involves some of the underlying principles of the database, because I am not so professional to the database, so in this process, but also asked the company’s DBA Bin brother, thank you very much for his assistance.
In addition, I don’t recommend using a pessimistic lock like for Update. It relies too much on the database layer, and when concurrency is high, while data consistency is guaranteed, performance is compromised, productivity is compromised, database resources are exhausted, and, as in this case, Some developers use for UPDATE and forget to commit transactions, causing many lock failures.
