营销型网站平台,安庆哪里做网站,企业网站后台怎么做,图书馆网站建设费用注#xff1a;以下讨论基于InnoDB引擎。 文章目录 问题引入猜想1#xff1a;只加了一行写锁#xff0c;锁住要修改的这一行。语义问题数据一致性问题 猜想2#xff1a;要修改的这一行加写锁#xff0c;扫描过程中遇到其它行加读锁猜想3#xff1a;要修改的这一行加写锁以下讨论基于InnoDB引擎。 文章目录 问题引入猜想1只加了一行写锁锁住要修改的这一行。语义问题数据一致性问题 猜想2要修改的这一行加写锁扫描过程中遇到其它行加读锁猜想3要修改的这一行加写锁扫描过程中遇到其它行加读锁行与行之间的间隙加上间隙锁。next-key锁next-key锁带来的问题 总结 问题引入
创建一张表t并插入一些数据
CREATE TABLE t (id int(11) NOT NULL,c int(11) DEFAULT NULL,d int(11) DEFAULT NULL,PRIMARY KEY (id),KEY c (c)
) ENGINEInnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);表中数据如下
执行以下sql
select * from t where d 5 for update;问这句sql是当前读读的是d5的这一行我们都知道当前读的时候会加锁。所以这句sql在读 d5这一行时会在该行上加写锁。现在问题来了除了在d5这一行上加上写锁之外还会加其他锁吗
猜想1只加了一行写锁锁住要修改的这一行。
时间事务A事务B事务Ct0begin;t1select * from t where d 5 for update; (5, 5, 5)t2update t set d 5 where id 0;t3select * from t where d 5 for update; (0, 0, 5) (5, 5, 5)t4insert into t values(1,1,5);t5select * from t where d 5 for update; (0, 0, 5) (1, 1, 5) (5, 5, 5)t6commit;
如果猜想一正确事务B和事务C不会被阻塞事务A三次select结果显示在表中高亮部分。我们来分析以下这三条结果
t1时刻表中只有一行d5所以返回一行t2时刻事务B将id0的一行的d值修改为5所以查出来是两行。这里发生了不可重复读问题t3时刻事务C插入了一行所以查出来d5有三行。这里发生了幻读问题。即前后两次查询中后一次查询看到了前一次查询没有看到的行。
注意t2时刻不是幻读因为幻读只是针对插入新行。 你也许会说for update是当前读事务B和事务C修改了表就应该看到最新修改的结果啊。 我们再来看看如果按猜想1这样设计会导致什么问题果
语义问题
时间事务A事务B事务Ct0begin;t1select * from t where d 5 for update;t2update t set d 5 where id 0;update t set c 5 where id 0;t3select * from t where d 5 for update;t4insert into t values(1,1,5);update t set c 5 where id 1;t5select * from t where d 5 for update;t6commit;
t1时刻事务A执行的语义是“我要将d5的行加上行锁” t2时刻id0这一行的d值等于5按照猜想一这一行是没有加锁的所以事务B可以执行下面修改语句。 t4时刻同理事务C也可以修改新插入的行 事务B和事务C在执行各自第二个update语句时就破坏了事务A宣布的语义。
数据一致性问题
时间事务A事务B事务Ct0begin;t1select * from t where d 5 for update; update t set d 100 where d5;t2update t set d 5 where id 0;update t set c 5 where id 0;t3select * from t where d 5 for update;t4insert into t values(1,1,5);update t set c 5 where id 1;t5select * from t where d 5 for update;t6commit;
此时数据库表中的d5的行记录应该是
idcd055155
底层binlog在记录时会按照事务的提交顺序记录操作。事务的提交顺序是B - C - A 所以在binlog里面日志是这样记录的
// 事务B
update t set d 5 where id 0; // (0,0,5)
update t set c 5 where id 0; // (0,5,5)
// 事务C
insert into t values(1,1,5); // (1,1,5)
update t set c 5 where id 1; // (1,5,5)
// 事务A
select * from t where d 5 for update; // ()
update t set d 100 where d5; // 所有d5的行都把d值修改为100
select * from t where d 5 for update;
select * from t where d 5 for update;如果拿这个binlog去备份备库备库中就没有d5的行了。而主库表中实际上是由两行d5的记录的。这里发生了主库与备库数据不一致的问题。 综上所述吗猜想1不正确。
猜想2要修改的这一行加写锁扫描过程中遇到其它行加读锁
时间事务A事务B事务Ct0begin;t1select * from t where d 5 for update; update t set d 100 where d 5;t2update t set d 5 where id 0; blocked update t set c 5 where id 0;t3select * from t where d 5 for update;t4insert into t values(1,1,5);update t set c 5 where id 1;t5select * from t where d 5 for update; t6commit;
如果猜想2正确t1时刻事务A执行后会将扫描到的行都锁柱所以t2时刻事务B想要更新id0的这一行记录会被阻塞只有A提交之后才会执行B。而t4时刻事务C想表中插入行不会被阻塞因为事务A在t1时刻只锁住了表中存在的行而新插入的行在t1时刻还不存在所以可以可以执行插入。同样地在t5时刻发生了幻读即同一事务前后两次查询后一次查询看到了前一次查询没有看到的新行。 三个事务执行完数据库表中的d5的行应该是
idcd055155
我们还是看看binlog日志里面是怎么记录的由于B被阻塞所以事务B只有等A提交之后才会执行执行顺序应该是C-A-B。
// 事务C
insert into t values(1,1,5); // (1,1,5)
update t set c 5 where id 1; // (1,5,5)
// 事务A
select * from t where d 5 for update; // ()
update t set d 100 where d5; // 所有d5的行都把d值修改为100
select * from t where d 5 for update;
select * from t where d 5 for update;
// 事务B
update t set d 5 where id 0; // (0,0,5)
update t set c 5 where id 0; // (0,5,5)用binlog生成备库时由于事务B是在事务A之后的所以id0这一行数据不一致问题解决了但是id1这一行数据还是不一致。
也就是说即使把表中所有行都加上了锁还是无法阻止并发事务插入新的记录。
猜想3要修改的这一行加写锁扫描过程中遇到其它行加读锁行与行之间的间隙加上间隙锁。
我们再来看猜想3猜想3是加锁最多的
d5的行加了行锁表中所有存在的行加了行锁行与行的间隙加了间隙锁 我们来看看什么是间隙锁表t初始化插入了6条记录 6条记录就会产生7个间隙如图所示 所以当执行select * from t where d 5 for update; 语句时不仅加了6个行锁还加了7个间隙锁。这样就确保了事务A执行过程中无法插入新的记录。 也就是说在一行行扫描过程中不仅给行加上了行锁还给行两边的间隙加上了间隙锁。读锁和读锁之间不互斥间隙锁也一样。
读锁写锁读锁不互斥互斥写锁互斥互斥
间隙锁与间隙锁之间是不互斥的也就是说执行下面这个并发事务时
时间事务A事务Bt0begin;select * from t where id 5 for update;t1begin;select * from t where id 10 for update; 事务B是不会被阻塞的间隙锁只对insert语句生效。
next-key锁
还用t表说明d上没有索引 这里图中的数字是id
where条件是d25由于d上没有索引所以要扫描所有的行扫描到的行都加上读锁。行与行之间的间隙加了间隙锁。要修改的d25这一行(25,25,25)加上写锁。
next-key锁就是间隙锁开区间加 行锁单值变成一个左开右闭区间 比如 (-inf, 0)的间隙锁加上id 0这一行的行锁 就等于 (-inf, 0] 的next-key锁
next-key锁带来的问题
间隙锁和next-key锁虽然可以解决幻读问题但是会带来其他困扰。 比如对于下面的sql语句
begin;
select * from t where idN for update;
/*如果行不存在*/
insert into t values(N,N,N);
/*如果行存在*/
update t set dN set idN;
commit;如果在两个事务并发的执行可能会造成死锁我们用表格来说明一下
时间事务A事务Bt0begin;select * from t where id9 for update;t1begin;select * from t where id9 for update;t2insert into t values(9,9,9);blockedt3insert into t values(9,9,9);blockeddead lock
两个事务A和B并发地执行上面的业务sql t0时刻事务A开启事务执行select * from t where id9 for update; 由于id上有索引索引只会定位到id9这一行不会扫描其他行但是id9这一行不存在所以加不上写锁。同时会给510这个间隙加上间隙锁。 t1时刻事务B开启事务执行select * from t where id9 for update; 与A一样只会给510加上间隙锁 t2时刻当事务A执行inser操作往510间隙插入行是由于事务B也有间隙锁所以会被阻塞等事务B commit 释放锁之后才能执行插入操作 t3时刻当事务B执行insert操作往510间隙插入行是由于事务A也有间隙锁所以会被阻塞等事务A commit 释放锁之后才能执行插入操作。事务A和事务B同时在等待对方的资源才会释放自己占用的锁造成死锁。
总结
在可重复读的隔离级别下mysql的当前读加锁规则 1、满足where条件的行加写锁 2、扫描过程中遇到的行加读锁 3、表中已存在的行会有间隙扫描过程中遇到间隙会加间隙锁
