Tags: public number article

If our business is at a very early stage and the concurrency level is low, we can go years without having a deadlock problem. If our business is at a very high concurrency level, then the occasional deadlock problem will cause us a lot of headaches. 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. 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.

The preparatory work

For the story to run smoothly, we need to build a table:

CREATE TABLE hero (
    id INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (id),
    KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;
Copy the code

We created a clustered index for the ID column of the Hero table and a secondary index for the name column. The hero table is used to store the heroes of The Three Kingdoms period. Insert some records into the table:

INSERT INTO hero VALUES
    (1, 'l liu bei'.'shu'),
    (3, 'Zhuge Liang'.'shu'),
    (8, 'cao cao c'.'魏'),
    (15, 'x xun yu'.'魏'),
    (20, 's sun quan'.'wu');
Copy the code

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 rowsin set (0.00 sec)
Copy the code

The preparations are done.

Create a deadlock scenario

Let’s create A situation where A deadlock occurs and execute two transactions in Session A and Session B respectively. The details are as follows:

Let’s analyze:

  • In step 3, Session A locks A record with id 1 on the cluster index of the hero table.

  • In step 4, Session B adds an x-type record lock to a record whose id is 3 on the hero cluster index.

  • Session A then tries to add A record lock (id = 3) to the hero cluster index (id = 3). Session A then tries to add A record lock (id = 3) to the hero cluster index (id = 3). Session A then tries to add A record lock (id = 3) to the hero cluster index (id = 3).

  • 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. 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
Copy the code

Above 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.

View the deadlock log

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 ... Omit a lot of other information -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the LATEST DETECTED DEADLOCK -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the 2019-06-20 13:39:19 0 x70000697e000 *** (1) TRANSACTION: TRANSACTION 30477, ACTIVE 10 sec starting indexread
mysql tables in use 1, locked 1
LOCK WAIT 3 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
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 4; hex 80000003; asc ;; 1: len 6; hex 000000007517; asc u ;; 2: len 7; hex 80000001d0011d; asc ;; 3: len 10; hex 7ae8afb8e8919be4baae; asc z ;; 4: len 3; hex e89c80; asc ;; *** (2) TRANSACTION: TRANSACTION 30478, ACTIVE 8 sec starting indexread
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 3, OS thread handle 123145412927488, query id 47 localhost 127.0.0.1 root statistics
select * from hero where id = 1 forupdate *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec  but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 000000007517; asc u ;; 2: len 7; hex 80000001d0011d; asc ;; 3: len 10; hex 7ae8afb8e8919be4baae; asc z ;; 4: len 3; hex e89c80; asc ;; *** (2) 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 30478 lock_mode X locks rec  but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000007517; asc u ;; 2: len 7; hex 80000001d00110; asc ;; 3: len 7; hex 6ce58898e5a487; asc l ;; 4: len 3; hex e89c80; asc ;; *** WE ROLL BACK TRANSACTION (2) ------------ ... A lot of other information is omittedCopy the code

We are only concerned with the most recent deadlocks, so let’s separate out the LATEST DETECTED DEADLOCK for analysis. Let’s see what the deadlock log output means line by line:

  • Look at the first sentence:

    2019-06-20 13:39:19 0x70000697e000
Copy the code

    The following hexadecimal string 0x70000697e000 indicates the thread ID assigned by the operating system to the current session.

  • Then there is information about the first transaction when the deadlock occurs:

    *** (1) TRANSACTION:

# set transaction id to 30477, transaction ACTIVE for 10 seconds, transaction is now doing: "starting index read"
TRANSACTION 30477, ACTIVE 10 sec starting index read

This transaction uses a table and locks the table (not a table that is being read consistently). For details on how to lock the table, see the lock statement analysis or the volume section.
mysql tables in use 1, locked 1

This transaction is in the LOCK WAIT state and has 3 LOCK structures (2 row locks and 1 table X type intended LOCK structure, which is discussed in the volume). Heap size is the amount of memory requested to store the LOCK structures (which we can ignore)
LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s)

MySQL > select * from 'id' where 'id' = '46' where 'id' = '46' where 'id' = '46' where 'id' = '46
MySQL thread id 2, OS thread handle 123145412648960, query id 46 localhost 127.0.0.1 root statistics

# The statement that this transaction is blocking
select * from hero where id = 3 for update

