SQL从入门到放弃系列——事务[十二]

2019年7月18日08:57:22 发表评论 63
摘要

1.什么是事务特性?
2.如何控制事务?
3.为什么有些commit和rollback会失败?
4.事务并发处理的可能异常有哪些?
5.针对可能存在的异常,四种事务的隔离级别分别是什么?

什么是事务

事务transaction,将多个数据库读或写的操作组成一个基本单元,要么完全执行,要么都不执行。

事务特性(ACID)

ACID是事务四大特性,原子性是基础,隔离性是手段,一致性是约束条件,持久性是目的。

A 原子性 Atomicity

数据处理操作的最基本单位。

C 一致性 Consistency

事务被提交或回滚后,数据库的完整性约束不被破坏。也就是说,任何写入数据库的数据都需要满足事先定义的约束规则。

I 隔离性 Isolation

每个事务彼此独立,不受其他事务执行的影响。即一个事务在提交之前,对其他事务不可见。

D 持久性 Durability

事务提交后对数据的修改是持久性的,即使系统故障情况下,数据修改依然有效。因为当事务完成,数据库的日志会被更新,此时可以通过日志,让系统恢复到最后一次成功的更新状态。

持久性是通过事务日志来保证的,日志包括回滚日志和重做日志。当通过事务对数据修改的时候,首先会将数据库的变化信息记录到重做日志中,然后在对数据库进行修改。所以即使数据库系统崩溃,数据库重启后也能找到没有更新到数据库系统中的重做日志,重新执行,从而使事务具有持久性。

事务的控制

Oracle支持事务,MySQL中InNoDB存储引擎支持事务,MyISAM存储引擎不支持,这些可以通过show engines命令查看MySQL中哪些存储引擎支持哪些不支持事务。

常用事务控制语句

  • 显式开启事务

start transaction或begin

  • 提交事务

commit,事务提交后,对数据库修改是永久性的。

  • 回滚事务

rollback或rollback to [savepoint],撤销正在进行的所有没有提交的修改,或者将事务回滚到某个保存点。

  • 创建保存点

savepoint,在事务中创建保存点,方便回滚,一个事务中可以有多个保存点。

  • 删除保存点

release savepoint,删除某个保存点。

  • 设置事务隔离级别

set transaction,设置事务的隔离级别。

 

显式事务和隐式事务

隐式事务就是自动提交,Oracle默认不自动提交,需要手写commit命令,MySQL默认自动提交,但是可以配置MySQL的参数:

mysql> set autocommit =0;  // 关闭自动提交
mysql> set autocommit =1;  // 开启自动提交
示例1
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
BEGIN;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;

结果是一条关羽。

说明:在这个事务中,整个SQL一共执行了2个事务,第一个是插入关羽,提交后执行成功,第二个事务是插入两次张飞,注意的是,name字段为主键,主键的值是唯一的,那么第二次张飞插入或产生错误,然后执行rollback相当于对事务进行了回滚,所以结果只有一条数据关羽。

示例2
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;

结果是两条,关羽和张飞。

说明:这两条张飞不在同一个事务中,对MySQL来说这是默认两个事务,在autocommit=1的情况下,MySQL会进行隐式事务,即自动提交,因此进行第一次插入张飞后,数据库里存在了两行数据,第二次插入张飞后就会报错:1062-duplicate entry '张飞' for key 'primary'。

即使rollback,实际上事务已经自动提交了,无法回滚了。

示例3
CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
SET @@completion_type = 1;
BEGIN;
INSERT INTO test SELECT '关羽';
COMMIT;
INSERT INTO test SELECT '张飞';
INSERT INTO test SELECT '张飞';
ROLLBACK;
SELECT * FROM test;

结果只有一条关羽。

说明:SQL中,在事务开始之前设置了set @@completion_type=1;结果和示例1一样。这里说一下completion_type参数的作用:

1.completion_type=0,默认为0。意思是执行commit时提交事务,在执行下一个事务时,需要手动开启。

2.completion_type=1,意思是当提交事务后,相当于执行了commit and chain,也就是开启一个链式事务,即当提交事务后开启一个相同隔离级别的事务。

3.completion_type=2,这种情况下commit=commit and release,即当提交事务后,会自动断开与服务器的连接。

所以上述示例3的第二个事务进行了回滚后结果只有一条关羽。

提醒:如果显式开启事务,那么同时需要commit才能生效,在rollback时才会回滚。

事务并发处理的三种可能异常

脏读

读到了其他事务还未提交的数据。

不可重复读

对某数据进行读取,发现两次读取的结果不同,即没有读到相同的内容。这是因为有其他事务对这个数据进行了修改或删除。

幻读

事务a根据条件查询得到了n条数据,但此时事务b更改或增加了m条符合事务a查询条件的数据,这样当事务a再次进行查询的时候发现会有n+m条数据,产生了幻读。

隔离级别从低到高:

隔离级别 脏读 不可重复读 幻读
读未提交
读已提交 ×
可重复读 × ×
可串行化 × × ×

可串行化,将事务进行串行化,也就是一个队列中按照顺序执行,是最高级别的隔离等级,可以解决事务读取中所有可能出现的异常情况,但是牺牲了系统的并发性。

MySQL默认的隔离级别就是可重复读。

 

总结

隔离级别越低,系统吞吐量即并发程度越大,但同时意味着出现异常问题的可能性会更大。在实际使用过程中,往往需要在性能和正确性上进行权衡和取舍,没有完美的解决方案,只有适合与否。

思考

不可重复读和幻读区别

不可重复读和幻读都是在先后两次读取的时候发现不一致的情况,但是两种读取略有差别。不可重复读是对于同一条记录内容的“不可重复读”,幻读是对于某一范围的数据集,发现查询数据集的行数多了或者少了,从而出现的不一致。所以不可重复读的原因是:对于要查询的那条数据进行了UPDATE或DELETE,而幻读是对于要查询的那个范围的数据集,进行了INSERT。

(本文完)

  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin
  • A+
所属分类:SQL

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: