MySQL thread id 3, OS thread handle 123145412927488, query id 47 localhost 127.0.0.1 root statistics
Select * from hero where id = 3 forupdate *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30477 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 5 compact format info bits 0 0: len 0003 asc 1: len 00007517 asc u 2: len 0001d0011d asc 3: len 10 hex 7ae8afb8e8919be4baae asc z 4: len 3 hex e89c80 asc *** (2) TRANSACTION: TRANSACTION 30478, ACTIVE 8 sec starting index readģ lock struct(s), heap size 1160, 2 row lock(s) MySQL thread id 2, OS thread handle 123145412648960, query id 46 localhost 127.0.0.1 root statistics LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s) Omit a lot of other information - the LATEST DETECTED DEADLOCK - the 13:39:19 0 x70000697e000 *** (1) TRANSACTION: TRANSACTION 30477, ACTIVE 10 sec starting index read InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB: InnoDB When a deadlock occurs in the example above, we run this command: mysql SHOW ENGINE INNODB STATUS\G. The MySQL server's deadlock detection mechanism detected the occurrence of a deadlock, so select a transaction to roll back and send a message to the client: ERROR 1213 (40001): Deadlock found when trying to get lock try restarting transactionĪbove is our from the perspective of what statement added a lock for a deadlock situation analysis, but in practical application, we may never know what a few statements produced a deadlock, we need according to MySQL in a deadlock occurs when a deadlock log to reverse what positioning a statement produced a deadlock, so as to optimize our business again. Session A and Session B are waiting for each other to hold A lock, and Session A and Session B are waiting for each other to hold A lock, and Session B is waiting for each other to hold A lock. Session A then tries to add A record lock (id = 3) to the hero cluster index (id = 3). In step 4, Session B adds an x-type record lock to a record whose id is 3 on the hero cluster index. In step 3, Session A locks A record with id 1 on the cluster index of the hero table. Let's create A situation where A deadlock occurs and execute two transactions in Session A and Session B respectively. Now the data in the table looks like this: mysql SELECT * FROM hero + - + - + - + | | id name | country | + - + - + - + | 1 | | | l liu bei shu zhuge liang | | 3 | z shu | | 8 Cao cao | | c w | | | x 15 xun yu | w | | | s | | wu sun quan2 20 + + - + - + 5 rows in set (0.00 sec) Insert some records into the table: INSERT INTO hero VALUES The hero table is used to store the heroes of The Three Kingdoms period. We created a clustered index for the ID column of the Hero table and a secondary index for the name column. The preparatory workįor the story to run smoothly, we need to build a table: CREATE TABLE hero ( In fact, if you read our previous three articles on statement lock analysis in MySQL, and this article on deadlock log analysis, it should not be so confusing to solve the deadlock problem. However, when the deadlock problem occurs, the first reaction of many inexperienced students is to become an ostrich: this thing is very deep, I don't understand, let fate, it doesn't happen all the time. If our business is at a very high concurrency level, then the occasional deadlock problem will cause us a lot of headaches. If our business is at a very early stage and the concurrency level is low, we can go years without having a deadlock problem.
0 kommentar(er)
