Mysql – Select query on an index column with gap locking

deadlockinnodblockingMySQL

I am trying to understand in which cases will a select query make a gap locking on an indexed column select.

What I read so far:

  • Select queries use the repeatable read mode in InnoDB and will not lock any row in any isolation level except serialized.
  • If you use select.. for update, or LOCK IN SHARE MODE, than a lock will be performed.

Now I have received a deadlock between these 2 transactions:

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

And:

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

Where the fileso.lastSeen column has a non-clustered index.

In this case, I got a deadlock on the lastseen index. But from what I have read that shouldn't happen when using select without specifying 'for update' or 'lock in shared mode'. So why am I getting a deadlock here?

My question is: Does a subquery select with an update in the outer query act like a 'select from update'?

InnoDB Status

------------------------
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)

Table definition

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;

Best Answer

It took me some time to find a plausible reason because your statement update .... select acquires a shared lock. I have thought of the following reasons.

  • statements executed before current one showed in innodb status
  • something about transaction isolation level
  • something related to a index or foreign constraint

Then I read the manual:

14.3.4 Consistent Nonlocking Reads

The type of read varies for selects in clauses like INSERT INTO ... SELECT, UPDATE ... (SELECT), and CREATE TABLE ... SELECT that do not specify FOR UPDATE or LOCK IN SHARE MODE.

Select...update happens to take a shared lock on selected the table. I have done the following experiment:

Setting the following variables has the effect to print on error log the innodb monitor with extra informations about locks. (I use MySQL 5.7, but you can activate the innodb monitor for previous version in different way).

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

I created the following tables:

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);

And finally I issued a query similar to yours:

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;

Without commit. If you look at the MySQL error log, you will find something like this:

---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     ;;

So insert ... select has acquired three locks in particular one shared on fo table and another shared on foi table. Previous table are involved into the select subquery.

Related Question