MySQL(事物上)
目录
示例:
一 引入事物
1. 概念
2. 事物的4大特性
3. 为什么要有事物?
二 事物操作
1. 查看存储引擎支持的事物
2. 事物的提交方式
2.1 查看事物的默认提交方式
2.2 设置事物的默认提交方式
2.3 查看事物的全局隔离级别
2.4 验证事物的回滚性(在事物执行过程中进行回滚)
1. 启动事物/手动提交事务
2. 设置保存点/回滚到保存点/回滚到最开始状态
总结:
三 事物的隔离性
1. 为什么要有隔离性?
2. 隔离级别
1. 查看隔离级别/修改隔离级别
2. 验证读未提交
3. 验证读提交
4. 验证可重复读
5. 验证串行化
总结:
示例:
假设增删查改语句不加控制会有什么问题?
假设票数还有一张,线程A判断成功进入执行SQL语句(一组SQL语句/单SQL语句)被切走,票数没变,B线程判断也成功执行SQL语句票数--,A回来继续执行SQL语句,票数就变成-1了,显然是不合理的,在这些CURD操作不加控制的情况下有问题。
那么增删查改需要满足什么条件才能解决多线程并发带来的问题呢?
1. 上述买票的过程要是原子的吧?也就是要么买完,要么就不买,不存在中间过程,如果中间出问题,就回溯到最开始的状态。
2. 多个线程买票之间不能互相影响吧?比如上述的买票买成负数的情况。
3. 买票完成之后的数据应该是永久有效的吧?不能买到一半或者买完,比如服务崩溃了,网络出问题了,导致数据没更新。
4. 买票前和买票后应该有确定的状态吧?不能买完票之后的状态还是买票前的状态或者是其他的状态。
一 引入事物
1. 概念
事物就是一组或单个DML(增删查改操作)语句的组合,他们之间的关系紧密,比如买票,要进行--票数,然后把买票的人的状态改成已买状态,虽然在程序员看来是2组SQL语句,但他们是有关系的,也就是组合起来有某种意义,这些组合起来的语句看成一个整体,要么全部执行成功,要么全部执行失败,不应该有中间的状态,也就是要是原子的,如果有多条事物并发运行,不能一个事物查询到的结果每次都不一样吧?他们互相之间不能被影响,或者一个事物执行到一半由于某种原因不能向后执行,应该回到最开始的状态,事物结束影响到的数据应该是永久有效的吧,不应该存在执行完了,数据不变等影响,基于这些情况MySQL给事物定义了4大特性。
2. 事物的4大特性
- 原子性:事物要么执行全部完,要么不执行,不应该存在中间状态,如果出现错误或其他问题,那么就应该回到最开始的状态,也就是回滚操作。
- 持久性:事物处理后的结果应该永久有效,不存在数据丢失或处理之后的结果无效。
- 一致性:从一个合法的状态转换到另一个合法的状态,如果转换中有问题,应该初始状态,不能出现不确定的状态(原子性),转换后的状态要是永久有效的((持久性)),不能转换后的状态跟没转换的状态是一样的,换句话说就是转换后的状态要是符合预期的,多个事物读写并发互相转换可能会互相影响,从而造成的结果不符合预期等(隔离性),站在应用层角度来看,假设用户乱操作造成的状态也是不符合预期的,比如转账逻辑出现问题,买票买成负数等问题,所以一致性是由其他3个特性和应用层共同维护的。
- 隔离性:事物之间不能相互影响,也就是让他们隔离起来,事物之间的到来有先后顺序,哪些是应该看的数据,哪些是不应该看到的数据。
上面四个属性,可以简称为 ACID 。原子性( A tomicity ,或称不可分割性)一致性( C onsistency )隔离性( I solation ,又称独立性)持久性( D urability )
3. 为什么要有事物?
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务 。
二 事物操作
1. 查看存储引擎支持的事物
show engines;
show variables like 'autocommit';
只有InnoDB存储引擎支持事物。
2. 事物的提交方式
- 自动提交
- 默认提交
2.1 查看事物的默认提交方式
show variables like 'autocommit';
2.2 设置事物的默认提交方式
set autocommit=0/1;
2.3 查看事物的全局隔离级别
//查看MySQL版本
select version();// 5.0以上版本
select @@tx_isolation;// 8.0以上版本
select @@transaction_isolation;
需要重启客户端生效。
2.4 验证事物的回滚性(在事物执行过程中进行回滚)
需要设置默认隔离级别为最低隔离级别测试
set global transaction isolation level read uncommitted;
// 创建测试表
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
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
1. 启动事物/手动提交事务
// 第一种
start transaction;// 第二种
begin;// 手动提交事物
commit;
2. 设置保存点/回滚到保存点/回滚到最开始状态
// 设置保存点
savepoint 保存点名称;// 回到保存点
rollback to 保存点名称;// 回到初始状态
rollback;
首先启动2个事物,验证回滚。
验证提交后的结果
验证异常的结果
当MySQL事物执行中异常,则会自动回滚到最开始的状态。
全局的默认提交的开启和关闭对单SQL的影响,也就是不手动启动事物,只执行普通的SQL语句。
当全局的默认提交为0,当普通事物被执行,然后异常,也会被回滚到最开始的状态,换句话说普通的单SQL也是事物,如果全局的默认提交为0,则执行只会不会提交,直到手动commit,否则异常自动回滚,如果全局的默认提交为1,执行当SQL会自动提交。
总结:
- 只要输入 begin/start transaction ,事物就需要手动commit,与是否是自动提交没有关系
- 执行事物的时候异常,MySQL会自动回滚到最开始的状态
- 普通的单条SQL也是事物,如果全局提交为0,则需要手动commit,为1执行完自动提交, select 除外
- 上述的操作验证了原子性(回滚),持久性(手动提交(commit)/自动提交(set auto commit=1)) 数据永久有效
事物使用注意事项:
- 事物执行中可以回滚,可以回滚(rollback to 保存点名 / rollback回到最开始状态)到保存点(savepoint 名称)/没有则回滚到最开始状态,如果commit回滚无效
- 只有Innodb支持事物
那么隔离性?一致性体现在哪?
三 事物的隔离性
1. 为什么要有隔离性?
假设你正在学习,在学习的过程中可能受到影响,影响的程度不同,或者没收到影响,对你最终的结果造成影响,就好比事物在执行过程中应不应该被其他事物影响到?受什么程度的影响?事物的到来总会有先后顺序,哪些能看到前面先到的事物的操作?哪些能看到正在执行的?哪些能看到提交之后的?如果这些不加以控制,事物在执行过程中必定会受到影响。MYSQLD是网络服务,势必会被多个客户端,比如命令行,语言,图形化界面版的MYSQL客户端进行访问,这么多的客户端打包执行的事物也必须加以控制,所以有了隔离性的概念。
2. 隔离级别
- 在并发读写的情况下:
- READ UNCOMMITTED(读未提交):事物在执行中可以看到其他事物的操作结果
- READ COMMITTED(读提交):事物在执行中可以看到其他事物的提交后的操作结果
- REPEATABLE READ(可重复读):事物在执行中看不到其他事物的操作结果,即使是提交后
- SERIALIZABLE(串行化):所有事物必须串行执行,先来的先执行
1. 为什么上述都跟读有关?
因为在MySQL的大多数场景下都跟读有关,而写写操作 事物之间必定会互相影响,必定会保护串行跑,读读都不修改数据(不考虑),读写,虽然读不会修改数据,但读可能会读到其他事物修改后的数据,可能读到垃圾数据等问题,不同的隔离级别对读的影响程度不同。
上述图表示不同隔离级别带来的效率和安全的影响。
1. 查看隔离级别/修改隔离级别
// 查看全局隔离级别/当前会话隔离级别// 5.0以上版本
select @@session/global.tx_isolation;
// 8.0Y以上版本
select @@session/global.transaction_isolation;// 设置全局/当前会话隔离级别
set global/session transaction isolation level READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ/SERIALIZABLE
当登录客户端当前的会话隔离级别会默认继承全局隔离级别,修改全局隔离级别需重新启动客户端。
2. 验证读未提交
当多个事物并发读写,读事物读到另一个事物的修改,称为脏读,因为写事物相对于读事物是原子的,读事物要么读到写事物一开始的状态要么是结束的状态,读到中间状态是不合理的,也就是读到写事物未提交的结果。
3. 验证读提交
当读事物重复读取相同的内容,可能会读到不同的结果,也就是读到写事物提交后的结果,那么这种隔离级别下有问题吗?
假设表有工资字段:1000~2000,2001~3000,要筛出这2个区间的人,假设1000~2000有个人是张三,正准备读取2001~3000的时候,写事物把张三的薪资加到2001~3000这个范围为内,并且提交,读事物然后在读取2001~3000内的人,就会又读到张三,显然是有问题的,也就是相同的读取读到同一个数据可能不一样,也就是不可重复读。
4. 验证可重复读
读事物不会读到写事物提交后的结果或者执行的结果,也就是可重复读取同样的数据。
5. 验证串行化
每个事物必须串行的执行,一旦有事物进行增删改,就会锁住,等另一个事物结束才能继续执行,读读例外,也就是每个事物必须按照先后到来顺序进行排队。
- 脏读:读事物读到写事物修改并未提交的数据
- 不可重复读:读事物读到写事物提交后的数据
- 幻读:因为不同的隔离级别是通过加锁完成的,但插入数据,这个数据之前是没有的,但上述的MySQL可重复读是解决了幻读问题,如果可重复读出现了幻读问题,也可以归类与不可重复读问题
总结:
1. 隔离级别越高安全越好,多事物并发效率越低,隔离级别越低安全越低,多事物并发效率越高。
2. 不可重复读重点在于修改和删除,同样的查询读到的结果不一样,幻读重点在于新增。
3. 上面的例子可以看出来不同的隔离级别相互之间的影响,特别是长或短事物需要谨慎选择合适的隔离级别,MySQL默认的隔离级别(可重复读)。
隔离级别 脏读 不可重复读 幻读 加锁读
- 读未提交 √ √ √ ×
- 读提交 × √ √ ×
- 可重复读 × × × ×
- 串行化 × × × √