Concurrency Error: Transaction (process ID) has been deadlocked with another process on the lock resource, and the transaction has been selected as the deadlock victim

This is a database transaction error caused by the concurrency situation, and the background is introduced first.

Background

Springboot+springmvc+sqlserver+mybatis

A controller has five or six interfaces, these interfaces are used in spring transaction management, these interfaces are normal when a single call, when I simulate dozens of concurrent requests for these interfaces, there will always be one or two times the persistence of the MyBatis error, the specific error:

Nested exception isorg.apache.ibatis.exceptions.PersistenceException: # # # Error UpdatingDatabase. cause:java.lang.reflect.undeclaredthrowableexception### the error may involve com.xxxxxxxxx-inline### the error occurred whilesetting parameters### SQL:UpdateXxxxSetCcccc=? , Jid=? , status=?whereBcode=? andStatus!= 0# # Cause:java.lang.reflect.UndeclaredThrowableException

According to the hint of this undeclaredthrowableexception, to the online search is said MyBatis mapping file in the field attribute and the model of the write inconsistent.

But if the writing is not correct, should every request error Ah, now is dozens of times only one or two times the error, certainly not this reason.

Search on the internet for half a day, no fruit, so put the code on the server to simulate concurrent testing, or there will be one or two times the error, but the report of the error is very clear

# # # Error QueryingDatabase. Cause:com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID +) is deadlocked with another process on the lock resource and has been selected as a deadlock victim. Please rerun the transaction. # # # The error may existinch file [E:\kkkkk\xxxMapper.xml]# # # The error may involve com.xxxxx.ppppp### the error occurred whilehandling results### SQL:SELECTB.*  fromXXXXX A Right JOINOoooo b onA.gid=b.IDwhereA.bcode= '11111'# # # Cause:com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID +) is deadlocked with another process on the lock resource and has been selected as a deadlock victim. Please rerun the transaction.; SQL[]; Transaction (Process ID +) is deadlocked with another process on the lock resource and has been selected as a deadlock victim. Please rerun the transaction.; Nested exception isCom.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID +) is deadlocked with another process on the lock resource and has been selected as a deadlock victim. Please rerun the transaction.

The original is the concurrency caused by the database transaction error, the specific reasons and changes to see this: Spring in the transaction @transactional annotation Understanding

--------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------

I learned about the transaction mechanism, which was originally thrown in the database. Because different threads process the same data in a transaction, the database takes one execution and the other discards execution, and the error is the above. This error is not easy to insert because the insertion speed is fast and not easy to appear. At the same time, if you modify the data according to the condition of the speed will be slow, if you follow the primary key or index modification speed will be fast, and it is not easy to appear this error. @xmt1139057136, although did not give the concrete method actually is according to this thought to come out, thanks. By the way, I found that the speed of Tomcat in the local area network was significantly slower than that of the native, and could not handle the concurrent 1000 (this machine is possible), to consult.

--------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------

Workaround

1.Spring Exception retry Framework Spring Retry

2. Change the isolation level of the transaction to loose, read_uncommited

Concurrency Error: Transaction (process ID) has been deadlocked with another process on the lock resource, and the transaction has been selected as the deadlock victim