InnoDB中外键与锁

环境：MySQL 5.7.26

外键主要用于引用完整性的约束检查。在InnoDB中，对于一个外键列，如果没有显式地对这个列加索引, 引擎会自动对其加一个索引。

外键的插入或更新，首先需要查询父表中的记录，即select父表，对于父表的select操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题。因此使用的是 select ... lock in share mode 方式，会主动对父表加一个 S 锁。

表与数据

create table a
(
    id   int auto_increment
        primary key,
    name varchar(64) null,
    age  int         null,
    b_id int         null,
    constraint a_b_id_fk
        foreign key (b_id) references b (id)
);
[
  {
    "id": 3,
    "name": "hua",
    "age": 30,
    "b_id": 3
  }
]
create table b
(
    id   int auto_increment
        primary key,
    name varchar(64) null,
    time bigint      null
);
[
  {
    "id": 4,
    "name": "t",
    "time": 224
  }
]

事务执行流程

	事务1 trx_id：56475	事务2 trx_id：56476
步骤	select * from b where id=4 for update
		update a set b_id=4 where id=3

查看事务与锁

[
    {
      "trx_id": "56476",
      "trx_state": "LOCK WAIT",
      "trx_started": "2022-08-22 09:51:34",
      "trx_requested_lock_id": "56476:226:3:5",
      "trx_wait_started": "2022-08-22 09:51:34",
      "trx_weight": 5,
      "trx_mysql_thread_id": 37,
      "trx_query": "/* ApplicationName=DataGrip 2022.2 */ update a set b_id=4 where id=3",
      "trx_operation_state": "updating or deleting",
      "trx_tables_in_use": 1,
      "trx_tables_locked": 2,
      "trx_lock_structs": 4,
      "trx_lock_memory_bytes": 1136,
      "trx_rows_locked": 2,
      "trx_rows_modified": 1,
      "trx_concurrency_tickets": 0,
      "trx_isolation_level": "REPEATABLE READ",
      "trx_unique_checks": 1,
      "trx_foreign_key_checks": 1,
      "trx_last_foreign_key_error": null,
      "trx_adaptive_hash_latched": 0,
      "trx_adaptive_hash_timeout": 0,
      "trx_is_read_only": 0,
      "trx_autocommit_non_locking": 0,
      "lock_id": "56476:226:3:5",
      "lock_trx_id": "56476",
      "lock_mode": "S",
      "lock_type": "RECORD",
      "lock_table": "`dps`.`b`",
      "lock_index": "PRIMARY",
      "lock_space": 226,
      "lock_page": 3,
      "lock_rec": 5,
      "lock_data": "4"
    },
    {
      "trx_id": "56475",
      "trx_state": "RUNNING",
      "trx_started": "2022-08-22 09:51:31",
      "trx_requested_lock_id": null,
      "trx_wait_started": null,
      "trx_weight": 2,
      "trx_mysql_thread_id": 36,
      "trx_query": null,
      "trx_operation_state": null,
      "trx_tables_in_use": 0,
      "trx_tables_locked": 1,
      "trx_lock_structs": 2,
      "trx_lock_memory_bytes": 1136,
      "trx_rows_locked": 1,
      "trx_rows_modified": 0,
      "trx_concurrency_tickets": 0,
      "trx_isolation_level": "REPEATABLE READ",
      "trx_unique_checks": 1,
      "trx_foreign_key_checks": 1,
      "trx_last_foreign_key_error": null,
      "trx_adaptive_hash_latched": 0,
      "trx_adaptive_hash_timeout": 0,
      "trx_is_read_only": 0,
      "trx_autocommit_non_locking": 0,
      "lock_id": "56475:226:3:5",
      "lock_trx_id": "56475",
      "lock_mode": "X",
      "lock_type": "RECORD",
      "lock_table": "`dps`.`b`",
      "lock_index": "PRIMARY",
      "lock_space": 226,
      "lock_page": 3,
      "lock_rec": 5,
      "lock_data": "4"
    }
  ]

分析

事务1 执行 select * from b where id=4 for update 对b中的id=4的行添加 X 锁

事务2 执行 update a set b_id=4 where id=3， 更新 a 中 id=3 的外键数据时，需要对 b 中 id=4 的行添加 S 锁，此时会阻塞。从上面的数据可以看到 事务2 中的锁为 共享锁(S)。

"lock_mode": "S"

如果将事务1中的语句改为select * from b where id=4 lock in share mode，则事务2 中的语句不会阻塞。