阅读 292

Mysql事务隔离级别与锁机制

写在前面

前面复习总结了Mysql索引的相关知识内容。接下来来总结一些mysql的事务和锁机制。在开发使用过程中,mysql都会有并发操作,并发执行多个事务对相同的一批数据进行增删改查,会导致脏读、脏写、不可重复读、幻读问题。为了解决多事务之间的并发问题,数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制,来解决多事务的并发问题。首先来了解一下事务及ACID。

事务和ACID

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,叫ACID。

  • 原子性(Atomicity):事务是一个原子操作,对数据的操作,要么全部执行,要么全部不执行。
  • 一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。
  • 隔离性(Isolation):数据库系统提供了隔离机制,不同事务之间的操作,互不影响。
  • 持久性(Durable):事务完成之后,它对于数据的修改是永久性的。

并发事务处理带来的问题

更新丢失(脏写)

当多个事务在对同一行数据更新改行时,由于事务之间相互隔离,不知道其他事务的存在,就会发生脏写问题,最后的更新覆盖了其他事务所做的更新。(白话:第一个事务读取到一行数据值为3,第二个事务读取到相同行数据也为3,第一个事务先更新数据值3+1=4,第二个事务,也更新数据值3+2=5,此时第二个事务的值5会覆盖第一个事务更新的值。正确情况是:第一个事务更新3+1=4,第二个事务更新要在第一个事务的基础上,4+2=6才对)

脏读

一个事务正在对一条记录做修改,事务完成,但还没提交,这条记录就处于不一致的状态,这时候另一个事务来读取相同一条记录,如果不加控制,第二个事务读取了这些"脏"数据(也就是数据已经被修改,但未提交的数据),并据此作进一步的处理,就会产生未提交的数据依赖关系。(白话:事务A读取到了事务B修改但未提交的数据,还在这个数据的基础上做了操作,如果事务B回滚,A读取的数据无效,不符合一致性要求)

不可重复读

一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,发现数据已经被修改或者删除。(白话:事务A内部相同的查询语句在不同时刻查询出来的结果不一致)

幻读

一个事务按相同的查询条件读取以前读取过的记录,发现其他事务插入了满足其查询条件的新数据。(白话:事务A读取到了事务B提交的新增数据,不符合隔离性)

事务隔离级别

隔离级别脏读不可重复读幻读
读未提交可能可能可能
读已提交不可能可能可能
可重复读不可能不可能可能
可串行化不可能不可能不可能

数据库的事务隔离级别越严格,并发副作用越小,但付出的代价越高,因为事务隔离实质上就是使事务在一定程度上“串行化”。
查看当前数据库事务隔离级别: show variables like'tx_isolation';
设置事务隔离级别:set tx_isolation='REPEATABLE-READ'; Mysql默认的事务隔离级别是可重复读,用Spring开发程序时,如果不设置隔离级别默认用MySQL设置的隔离级别。

  • 从性能上分为乐观锁和悲观锁
  • 从对数据库的操作类型分为读锁和写锁(都属于悲观锁)

读锁(共享锁,S锁):针对同一份数据,多个读操作可以同时进行而不会互相影响
写锁(排他锁,X锁):当前在写操作,会阻塞其他写锁和读锁

  • 从对数据操作的粒度分为表锁和行锁

表锁每次操作锁住整张表。开销小,加锁快;不会出现死锁;锁的粒度大,发送锁冲突的概率高,并发低;一般用在表数据迁移的场景。 行锁每次操作锁住一行数据。开销大,加锁慢,会出现死锁,锁的粒度小,发生锁冲突的概率低,并发度高。

行锁与事务隔离级别示例

读未提交

1.打开客户端A,设置set tx_isolation='read-uncommitted';(读未提交),查询表account的值。 image.png
2.在客户端A,事务提交之前,打开客户端B,更新表account的值。 image.png
3.在客户端A查询account表的数据,发现可以查询到客户端B未提交的事务;
image.png
4.客户端B,进行回滚,所有的操作都会被取消,那客户端A查询到数据就变成了脏数据
image.png
5.在客户端A执行更新语句update account set balance = balance - 50 where id =1,没有变成350,跟预想的结果不一致。这是因为在更新数据库的时候使用了balance = balance - 50,balance是数据库里面最新的数据,所以不会变成350,但是如果在应用程序代码中,一般是先查出来balance的数值,然后再减去50,也就是查出来的值是400,这时候并应用程序并不知道,MySQL的事务回滚了,再减去50,这时候就变成了350。 也就是脏读。
image.png

读已提交

1.打开客户端A设置set tx_isolation='read-committed'; 读已提交,查询表中记录。 image.png
2.在客户端A事务提交之前打开B客户端,更新记录。 image.png
3.客户端B事务还没提交,客户端A不能查询到B已经更新了的数据,解决了脏读问题 image.png
4.客户端B事务提交。 image.png
5.客户端A执行的查询结果,与上一次不一致,产生了不可重复读问题。 image.png

可重复读

1.客户端A,设置set tx_isolation='repeatable-read'; 可重复读,查询记录。 image.png
2.客户端B,更新并提交记录。 image.png
3.在客户端A,同样查询,结果一致,解决了不可重复读问题。
image.png
4.在客户端A,继续执行update account set balance = balance - 50 where id = 1更新操作,balance没有变成400-50=350,变成了300,数据的一致性并没有被破坏。在可重复读的隔离级别下使用了MVCC机制,select操作不会更新版本号,是快照读;insert、update、delete会更新版本号,是当前读。
image.png
5.客户端B,重新开启一个事务。并插入数据
image.png
6.在客户端A,查询记录,没有出现新增的数据,没有出现幻读。 image.png

间隙锁

间隙锁,锁的是两个值之间空隙。MySQL的默认隔离级别是可重复读
image.png
例如:上图间隙有id为(3,10),(10,20),(20,正无穷)三个区间,执行update account set name='smart' where id >7 and id <18;则其他session就没有办法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任务数据。即id在(3,20]区间都无法修改数据。间隙锁是在可重复读隔离级别下才会生效

临键锁

临键锁是行锁和间隙锁的组合。例如上面(3,20]的整个区间可叫做临键锁

无索引行锁会升级为表锁

锁优化建议

  • 尽可能让所有数据检索都通过索引来完成,避免无索引行锁升级为表锁
  • 合理设计索引,尽量缩小锁的范围
  • 尽量减少检索条件范围,避免间隙锁
  • 尽量控制事务大小,减少锁定资源量和时间长度,涉及事务加锁的sql尽量放在事务最后执行
  • 尽可能低级别事务隔离
文章分类
后端
文章标签