介绍
众所周知,在不同隔离级别下,会发生如下问题。
√ 为会发生,×为不会发生
隔离级别 |
脏读 |
不可重复读 |
幻读 |
read uncommitted(未提交读) |
√ |
√ |
|
read committed(提交读) |
× |
√ |
√ |
repeatable read(可重复读) |
× |
× |
√ |
× |
× |
× |
不知道这些问题是如何产生的,可以看如下文章《面试官:脏读,不可重复读,幻读是如何发生的?》
那么mysql是如何避免脏读,不可重复度,幻读的?其实有两种方案
方案一:读操作使用多版本并发控制(MVCC),只对写操作加锁
mvcc之前已经介绍过,每次事务开启的时候,都会生成一个ReadView,然后找到版本链上对当前事务可见的版本。读记录的历史版本和改动记录的最新版本这两者并不冲突,所以采用MVCC时,读写并不会冲突
方案二:读写操作都采用加锁的方式
脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。
如果另一个事务在修改记录的时候对记录加锁,在事务提交后释放锁,那么当前事务在读取记录的时候获取不到锁,就不会出现脏读
不可重复读是指在事务1内,读取了一个数据,事务1还没有结束时,事务2也访问了这个数据,修改了这个数据,并提交。紧接着,事务1又读这个数据。由于事务2的修改,那么事务1两次读到的的数据可能是不一样的,因此称为是不可重复读。
如果当前事务在读取记录时就给该记录加锁,那么另一个事务就无法修改该记录,自然也就不会出现不可重复读的现象了
幻读指的是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会读到别的事务插入的记录,这些新记录就是幻影记录(InnoDB存储引擎通过多版本并发控制(MVCC)和间隙锁解决了这种情况的幻读问题)
MVCC和间隙锁我们后面接着聊。
这种情况下该怎么加锁呢?因为第一次读取记录的时候,幻影记录并不存在。我们后面见
MySQL中的锁
在MySQL中有三种级别的锁,表锁,行锁,页锁
表锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。 会发生在:MyISAM、memory、InnoDB、BDB 等存储引擎中
行锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高。会发生在:InnoDB 存储引擎
页锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。会发生在:BDB 存储引擎
InnoDB中的锁
InnoDB存储引擎中有如下两种类型的行级锁
- 共享锁(Shared Lock,简称S锁),在事务需要读取一条记录时,需要先获取改记录的S锁
- 排他锁(Exclusive Lock,简称X锁),在事务要改动一条记录时,需要先获取该记录的X锁
如果事务T1获取了一条记录的S锁之后,事务T2也要访问这条记录。如果事务T2想再获取这个记录的S锁,可以成功,这种情况称为锁兼容,如果事务T2想再获取这个记录的X锁,那么此操作会被阻塞,直到事务T1提交之后将S锁释放掉
如果事务T1获取了一条记录的X锁之后,那么不管事务T2接着想获取该记录的S锁还是X锁都会被阻塞,直到事务1提交,这种情况称为锁不兼容。
多个事务可以同时读取记录,即共享锁之间不互斥,但共享锁会阻塞排他锁。排他锁之间互斥
S锁和X锁之间的兼容关系如下
兼容性 |
X锁 |
S锁 |
X锁 |
不兼容 |
|
S锁 |
不兼容 |
兼容 |
我们可以通过如下语句对读取的记录加锁。
对读取的记录加S锁
select .. lock in share mode;
对读取的记录加X锁
select ... for update
表锁
表级别的S锁,X锁
在对某个表执行select,insert,update,delete语句时,innodb存储引擎是不会为这个表添加表级别的S锁或者X锁。
在对表执行一些诸如ALTER TABLE,DROP TABLE这类的DDL语句时,会对这个表加X锁,因此其他事务对这个表执行诸如SELECT INSERT UPDATE DELETE的语句会发生阻塞
在系统变量autocommit=0,innodb_table_locks = 1时,手动获取InnoDB存储引擎提供的表t的S锁或者X锁,可以这么写
对表t加表级别的S锁
lock tables t read
对表t加表级别的X锁
lock tables t write
如果一个事务给表加了S锁,那么
- 别的事务可以继续获得该表的S锁
- 别的事务可以继续获得表中某些记录的S锁
- 别的事务不可以继续获得该表的X锁
- 别的事务不可以继续获得表中某些记录的X锁
如果一个事务给表加了X锁,那么
- 别的事务不可以继续获得该表的S锁
- 别的事务不可以继续获得表中某些记录的S锁
- 别的事务不可以继续获得该表的X锁
- 别的事务不可以继续获得表中某些记录的X锁
所以修改线上的表时一定要小心,因为会使大量事务阻塞,目前有很多成熟的修改线上表的方法,不再赘述
表级别的IS锁,IX锁
为什么要有表级别的IS锁,IX锁?
我们用教学楼和教室的例子类比一下
我们每个人都可以去教室学习,一个教室可以容纳多个人去学习,来一个人学习在门口挂一把S锁,教室可以挂多个S锁(相当于行级别的S锁)。而当教室进行维修的时候,别的工作都不能进行,在教室门口挂了一把X锁(相当于行级别的X锁)
有领导来教学楼视察,学生可以正常学习,但是不能有教室在维修,在教学楼门口挂一把S锁(相当于表级别的S锁)。学生看到教学楼的S锁,可以正常学习,修理工看到教学楼的X锁,就一直等着
学校要占用教学楼考试,不允许学生学习,也不允许维修,在教学楼门口挂一把X锁(相当于表级别的X锁),此时学生和修理工都得等着
这样做有两个问题
- 想对教学楼上S锁,必须保证没有正在维修的教室
- 想对教学楼上X锁,必须保证没有正在维修的教室以及教室都是空的
一个一个去查看教室,这样效率太低了。可以这样做
- 学生去教室学习,先在教学楼门口挂一把IS锁,然后在教室门口挂一把S锁
- 维修工去教室学习,先在教学楼门口挂一把IX锁,然后在教室门口挂一把X锁
这样想对教学楼上S锁,只需要看教学楼门口有没有IX锁即可
这样想对教学楼上X锁,只需要看教学楼门口有没有IX以及IS锁即可
使用InnoDB存储引擎,在对表的记录加S锁之前,需要先在表级别加一个IS锁。在对表的记录加X锁之前,需要先在表级别加一个IX锁。IS锁和IX锁只是为了在后续加表级别的S锁和X锁时判断表中是否有已经被加锁的记录,避免用遍历的方式来查看表中有没有上锁的记录
表级别的AUTO-INC锁
在使用MySQL过程中,我们可以为表的某个列添加AUTO_INCREMENT属性,之后在插入记录的时候,可以不指定该列的值列,系统为他赋上递增的值
如下面这个表
CREATE TABLE t (
id INT NOT NULL AUTO_INCREMENT,
c VARCHAR(100),
PRIMARY KEY (id)
) Engine=InnoDB CHARSET=utf8;
INSERT INTO t(c) VALUES('aa'), ('bb');
发表评论