北京响应式网站建设,wordpress作者关注,搜索排行榜,网站建设教育培训「前言」文章内容大致是MySQL事务管理。 「归属专栏」MySQL 「主页链接」个人主页 「笔者」枫叶先生(fy) 目录 一、事务概念二、事务的版本支持三、事务提交方式四、事务常见的操作方式4.1 事务正常操作4.2 事务异常验证 五、事务隔离级别5.1 查看与设置隔离性5.2 读未提交Read Uncommitted5.3 读提交Read Committed5.4 可重复读Repeatable Read5.5 串行化Serializable5.6 隔离级别总结 六、一致性 一、事务概念 事务的概念 MySQL事务是指一系列的数据库操作一组DML语句这些操作要么全部成功执行要么全部失败回滚。事务的目的是确保数据库的一致性和完整性事务就是要做的或所做的事情主要用于处理操作量大复杂度高的数据。假设一个电商网站的订单支付场景。在这个场景中订单支付涉及到多个步骤和多个数据表的操作如订单表、库存表和支付记录表等。例如当用户点击支付按钮时开始一个事务然后依次执行创建订单、扣减库存和创建支付记录的操作。如果其中任何一个操作失败整个事务会被回滚订单不会被创建库存不会被扣减支付记录也不会被创建。只有当所有操作都成功执行后事务才会被提交订单支付完成这样需要多条MySQL语句构成那么所有这些操作合起来就构成了一个事务再次回忆DMLDML【data manipulation language】数据操纵语言用来对数据进行操作代表指令 insertdelete、update。DML中又单独分了一个DQL数据查询语言代表指令select
MySQL同一时刻可能存在大量事务如果不对这些事务加以控制在执行时就可能会出现问题。
每条事务至少一条SQL或者很多条的SQL这样如果大家都访问同样的表数据在不加保护的情况就绝对会出现问题。甚至因为事务由多条SQL构成那么也会存在执行到一半出错或者不想再执行的情况那么已经执行的怎么办
因此一个完整的事务并不是简单的SQL集合事务还需要满足如下四个属性
原子性一个事务transaction中的所有操作要么全部完成要么全部不完成不会结束在中间某个环节。事务在执行过程中发生错误会被回滚Rollback到事务开始前的状态就像这个事务从来没有执行过一样一致性在事务开始之前和事务结束以后数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作隔离性数据库允许多个并发事务同时对其数据进行读写和修改的能力隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别包括读未提交Read uncommitted、读提交read committed、可重复读repeatable read和串行化Serializable持久性事务处理结束后对数据的修改就是永久的即便系统故障也不会丢失
上面的四个属性简称ACID
原子性Atomicity又称不可分割性一致性Consistency隔离性Isolation又称独立性持久性Durability 为什么会出现事务 事务被MySQL编写者设计出来本质是为了当应用程序访问数据库的时候事务能够简化我们的编程模型不需要用户自己去考虑各种各样的潜在错误和并发问题如果MySQL只是单纯的提供数据存储服务那么用户在访问数据库时就需要自行考虑各种潜在问题包括网络异常、服务器宕机等。因此事务本质是为了应用服务的而不是伴随着数据库系统天生就有的而是后面使用了数据库一段时间之后发现是需要事务这个功能的MySQL的编写者就对事务进行的封装和支持
二、事务的版本支持
在 MySQL 中只有使用了Innodb数据库引擎的数据库或表才支持事务 MyISAM不支持
通过以下命令查看MySQL的数据库引擎查看哪些引擎支持事务
show engines;说明
Transactions表示存储引擎是否支持事务可以看到InnoDB存储引擎支持事务而MyISAM存储引擎不支持事务
三、事务提交方式
事务常见的提交方式有两种分别是自动提交和手动提交
查看事务的提交方式
show variables like autocommit;注ON表示自动提交被打开值为OFF表示自动提交被关闭即事务的提交方式为手动提交 可以用SET来改变MySQL的自动提交模式
SET AUTOCOMMIT0; --禁止自动提交
SET AUTOCOMMIT1; --开启自动提交注设置为0表示关闭自动提交相当于将事务提交方式设置为手动提交
四、事务常见的操作方式
为了便于演示需要将MySQL的隔离级别设置成读未提交也就是把隔离级别设置的比较低方便看到实验现象
隔离级有隔离级别依次提高读未提交隔离级别最低
读未提交Read uncommitted读提交read committed可重复读repeatable read串行化Serializable
设置最低的隔离级别
set global transaction isolation level read uncommitted;注意设置全局隔离级别后当前会话的隔离级别不会改变只会影响后续与MySQL新建立的连接因此需要重启终端才能看到会话的隔离级别被成功设置
重启客户端之后再进行登录查看如下 然后创建测试表 create table if not exists account(
id int primary key,
name varchar(50) not null default ,
blance decimal(10,2) not null default 0.0
)ENGINEInnoDB DEFAULT CHARSETUTF8;4.1 事务正常操作 测试1 启动两个终端两个终端都各启动一个事务右边不启动事务也行启动事务使用以下命令任意一个
begin;
-- or
start transaction启动事务之后查询该表是没有数据的
注这里事务提交方式为自动提交 左终端中的事务使用savepoint命令创建一个保存点该命令用于设置一个保存点
savepoint 保存点名字;在左终端中插入一条数据在右终端中就能查看到左终端的事务向表中插入的记录 左终端中的事务使用savepoint命令创建一个保存点然后继续向表中插入一条记录这时在右终端中也能看到新插入的这条记录读未提交 左终端中的事务使用rollback命令回滚到事务
rollback to 保存点; -- 事务回滚到该保存点
rollback; -- 默认回滚到事务的最开始事务回滚到保存点s2这时右终端在查看表中数据时就看不到刚才插入的第二条记录了 左终端中的事务直接使用rollback命令回滚到事务最开始右边的终端查询数据为空 使用commit命令可以提交事务代表一个事务结束提交事务后就不能回滚了 注意提交事务之后就不能回滚了再次rollback也无法回滚
上面是事务操作的正常情况而事务更多是为了应对非正常的情况比如MySQL客户端突然崩溃不小心关闭等等
4.2 事务异常验证 演示1体现事务的原子性 目的证明事务未commit客户端崩溃MySQL自动会回滚隔离级别设置为读未提交事务提交方式为自动提交
两个终端都各启动一个事务表中事先有一条数据右边不启动事务也行 左终端中的事务向表中插入一条记录由于隔离级别是读未提交因此在右终端中能够查询到插入的这条记录 左边的客户端异常退出ctrl \没有提交事务 结果是MySQL会自动让事务回滚到最开始这时右终端中就看不到之前插入的记录了 演示2体现事务的持久性 目的证明事务commit了即使客户端崩溃MySQL数据不会在受影响数据已经持久化已经保存到磁盘隔离级别依旧设置为读未提交事务提交方式为自动提交
两个终端都各启动一个事务右边不启动事务也行 左终端中的事务向表中插入一条记录由于隔离级别是读未提交因此在右终端中能够查询到插入的这条记录 左终端进行提交事务然后再异常退出 右终端中仍然可以看到之前插入的记录因为事务提交后数据就被持久化了 即使是事务自动提交关闭了只要commit了数据依旧就被持久化了下面实验3进行验证 演示3验证实验 目的证明begin操作会自动更改提交方式不会受MySQL是否自动提交影响
关闭自动提交
set autocommit 0;左终端中启动一个事务并向表中新插入一条记录右边也可以启动一个事务由于隔离级别是读未提交因此在右终端中能够查询到新插入的这条记录 左终端进行提交事务然后再异常退出右终端中仍然可以看到之前插入的记录因为事务提交后数据就被持久化了
上述说明使用begin或start transaction·命令启动的事务都必须要使用commit命令手动提交数据才会被持久化与是否设置autocommit无关 演示4 目的证明单条SQL与事务的关系
左右两边的终端都关闭自动提交右边不关也行右边不影响操作
set autocommit 0;不启动事务直接删除一条数据由于隔离级别是读未提交右边的终端也能看到结果 执行完单条SQL语句后左边的客户端直接异常退出而右边终端被删除的数据被回滚回来了 左边的终端打开自动提交右边不影响操作
set autocommit 1;不启动事务直接删除一条数据由于隔离级别是读未提交右边的终端也能看到结果 执行完单条SQL语句后左边的客户端直接异常退出右边的数据没有发生变化即左边终端的操作没有被回滚 实验结果说明实际我们之前一直都在使用单SQL事务只不过autocommit默认是打开的因此单SQL事务执行后自动就被提交了
全局变量autocommit是否被设置影响的是单条SQL语句InnoDB中的每一条SQL都会默认被封装成事务如果autocommit为ON则单条SQL语句执行后会自动被提交如果为OFF则SQL语句执行后需要使用commit进行手动提交如果不手动提交执行的SQL会发生回滚 上述实验结论 只要输入begin或者start transaction事务便必须要通过commit提交才会持久化与是否设置set autocommit无关事务可以手动回滚同时当操作异常MySQL会自动回滚对于InnoDB每一条 SQL 语言都默认封装成事务自动提交select有特殊情况因为MySQL有 MVCC 下面谈上述实验体现了事务本身的原子性(rollback)持久性(commit) 事务操作注意事项 如果没有设置保存点也可以回滚只能回滚到事务的开始。直接使用rollback前提是事务还没有提交如果一个事务被提交了commit则不可以回退rollback事务可以选择回退到哪个保存点前提是事务还没有提交InnoDB支持事务MyISAM不支持事务
五、事务隔离级别
MySQL服务可能会同时被多个客户端进程线程访问访问的方式以事务的方式进行一个事务可能由多条SQL语句构成也就意味着任何一个事务都有执行前、执行中和执行后三个阶段而所谓的原子性就是让用户层要么看到执行前要么看到执行后执行中如果出现问题可以随时进行回滚所以单个事务对用户表现出来的特性就是原子性但毕竟每个事务都有一个执行的过程在多个事务各自执行自己的多条SQL时仍然可能会出现互相影响的情况比如多个事务同时访问同一张表甚至是表中的同一条记录数据库为了保证事务执行过程中尽量不受干扰于是出现了隔离性数据库为了允许事务在执行过程中受到不同程度的干扰于是出现了隔离级别 隔离级别依次提高 读未提交Read Uncommitted在该隔离级别所有的事务都可以看到其他事务没有提交的执行结果实际生产中不可能使用这种隔离级别的但是相当于没有任何隔离性也会有很多并发问题如脏读幻读不可重复读等我们上面为了做实验方便用的就是这个隔离性读提交Read Committed该隔离级别是大多数数据库的默认的隔离级别不是 MySQL 默 认的。它满足了隔离的简单定义一个事务只能看到其他的已经提交的事务所做的改变。这种隔离级别会引起不可重复读即一个事务执行时如果多次 select可能得到不同的结果可重复读Repeatable Read这是MySQL默认的隔离级别它确保同一个事务在执行中多次读取操作数据时会看到同样的数据行但是会有幻读问题串行化Serializable这是事务的最高隔离级别它通过强制事务排序使之不可能相互冲突从而解决了幻读的问题。它在每个读的数据行上面加上共享锁但是可能会导致超时和锁竞争这种隔离级别太极端实际生产基本不使用
隔离级别基本都是通过锁实现的不同的隔离级别锁的使用是不同的。常见有表锁行锁读锁写锁间隙锁GAPNext-Key锁(GAP行锁)等
5.1 查看与设置隔离性 查看全局隔级别 select global.tx_isolation;查看当前会话隔离级别 select session.tx_isolation;还可以使用以下命令查看当前会话的隔离级别
select tx_isolation;设置当前会话隔离级别 set session transaction isolation level 隔离级别;例如给当前会话设置串行化
set session transaction isolation level serializable;注设置当前会话的隔离级别只会影响当前会话新起的会话依旧采用全局的隔离级 设置全局隔离级别 set global transaction isolation level 隔离级别;比如设置全局可重复读 注意设置全局隔离级别会影响后续的新会话但是不会影响当前会话如果需要让全局隔离级别生效需要重启该客户端 下面进行实验演示演示四个隔离级别
5.2 读未提交Read Uncommitted
启动两个终端将隔离级别都设置为读未提交然后查看表中数据
set session transaction isolation level read uncommitted;左右两个终端各启动一个事务左边终端对表中数据进行修改左边终端的事务没有提交右边终端的事务可以看到修改的数据
说明
像这种一个事务在执行中读到另一个执行中事务的更新或其他操作但是未commit的数据这种现象叫做脏读(dirty read)读未提交是事务的最低隔离级别几乎没有加锁虽然效率高但是问题比较多所以严重不建议使用
5.3 读提交Read Committed
启动两个终端将隔离级别都设置为读提交然后查看表中数据 两个终端各自启动一个事务左终端进行对表中数据修改在左终端事务未提交之前右终端的事务看不到另一个事务的对数据的修改结果 只有当左终端中的事务提交后右终端中的事务才能看到修改后的数据 说明
像这种当前事务并未commit和当前事务提交事务这两个时间段另一个事务在这两个时间段读取到了不同的值这种现象叫做不可重复读non reapeatable read这种现象可能会导致数据的一致性问题因为一个事务在不同的时间点读取到不同的数据值可能会产生错误的计算结果或逻辑错误不建议使用该隔离级别
5.4 可重复读Repeatable Read
启动两个终端将隔离级别都设置为可重复读然后查看表中数据 两个终端各自启动一个事务左终端中的事务所作的修改在没有提交之前右终端中的事务无法看到 并且当左终端中的事务提交后右终端中的事务仍然看不到修改后的数据 只有当右终端中的事务提交事务之后再查看表中数据此时才能看到表中数据 说明
向上面这样的称之为可重复读但是一般的数据库在可重复读情况的时候update数据是满足可重复读的但insert数据会存在幻读问题因为隔离性是通过对数据加锁完成的而新插入的数据原本是不存在的因此一般的加锁无法屏蔽这类问题但是MySQL解决了可重复读隔离级别下的幻读问题通过Next-Key锁GAP行锁来解决幻读问题幻读一个事务在执行过程中相同的select语句查询得到了不同的数据出现新数据如同出现了幻觉这种现象叫做幻读
下面演示MySQL的可重复读是没有幻读问题
启动两个终端将隔离级别都设置为可重复读然后查看表中数据 然后两个终端各自启动一个事务左终端向表中插入新数据并提交事务右终端中的事务仍然看不到新插入的数据证明没有幻读问题
5.5 串行化Serializable
启动两个终端将隔离级别都设置为串行化然后查看表中数据 然后两个终端各自启动一个事务如果这两个事务都对表进行的是读操作那么这两个事务可以并发执行不会被阻塞 果这两个事务中有一个事务要对表进行写操作那么这个事务就会立即被阻塞 直到右边终端事务提交了之后左终端的事务才能对表进行修改操作 说明
对所有操作全部加锁进行串行化不会有问题但是只要串行化效率很低几乎完全不会被采用
5.6 隔离级别总结
其中隔离级别越严格安全性越高但数据库的并发性能也就越低往往需要在两者之间找一个平 衡点mysql 默认的隔离级别是可重复读一般情况下不要修改
隔离级别总结如下
六、一致性
事务执行的结果必须使数据库从一个一致性状态变到另一个一致性状态。当数据库只包含事务 成功提交的结果时数据库处于一致性状态。
事务在执行过程中如果发生错误则需要自动回滚到事务最开始的状态就像这个事务从来没有执行过一样即一致性需要原子性来保证事务处理结束后对数据的修改必须是永久的即便系统故障也不能丢失即一致性需要持久性来保证多个事务同时访问同一份数据时必须保证这多个事务在并发执行时不会因为由于交叉执行而导致数据的不一致即一致性需要隔离性来保证其实一致性和用户的业务逻辑强相关一般MySQL提供技术支持但是一致性还是要用户业务逻辑做支撑也就是说一致性是由用户决定的在技术上一致性需要原子性、持久性和隔离性来保证
--------------------- END ----------------------
「 作者 」 枫叶先生
「 更新 」 2023.9.7
「 声明 」 余之才疏学浅故所撰文疏漏难免或有谬误或不准确之处敬请读者批评指正。
