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

This article mainly introduces "how to solve the problem of Lock wait timeout exceeded". In daily operation, I believe many people have doubts about how to solve the problem of Lock wait timeout exceeded. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubt of "how to solve the problem of Lock wait timeout exceeded"! Next, please follow the editor to study!

Background

Recently, when troubleshooting problems, it was found that there was an occasional database lock timeout, and an error message like the following occurred:

Exception in thread "pool-3-thread-1" org.springframework.dao.CannotAcquireLockException: # # Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction### The error may involve com.zr.center.mybatis.auto.mapper.UserMapper.updateByExampleSelective-Inline### The error occurred while setting parameters### SQL: update user SET user_name =? WHERE (user_id =?) # Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction;]; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded Try restarting transaction at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate (SQLErrorCodeSQLExceptionTranslator.java:262) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate (AbstractFallbackSQLExceptionTranslator.java:72) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible (MyBatisExceptionTranslator.java:75) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke (SqlSessionTemplate.java:447) at com.sun.proxy.$Proxy101.update (Unknown Source) at org.mybatis.spring.SqlSessionTemplate .update (SqlSessionTemplate.java:295) at org.apache.ibatis.binding.MapperMethod.execute (MapperMethod.java:59) at org.apache.ibatis.binding.MapperProxy.invoke (MapperProxy.java:53) at com.sun.proxy.$Proxy103.updateByExampleSelective (Unknown Source) at com.zr.center.framework.web.service.BaseService.updateByExampleSelective (BaseService.java:97) at com.zr.center.api.test.service.TestService.updateUserName (TestService.java:34) at com.zr.center.api.test.service.TestService$$FastClassBySpringCGLIB$$bd3aa32.invoke () at org.springframework.cglib.proxy.MethodProxy.invoke (MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint (CglibAopProxy.java:746) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed (ReflectiveMethodInvocation.java:163) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction (TransactionAspectSupport.java:294) ) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke (TransactionInterceptor.java:98) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed (ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept (CglibAopProxy.java:688) at com.zr.center.api.test.service.TestService$$EnhancerBySpringCGLIB$$59b19302.updateUserName () at com.zr.center.ApplicationTests.lambda$testTxLockWaiting$0 (ApplicationTests.java:32) at java. Util.concurrent.ThreadPoolExecutor.runWorker (ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:617) at java.lang.Thread.run (Thread.java:745) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded Try restarting transaction at sun.reflect.NativeConstructorAccessorImpl.newInstance0 (Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance (NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance (DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance (Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance (Util.java:425) at com.mysql.jdbc.Util.getInstance (Util.java:408) At com.mysql.jdbc.SQLError.createSQLException (SQLError.java:952) at com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:3976) at com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:3912) at com.mysql.jdbc.MysqlIO.sendCommand (MysqlIO.java:2530) at com.mysql.jdbc.MysqlIO.sqlQueryDirect (MysqlIO.java:2683) at com.mysql.jdbc.ConnectionImpl.execSQL (ConnectionImpl.java:2486) troubleshooting

After troubleshooting, there is no deadlock in the log given by DBA, and the cause of deadlock is eliminated. Querying the business log shows that there are sometimes repeated requests in the period of high concurrency, and a service will send a mq message when processing a certain logic, and this message will be consumed at the same time, which will also cause lock timeout. The reason for timeout is that there is too much logic to be processed in a transaction, such as calling external services (timeout), and updating multiple other tables, which will cause subsequent transaction requests to time out and report the above error.

Reproduce the step database configuration

For the view of configuration information, you can see that transaction isolation is RR, and the transaction lock waiting time is 50s by default.

Transaction code / * * @ description: user service timeout test * @ author: chong guo * @ create: 2018-12-10 14:44 * / @ Service@Slf4jpublic class TestService extends BaseService {/ * * Update user name * * @ param userId * @ param name * / @ Transactional (rollbackFor = Exception.class) public void updateUserName (Long userId) String name) throws InterruptedException {log.info ("start updating user name [{}]] User ID is [{}] ", name, userId) UserExample userExample = new UserExample (); userExample.createCriteria (). AndUserIdEqualTo (userId); User user = new User (); user.setUserName (name); super.updateByExampleSelective (user, userExample); / / Simulation service timeout, it is possible to call external remote service timeout, or to handle other logical Thread.sleep (55000) Log.info ("end updating user name [{}], user ID is [{}]", name, userId);}} simulate concurrency / * * @ author Chong Guo * / @ RunWith (SpringRunner.class) @ SpringBootTest@Slf4jpublic class ApplicationTests {@ Resource TestService testService; private final int threadCount = 5; @ Test public void testTxLockWaiting () throws InterruptedException {CountDownLatch countDownLatch = new CountDownLatch (threadCount); ExecutorService threadPool = Executors.newFixedThreadPool For (int I = 0; I

{try {testService.updateUserName (611526166943105024L, "chongguo");} catch (InterruptedException e) {/ / TODO Auto-generated catch block e.printStackTrace ();} finally {countDownLatch.countDown ();}}) } countDownLatch.await (); threadPool.shutdown (); log.info ("Test tx lock is over"); Thread.sleep (100000);}}

After running the code, there will be three failures and two successes, all due to lock timeout

At this point, the study on "how to solve the problem of Lock wait timeout exceeded" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.