MySQL上帶有innodb的多列索引的奇怪死鎖
我在 MySQL 中看到了一些奇怪的（對我而言）行為。讓我們從我將要討論的表格開始。
CREATE TABLE `active_foo` ( `active_foo_id` bigint(20) NOT NULL AUTO_INCREMENT, `c_id` bigint(20) NOT NULL, `aa_id` bigint(20) NOT NULL, `bar` varchar(60) DEFAULT NULL, `foo_string` varchar(60) NOT NULL, `handle_id` bigint(20) NOT NULL DEFAULT '-1', `hostname` varchar(64) DEFAULT NULL, `usage` int(11) DEFAULT NULL, `foo_id` int(11) NOT NULL, `bucket_id` int(11) NOT NULL, PRIMARY KEY (`active_foo_id`), KEY `idx_active_foo_foo_config` (`foo_id`), KEY `idx_active_foo_aa` (`aa_id`), KEY `foo_index` (`c_id`,`foo_id`,`foo_string`), KEY `idx_active_foo_buckets1` (`bucket_id`), KEY `handle_idx` (`handle_id`), KEY `host_idx` (`hostname`), CONSTRAINT `fk_active_foo_aa_idx` FOREIGN KEY (`aa_id`) REFERENCES `aa` (`access_id`) ON DELETE CASCADE, CONSTRAINT `fk_active_foo_buckets1` FOREIGN KEY (`bucket_id`) REFERENCES `foo_buckets` (`bucket_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_active_foo_foo_config` FOREIGN KEY (`foo_id`) REFERENCES `foo_config` (`foo_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;我有兩個看起來像這樣的插入：
insert into active_foo(c_id, foo_string, `usage`, foo_id, bucket_id, aa_id) values(6, '#TAG', 1, 1, 2, 3268192)和
insert into active_foo(c_id, foo_string, `usage`, foo_id, bucket_id, aa_id) values(7, '#TAG', 1, 1, 1, 3268193)不知何故，它們似乎在同一個索引上死鎖，但我很困惑，因為我希望只有當涉及第二個索引並且一個持有而另一個等待時才會發生死鎖。最新的死鎖報告似乎表明他們實際上正在等待同一個索引，我認為這只會導致一個小的延遲，而不是死鎖。
180904 12:32:02 *** (1) TRANSACTION: TRANSACTION 34018365, ACTIVE 1 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 23 lock struct(s), heap size 3112, 17 row lock(s), undo log entries 3 MySQL thread id 459744, OS thread handle 0x2b42d5470700, query id 603275321 someotherip myuser update insert into active_foo(c_id, foo_string, `usage`, foo_id, bucket_id, aa_id) values(6, '#TAG', 1, 1, 2, 3268192) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 60 page no 4 n bits 152 index `foo_index` of table `mydb`.`active_foo` trx id 34018365 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 34 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 8; hex 80000000000000b7; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 23; hex 23544147; asc #TAG;; 3: len 8; hex 800000000005b184; asc ;; *** (2) TRANSACTION: TRANSACTION 3401837B, ACTIVE 1 sec inserting mysql tables in use 1, locked 1 21 lock struct(s), heap size 3112, 13 row lock(s), undo log entries 3 MySQL thread id 460530, OS thread handle 0x2b415579b700, query id 603275331 someip myuser update insert into active_foo(c_id, foo_string, `usage`, foo_id, bucket_id, aa_id) values(7, '#TAG', 1, 1, 1, 3268193) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 60 page no 4 n bits 152 index `foo_index` of table `mydb`.`active_foo` trx id 3401837B lock_mode X locks gap before rec Record lock, heap no 34 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 8; hex 80000000000000b7; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 23; hex 23544147; asc #TAG;; 3: len 8; hex 800000000005b184; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 60 page no 4 n bits 152 index `foo_index` of table `mydb`.`active_foo` trx id 3401837B lock_mode X locks gap before rec insert intention waiting Record lock, heap no 34 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 8; hex 80000000000000b7; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 23; hex 23544147; asc #TAG;; 3: len 8; hex 800000000005b184; asc ;; *** WE ROLL BACK TRANSACTION (2)請注意，
foo_index不僅是事務 2 持有的鎖，還有等待授予的鎖。根據最新的死鎖報告，我無法確定哪一行被鎖定，我只知道foo_string在這兩種情況下它都包含列的#TAG。我想一個鎖可能用於 c_id 6，另一個鎖用於 c_id 7。這就是我開始質疑我對索引鎖定如何準確工作的理解的地方，尤其是在涉及多列索引時。用於確定要鎖定哪一行的列的順序是否未定義？例如，使用以下索引：
KEY `foo_index` (`c_id`,`foo_id`,`foo_string`)是否可以一個插入首先鎖定與左->右列匹配的行，第二個插入鎖定與右->左列匹配的行？似乎這可能會造成死鎖，因為 foo_string #TAG 首先針對一個查詢被鎖定，但最後一個查詢會被鎖定。關於為什麼我似乎應該只是鎖定、等待和繼續前進時為什麼會陷入僵局的任何想法？
lock_mode X locks gap before rec insert從中拉出的關鍵詞是“鎖定差距”。MySQL具有
Gap Locks鎖定頁面內索引記錄之間的間隙。由於第二條記錄插入到與第一條記錄相鄰的位置，因此它們都在等待相同的gap lock.https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/
間隙鎖是對索引記錄之間間隙的鎖。由於這個間隙鎖，當您執行相同的查詢兩次時，您會得到相同的結果，而不管該表上的其他會話修改。這使讀取保持一致，從而使伺服器之間的複制保持一致。如果您執行 SELECT * FROM id > 1000 FOR UPDATE 兩次，您希望兩次獲得相同的值。為此，InnoDB 使用排他鎖鎖定 WHERE 子句找到的所有索引記錄，並使用共享間隙鎖鎖定它們之間的間隙。
你不是唯一遇到這些的人： MySql Gap Lock Deadlock on Inserts。沒有真正的解決方案。