我在两个不同的事务中尝试这些查询：

START TRANSACTION;
DELETE FROM `StopHistory` WHERE `deviceId` = 34;

START TRANSACTION;
SELECT id FROM `StopHistory` WHERE deviceId = 33 AND endAt > '2018-06-18 17:01:32.473';

第二个查询等待，因为第一个查询正在endat字段上创建锁。

MySQL [(none)]> select * from INFORMATION_SCHEMA.INNODB_LOCKS;
+-----------------------+-------------+-----------+-----------+--------------------------+------------+------------+-----------+----------+--------------------------+
| lock_id               | lock_trx_id | lock_mode | lock_type | lock_table               | lock_index | lock_space | lock_page | lock_rec | lock_data                |
+-----------------------+-------------+-----------+-----------+--------------------------+------------+------------+-----------+----------+--------------------------+
| 104222007:699:1035:16 | 104222007   | S         | RECORD    | `db`.`StopHistory`       | endAt      |        641 |      1035 |       16 | 0x99A026D21307DA, 115347 |
| 104221958:699:1035:16 | 104221958   | X         | RECORD    | `db`.`StopHistory`       | endAt      |        641 |      1035 |       16 | 0x99A026D21307DA, 115347 |
+-----------------------+-------------+-----------+-----------+--------------------------+------------+------------+-----------+----------+--------------------------+

    ---TRANSACTION 104231466, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 376, 3 row lock(s)
MySQL thread id 59556, OS thread handle 0x2b1375f03700, query id 20584248 172.31.2.181 db Sending data
SELECT id FROM `StopHistory` WHERE deviceId = 33 AND endAt > '2018-06-18 17:01:32.473'
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 641 page no 1035 n bits 16 index `endAt` of table `db`.`StopHistory` trx id 104231466 lock mode S waiting
Record lock, heap no 16 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len=7; bufptr=0x2b134db1c16c; hex= 99a026d21307da; asc   &    ;;
 1: len=4; bufptr=0x2b134db1c173; hex= 8001c293; asc     ;;

------------------
---TRANSACTION 104231429, ACTIVE 11 sec
44 lock struct(s), heap size 376, 3103 row lock(s), undo log entries 1549
MySQL thread id 59555, OS thread handle 0x2b1375f03700, query id 20584210 172.31.2.181 db delayed send ok done

这个 StopHistory 表在deviceid上有一个外键，在endat上有一个索引

CREATE TABLE `StopHistory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `endAt` datetime(3) DEFAULT NULL,
  `deviceId` int(11) DEFAULT NULL,
  `beginAt` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_StopHystory_2_idx` (`deviceId`),
  KEY `beginAt` (`beginAt`),
  KEY `endAt` (`endAt`),
  KEY `device_beginAt` (`deviceId`,`beginAt`),
  CONSTRAINT `fk_StopHystory_2` FOREIGN KEY (`deviceId`) REFERENCES `Devices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=115351 DEFAULT CHARSET=utf8;

解释的输出：

MySQL [db]> explain DELETE FROM `StopHistory` WHERE `deviceId` = 34 \G

***************************1. row***************************

           id: 1
  select_type: SIMPLE
        table: StopHistory
         type: range
possible_keys: PRIMARY,fk_StopHystory_1_idx,fk_StopHystory_2_idx,fk_StopHistory_1_idx,beginAt,endAt,device_beginAt
          key: fk_StopHystory_2_idx
      key_len: 5
          ref: NULL
         rows: 1548
        Extra: Using where

为什么endat字段被锁定？