请选择 进入手机版 | 继续访问电脑版
查看: 56|回复: 0

[资料分享] 基于innodb表的外键和锁使用解析

[复制链接]

签到天数: 193 天

[LV.7]化身百千

发表于 2019-3-13 20:25:21 | 显示全部楼层 |阅读模式

在innodb表中,创建外键的时候若外键列上没有索引,则会在创建过程中自动在外键列上隐式地创建索引。

存在这样一种情况,当向子表中插入数据的时候,会向父表查询该表中是否存在对应的值以判断将要插入的记录是否满足外键约束,也就是说会对父表中对应的记录加上依赖性的共享锁,并在表上加意向共享锁。如果此时父表上对应的记录正好有独占锁,那么插入就会失败。同理,从子表中删除或更新记录也是一样的。

现在创建父表parent和子表child,并不要在外键列(pid)上显式创建索引。

create table parent(pid int primary key);
create table child(cid int primary key,pid int,foreign key(pid) references parent(pid));
show create table child\G
*************************** 1. row ***************************
       Table: child
Create Table: CREATE TABLE `child` (
  `cid` int(11) NOT NULL,
  `pid` int(11) DEFAULT NULL,
  PRIMARY KEY (`cid`),
  KEY `pid` (`pid`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `parent` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
从show的结果中可以发现,已经自动添加了索引列pid。

插入一些测试记录。

insert into parent values(1),(2),(3);
在会话1中执行:

begin;
delete from parent where pid=3;
在会话2中执行:

begin;
insert into child select 3,3;
这时会发现会话2被阻塞了。通过innodb_trx和innodb_locks表的联合,得到如下结果:

SELECT
    trx_id,
    trx_state,
    lock_id,
    lock_mode,
    lock_type,
    lock_table,
    trx_mysql_thread_id,
    trx_query
FROM
    information_schema.innodb_trx t
JOIN information_schema.innodb_locks l ON l.lock_trx_id = t.trx_id\G
*************************** 1. row ***************************
             trx_id: 14951
          trx_state: LOCK WAIT
            lock_id: 14951:185:3:4
          lock_mode: S
          lock_type: RECORD
         lock_table: `test`.`parent`
trx_mysql_thread_id: 34
          trx_query: insert into child select 3,3
*************************** 2. row ***************************
             trx_id: 14946
          trx_state: RUNNING
            lock_id: 14946:185:3:4
          lock_mode: X
          lock_type: RECORD
         lock_table: `test`.`parent`
trx_mysql_thread_id: 1
          trx_query: NULL
不难看出,insert语句想要在父表parent上的资源"14951:185:3:4"加共享锁,但是此时父表上该资源已经有了独占锁,所以被阻塞了。

并且也可以判断出,通过外键读取父表时的模式是lock in share mode,而不是基于快照的行版本读(什么是lock in share mode和行版本快照读见事务隔离级别内容),假如是基于行版本的快照读,那么就可以查出存在pid=3的记录而导致子表插入成功,这样也可能导致父表和子表不满足外键约束。

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

站长推荐上一条 /2 下一条

返回顶部