欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql幻读、MVCC、间隙锁、意向锁(IX\IS)

程序员文章站 2022-06-14 16:55:52
...

IO即性能

顺序主键写性能很高,由于B+树的结构,主键如果是顺序的,则磁盘页的数据会按顺序填充,减少数据移动,随机主键则可能由于记录移动产生很多io

查询二级索引时,会再根据主键id获取数据页,产生一次磁盘io,但如果在高并发场景下,二级索引不大而被整个缓存到内存时,它甚至比主键查询还快
虽然二级索引表的读是离散的,但是索引一般字段不会太多,数据量小,索引表被整个cache到内存不是难事,而如果内存中有cache页,可以直接根据id找到记录(涉及到mysql cache方式了,还不明确),可能会更快,所以利用二级索引和自增主键反而不会引起很多的直接io,而如果使用业务主键直接读,很可能数据是贯穿整个表,数据表表的数据一般比较大,想要整个cache起来非常困难,反而在并发读取下会带来大量的cache挤占,真实io更大

引用:
作者:聿明leslie
链接:https://www.zhihu.com/question/266011062/answer/310929189
来源:知乎
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

意向锁

锁分类:共享读锁、排他写锁,主要是为了对整表加写锁时,更搞笑,当对整表加写锁后,理论上可以修改表中的任意一行

共享读锁

多个读操作可以共享这个锁,可以并发访问

排他写锁

写锁都是排他的,一旦获得了某个锁,其他线程就无法获取对应行的写锁和读锁

如果事务A先申请了行的读锁,事务B想申请整个表的写锁,则A释放行锁之前,事务B都不会成获得整表的写锁

当需要锁行时,先向整个表申请意向(共享|排他)锁,再申请行的对应锁,这样有对表加写锁的请求时,会发现表上有意向锁,会先等待阻塞,防止表锁上的冲突,实现多粒度的控制

MVCC

mysql的写操作分为

  • 修改数据区
  • redo日志

如果事务A要写一个数据,需要找到对应的数据页,load到内存中,成为cache页,然后修改cache中的数据区,再记一条日志,等待日志落盘后,就返回给客户端,而非数据落盘,而此时事务可能还未提交。
当其他事务B中有请求要查询该内存页时,而A还未提交,此时显然不能直接读脏数据(cache中的),那么该如何处理呢?
此时事务A会开辟一块undo的数据区,作用是将数据回放到上一个事务的完结状态,事务B的查询操作就会访问到undo数据区,

简单流程

  • 事务1:将一行数据a=1 改成 a=2,那么它会对这行数据先加排他锁,再将这行数据上一个已提交的事务verison数据copy到undo数据区
  • 事务2:拿到一个全局的已提交的version去读这一行,发现被事务1修改的那一行version比查询的那一行大,则去undo区里面去查

不可重复读

一个事务中,同样的查询,两次结果不相同,就叫不可重复读。
主要说的如某个事务执行过程中,前一次查询和后一次查询,数据不一样,比如某一列的值被修改了,这种情况通常为两次查询过程中,另一个事务操作了这一行数据。

解决不可重复读

通过mvcc版本比较,解决不可重复读的问题,事务会访问到另一个事务开辟undo区域,保证后一次读和前一次读的结果相同;
再加上写锁的排他特性,保证同一时刻,只会有一个事务可以操作某一行数据

幻读

幻读主要指的是两此select(count)之类的查询结果不一样,出现了幽灵行数据。
事务A第一次执行查询后,事务B通过insert插入了一行数据,事务A再次执行查询时,发现多了一行数据。

解决幻读

把mysql的读分为

  • 快照读 如两个普通的select语句
  • 当前读 如select加锁读或DML修改数据

快照读(事务中的普通select)的时候,mysql通过mvcc解决幻读
间隙锁解决当前读(select * for update或update\del\insert),不只在行上加锁,而且在行与行直接的间隙加锁

next-key锁

X锁 + 间隙锁,即锁定记录行,又锁定间隙,它是innodb ,RR隔离级别下的默认锁模式

不可重复读和幻读的区别
很多人容易搞混不可重复读和幻读,确实这两者有些相似。但不可重复读重点在于update和delete,而幻读的重点在于insert。

隔离级别

https://www.cnblogs.com/windliu/p/8144202.html
如果使用锁机制来实现这两种隔离级别,在可重复读中,该sql第一次读取到数据后,就将这些数据加锁,其它事务无法修改这些数据,就可以实现可重复读了。但这种方法却无法锁住insert的数据,所以当事务A先前读取了数据,或者修改了全部数据,事务B还是可以insert数据提交,这时事务A就会发现莫名其妙多了一条之前没有的数据,也就是幻读,不能通过行锁来避免。需要Serializable隔离级别 ,读用读锁,写用写锁,读锁和写锁互斥,这么做可以有效的避免幻读、不可重复读、脏读等问题,但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别,就在于如何通过锁机制来解决他们产生的问题。

上面说的,是使用悲观锁机制来处理这两种问题,但是MySQL、ORACLE、PostgreSQL等成熟的数据库,出于性能考虑,都是使用了以乐观锁为理论基础的MVCC(多版本并发控制)来避免这两种问题。

“读”与“读”的区别

可能有读者会疑惑,事务的隔离级别其实都是对于读数据的定义,但到了这里,就被拆成了读和写两个模块来讲解。这主要是因为MySQL中的读,和事务隔离级别中的读,是不一样的。

