Mysql

在具有間隙鎖定的索引列上選擇查詢

  • March 21, 2016

我試圖了解在哪些情況下選擇查詢會對索引列選擇進行間隙鎖定。

到目前為止我讀到的：

  • Select 查詢在 InnoDB 中使用可重複讀取模式，並且不會鎖定除序列化之外的任何隔離級別的任何行。
  • 如果您使用 select.. 進行更新或 LOCK IN SHARE MODE，則會執行鎖定。

現在我收到了這兩個事務之間的死鎖：

UPDATE indicators.filesi fi
JOIN
(
   SELECT fo.sha256
   FROM indicators.filesoi fo
   WHERE fo.lastSeen > '2016-03-16 11:10:22' 
) x ON x.sha256=fi.sha256 
SET hasAutorunOccurances=1

和：

INSERT IGNORE INTO indicators.fileso (hostId, ...) 
VALUES                                  
('8662', ...),                          
('9143', ...),
on duplicate key update ....

其中fileo.lastSeen列具有非聚集索引。

在這種情況下，我在lastseen索引上遇到了死鎖。但是從我讀到的內容來看，在沒有指定“更新”或“鎖定共享模式”的情況下使用 select 時不應該發生這種情況。那麼為什麼我會在這裡陷入僵局？

我的問題是：在外部查詢中選擇帶有更新的子查詢是否像“從更新中選擇”？

InnoDB 狀態

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-03-16 11:15:24 2580
*** (1) TRANSACTION:
TRANSACTION 2264758566, ACTIVE 1 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 13566 lock struct(s), heap size 1603112, 43921 row lock(s)
MySQL thread id 1319, OS thread handle 0x1c64, query id 260771445 localhost 127.0.0.1 root Sending data
UPDATE indicators.filesi fi
JOIN
(
   SELECT fo.sha256
   FROM indicators.filesoi foi
   JOIN indicators.fileso fo ON foi.uniqueness = fo.uniqueness
   WHERE fo.lastSeen > '2016-03-16 11:10:22'  AND foi.fileType IN (3, 4, 50, 52, 53, 54, 56, 57, 58, 59, 60, 
   61,62,63,64,65,66,67)
) x ON x.sha256=fi.sha256 
SET hasAutorunOccurances=1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 859892 page no 1508578 n bits 888 index `lastSeen` of table `indicators`.`fileso` trx id 2264758566 lock mode S waiting

*** (2) TRANSACTION:
TRANSACTION 2264758569, ACTIVE 1 sec updating or deleting
mysql tables in use 1, locked 1
42 lock struct(s), heap size 6544, 72 row lock(s), undo log entries 39
MySQL thread id 1310, OS thread handle 0x2580, query id 260771450 localhost 127.0.0.1 root update
INSERT IGNORE INTO indicators.fileso (hostId, sha256, p_runningUser, NTFSOwner, fullPath, 
                                   fileName, s_serviceName, d_driverName, p_commandLineParams,
                                   p_ParentPath ,ar_regPath, ar_regKey, ar_regValue, hostName, extension, p_parentUser,
                                   uniqueness, riskLevel) VALUES ('8662',0xB2816FFDF5D612811E9A6CBDCD16C4271A7C16179E410E7C9F8CDE40BB6CC38B,'NT AUTHORITY\\SYSTEM','NT SERVICE\\TrustedInstaller','c:\\windows\\system32\\conhost.exe','conhost.exe','','','"-6004143636298202223525083761531271068-1940101893-8600483621900200321-29408863','c:\\windows\\system32\\csrss.exe','',
                                   '','','DAVIDMI','exe','NT AUTHORITY\\SYSTEM',0x1fdecd56f60185cf77581b1841d6ffe7ebdd100de05471c55aa01b238d40ac34,'-45'),('9143',0xB2816FFDF5D612811E9A6CBDCD16C4271A7C16179E410E7C9F8CDE40BB6CC38B,'NT AUTHOR
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 859892 page no 1508578 n bits 888 index `lastSeen` of table `indicators`.`fileso` trx id 2264758569 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 859892 page no 1508578 n bits 904 index `lastSeen` of table `indicators`.`fileso` trx id 2264758569 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)

表定義

