Mysql|深入理解Mysql事务隔离级别与锁机制 Mysql|mysql

目录

一：概述
二：事务及其ACID特性
三：并发事务处理带来的问题
四：事务隔离级别
五：详解
六：行锁与事务隔离级别案例分析
读未提交：
读已提交
可重复读
七：间隙锁(Gap Lock)
八：锁优化建议
九：MVCC多版本并发控制机制
十：undo日志版本链与read view机制详解

一：概述我们的数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致我们说的脏写、脏读、不可重复读、幻读这些问题。这些问题的本质都是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。 【Mysql|深入理解Mysql事务隔离级别与锁机制】
二：事务及其ACID特性事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。
隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

三：并发事务处理带来的问题脏读：事务A读取到了事务B已经修改但尚未提交的数据
不可重读：事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性
幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。一句话：事务A读取到了事务B提交的新增数据，不符合隔离性

四：事务隔离级别
文章图片

常看当前数据库的事务隔离级别: show variables like 'tx_isolation'; 设置事务隔离级别： set tx_isolation='REPEATABLE-READ'; Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别五：详解锁是计算机协调多个进程或线程并发访问某一资源的机制。锁分类从性能上分为----> 乐观锁 (用版本对比来实现)和悲观锁从对数据库操作的类型分为----> 读锁和写锁 (都属于悲观锁)读锁（共享锁，S锁( S hared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响写锁（排它锁，X锁(e X clusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁从对数据操作的粒度分，分为----> 表锁和行锁表锁每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。
行锁每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。 InnoDB与MYISAM的最大不同有两点：InnoDB支持事务（ TRANSACTION）InnoDB支持行级锁行锁演示一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞总结：MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。六：行锁与事务隔离级别案例分析

1CREATE TABLE `account`(2`id`int ( 11 )NOT NULL AUTO_INCREMENT ,3`name`varchar ( 255 )DEFAULT NULL ,4`balance`int ( 11 )DEFAULT NULL ,5PRIMARY KEY( `id` )6)ENGINE = InnoDB DEFAULT CHARSET = utf8 ; 7INSERT INTO `test` . `account`( `name` ,`balance` )VALUES( 'lilei' ,'450' ); 8INSERT INTO `test` . `account`( `name` ,`balance` )VALUES( 'hanmei' ,'16000' ); 9INSERT INTO `test` . `account`( `name` ,`balance` )VALUES( 'lucy' ,'2400' );

读未提交：（1）打开一个客户端A，并设置当前事务模式为read uncommitted（未提交读），查询表account的初始值： set tx_isolation=' read-uncommitted ';

文章图片
（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：

文章图片

（3）这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据：

文章图片

（4）一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到的数据其实就是脏数据

文章图片

（5）在客户端A执行更新语句update account set balance = balance - 50 where id =1，lilei的balance没有变成350，居然是400，是不是很奇怪，数据不一致啊，如果你这么想就太天真了，在应用程序中，我们会用400-50=350，并不知道其他会话回滚了，要想解决这个问题可以采用读已提交的隔离级别

文章图片

读已提交（1）打开一个客户端A，并设置当前事务模式为read committed（未提交读），查询表account的所有记录： set tx_isolation=' read-committed ';

文章图片

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：

文章图片
（3）这时，客户端B的事务还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题

文章图片

（4）客户端B的事务提交

文章图片

（5）客户端A执行与上一步相同的查询，结果与上一步不一致，即产生了不可重复读的问题

文章图片

可重复读（1）打开一个客户端A，并设置当前事务模式为repeatable read，查询表account的所有记录set tx_isolation=' repeatable-read ';

文章图片

（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account并提交

文章图片

（3）在客户端A查询表account的所有记录，与步骤（1）查询结果一致，没有出现不可重复读的问题

文章图片

（4）在客户端A，接着执行update account set balance = balance - 50 where id = 1，balance没有变成400-50=350，lilei的balance值用的是步骤2中的350来算的，所以是300，数据的一致性倒是没有被破坏。可重复读的隔离级别下使用了 MVCC(multi-version concurrency control)机制，select操作不会更新版本号，是快照读（历史版本）； insert、update和delete会更新版本号，是当前读（当前版本）。

文章图片

（5）重新打开客户端B，插入一条新数据后提交

文章图片

（6）在客户端A查询表account的所有记录，没有查出新增数据，所以没有出现幻读

文章图片

（7)验证幻读 --->在客户端A执行update account set balance=888 where id = 4; 能更新成功，再次查询能查到客户端B新增的数据

文章图片

七：间隙锁(Gap Lock) 间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，有办法解决幻读问题吗？间隙锁在某些情况下可以解决幻读问题。假设account表里数据如下：

文章图片
那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，在Session_1下面执行 update account set name = 'zhuge' where id > 8 and id <18; ，则其他Session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在 (3,20]区间都无法修改数据，注意最后那个20也是包含在内的。间隙锁是在可重复读隔离级别下才会生效。
八：锁优化建议（1）尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁（2）合理设计索引，尽量缩小锁的范围（3）尽可能减少检索条件范围，避免间隙锁（4）尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行（5）尽可能低级别事务隔离
九：MVCC多版本并发控制机制 Mysql在可重复读隔离级别下如何保证事务较高的隔离性，我们上节课给大家演示过，同样的sql查询语句在一个事务里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。这个隔离性就是靠MVCC( Multi-Version Concurrency Control )机制来保证的，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制十：undo日志版本链与read view机制详解 undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链(见下图，需参考视频里的例子理解

文章图片

在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成 )，这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。版本链比对规则：1. 如果 row 的 trx_id 落在绿色部分( trx_idmax_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若row 的 trx_id 就是当前自己的事务是可见的）； 3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的事务是可见的)；b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。