亲宝软件园·资讯

展开

MySQL锁

菜鸟~~ 人气:0

前言

如果我们和面试官聊到事务的问题,怎么回答呢?

先说下事务是什么,因为我们业务是比较复杂的,不可能一个sql就能解决的,涉及多个sql就组成一个事务。事务就是一组sql共同执行,要么完全成功,要么完全失败,不能出现部分成功或者部分失败的情况。一个事务有ACID特性(可以参考:事务的ACID特性MySQL事务的隔离级别):

那么我们为了解决事务并发执行遇到的问题就给出了事务的隔离级别:

注意:

事务隔离级别的实现原理:锁+MVCC。串行化底层实现原理是锁,锁有共享锁、排它锁、意向共享锁、意向排它锁、间隙锁和死锁,InnoDB的已提交读和可重复读的底层实现原理:MVCC(多版本并发控制),MVCC提供了一种并发读取方式,包括快照读(同一份数据会有多个版本)、当前读、undo log和redo log。MVCC是已提交读和可重复读的原理,锁是串行化的原理

ACD特性用事务日志实现,I 特性用共享锁、排它锁、MVCC 实现。事务日志分为undo log(回滚日志) 和 redo log(重做日志)

一、表级锁&行级锁

MyISAM存储引擎只支持表级锁,InnoDB支持事务处理,支持行级锁,并发能力更好

二、排它锁&共享锁

读读(SS)之间是可以兼容的,但是读写(SX、SX)之间,写写(XX)之间是互斥的

1. 测试不同事务之间排它锁和共享锁的兼容性

我们先查看表SQL及内容

查看隔离级别:

首先开启一个事务A,给id=7的数据加上排它锁:

在另一个客户端开启事务B:

给id=7不管加排它锁和共享锁都阻塞了并没有查询出来,因为A事务给id=7这一行的数据加了排它锁,就是写锁,其他人不能读也不能写。

总结:不同事务之间对于数据的锁,只有SS锁可以共存,XX、SX、XS都不能共存

2. 测试行锁加在索引项上

其实行锁是加在索引树上的。

每次做完测试都把刚做的rollback。

用表的无索引字段作为过滤条件

那现在事务2获取不同行chenwei的记录

InnoDB是支持行锁的,刚才以主键id为过滤条件时,事务1和事务2获取不同行的锁是可以成功的。然而现在我们发现获取name为chenwei的排它锁也获取不到了,这是为什么?我们解释一下:

InnoDB的行锁是通过给索引项加锁来实现的,而不是给表的行记录加锁实现的

而我们用name作为过滤条件没有用到索引,自然就不会使用行锁,而是使用表锁。这就意味着只有通过索引检索数据,InnoDB才使用行级锁,否则InnoDB都将使用表锁!!!

我们给name字段加上索引:

然后再做刚才的操作:

我们发现,给name加上索引后,两个事务可以获取到不同行的排它锁(for update),再一次证明了InnoDB的行锁是加在索引项上的。

因为现在name走的是索引, 通过zhangsan在辅助索引树上找到它所在行记录的id是7,然后到主键索引树上,获取对应行记录的排他锁(个人猜测应该是辅助索引树和主键索引树相应的记录都加了锁)

三、串行化隔离级别测试

串行化所有事务用的都是共享锁或者排它锁,不需用手动添加。select获取的是共享锁,insert、delete和update获取的都是排它锁。

设置串行化隔离级别:

两个事务可以同时获取共享锁(SS共存:

现在让事务2插入数据;

由于Insert需要加排它锁,但是由于事务1已经对整张表加了共享锁,事务2无法再对表成功加锁(sx不共存)

rollback一下,把所有获取锁的状态都回退掉:

开启两个事务:

因为我们给name加上了索引,以上的select相当于给name为zhangsan的数据加上了行共享锁

事务2update;

事务2不能update,因为此时已经被事务1的共享锁锁住了整个表

事务2在辅助索引树上找zhangsan,找到对应的主键值,然后去主键索引树找到相应的记录,但是发现这行记录已经被共享锁锁住了,事务2可以获取共享锁,但是不能获取排他锁

我们再用主键索引试试id能不能update

依然阻塞住了,虽然我们where后面的字段现在使用的id而不是name,但是name也是通过辅助索引树找到对应的主键,再到主键索引树上找相应的记录,而主键索引树上的记录加了锁

我们update id=8的数据,成功了。因为我们select的时候,只是给id=7的数据加上了行锁,我们操作id=8的数据当然可以成功

有索引,则使用行锁;没有索引,则使用表锁。

表级锁还是行级锁说的是锁的粒度,共享锁和排他锁说的是锁的性质,不管是表锁还是行锁,都有共享锁和排他锁的区分。

串行化玩的就是排它锁和共享锁,在可重复读级别下,不手动加锁的话,用的就是MVCC机制,实际上并没有用到锁,我们也可以手动加锁。InnoDB如果不创建索引的话,用的是表锁,如果查询的时候用到了索引项,它用的就是行锁了,行锁是给索引加锁,而不是单纯给一行数据加锁。

加载全部内容

相关教程
猜你喜欢
用户评论