我们且看,在RR级别中,通过MVCC机制,虽然让数据变得可重复读,但我们读到的数据可能是历史数据,是不及时的数据,不是数据库当前的数据!这在一些对于数据的时效特别敏感的业务中,就很可能出问题。

对于这种读取历史数据的方式,我们叫它快照读 (snapshot read),而读取数据库当前版本数据的方式,叫当前读 (current read)。很显然,在MVCC中:

快照读:就是select

    select * from table ....;

当前读:特殊的读操作,插入/更新/删除操作,属于当前读,处理的都是当前的数据,需要加锁。

    select * from table where ? lock in share mode;
    select * from table where ? for update;
    insert;
    update ;
    delete;

事务的隔离级别实际上都是定义了当前读的级别,MySQL为了减少锁处理(包括等待其它锁)的时间,提升并发能力,引入了快照读的概念,使得select不用加锁。而update、insert这些“当前读”,就需要另外的模块来解决了。

解决幻读

解决幻读:通过行锁 + 间隙gap锁,这里说的行锁是通过mvcc实现的乐观锁,比较cache页中数据行的version和当前事务的version,如果当前version比较小,则去undo区域,在普通select读这种快照读情况下,实际上是不会有锁的,而在dml中,涉及到数据变更,会锁住被修改的行,同时,加gap锁,防止其他事务插入数据导致的幻读

唯一索引的唯一搜索(非范围查询,如t_key = 1)不用加gap锁,因为其他事务在尝试获取index-record lock时会失败

设table_a 有非唯一索引t_key,上一个已提交最新版本的全局version为10000

    CREATE TABLE `table_a` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT,
      `t_key` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_t_key` (`t_key`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

事务1:
事务id:10001

    select * from table_a where t_key = '1';   //load page into cache/memory
    update table_a set t_key = '2' where  t_key = '1'; //对t_key = 1 的数据行加锁,为了防止其他事务insert t_key(1),加间隙锁,并将t_key='1'的上一个版本数据行copy到undo区域,undo区域中t_key还是1,cache页中数据行版本变为10001,此时事务还没有提交,因此cache页是一个脏数据页

(事务2此时介入,查询sql select * from table_a where t_key = '1';)
....

end

事务2:
事务id:10002

    select * from table_a where t_key = '1'; //查询cache页,发现上一个最新已提交version为10000,小于10001,去undo区域获取数据行
    insert(t_key) table_a values(1) //事务1对table_a加了间隙锁,如索引节点 [-无穷,1]和[1,2]中间被锁住(假设有多条记录,t_key分别为1,2,3),不包括2,获取锁时都需要等待,如果table_a的t_key字段没有索引,在innodb会锁定整个表,锁住整个表的方式,通过实验,确定为锁住primary索引,包括不存在的记录
    

(等待事务1 end)
(没有间隙锁释放,可以插入)

间隙锁的定位方式为最近锁定行的左右区间,避免锁定没有必要的行,假如有索引数据(1,1)(3,4),(5,5)(8,5)(9,7),(主键,t_key)
update where t_key = 4时,会锁住(1)~(4)之间,(4)~(5)之间,而不是锁住(1) ~ (5),所以才叫间隙锁,通过分析,间隙锁应该是发生了B+树的叶子节点上,并且已经对应到行记录上了
事务1锁定了where t_key = 4 时,间隙锁t_key(1,1)~(3,4)之间,(3,4)~(5,5)之间,此时还需要关注主键的位置
则insert(2,3)时,它在(1,1)(3,4)之间,会阻塞
insert(4,5)时,它在(3,4)~(5,5)之间,会阻塞
insert(6,5)时,成功,因为它没在(1,1)~(3,4)之间,(3,4)~(5,5)之间,同样t_key都是5,一个可以成功一个不可以

如果一个事务通过update 主键id锁定了行(3,4),则另一个事务此时也无法通过t_key索引更新(3,4),因为都对应到了主键id为3的数据行

假如table_a还有一个name字段

select * from table_a where t_key=5 and name =‘5’ for update,将会锁住t_key的索引;
select * from table_a where name ='5' for update,会锁住primary索引

事务1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

    mysql> select * from table_a where name='5';
    Empty set (0.00 sec)

    mysql> select * from table_a where name='6';
    +----+-------+------+
    | id | t_key | name |
    +----+-------+------+
    |  5 |     5 | 6    |
    |  8 |     5 | 6    |
    +----+-------+------+
    2 rows in set (0.00 sec)

    mysql> update table_a set name='5' where t_key=5;
    Query OK, 2 rows affected (0.00 sec)
    Rows matched: 2  Changed: 2  Warnings: 0

    mysql> commit;
    Query OK, 0 rows affected (0.01 sec)

事务1 update操作执行,但没有commit时,执行事务2

事务2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

    mysql> select * from table_a where t_key =5;
    +----+-------+------+
    | id | t_key | name |
    +----+-------+------+
    |  5 |     5 | 6    |
    |  8 |     5 | 6    |
    +----+-------+------+
    2 rows in set (0.00 sec)

    mysql> update table_a set name='8' where t_key=5 and name='6';
    Query OK, 0 rows affected (4.64 sec)
    Rows matched: 0  Changed: 0  Warnings: 0

    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

    最终name的值为
    mysql> select * from table_a where t_key =5;
    +----+-------+------+
    | id | t_key | name |
    +----+-------+------+
    |  5 |     5 | 5    |
    |  8 |     5 | 5    |
    +----+-------+------+
    2 rows in set (0.00 sec)