校园网站建设的必要性论文,手机制作模板图片的app,做阿里巴巴跟网站哪个更好,建设银行小微企业网站进不了注意以下操作都是以InnoDB引擎为操作基准。
一#xff0c;前置知识准备
1#xff0c;MVCC简介
MVCC 是多版本并发控制#xff08;Multiversion Concurrency Control#xff09;的缩写。它是一种数据库事务管理技术#xff0c;用于解决并发访问数据库的问题。MVCC 通过创…注意以下操作都是以InnoDB引擎为操作基准。
一前置知识准备
1MVCC简介
MVCC 是多版本并发控制Multiversion Concurrency Control的缩写。它是一种数据库事务管理技术用于解决并发访问数据库的问题。MVCC 通过创建多个版本的同一数据每个版本与一个事务关联来实现并发控制。
数据库在执行更新操作时会保留之前版本的数据以便其他正在执行事务的用户可以访问这些数据。每个事务都能看到一个稳定的数据快照并且仅接触到他们自己的版本这意味着每个事务可以独立地读取和写入数据而不会干扰其它事务。
MVCC 在数据库的可伸缩性和性能方面具有重要作用尤其是对于高并发的应用程序如电子商务网站和社交媒体应用。
2MySQL的逻辑架构
第一层处理客户端连接、授权认证安全校验等。
第二层服务器server层负责对SQL解释、分析、优化、执行操作引擎等。
第三层存储引擎负责MySQL中数据的存储和提取。
3事务的四大特性
ACID是指数据库事务所必须具备的四个特性包括 原子性Atomicity事务是一个不可分割的原子操作要么全部执行成功要么全部失败回滚不允许出现部分执行成功或失败的情况。【undolog】 一致性Consistency事务执行前和执行后数据库的完整性约束没有被破坏也就是说在事务完成后数据库从一个一致性状态转变为另一个一致性状态。 隔离性Isolation事务之间是相互隔离的一个事务的执行不能被其他事务干扰。多个事务并发执行时它们之间的执行是独立的每个事务感觉就像是在独占数据库。【锁mvcc】 持久性Durability事务完成后对于数据的修改是永久性的即使系统故障也不会导致数据的丢失。因此数据库中所有的数据修改都需要记录到日志中以便在系统故障恢复时进行重做。【redolog】
4事务的四大隔离级别
事务的隔离级别是指多个事务同时操作一个数据库时数据库系统对这些事务分配的隔离程度。在不同的隔离级别下事务之间的隔离程度也不同。常见的隔离级别包括 读未提交Read Uncommitted允许一个事务读取另一个事务未提交的数据。这种隔离级别对数据的完整性和一致性影响较大。【存在脏读】 读已提交Read Committed一个事务只能读取已经提交的数据其他未提交的事务所做的修改对它不可见。这种隔离级别对数据完整性和一致性的保护较好。【解决脏读存在不可重复读】【mysql InnoDB 通过 mvcc 解决 】 可重复读Repeatable Read一个事务在执行期间看到的数据是固定的不受其他并发事务的影响。即使其他事务已经提交了对数据的修改当前事务也只能看到自己在读取数据时的版本。这种隔离级别对数据的完整性和一致性有一定保护。【解决不可重复读存在幻读】【mysql InnoDB 通过 mvcc 解决 】 串行化Serializable最高的隔离级别确保事务之间的操作是完全独立的一个事务的操作必须等另一个事务结束后才能开始。这种隔离级别对数据完整性和一致性的保护最好但并发性能较低。【解决脏读解决不可重复读解决幻读】【mysql InnoDB 通过 加锁解决 】
在实际应用中应根据业务需求和系统性能等因素选择合适的隔离级别。
5mysql 常用的日志
server 层
错误日志(Error Log): 记录 MySQL 服务器启动、运行过程中出现的重要错误和警告信息。慢查询日志(Slow Query Log): 记录执行时间超过预设时间的查询语句通常用于优化查询性能。二进制日志(Binary Log): 记录数据库更新操作用于主从复制和数据恢复。中继日志(Relay Log): 只在主从复制时使用记录从服务器复制主服务器二进制日志的过程。
引擎层
重做日志(Redo log): 是一种用于恢复数据库中未提交和已提交事务的机制。其包含了所有已经被写入到磁盘上的事务通常被存储在磁盘上的一组文件中。当数据库系统宕机或者发生崩溃时可以通过redo log来恢复数据库并且保证ACID属性。回滚日志(Undo log): 则用于撤销已经提交或者未提交的事务。它记录了事务执行前的数据以便当出现错误时可以将数据回滚到事务执行前的状态。在数据库中undo log通常被用于避免脏读、不可重复读和幻读等问题。
二mvcc 原理
1当前读和快照读
在学习 MVCC 多版本并发控制之前我们必须先了解一下什么是 MySQL InnoDB 下的当前读和快照读。 当前读像 select lock in share mode (共享锁), select for update; update;insert; delete (排他锁)这些操作都是一种当前读为什么叫当前读 ? 就是它读取的是记录的最新版本读取时还要保证其他并发事务不能修改当前记录会对读取的记录进行加锁。 快照读像不加锁的 select操作就是快照读即不加锁的非阻塞读;快照读的前提是隔离级别不是串行级别串行级别下的快照读会退化成当前读之所以出现快照读的情况是基于提高并发性能的考虑快照读的实现是基于多版本并发控制即MVCC.可以认为 MVCC 是行锁的一个变种但它在很多情况下避免了加锁操作降低了开销;既然是基于多版本即快照读可能读到的并不一定是数据的最新版本而有可能是之前的历史版本。
在InnoDB中的每一条记录实际都会存在三个隐藏列 DB_TRX_ID事务 ID是根据事务产生时间顺序自动递增的是独一无二的。如果某个事务执行过程中对该记录执行了增、删、改操作那么InnoDB存储引擎就会记录下该条事务的 id。 DB_ROLL_PTR回滚指针本质上就是一个指向记录对应的undo log的一个指针InnoDB 通过这个指针找到之前版本的数据 DB_ROW_ID主键如果有自定义主键那么该值就是主键如果没有主键那么就会使用定义的第一个唯一索引如果没有唯一索引那么就会默认生成一个隐藏列作为主键。
2undolog
这些数据快照都是存储在undolog 中的这些数据分为两类 Insert undo log insert生成的日志仅在事务回滚中需要并且可以在事务提交后立即丢弃。 Update undo logupdate/delete生成的日志除了用于事务回滚还用于一致性读取只有不存在innodb为其分配快照的事务之后才能丢弃它们在一致读取中可能需要update undo log中的信息来构建数据库行的早期版本。
删除操作实际上不会直接删除而只是标记为删除最终的删除操作是purge线程完成的
purge线程作用一是清理undo log二是清除page里面带有Delete_Bit标识的数据行。在InnoDB中事务中的Delete操作实际上并不是真正的删除掉数据行而是一种Delete Mark操作在记录上标识删除真正的删除工作需要后台purge线程去完成。
3Read View(读视图)
事务进行快照读操作的时候生产的读视图(Read View)在该事务执行的快照读的那一刻会生成数据库系统当前的一个快照。 记录并维护系统当前活跃事务的ID(trx_id)(没有commit当每个事务开启时都会被分配一个ID, 这个ID是递增的所以越新的事务ID值越大)是系统中当前不应该被本事务看到的其他事务id列表。 Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候对该记录创建一个Read View读视图把它比作条件用来判断当前事务能够看到哪个版本的数据既可能是当前最新的数据也有可能是该行记录的undo log里面的某个版本的数据。 Read View几个属性
MVCC 只在 Read Commited读已提交 和 Repeatable Read可重读读 两种隔离级别下工作。 在RC隔离级别下是每个快照读都会生成并获取最新的Read View这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因。 在RR隔离级别下则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View从而做到可重复读。
4可见性算法
1、首先比较DB_TRX_ID up_limit_id,如果小于则当前事务能看到DB_TRX_ID所在的记录如果大于等于进入下一个判断。
2、接下来判断DB_TRX_ID low_limit_id,如果大于等于则代表DB_TRX_ID所在的记录在Read View生成后才出现的那么对于当前事务肯定不可见如果小于则进入下一步判断。
3、判断DB_TRX_ID是否在活跃事务中如果在则代表在Read View生成时刻这个事务还是活跃状态还没有commit修改的数据当前事务也是看不到如果不在则说明这个事务在Read View生成之前就已经开始commit那么修改的结果是能够看见的。
5mvcc能否解决幻读 在一次事务里面多次查询之后结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做幻行。
在快照读读情况下mysql通过mvcc来避免幻读。
在当前读读情况下mysql通过next-key来避免幻读。
不能把快照读和当前读得到的结果不一样这种情况认为是幻读这是两种不同的使用。所以mysql的RR级别是解决了幻读的。
三mvcc 场景验证
所谓光说不练假把式光练不说傻把式又练又说真把式上面说的只是结论下面进行实际的操作演示。
mysql 版本 5.7
创建测试表
CREATE TABLE user (id bigint(20) NOT NULL AUTO_INCREMENT,name varchar(20) DEFAULT NULL,age tinyint(3) DEFAULT NULL,gender tinyint(1) DEFAULT NULL,PRIMARY KEY (id),KEY idx_abc (name,age,gender) USING BTREE
) ENGINEInnoDB AUTO_INCREMENT6 DEFAULT CHARSETutf8开始之前将事务自动提交关闭
1场景一
当前的事务隔离级别为可重复读Repeatable Read
步骤一t1时刻同时开启事务
步骤二t2时刻事务2更新并提交
步骤三t3时刻事务1查询
问题事务1在t3时刻是否能查询t2时刻事务2提交的数据。
预期结果能 因为执行的是select所以为快照读但是由于Read View是在快照读的时候才产生并且可重复读Repeatable Read隔离级别下只产生一个Read View。事务2更新并提交完成时候由于此时事务1还没进行快照读。所以此时事务1查询的时候是可以查询到事务2更新并提交后的数据的。 sql 执行验证
查询结果符合预期
通过算法核对
trx_list13 【当前活跃事务为 13因为2已经提交了】
low_limit_id4【当前系统最大事务版本号1因为已经开启了3个事务所以下一个应该是4】
up_limit_id1 【创建当前read view 时“系统正处于活跃事务最小版本号活跃的只有13最小的是1】
算法验证验证当前事务1是否能看到事务2提交的数据。
1DB_TRX_ID up_limit_id 【21】不成立继续判断
2DB_TRX_ID low_limit_id 【24】不成立继续判断
3判断DB_TRX_ID是否在活跃事务中不在则说明这个事务在Read View生成之前就已经开始commit那么修改的结果是能够看见的。
2场景二
当前的事务隔离级别为可重复读Repeatable Read
步骤一t1时刻同时开启事务
步骤二t2时刻事务1进行快照读
步骤三t3时刻事务2修改并提交
步骤四t4时刻事务1进行快照读
问题事务1在t4时刻是否能查询t3时刻事务2提交的数据。
预期结果不能 事务1在t2时刻执行的是select所以为快照读此时Read View产生并且可重复读Repeatable Read隔离级别下只产生一个Read View。事务2在t3更新并提交完成时候由于此时事务1已经产生了Read View再次进行快照读。所以此时事务1查询的时候是可以查询不到事务2更新并提交后的数据的因为读取的是快照中的数据。 sql 执行验证
查询结果符合预期
通过算法核对
第一次查询
trx_ids1,2,3 【当前活跃事务为 123此时事务2还没开始修改提交】
low_limit_id4【当前系统最大事务版本号1因为已经开启了3个事务所以下一个应该是4】
up_limit_id1 【创建当前read view 时“系统正处于活跃事务最小版本号活跃的只有13最小的是1】
第二次查询
可重复读Repeatable Read事务隔离级别下只产生一个read view
trx_ids1,3 【当前活跃事务为 13此时事务2已经修改提交】
low_limit_id4【当前系统最大事务版本号1因为已经开启了3个事务所以下一个应该是4】
up_limit_id1 【创建当前read view 时“系统正处于活跃事务最小版本号活跃的只有13最小的是1】 第二次快照读的时候当前数据的DB_TRX_ID为2
1DB_TRX_ID up_limit_id 【21】不成立继续判断
2DB_TRX_ID low_limit_id 【24】不成立继续判断
3判断DB_TRX_ID是否在活跃事务中在则代表在Read View生成时刻这个事务还是活跃状态还没有commit修改的数据当前事务也是看不到。
3场景三
当前的事务隔离级别为可重复读Repeatable Read
如果事务中全部都是快照读不会产生幻读问题但是当快照读和当前读一起使用的时候就会产生幻读问题。
Mysql的解决方案是加锁想要解决这个问题必须要保证当前读和快照读的数据必须要一致只能去阻止其他事务进行插入操作所以只能加锁。
sql 执行验证
1如果事务中全部都是快照读不会产生幻读问题 2当快照读和当前读一起使用的时候就会产生幻读问题
3如果事务中全部都是当前读不会产生幻读问题【加锁实现的】 提交后立刻执行
4场景四
当前的事务隔离级别为读已提交Read Committed
验证在此隔离级别每一次快照读都会产生一次新的read view
步骤一t1时刻同时开启事务
步骤二t2时刻事务1进行快照读
步骤三t3时刻事务2修改并提交
步骤四t4时刻事务1进行快照读
问题事务1在t4时刻是否能查询t3时刻事务2提交的数据。
预期结果能 事务1在t2时刻执行的是select所以为快照读此时Read View产生读已提交Read Committed隔离级别下产生多个Read View。所以事务2在t3更新并提交完成时候事务1在t4时刻再次进行快照读。此时又产生了新的Read View。新的Read View是在t3时刻事务2修改并提交产生的所以可以查看到。 sql 验证
符合预期结果
通过算法核对
第一次查询
trx_ids1,2,3 【当前活跃事务为 123此时事务2还没开始修改提交】
low_limit_id4【当前系统最大事务版本号1因为已经开启了3个事务所以下一个应该是4】
up_limit_id1 【创建当前read view 时“系统正处于活跃事务最小版本号活跃的只有13最小的是1】
第二次查询
可重复读Repeatable Read事务隔离级别下只产生一个read view
trx_ids1,3 【当前活跃事务为 13此时事务2已经修改提交】
low_limit_id4【当前系统最大事务版本号1因为已经开启了3个事务所以下一个应该是4】
up_limit_id1 【创建当前read view 时“系统正处于活跃事务最小版本号活跃的只有13最小的是1】 第二次快照读的时候当前数据的DB_TRX_ID为2
1DB_TRX_ID up_limit_id 【21】不成立继续判断
2DB_TRX_ID low_limit_id 【24】不成立继续判断
3判断DB_TRX_ID是否在活跃事务中不在则说明这个事务在Read View生成之前就已经开始commit那么修改的结果是能够看见的。