This transaction is currently waiting for the lock to be acquired:
*** (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 waitingHeap_no = 2 (heap_no = 2);
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

# This is the primary key
0: len 4; hex 80000003; asc     ;;

# This is the trx_id hidden column
1: len 6; hex 000000007517; asc     u ;;

# This is the roll_pointer hidden column
2: len 7; hex 80000001d0011d; asc        ;;

# This is the name column
3: len 10; hex 7ae8afb8e8919be4baae; asc z         ;;

# This is the country column
4: len 3; hex e89c80; asc    ;;
Copy the code

    In Session A, the transaction generated A lock structure for two records, but the lock (rec but not Gap) was not obtained for one record (table space 151, page 3, heap_no 2). InnoDB has a primary key of 80000003, which is InnoDB’s internal storage format and represents the number 3. The transaction is waiting for the x-type lock on the record with a primary key of 3 in the hero table.

  • Then there is information about the second transaction when the deadlock occurs:

    We’ve already covered most of this information, so let’s pick the important ones and say:

    *** (2) TRANSACTION:
TRANSACTION 30478, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1160, 2 row lock(s)
MySQL thread id 3, OS thread handle 123145412927488, query id 47 localhost 127.0.0.1 root statistics
select * from hero where id = 1 for update

# indicates the lock information acquired by the transaction*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec  but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0# primary key is 3
0: len 4; hex 80000003; asc     ;;
1: len 6; hex 000000007517; asc     u ;;
2: len 7; hex 80000001d0011d; asc        ;;
3: len 10; hex 7ae8afb8e8919be4baae; asc z         ;;
4: len 3; hex e89c80; asc    ;;

# indicates the lock information that the transaction is waiting for*** (2) 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 30478 lock_mode X locks rec  but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0# primary key is 1
0: len 4; hex 80000001; asc     ;;
1: len 6; hex 000000007517; asc     u ;;
2: len 7; hex 80000001d00110; asc        ;;
3: len 7; hex 6ce58898e5a487; asc l      ;;
4: len 3; hex e89c80; asc    ;;
Copy the code

    Session B acquires an X lock on a record whose primary key is 3 in the hero cluster index. A record lock of type X is waiting to be acquired for A record with primary key 1 on the Hero table. This means that the record lock of type X has been acquired by A transaction in SESSION A.

  • Look at the last part:

    *** WE ROLL BACK TRANSACTION (2)
Copy the code

    Eventually the InnoDB storage engine decided to roll back the second transaction, the one in Session B.

Ideas for deadlock analysis

  1. When looking at the deadlock log, first take a look at the statements waiting for the lock to be acquired by the transaction that issued the death or death lock.

    In this example, the statement where SESSION A is found to be blocked is:

    select * from hero where id = 3 for update
Copy the code

    SESSION B blocks from:

    select * from hero where id = 1 for update
Copy the code

    Then remember: go to your own business code and find the other statements in the transaction that these two statements are in.

  2. After all statements in the transaction where the deadlock occurred are found, the deadlock process is analyzed against the information about the locks acquired by the transaction and the locks that are waiting.

    SESSION A obtained A record lock of type X on the hero cluster index (id: 1). SESSION A obtained A record lock of type X on the hero cluster index (id: 1) It is found that the following statement is the cause of the lock (compared to the three articles) :

    select * from hero where id = 1 for update;
Copy the code

    SESSION B obtains an X lock on a record (id = 3) from the hero table. SESSION B obtains an X lock on a record (id = 3) from the hero table. SESSION B obtains a lock on a record (id = 3) from the hero table.

    select * from hero where id = 3 for update;
Copy the code

    Select * from SESSION A where SESSION A is waiting on A record (id = 3) where SESSION A is waiting on A record (id = 3);

    select * from hero where id = 3 for update;
Copy the code

    Select * from SESSION B where SESSION B is waiting on a record (id = 1) where SESSION B is waiting on a record (id = 1)

    select * from hero where id = 1 for update;
Copy the code

    The entire deadlock formation process is then restored from the deadlock log.

digression

Writing articles can be tiring. Sometimes you think the reading is smooth, but it’s actually the result of many revisions behind it. If you feel good, please help to forward it, thank you very much ~ here is my public number “we are all small frogs”, there are more technical dry goods, from time to time pull a calf, welcome to pay attention: