A scenario

A deadlock problem was reported in an online database two years ago. First, the table structure:

CREATE TABLE `miza_log`.`blind_date_user_info` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id', 'blind_date_id' int(11) NOT NULL DEFAULT '0' COMMENT ', 'user_id' int(11) NOT NULL DEFAULT '0' COMMENT ', 'choose_id' int(11) NOT NULL DEFAULT '0' COMMENT '表 示 ', 'recv_gift' int(11) NOT NULL DEFAULT '0' COMMENT '表 示 ', 'send_gift' int(11) NOT NULL DEFAULT '0' COMMENT ', 'hat_num' int(11) NOT NULL DEFAULT '0' COMMENT ', 'img_url' varchar(256) NOT NULL DEFAULT '0' COMMENT 'match_result' int(11) NOT NULL DEFAULT '0' COMMENT '0: 1: The match is successful. 2: The match is successful. ', 'create_time' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create time ', 'modify_time' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modify time ', PRIMARY KEY (`id`), KEY `blind_date_id` (`blind_date_id`), KEY 'user_id' (' user_id ')ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' user_id ';Copy the code

Id, blind_date_id and user_id are indexed respectively.

SQL > select * from transaction where deadlock occurs

update blind_date_user_info set hat_num = hat_num + 1 where blind_date_id = 344 and  user_id = 2102142;

update blind_date_user_info set recv_gift = recv_gift + 0 where blind_date_id = 344  and user_id = 99264271;
Copy the code

SQL execution plan:

Deadlock log:

===================================== 2021-02-01 16:32:45 0x7f385337f700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 2 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 1101094 srv_active, 0 srv_shutdown, 40 srv_idle srv_master_thread log flush and writes: 1101061 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 566444 OS WAIT ARRAY INFO: signal count 2252846 RW-shared spins 0, rounds 2205774, OS waits 248328 RW-excl spins 0, rounds 36978240, OS waits 84670 RW-sx spins 1320303, rounds 11237389, OS waits 54800 Spin rounds per wait: RW - Shared 2205774.00, 36978240.00 RW - excl, 8.51 RW - sx -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the LATEST DETECTED DEADLOCK -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the 2021-02-01 16:30:23 0 x7f376cf43700  *** (1) TRANSACTION: TRANSACTION 1928467447, ACTIVE 0 sec fetching rows mysql tables in use 3, locked 3 LOCK WAIT 10 lock struct(s), heap size 1136, 4 row lock(s) MySQL thread id 481042, OS thread handle 139877068830464, Query ID 1006475452 172.17.0.83 root updating blind_date_user_info set hat_num = hat_num + 1 where blind_date_id = 344 and user_id = 2102142 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 12499 page no 66 n bits 280 index PRIMARY of table `miza_log`.`blind_date_user_info` trx id 1928467447 lock_mode X locks rec but not gap waiting Record lock, heap no 186 PHYSICAL RECORD: n_fields 13; compact format; info bits 0 0: len 4; hex 800020fc; asc ;; 1: len 6; hex 000072f213f6; asc r ;; 2: len 7; hex 770000707a116f; asc w pz o;; 3: len 4; hex 80000158; asc X;; 4: len 4; hex 85eaa70f; asc ;; 5: len 4; hex 80000000; asc ;; 6: len 4; hex 80001e46; asc F;; 7: len 4; hex 800003e8; asc ;; 8: len 4; hex 8000001f; asc ;; 9: len 0; hex ; asc ;; 10: len 4; hex 80000000; asc ;; 11: len 4; hex 6017b6b9; asc ` ;; 12: len 4; hex 6017bc1f; asc ` ;; *** (2) TRANSACTION: TRANSACTION 1928467448, ACTIVE 0 sec fetching rows mysql tables in use 3, locked 3 7 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 546874, OS thread handle 139876027873024, Query ID 1006475453 172.17.0.2 root updating update blind_date_user_info set recv_GIFT = recv_GIFT + 0 WHERE blind_date_id = 344 and user_id = 99264271 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 12499 page no 66 n bits 280 index PRIMARY of table `miza_log`.`blind_date_user_info` trx id 1928467448 lock_mode X locks rec but not gap Record lock, heap no 186 PHYSICAL RECORD: n_fields 13; compact format; info bits 0 0: len 4; hex 800020fc; asc ;; 1: len 6; hex 000072f213f6; asc r ;; 2: len 7; hex 770000707a116f; asc w pz o;; 3: len 4; hex 80000158; asc X;; 4: len 4; hex 85eaa70f; asc ;; 5: len 4; hex 80000000; asc ;; 6: len 4; hex 80001e46; asc F;; 7: len 4; hex 800003e8; asc ;; 8: len 4; hex 8000001f; asc ;; 9: len 0; hex ; asc ;; 10: len 4; hex 80000000; asc ;; 11: len 4; hex 6017b6b9; asc ` ;; 12: len 4; hex 6017bc1f; asc ` ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 12499 page no 53 n bits 720 index blind_date_id of table `miza_log`.`blind_date_user_info` trx id 1928467448 lock_mode X locks rec but not gap waiting Record lock, heap no 621 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000158; asc X;; 1: len 4; hex 800020fc; asc ;; *** WE ROLL BACK TRANSACTION (2)Copy the code