CREATE TABLE `fileso` (
   `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
   `hostId` INT(11) UNSIGNED NOT NULL COMMENT 'code',
   `sha256` BINARY(32) NOT NULL COMMENT 'meir',
   `fileName` VARCHAR(150) NULL DEFAULT NULL COMMENT 'meir',
   `fullPath` VARCHAR(350) NULL DEFAULT NULL COMMENT 'meir',
   `datein` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `lastSeen` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   `lastRA` TIMESTAMP NULL DEFAULT NULL COMMENT '?',
   `riskLevel` SMALLINT(6) NOT NULL DEFAULT '-1' COMMENT '?',
   `policiesViolated` VARCHAR(1000) NULL DEFAULT NULL,
   `NTFSOwner` VARCHAR(255) NULL DEFAULT NULL COMMENT 'meir',
   `NTFSOwnerId` INT(11) NOT NULL DEFAULT '-1' COMMENT 'onIdle -done',
   `p_runningUser` VARCHAR(255) NULL DEFAULT NULL COMMENT 'meir',
   `p_parentUser` VARCHAR(255) NULL DEFAULT NULL,
   `p_commandLineParams` VARCHAR(1000) NULL DEFAULT NULL COMMENT 'meir',
   `p_runningUserId` INT(11) NOT NULL DEFAULT '-1' COMMENT 'onIdle -done',
   `p_ParentPath` VARCHAR(350) NULL DEFAULT NULL COMMENT 'meir',
   `ar_regPath` VARCHAR(350) NULL DEFAULT NULL COMMENT 'meir',
   `ar_regKey` VARCHAR(100) NULL DEFAULT NULL COMMENT 'meir',
   `ar_regValue` VARCHAR(350) NULL DEFAULT NULL COMMENT 'meir',
   `s_serviceName` VARCHAR(100) NULL DEFAULT NULL COMMENT 'meir',
   `d_driverName` VARCHAR(100) NULL DEFAULT NULL COMMENT 'meir',
   `hostName` CHAR(20) NULL DEFAULT NULL,
   `extension` CHAR(5) NOT NULL DEFAULT '',
   `uniqueness` BINARY(32) NOT NULL DEFAULT '0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0',
   PRIMARY KEY (`id`),
   UNIQUE INDEX `uniqueness` (`uniqueness`),
   INDEX `p_runningUserId` (`p_runningUserId`),
   INDEX `riskLevel` (`riskLevel`),
   INDEX `NTFSOwnerId` (`NTFSOwnerId`),
   INDEX `sha256` (`sha256`, `extension`),
   INDEX `lastSeen` (`lastSeen`),
   INDEX `datein` (`datein`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2328978;

我花了一些時間才找到一個合理的原因，因為您的語句update .... select獲得了共享鎖。我想到了以下幾個原因。

  • 在目前語句以 innodb 狀態顯示之前執行的語句
  • 關於事務隔離級別的一些東西
  • 與索引或外部約束有關的東西

然後我閱讀了手冊：

14.3.4 一致的非鎖定讀

讀取的類型因子句中的選擇而異，如INSERT INTO ... SELECT,UPDATE ... (SELECT)和CREATE TABLE ... SELECT未指定FOR UPDATEor LOCK IN SHARE MODE。

Select...update碰巧對選定的表進行了共享鎖定。我做了以下實驗：

設置以下變數會在錯誤日誌上列印 innodb 監視器，其中包含有關鎖的額外資訊。（我使用 MySQL 5.7，但您可以以不同的方式啟動以前版本的 innodb 監視器）。

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

我創建了以下表格：

create table fi  (i int primary key, hasAutorunOccurances int, sha256 int);
insert into fi (i, hasAutorunOccurances, sha256) values (1,1,1);
create table fo  (i int primary key, uniqueness int, sha256 int, lastSeen int);
insert into fo (i,uniqueness, sha256, lastSeen) values (1,1,1,1);
create table foi (i int primary key, uniqueness int);
insert into foi (i, uniqueness) values (1,1);

最後我發出了一個類似於你的查詢：

begin;

UPDATE fi
JOIN
(
   SELECT fo.sha256
   FROM foi
   JOIN fo  ON foi.uniqueness = fo.uniqueness
   WHERE fo.lastSeen > 1 
) x ON x.sha256=fi.sha256 
SET hasAutorunOccurances=1;

沒有承諾。如果您查看 MySQL 錯誤日誌，您會發現如下內容：

---TRANSACTION 21313, ACTIVE 20 sec
6 lock struct(s), heap size 1136, 6 row lock(s)
MySQL thread id 3, OS thread handle 123145316552704, query id 8870 localhost 127.0.0.1 root cleaning up
TABLE LOCK table `test_dbaexchange`.`fi` trx id 21313 lock mode IX
RECORD LOCKS space id 1120 page no 3 n bits 72 index PRIMARY of table `test_dbaexchange`.`fi` trx id 21313 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

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 00000000533d; asc     S=;;
2: len 7; hex b2000001120110; asc        ;;
3: len 4; hex 80000001; asc     ;;
4: len 4; hex 80000001; asc     ;;

TABLE LOCK table `test_dbaexchange`.`foi` trx id 21313 lock mode IS
RECORD LOCKS space id 1122 page no 3 n bits 72 index PRIMARY of table `test_dbaexchange`.`foi` trx id 21313 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000001; asc     ;;
1: len 6; hex 00000000533b; asc     S;;;
2: len 7; hex b0000001240110; asc     $  ;;
3: len 4; hex 80000001; asc     ;;

TABLE LOCK table `test_dbaexchange`.`fo` trx id 21313 lock mode IS
RECORD LOCKS space id 1121 page no 3 n bits 72 index PRIMARY of table `test_dbaexchange`.`fo` trx id 21313 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000001; asc     ;;
1: len 6; hex 00000000533c; asc     S<;;
2: len 7; hex b1000001250110; asc     %  ;;
3: len 4; hex 80000001; asc     ;;
4: len 4; hex 80000001; asc     ;;
5: len 4; hex 80000001; asc     ;;

因此insert ... select獲得了三個鎖，特別是一個在 table 上共享fo，另一個在foitable 上共享。前一個表涉及到 select 子查詢。

引用自：https://dba.stackexchange.com/questions/132724

相關問答

Mysql

使用多個 WHERE 列最小化對 UPDATE 的鎖定

  • July 18, 2019
檢視問答
Mysql

MySQL：安全地殺死卡在“等待表元數據鎖定”中的事務

  • January 10, 2018
檢視問答
Mysql

如何使選擇語句被阻止？

  • August 9, 2017
檢視問答
Mysql

‘LATEST DETECTED DEADLOCK’ InnoDB 狀態輸出中缺少語句資訊

  • January 11, 2022
檢視問答
Mysql

生成連續票號時如何避免死鎖？

  • December 18, 2021
檢視問答
Mysql

Mysql 鎖定 - 如何為每個 userId 在數據庫中獲取鎖定

  • December 18, 2021
檢視問答