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

This article introduces the knowledge of "Mysql transaction deadlock triggers Rollback exception troubleshooting process". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The whole event originated from an Error, and the log is as follows:

2019-10-31 12 com.jollycorp.pop.web.PopExceptionResolver.doResolveException 55 com.jollycorp.pop.web.PopExceptionResolver.doResolveException 53953 ERROR [http-apr-8080-exec-5] (PopExceptionResolver.java:73) unresolved substitutions. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction### The error may involve com.jollycorp.pop.entity.pop.goods.PopGoodsMapper.updateByPrimaryKeySelective-Inline### The error occurred while setting parameters### SQL: update pop_goods SET... Where goods_id =?. # Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction...

The log makes it clear that a deadlock was found when updating a row of data and a MySQLTransactionRollbackException exception was thrown.

So the question is, why is it deadlocked?

Mysql InnoDB provides a deadlock detection mechanism, which is implemented by Wait-For-Graph algorithm. To put it simply, the transaction and the lock it is waiting for are maintained into a directed graph, and then the loop is checked. If a loop is found, a deadlock occurs. InnoDB needs to roll back a transaction to break the ring, so the above exception is thrown.

So, for the update on the log, there must be another transaction waiting for a mutual lock with the transaction in which the update is located. To find another transaction, you can execute a show engine innodb status query in the database for the most recent deadlock, as shown in the log:

LATEST DETECTED DEADLOCK----2019-10-29 16:15:09 7f3c0eba1700 questions * (1) TRANSACTION:TRANSACTION 872050326, ACTIVE 0 sec starting index readmysql tables in use 1, locked 1LOCK WAIT 42 lock struct (s), heap size 6544, 3 row lock (s), undo log entries 1MySQL thread id 79774, OS thread handle 0x7f3c93c5c700 Query id 57613264 172.room.room.10 pop_mq Searching rows for updateupdate pop_sku_relation set status = 0 where goods_rec_id... * * (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 5155 page no 23 n bits 144index `PRIMARY`of table `jolly_pop_ center`.`pop _ sku_ room`trx id 872050326 lock_mode X locks rec but not gap waitingRecord lock, heap no 38 PHYSICAL RECORD: n_fields 41 Compact format; info bits 00: len 4; hex 0000070d; asc;;... 40: len 1; hex 81; asc; * (2) TRANSACTION:TRANSACTION 872050288, ACTIVE 0 sec starting index readmysql tables in use 1, locked 14 lock struct (s), heap size 1184, 2 row lock (s), undo log entries 1MySQL thread id 79171, OS thread handle 0x7f3c0eba1700, query id 57613277 172.room.room.10 pop_seller updatingupdate pop_goods. Where goods_id = 1253 trx id * (2) HOLDS THE LOCK (S): RECORD LOCKS space id 5155 page no 23 n bits 144414 index `PRIMARY` of table `PRIMARY` trx id 872050288 lock_mode X locks rec but not gapRecord lock, heap no 38 PHYSICAL RECORD: n_fields 41; compact format; info bits 00: len 4; hex 0000070d; asc;;... 40: len 1; hex 81; asc * (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 11311 page no 66 n bits 96 index `PRIMARY` of table `PRIMARY`.`pop _ goods` trx id 872050288 lock_mode X locks rec but not gap waitingRecord lock, heap no 23 PHYSICAL RECORD: n_fields 92; compact format; info bits 00: len 3; hex 0004e5; asc;. 91: len 1; hex 80; asc; * * WE ROLL BACK TRANSACTION (2)

As you can see, transaction 1 (xid 872050326) waits for an X lock for pop_sku_relation:

RECORD LOCKS space id 5155 page no 23 n bits 144 index PRIMARY of table jolly_pop_center.pop_sku_relation trx id 872050326 lock_mode X locks rec but not gap waiting Record lock

Transaction 2 (xid 872050288) holds an X lock of pop_sku_relation (note that it is an one-line record):

RECORD LOCKS space id 5155 page no 23 n bits 144 index PRIMARY of table jolly_pop_center.pop_sku_relation trx id 872050288 lock_mode X locks rec but not gap Record lock

Transaction 2 (xid 872050288) then waits for an X lock for pop_goods:

RECORD LOCKS space id 11311 page no 66 n bits 96 index PRIMARY of table jolly_pop_center.pop_goods trx id 872050288 lock_mode X locks rec but not gap waiting Record lock

Transaction 2 (xid 872050288) is then rolled back:

* WE ROLL BACK TRANSACTION (2)

Here, you can ask DBA to help salvage the binlog of transaction 1 (xid 872050326), because after transaction 2 is rolled back, the deadlock is released, and if transaction 1 executes successfully, the binlog log will be recorded. You should find that transaction 1 also operates on the same line of pop_goods.

Finally, the tracking code shows that there is a transaction that updates the pop_goods first, and then updates the pop_sku_relation. In another transaction, pop_sku_relation is updated first, and then pop_goods is updated. In the case of concurrency, the above log occurs:

Transaction 1 locks the records of id=1253 in pop_goods with X

Transaction 2 locks the records of id=14616485 in pop_sku_relation with X

Transaction 1 requests an X lock for the record of id=14616485 in pop_sku_realtion, waiting because transaction 2 is already locked

Transaction 2 requests an X lock for the record of id=1253 in pop_goods, waiting because transaction 1 is already locked

Find that transaction 1 and transaction 2 wait for each other and roll back transaction 2

Finally, modify the code to make the update order consistent. In this case, the changes to different tables are inconsistent. In fact, deadlocks will occur if two transactions are out of order for different records of the same table. Therefore, if there are multiple records updated, different tables need to fix an update order, and different records of the same table need to be sorted and updated, so as to avoid deadlock.

This is the end of the content of "Mysql transaction deadlock triggers the troubleshooting process of Rollback exceptions". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.