移动端网站怎么提交,dw个人简历网页制作,保定seo关键词优化外包,宜春网站建设公司联系方式mysql 事务基础
1 什么是事务
事务是把⼀组SQL语句打包成为⼀个整体#xff0c;在这组SQL的执⾏过程中#xff0c;要么全部成功#xff0c;要么全部失败#xff0c;这组SQL语句可以是⼀条也可以是多条。再来看⼀下转账的例⼦#xff0c;如图#xff1a; 在这个例⼦中在这组SQL的执⾏过程中要么全部成功要么全部失败这组SQL语句可以是⼀条也可以是多条。再来看⼀下转账的例⼦如图 在这个例⼦中涉及了两条更新语句
# 账⼾表
CREATE TABLE account (id bigint PRIMARY KEY AUTO_INCREMENT, name varchar(255) NOT NULL, # 姓名balance decimal(10, 2) NOT NULL # 余额
);
INSERT INTO account(name, balance) VALUES(张三, 1000);
INSERT INTO account(name, balance) VALUES(李四, 1000);# 更新操作
# 张三余额减少100
UPDATE account set balance balance - 100 where name 张三;
# 李四余额增加100
UPDATE account set balance balance 100 where name 李四; 如果转账成功应该有以下结果
张三的账⼾余额减少 100 变成 900 李四的账⼾余额增加了 100 变成 1100 不能出现张三的余额减少⽽李四的余额没有增加的情况张三和李四在发⽣转账前后的总额不变也就是说转账前张三和李四的余额总数为100010002000 转账后他们的余额总数为 90011002000 转账后的余额结果应当保存到存储介质中以便以后读取还有⼀点需要要注意在转账的处理过程中张三和李四的余额不能因其他的转账事件⽽受到⼲扰
以上这四点在事务的整个执⾏过程中必须要得到保证这也就是事务的 ACID 特性即
Atomicity (原⼦性)⼀个事务中的所有操作要么全部成功要么全部失败不会出现只执⾏了⼀半的情况如果事务在执⾏过程中发⽣错误会回滚 Rollback 到事务开始前的状态就像这个事务从来没有执⾏过⼀样Consistency (⼀致性)在事务开始之前和事务结束以后数据库的完整性不会被破坏。这表⽰写⼊的数据必须完全符合所有的预设规则包括数据的精度、关联性以及关于事务执⾏过程中服务器崩溃后如何恢复Isolation (隔离性)数据库允许多个并发事务同时对数据进⾏读写和修改隔离性可以防⽌多个事务并发执⾏时由于交叉执⾏⽽导致数据的不⼀致。事务可以指定不同的隔离级别以权衡在不同的应⽤场景下数据库性能和安全后⾯的⼩节会详细介绍Durability (持久性)事务处理结束后对数据的修改将永久的写⼊存储介质即便系统故障也不会丢失。
2 为什么要使⽤事务
事务具备的ACID特性也是我们使⽤事务的原因在我们⽇常的业务场景中有⼤量的需求要⽤事务来保证。⽀持事务的数据库能够简化我们的编程模型, 不需要我们去考虑各种各样的潜在错误和并发问题在使⽤事务过程中要么提交要么回滚不⽤去考虑⽹络异常服务器宕机等其他因素因此我们经常接触的事务本质上是数据库对 ACID 模型的⼀个实现是为应⽤层服务的。
3 怎么使⽤事务
要使⽤事务那么数据库就要⽀持事务在MySQL中⽀持事务的存储引擎是InnoDB可以通过show engines; 语句查看 通过以下语句可以完成对事务的控制 START TRANSACTION 或 BEGIN 开始⼀个新的事务COMMIT 提交当前事务并对更改持久化保存ROLLBACK 回滚当前事务取消其更改SET autocommit 禁⽤或启⽤当前会话的默认⾃动提交模式 autocommit 是⼀个系统变量可以通过选项指定也可以通过命令⾏设置 --autocommit[{OFF|ON}] 演⽰开启⼀个事务执⾏修改后并回滚 演⽰开启⼀个事务执⾏修改后并回提交 默认情况下MySQL启⽤事务⾃动提交也就是说每个语句都是⼀个事务就像被 STARTTRANSACTION 和 COMMIT 包裹⼀样不能使⽤ ROLLBACK 来撤销执⾏结果但是如果在语句执⾏期间发⽣错误则⾃动回滚通过 SET autocommit 设置⾃动与⼿动提交 ⼿动提交模式下提交或回滚事务时直接使⽤ commit 或 rollback 注意只要使⽤ START TRANSACTION 或 BEGIN 开启事务必须要通过 COMMIT 提交才会持久化与是否设置 SET autocommit ⽆关。
InnoDB 和 ACID 模型
ACID模型是⼀组数据库设计原则强调业务数据的可靠性MySQL的InnoDB存储引擎严格遵循ACID模型不会因为软件崩溃和硬件故障等异常导致数据的不完整。在ACID的实现过程中涉及到⼀些系统变量和相关知识点在这⾥先列出来后⾯我们要逐步讲解
Atomicity(原⼦性)原⼦性⽅⾯主要涉及InnoDB的事务开启与提交我们之前做过详细讲解与回顾 设置 autocommit[{OFF|ON}] 系统变量开启和禁⽤事务是否⾃动提交使⽤ START TRANSACTION 或 BEGIN TRANSACTION 语句开启事务使⽤ COMMIT 语句提交事务使⽤ ROLLBACK 语句回滚事务。 Consistency(⼀致性)⼀致性主要涉及InnoDB内部对于崩溃时数据保护的相关处理相关特性包括 InnoDB 存储引擎的双写缓冲区 doublewrite buffer InnoDB 存储引擎专题中已经介绍过InnoDB 存储引擎的崩溃恢复 Isolation(隔离性)隔离⽅⾯主要涉及应⽤于每个事务的隔离级别相关特性包括: 通过 SET TRANSACTION 语句设置事务的隔离级别InnoDB 存储引擎的锁锁可以在 INFORMATION_SCHEMA 系统库和 PerformanceSchema 系统库中的 data_locks 和 data_lock_waits 表查看 Durability(持久性)持久性涉及MySQL与特定硬件配置的交互可能性取决于CPU、⽹络和存储设备的性能由于硬件环境⽐较复杂所以⽆法提供固定的操作指南只能根据实际环境进⾏测试得到最佳的性能相关特性包括: InnoDB 存储引擎的双写缓冲区 doublewrite buffer innodb_flush_log_at_trx_commit 系统变量的设置sync_binlog 系统变量的设置innodb_file_per_table 系统变量的设置存储设备(如磁盘驱动器、SSD或RAID磁盘阵列)中的写缓冲区存储设备中由电池⽀持的缓存运⾏MySQL的操作系统特别是对 fsync() 系统调⽤的⽀持不间断电源UPS (uninterruptible power supply)保护所有运⾏MySQL服务器和数据存储设备的电⼒供应备份策略例如备份的频率和类型以及备份保留周期分布式环境中数据中⼼之间的⽹络连接。 需要重点说明的是事务最终要保证数据的可靠和⼀致也就是说 ACID 中的Consistency(⼀致性)是最终的⽬的那么当事务同时满⾜了Atomicity(原⼦性)Isolation(隔离性)和Durability(持久性)时也就实现了⼀致性。
如何实现原子性
在⼀个事务的执⾏过程中如果多条DML语句顺利执⾏那么结果最终会写⼊数据库如果在事务的执⾏过程中其中⼀条DML语句出现异常导致后⾯的语句⽆法继续执⾏或即使继续执⾏也会导致数据不完整、不⼀致这时前⾯执⾏的语句已经对数据做了修改如果要保证⼀致性就需要对之前的修改做撤销操作这个撤销操作称为回滚 rollback 如下图所⽰ 那么回滚操作是如何实现的呢回滚过程中依据的是什么呢在InnoDB专题中介绍过UndoLog的作⽤和原理我们⼤致回顾⼀下在事务执⾏每个DML之前把原始数据记录在⼀个⽇志⾥做为回滚的依据这个⽇志称为 Undo Log (回滚⽇志或撤销⽇志)在不考虑缓存和刷盘的条件下执⾏过程如下所⽰ 当需要回滚操作时MySQL根据操作类型在Insert Undo链或Update Undo链中读取相应的⽇志记录并反向执⾏修改使数据还原完成回滚。通过 Undo Log 实现了数据的回滚操作这时就可以保证在事务成功的时候全部的SQL语句都执⾏成功在事务失败的时候全部的SQL语句都执⾏失败实现在原⼦性通过 Undo Log 实现了数据的回滚操作这时就可以保证在事务成功的时候全部的SQL语句都执⾏成功在事务失败的时候全部的SQL语句都执⾏失败实现在原⼦性。
如何实现持久性
提交的事务要把数据写⼊(持久化到)存储介质⽐如磁盘。在正常情况下⼤多没有问题可是在服务器崩溃或突然断电的情况下⼀个事务中的多个修改操作只有⼀部分写⼊了数据⽂件⽽另⼀部分没有写⼊如果不做针对处理的话就会造成数据的丢失从⽽导致数据不完整也就不能保证⼀致性。
在真正写⼊数据⽂件之前MySQL会把事务中的所有DML操作以⽇志的形式记录下来以便在服务器下次启动的时候进⾏恢复操作恢复操作的过程就是把⽇志中没有写到数据⽂件的记录重新执⾏⼀遍保证所有的需要保存的数据都持久化到存储介质中我们把这个⽇志称为 Redo Log (重做⽇志)⽣成重做⽇志是保证数据⼀致性的重要环节。在持久化的处理过程中还包括缓冲池、Doublewrite Buffer (双写缓冲区)、 Binary Log (⼆进制⽇志) 等知识点
隔离性实现原理
1 事务的隔离性
MySQL服务可以同时被多个客⼾端访问每个客⼾端执⾏的DML语句以事务为基本单位那么不同的客⼾端在对同⼀张表中的同⼀条数据进⾏修改的时候就可能出现相互影响的情况为了保证不同的事务之间在执⾏的过程中不受影响那么事务之间就需要要相互隔离这种特性就是隔离性。
2 事务的隔离级别
事务具有隔离性那么如何实现事务之间的隔离隔离到什么程度如何保证数据安全的同时也要兼顾性能这都是要思考的问题。
如果⼤家学习过多线程技术都知道在并发执⾏的过程中多个线程对同⼀个共享变量进⾏修改时在不加限制的情况下会出现线程安全问题我们解决线程安全问题时⼀般的做法是通过对修改操作进⾏加锁同理多个事务在对同⼀个表中的同⼀条数据进⾏修改时如果要实现事务间的隔离也可以通过锁来完成在MySQL中常⻅的锁包括读锁写锁⾏锁间隙锁Next-Key锁等不同的锁策略联合多版本并发控制可以实现事务间不同程度的隔离称为事务的隔离级别不同的隔离级别在性能和安全⽅⾯做了取舍有的隔离级别注重并发性有的注重安全性有的则是并发和安全适中在MySQL的InnoDB引擎中事务的隔离级别有四种分别是
READ UNCOMMITTED 读未提交READ COMMITTED 读已提交REPEATABLE READ 可重复读(默认)SERIALIZABLE 串⾏化
3 锁
实现事务隔离级别的过程中⽤到了锁所谓锁就是在事务A修改某些数据时对这些数据加⼀把锁防⽌其他事务同时对这些数据执⾏修改操作当事务A完成修改操作后释放当前持有的锁以便其他事务再次上锁执⾏对应的操作。不同存储引擎中的锁功能并不相同这⾥我们重点介绍InnoDB存储引擎中的锁
3.1 锁信息
锁的信息包括锁的请求(申请)锁的持有以及阻塞状态等等都保存在 performance_schema库的 data_locks 表中可以通过以下⽅式查看 锁类型
锁类型依赖于存储引擎在InnoDB存储引擎中按照锁的粒度分为⾏级锁 RECORD 和表级锁TABLE
⾏级锁也叫⾏锁是对表中的某些具体的数据⾏进⾏加锁表级锁也叫表锁是对整个数据表进⾏加锁 在之前版本的BDB存储引擎中还⽀持⻚级锁锁定的是⼀个数据⻚MySQL8中没有⻚级锁 锁模式
锁模式⽤来描述如何请求(申请)锁分为共享锁(S)、独占锁(X)、意向共享锁(IS)、意向独占锁(IX)、记录锁、间隙锁、Next-Key锁、AUTO-INC 锁、空间索引的谓词锁等
3.2 共享锁和独占锁 - Shared and Exclusive Locks InnoDB实现了标准的⾏级锁分为两种分别是共享锁(S锁)和独占锁(X锁)独占锁也称为排他锁 共享锁(S锁)允许持有该锁的事务读取表中的⼀⾏记录同时允许其他事务在锁定⾏上加另⼀个共享锁并读取被锁定的对象但不能对其进⾏写操作 独占锁(X锁)允许持有该锁的事务对数据⾏进⾏更新或删除同时不论其他事务对锁定⾏进⾏读取或修改都不允许对锁定⾏进⾏加锁 如果事务T1持有R⾏上的共享锁(S)那么事务T2请求R⾏上的锁时会有如下处理: T2请求S锁会⽴即被授予此时T1和T2都对R⾏持有S锁T2请求X锁不能⽴即被授予阻塞到T1释放持有的锁 如果事务T1持有R⾏上的独占锁(X)那么T2请求R⾏上的任意类型锁都不能⽴即被授予事务T2必须等待事务T1释放R⾏上的锁。 TIPS: 读锁是共享锁的⼀种实现写锁是排他锁的⼀种实现。 3.3 意向锁 - Intention Locks InnoDB⽀持多粒度锁允许⾏锁和表锁共存 InnoDB使⽤意向锁实现多粒度级别的锁意向锁是表级别的锁它并不是真正意义上的加锁⽽只是在 data_locks 中记录事务以后要对表中的哪⼀⾏加哪种类型的锁(共享锁或排他锁)意向锁分为两种: 意向共享锁(IS)表⽰事务打算对表中的单个⾏设置共享锁。意向排他锁(IX)表⽰事务打算对表中的单个⾏设置排他锁 在获取意向锁时有如下协议 在事务获得表中某⼀⾏的共享锁(S)之前它必须⾸先获得该表上的IS锁或更强的锁。在事务获得表中某⼀⾏的排他锁(X)之前它必须⾸先获得该表上的IX锁。 意向锁可以提⾼加锁的性能在真正加锁之前不需要遍历表中的⾏是否加锁只需要查看⼀下表中的意向锁即可 在请求锁的过程中如果将要请求的锁与现有锁兼容则将锁授予请求的事务如果与现有锁冲突则不会授予事务将阻塞等待直到冲突的锁被释放意向锁与⾏级锁的兼容性如下表 除了全表锁定请求之外意向锁不会阻⽌任何锁请求意向锁的主要⽬的是表⽰事务正在锁定某⾏或者正在意图锁定某⾏。
3.4 索引记录锁 - Record Locks
索引记录锁或称为精准⾏锁顾名思意是指索引记录上的锁如下SQL锁住的是指定的⼀⾏
# 防⽌任何其他事务插⼊、更新或删除值为1的⾏id为索引列
SELECT * FROM account WHERE id 1 For UPDATE;索引记录锁总是锁定索引⾏在表没有定义索引的情况下InnoDB创建⼀个隐藏的聚集索引并使⽤该索引进⾏记录锁定当使⽤索引进⾏查找时锁定的只是满⾜条件的⾏如图所⽰ 3.5 间隙锁 - Gap Lock
间隙锁锁定的是索引记录之间的间隙或者第⼀个索引记录之前再或者最后⼀个索引记录之后的间隙。如图所⽰位置根据不同的查询条件都可能会加间隙锁 例如有如下SQL锁定的是ID (10, 20)之间的间隙注意不包括10和20的⾏⽬的是防⽌其他事务将ID值为15的列插⼊到列 account 表中(⽆论是否已经存在要插⼊的数据列)因为指定范围值之间的间隙被锁定了
SELECT * FROM account WHERE id BETWEEN 10 and 20 For UPDATE;间隙可以跨越单个或多个索引值 对于使⽤唯⼀索引查询到的唯⼀⾏不使⽤间隙锁如下语句id列有唯⼀的索引只对id值为100的⾏使⽤索引记录锁
# 只使⽤Record Locks
SELECT * FROM account WHERE id 100;如果id没有被索引或者是⼀个⾮唯⼀的索引以上语句将锁定对应记录前⾯的间隙不同事务的间隙锁可以共存⼀个事务的间隙锁不会阻⽌另⼀个事务在相同的间隙上使⽤间隙锁共享间隙锁和独占间隙锁之间没有区别。当事务隔离级别设置为 READ COMMITTED 时间隙锁会被禁⽤对于搜索和索引扫描不再使⽤间隙锁定。
3.6 临键锁 - Next-Key Locks Next-key 锁是索引记录锁和索引记录之前间隙上间隙锁的组合如图所⽰ InnoDB搜索或扫描⼀个表的索引时执⾏⾏级锁策略具体⽅式是在扫描过程中遇到的索引记录上设置共享锁或排他锁因此⾏级锁策略实际上应⽤的是索引记录锁。索引记录上的 next-key 锁也会影响该索引记录之前的间隙也就是说 next-key 锁是索引记录锁加上索引记录前⾯的间隙锁。如果⼀个会话对索引中的⼀条记录R具有共享锁或排他锁则另⼀个会话不能在索引记录R之前的空⽩中插⼊新的索引记录⾏。 假设索引包含值10、11、13和20这些索引可能的 next-key 锁覆盖以下区间其中圆括号表⽰不包含区间端点⽅括号表⽰包含端点: 默认情况下 REPEATABLE READ 事务隔离级别开启 next-key 锁并进⾏搜索和索引扫描可以防⽌幻象⾏从⽽解决幻读问题后⾯我们再分析。
3.7 插⼊意向锁 - Insert Intention Locks 插⼊意向锁是⼀个特殊的间隙锁在向索引记录之前的间隙进⾏insert操作插⼊数据时使⽤如果多个事务向相同索引间隙中不同位置插⼊记录则不需要彼此等待。假设已经存在值为10和20的索引记录两个事务分别尝试插⼊索引值为15和16的⾏在获得插⼊⾏上的排他锁之前每个事务都⽤插⼊意向锁锁住10到20之间的间隙但不会相互阻塞因为他们所操作的⾏并不冲突 下⾯的⽰例演⽰⼀个事务在获得插⼊记录的排他锁之前使⽤了插⼊意向锁 3.8 AUTO-INC Locks
AUTO-INC锁也叫⾃增锁是⼀个表级锁服务于配置了 AUTO_INCREMENT ⾃增列的表。在插⼊数据时会在表上加⾃增锁并⽣成⾃增值同时阻塞其他的事务操作以保证值的唯⼀性。需要注意的是当⼀个事务执⾏新增操作已⽣成⾃增值但是事务回滚了申请到的主键值不会回退这意味着在表中会出现⾃增值不连续的情况。其他的相关内容这⾥我们不做深⼊讨论。
3.9 死锁
3.9.1 ⽰例
由于每个事务都持有另⼀个事务所需的锁导致事务⽆法继续进⾏的情况称为死锁。以下图为例两个事务都不会主动释放⾃⼰持有的锁并且都在等待对⽅持有的资源变得可⽤。 下⾯通过⼀个⽰例演⽰⼀下死锁的发⽣过程其中涉及两个客⼾端A和B并通过启⽤全局变量innodb_print_all_deadlocks 来查看死锁的信息同时死锁信息也会保存到错误⽇志中⾸先打开⼀个客⼾端A并执⾏以下操作 接下来打开客⼾端B并执⾏以下操作 在另⼀个客⼾端中查看两个select操作持有的锁信息
# 查看锁信息
mysql SELECT ENGINE_TRANSACTION_ID as Trx_Id, OBJECT_NAME as Table, INDEX_NAME as Index, LOCK_DATA as Data, LOCK_MODE as Mode, LOCK_STATUS as Status, LOCK_TYPE as Type FROM performance_schema.data_locks;在客⼾端B中更新animals表中的⾏
# 更新animals表中的⾏
mysql UPDATE animals SET value30 WHERE namedog;客⼾B开始等待可以查看锁的等待信息:
# 查看等待中的锁
mysql SELECT REQUESTING_ENGINE_LOCK_ID as Req_Lock_Id,REQUESTING_ENGINE_TRANSACTION_ID as Req_Trx_Id,BLOCKING_ENGINE_LOCK_ID as Blk_Lock_Id, BLOCKING_ENGINE_TRANSACTION_ID as Blk_Trx_IdFROM performance_schema.data_lock_waits;# 查看锁信息
mysql SELECT ENGINE_TRANSACTION_ID as Trx_Id, OBJECT_NAME as Table, INDEX_NAME as Index, LOCK_DATA as Data, LOCK_MODE as Mode, LOCK_STATUS as Status, LOCK_TYPE as Type FROM performance_schema.data_locks;InnoDB只有在事务试图修改数据时才使⽤顺序事务id之前的只读事务id由411549995855872 变 为52005 如果客⼾端A试图同时更新birds中的⼀⾏将导致死锁 死锁发⽣时InnoDB主动回滚导致死锁的事务此时可以看到客⼾端B的更新执⾏成功。 可以通过以下⽅式查看当前服务器发⽣死锁的次数 InnoDB的监视器包含了关于死锁和事务的相关信息可以通过 SHOW ENGINE INNODB STATUS; 查看 LATEST DETECTED DEADLOCK 节点的内容 错误⽇志中也记录了死锁相关的信息 3.9.2 死锁产⽣的条件
互斥访问如果线程1获取到了锁A那么线程2就不能同时得到锁A不可抢占获取到锁的线程只能⾃⼰主动释放锁别的线程不能从他的⼿中抢占锁保持与请求线程1已经获得了锁A还要在这个基础上再去获了锁B循环等待线程1等待线程2释放锁线程2也等待线程1释放锁死锁发⽣时系统中⼀定有由两个或两个以上的线程组成的⼀条环路该环路中的每个线程都在等待着下⼀个进程释放锁 以上四条是造成死锁的必要条件必须同时满⾜所以如果想要打破死锁可以破坏以上四个条件之⼀最常⻅的⽅式就是打破循环等待 3.9.3 InnoDB对死锁的检测
InnoDB在运⾏时会对死锁进⾏检测当死锁检测启⽤时(默认)InnoDB⾃动检测事务死锁并回滚⼀个或多个事务来打破死锁。InnoDB尝试选择⼩事务进⾏回滚其中事务的⼤⼩由插⼊、更新或删除的⾏数决定。如果系统变量 innodb_table_locks 1 (默认) 和 autocommit 0 InnoDB可以检测到表级锁和⾏级锁级别发⽣的死锁否则⽆法检测到由 lock TABLES 语句设置的表锁或由⾮InnoDB存储引擎设置的锁对于⽆法检测到的死锁可以通过设置系统变量innodb_lock_wait_timeout 的值来指定锁的超时时间来解决死锁问题当超过 200 个事务等待锁资源或等待的锁个数超过 1,000,000 个时也会被视为死锁并尝试将等待列表的事务回滚。在⾼并发系统中多个线程等待相同的锁时死锁检测可能会导致性能降性变慢此时禁⽤死锁检测并依赖 innodb_lock_wait_timeout 设置进⾏事务回滚可能性能更⾼。可以通过设置系统变量 innodb_deadlock_detect[{OFF|ON}] 禁⽤死锁检测。
3.9.4 如何避免死锁
MySQL是⼀个多线程程序死锁的情况⼤概率会发⽣但他并不可怕除⾮频繁出现导致⽆法运⾏某些事务InnoDB使⽤⾃动⾏级锁即使在只插⼊或删除单⾏的事务中也可能出现死锁。这是因为插⼊或删除⾏并不是真正的原⼦操作同时会对索引记录进⾏修改并设置锁使⽤以下技术来处理死锁并降低发⽣死锁的可能性: 使⽤事务⽽不是使⽤ LOCK TABLES 语句⼿动加锁并使⽤innodb_lock_wait_timeout 变量设置锁的超时时间保证任何情况下锁都可以⾃动释放经常使⽤ SHOW ENGINE INNODB STATUS 命令来确定最近⼀次死锁的原因。这可以帮助我们修改应⽤程序以避免死锁如果出现频繁的死锁警告可以通过启⽤ innodb_print_all_deadlocks 变量来收集调试信息。对于死锁的信息都记录在MySQL错误⽇志中调试完成后记得禁⽤此选项如果事务由于死锁⽽失败记得重新发起事务再执⾏⼀次尽量避免⼤事务保持事务粒度⼩且持续时间短这样事务之间就不容易发⽣冲突从⽽降低发⽣死锁的概率修改完成后⽴即提交事务也可以降低死锁发⽣的概率。特别注意的是不要在⼀个交互式会话中⻓时间打开⼀个未提交的事务当事务中要修改多个表或同⼀表中的不同⾏时每次都要以⼀致的顺序执⾏这些操作使事务中的修改操作形成定义良好的队列可以避免死锁。⽽不是在不同的位置编写多个类似的INSERT、UPDATE和DELETE语句。我们写的程序其实就是把⼀系列操作组织成⼀个⽅法或函数向表中添加适当的索引以便查询时扫描更少的索引并设置更少的锁可以使⽤EXPLAINSELECT来确定哪些索引⽤于当前的查询使⽤表级锁防⽌对表进⾏并发更新可以避免死锁但代价是系统的并发性降低如果在查询时加锁⽐如 SELECT…FOR UPDATE 或 SELECT…FOR SHARE 尝试使⽤较低的隔离级别⽐如 READ COMMITTED
4 查看并设置隔离级别
事务的隔离级别分为全局作⽤域和会话作⽤域查看不同作⽤域事务的隔离级别可以使⽤以下的⽅式 设置事务的隔离级别和访问模式可以使⽤以下语法 通过选项⽂件指定事务的隔离级别以便MySQL启动的时候读取并设置 通过SET语法设置系统变量的⽅式设置事务的隔离级别 设置事务隔离级别的语句不能在已开启的事务中执⾏否则将会报错 接下来介绍不同事务隔离级别的实现⽅式以及可能出现的问题
5 READ UNCOMMITTED - 读未提交与脏读
5.1 实现⽅式
读取时不加任何锁直接读取版本链中的最新版本也就是当前读可能会出现脏读不可重复读、幻读问题读取时不加任何锁直接读取版本链中的最新版本也就是当前读可能会出现脏读不可重复读、幻读问题
5.2 存在问题
事务的 READ UNCOMMITTED 隔离级别不使⽤独占锁所以并发性能很⾼但是会出现⼤量的数据安全问题⽐如在事务A中执⾏了⼀条 INSERT 语句在没有执⾏ COMMIT 的情况下会在事务B中被读取到此时如果事务A执⾏回滚操作那么事务B中读取到事务A写⼊的数据将没有意义我们这个理象叫做 “脏读” 。
5.3 问题重现
在⼀个客⼾端A中先设置全局事务隔离级别为 READ UNCOMMITTED 读未提交: 打开另⼀个客⼾端B并确认隔离级别 由于 READ UNCOMMITTED 读未提交会出现脏读现象在正常的业务中出现这种问题会产⽣⾮常危重后果所以正常情况下应该避免使⽤ READ UNCOMMITTED 读未提交这种的隔离级别。
6 READ COMMITTED - 读已提交与不可重复读
6.1 实现⽅式
读取时不加锁但使⽤快照读即按照 MVCC 机制读取符合 ReadView 要求的版本数据每次查询都会构造⼀个新的 ReadView 可以解决脏读但⽆法解决不可重复读和幻读问题更新时加独占⾏锁(X)事务结束时释放数据在修改完毕之前其他事务不能修改也不能读取这⾏数据。
6.2 存在问题
为了解决脏读问题可以把事务的隔离级别设置为 READ COMMITTED 这时事务只能读到了其他事务提交之后的数据但会出现不可重复读的问题⽐如事务A先对某条数据进⾏了查询之后事务B对这条数据进⾏了修改并且提交( COMMIT )事务事务A再对这条数据进⾏查询时得到了事务B修改之后的结果这导致了事务A在同⼀个事务中以相同的条件查询得到了不同的值这个现象要不可重复读。
6.3 问题重现
在⼀个客⼾端A中先设置全局事务隔离级别为 READ COMMITTED 读未提交: 打开另⼀个客⼾端B并确认隔离级别 在不同的客⼾端中执⾏事务 7 REPEATABLE READ - 可重复读与幻读
7.1 实现⽅式
读取时不加锁也使⽤快照读按照MVCC机制读取符合ReadView要求的版本数据但⽆论事务中有⼏次查询只会在⾸次查询时⽣成⼀个ReadView可以解决脏读、不可重复读配合Next-Key⾏锁可以解决⼀部分幻读问题更新时加Next-Key⾏锁事务结束时释放在⼀个范围内的数据修改完成之前其他事务不能对这个范围内的数据进⾏修改、插⼊和删除操作同时也不能被查询。
7.2 存在问题
事务的 REPEATABLE READ 隔离级别是会出现幻读问题的在 InnoDB 中使⽤了Next-Key⾏锁来解决⼤部分场景下的幻读问题那么在不加 Next-Key ⾏锁的情况下会出现什么问题吗
我们知道 Next-Key 锁锁住的是当前索引记录以及索引记录前⾯的间隙那么在不加 Next-Key 锁的情况下也就是只对当前修改⾏加了独占⾏锁(X)这时记录前的间隙没有被锁定其他的事务就可以向这个间隙中插⼊记录就会导致⼀个问题事务A查询了⼀个区间的记录得到结果集A事务B向这个区间的间隙中写⼊了⼀条记录事务A再查询这个区间的结果集时会查到事务B新写⼊的记录得到结果集B两次查询的结果集不⼀致这个现象就是幻读。
7.3 问题重现
由于 REPEATABLE READ 隔离级别默认使⽤了 Next-Key 锁为了重现幻读问量我们把隔离级回退到更新时只加了排他锁的 READ COMMITTED . 在不同的客⼾端中执⾏事务 8 SERIALIZABLE - 串⾏化
8.1 实现⽅式 读取时加共享表锁读取版本链中的最新版本事务结束时释放 更新时加独占表锁事务结束时释放完全串⾏操作可以解决所有事务问题。
8.2 存在问题
所有的更新都是串⾏操作效率极低
9 不同隔离级别的性能与安全 10 多版本控制(MVCC)
上⼀个⼩节介绍了实现事务隔离性的锁机制但是频繁加锁与释放锁会对性能产⽣⽐较⼤的影响为了提⾼性能InnoDB与锁配合同时采⽤另⼀种事务隔离性的实现机制 MVCC 即 Multi-Versioned Concurrency Control 多版本并发控制⽤来解决脏读、不可重复读等事务之间读写问题MVCC 在某些场景中替代了低效的锁在保证了隔离性的基础上提升了读取效率和并发性。
10.1 实现原理
10.1.1 版本链
MVCC的实现是基于 Undo Log 版本链和 ReadView 来完成的Undo Log做为回滚的基础在执⾏Update或Delete操作时会将每次操作的上⼀个版本记录在Undo Log中每条Undo Log中都记录⼀个叫做 roll_pointer 的引⽤信息通过 roll_pointer 就可以将某条数据对应的Undo Log组织成⼀个Undo链在数据⾏的头部通过数据⾏中的 roll_pointer 与Undo Log中的第⼀条⽇志进⾏关联这样就构成⼀条完整的数据版本链如下图所⽰Undo Log的具体结构和⾏结构请参考InnoDB 存储引擎专题 每⼀条被修改的记录都会有⼀条版本链体现了这条记录的所有变更当有事务对这条数据进⾏修改时将修改后的数据链接到版本链接的头部如下图中 UNDO3 10.1.2 ReadView 每条数据的版本链都构造好之后在查询时具体选择哪个版本呢这⾥就需要使⽤ ReadView 结构来实现了所谓 ReadView 是⼀个内存结构顾名思义是⼀个视图在事务使⽤ select 查询数据时就会构造⼀个ReadView⾥⾯记录了该版本链的⼀些统计值这样在后续查询处理时就不⽤遍历所有版本链了这些统计值具体包括 m_ids 当前所有活跃事务的集合m_low_limit_id 活跃事务集合中最⼩事务Idm_up_limit_id 下⼀个将被分配的事务Id也就是 版本链头的事务Id 1m_creator_trx_id 创建当前 ReadView 的事务Id 构造好 ReadView 之后需要根据⼀定的查询规则找到唯⼀的可⽤版本这个查找规则⽐较简单以下图的版本链为例在 m_creator_trx_id201 的事务执⾏ select 时会构造⼀个ReadView 同时对相应的变量赋值 m_ids 活跃事务集合为 [90, 100, 200]m_up_limit_id 活跃事务最⼩事务Id 90m_low_limit_id 预分配事务ID 202 最⼤事务Id 预分配事务ID - 1 201m_creator_trx_id 当前创建 ReadView 的事务Id 201 接下来找到版本链头从链头开始遍历所有版本根据四步查找规则判断每个版本 第⼀步判断该版本是否为当前事务创建若 m_creator_trx_id 等于该版本事务id意味着读取⾃⼰修改的数据可以直接访问如果不等则到第⼆步第⼆步若该版本事务Id m_up_limit_id (最⼩事务Id)意味着该版本在ReadView⽣成之前已经提交可以直接访问如果不是则到第三步第三步或该版本事务Id m_low_limit_id (最⼤事务Id)意味着该版本在ReadView⽣成之后才创建所以肯定不能被当前事务访问所以⽆需第四步判断直接遍历下⼀个版本如果不是则到第四步第四步若该版本事务Id在 m_up_limit_id (最⼩事务Id)和 m_low_limit_id (最⼤事务Id)之间同时该版本不在活跃事务列表中意味着创建ReadView时该版本已经提交可以直接访问如果不是则遍历并判断下⼀个版本 这样从版本链头遍历判断到版本链尾找到⾸个符合要求的版本即可就可以实现查询到的结果都是已经提交事务的数据解决了脏读问题。 10.2 MVCC是否可以解决不可重复读与幻读
⾸先幻读⽆法通过MVCC单独解决对于不可重复读问题在事务中的第⼀个查询时创建⼀个ReadView后续查询都是⽤这个ReadView进⾏判断所以每次的查询结果都是⼀样的从⽽解决不可重复读问题在REPEATABLE READ 可重复读隔离级别下就采⽤的这种⽅式如果事务每次查询都创建⼀个新的ReadView这样就会出现不可重复读问题在 READ COMMITTED 读已提交的隔离级别下就是这种实现⽅式
以上就是关于MVCC的相关介绍加上锁就可以实现完整的ACID中的隔离性。