Analysis of the

You can see from the deadlock log that two transactions are currently executing using the same index (blind_date_id). The resources locked by transaction 1 are:

RECORD LOCKS space id 12499 page no 66 n bits 280 index PRIMARY of table `miza_log`.`blind_date_user_info` trx id 1928467447 lock_mode X locks rec but not gap waiting
Record lock, heap no 186 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
Copy the code

It is important to note that LOCKS res but not GAP means that an index is locked, not a range

Similarly, resources locked by transaction 2:

RECORD LOCKS space id 12499 page no 66 n bits 280 index PRIMARY of table `miza_log`.`blind_date_user_info` trx id 1928467448 lock_mode X locks rec but not gap
Record lock, heap no 186 PHYSICAL RECORD: n_fields 13;
Copy the code

As you can see, transaction 2 is also competing for the same resources as transaction 1. In addition, both transactions use X locks (exclusive locks, which are not compatible with any locks, as described in mysqL-InnoDB Technology Insider’s section on locks) and wait for each other.

Four rows are locked in transaction 1 and three rows are locked in transaction 2.

Question 1. What is the scenario that causes two transactions to be concurrent? Transaction 1 and transaction 2 update different rows. Why lock the index?

The first problem requires us to go back to the business scenario and find the code in the business code that calls these two SQL statements:

In the business, two Goroutines are used to execute two statements. So it’s not surprising that concurrency occurs.

Back to question 2, you need to understand what mysql was doing before executing this statement.

First, there are three row locking algorithms in mysql:

  • Record Lock: Locks a single row Record
  • Gap Lock: A Gap Lock that locks a range but does not contain the record itself
  • Next-key Lock: Gap Lock + Record Lock Locks a range, and locks the Record itself.

The Lock involved in this is the first kind of Lock Record Lock, so this article is mainly aimed at the problem of analysis, as for other locks and principles, will write the corresponding blog analysis. Note that Rcord Lock always locks index records.

Innodb locks rows by locking index entries. This means innoDB uses row-level locks only when it retrieves data by index criteria. Otherwise innoDB uses table locks and locks all scanned rows. In this case, two indexes blind_date_id and user_id are used for update. As you can see from the EXPLian statement parsing the SQL, both statements are range lookups and end up using the index user_id. Using WHERE with extra, InnoDB scans the number of rows returned and locks them. For transaction 1, InnoDB will first look for the row with user_id = 99264271 and return it, and then scan for blind_date_id = 344. During this process, all scanned data is locked. The same is true for transaction 2, where both indexes are locked and are also locked during the scan. Eventually, the same number of rows is locked by two transactions at the same time, resulting in a deadlock.

After analysis, we can know that the deadlock is due to InnoDB in the scan, so there are two methods: 1. You can pick up a joint index for blind_date_id and user_id so that you can use using index to determine the specific row during the transaction and avoid deadlocks. 2.

select id form blind_date_user_info where where blind_date_id = 344 and user_id = 2102142; update blind_date_user_info set hat_num = hat_num + 1 where id in (....) ;Copy the code

The select after Update method is used. Note that select cannot add for update, otherwise it will still cause a deadlock.

Method 1: Simply add an index. Method 2: Illusory reading will occur, but due to the low accuracy of its own business for data, the final solution adopted the second method. Only one Goroutine performs two transactions at the same time.

Reference:

“MySQL technology insider – InnoDB storage engines” second edition, the author: davidjiang cloud.tencent.com/developer/a… Yq.aliyun.com/articles/33… Dev.mysql.com/doc/refman/… www.fanyilun.me/2017/04/20/…