# 什么是事务 开启一个事务可以包含多条sql语句,这样sql语句要么同时成功,或者同时失败,成为事务的原子性 # 作用 保证了数据操作的安全性 你在操作多条数据的时候可能会出现某几条操作不成功的情况 一组数据操作执行步骤,这些步骤被视为一个工作单元: 1)用于对多个语句进行分组 2)可以在多个客户机并发访问同一个表中的数据时使用 所有步骤都成功或都失败 1)如果所有步骤正常,则执行 2)如果步骤出现错误或不完整,则取消
# 事务相关的关键字 # 1、开启事务 start transaction # 2、回滚(回到事务执行之前的状态) rollback # 3、确认(确认之后就无法回滚了) commit # 总结: 当你想让sql语句同时保证数据的一致性,要么同时成功,要么同时失败,那么就可以考虑使用事务
#1.创建一个表 mysql> create table jiaoyi(id int primary key auto_increment,name varchar(10),money int); #2.插入两条数据 mysql> insert into jiaoyi values(1,'qiudao',100); Query OK, 1 row affected (0.01 sec) mysql> insert into jiaoyi values(2,'oldboy',200); Query OK, 1 row affected (0.00 sec) #3.再打开一个窗口 mysql> select * from jiaoyi; 数据与原窗口查看结果一致 #4.开启一个事务(符合逻辑) mysql> start transaction; #开启事务 # begin 也是开启事务 mysql> begin; mysql> update jiaoyi set money=0 where name='qiudao'; mysql> select * from jiaoyi; mysql> update jiaoyi set money=400 where name='oldboy'; mysql> select * from jiaoyi; #在执行commit之前,另一个窗口是看不到数据变化的 mysql> commit; #执行commit之后其他人都能看到数据的变化 #5.开启一个事务(不符合逻辑) mysql> start transaction; mysql> update jiaoyi set money=-100 where name='qiudao'; mysql> select * from jiaoyi; +------+--------+-------+ | id | name | money | +------+--------+-------+ | 1 | qiudao | -100 | | 2 | oldboy | 400 | +------+--------+-------+ 2 rows in set (0.00 sec) # 6、由于钱的值不能为负数,由逻辑判断,操作失败,回滚 mysql> rollback; #回滚事务 mysql> select * from jiaoyi; +------+--------+-------+ | id | name | money | +------+--------+-------+ | 1 | qiudao | 0 | | 2 | oldboy | 400 | +------+--------+-------+ 2 rows in set (0.00 sec)
#伴随着“交易”出现的数据库概念。 我们理解的“交易”是什么? 1)物与物的交换(古代) 2)货币现金与实物的交换(现代1) 3)虚拟货币与实物的交换(现代2) 4)虚拟货币与虚拟实物交换(现代3) 数据库中的“交易”是什么? 1)事务又是如何保证“交易”的“和谐”? 2)ACID
start transaction; #begin 开启事务 sql1 #真正事务处理的时候,是第一条sql语句执行完,才是开启事务的 sql2 sql3 ... commit; # 若用了begin手动开始编辑事务,编辑完后只要commit手动提交,另一端则能查询到数据
start transaction; sql1 sql2 sql3 ... rollback; # 若用了begin手动开始编辑事务,编辑完后且只要不commit手动提交,另一端则查询不到数据
ACID 四大特性 #ACID # A: 原子性 (Atomic) 一个事务是一个不可分割的单位,事务中包含的诸多操作,要么同时失败,要么同时成功 所有语句作为一个单元全部成功执行或全部取消。 # C:一致性 (Consistent) 事务必须是是数据库一致性的状态变成另外一个一致性的状态,一致性根原子性密切相关的, 如果数据库在事务开始时处于一致状态,则在执行该事务期间将保留一致状态。 # I:隔离性 (Isolated) 一个事务的执行不能被其他事务干扰,事务之间不相互影响。 # D:持久性 (Durable) 也可以称为永久性,一个事务一旦提交成功执行成功,那么它就是对数据库中的数据修改是永久的 接下来的其他操作或者故障不应该对其任何的影响 事务成功完成后,所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失。 我们可以分析一下,事务的四大特征中,所有的操作都会走向磁盘,所以持久性是事务操作的目的,而原子性是实现事务的基础,隔离性是实现数据安全的一种策略、手段,而最终维护的,就是数据的一致性,一致性才是事务中最重要的。四大特征之间,隔离性是为了达到一致性的手段。 ACID四大特征中,最难理解的不是一致性,而是事务的隔离性,数据库权威专家针对事务的隔离性研究出来了事务的隔离四种级别,四种事务隔离级别就是为了解决数据在高并发下产生的问题(脏读、不可重复读、幻读)。
# 0.手动提交 mysql> commit; #1.查看自动提交 mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.01 sec) #2.临时关闭 set autocommit =0; #3.永久关闭 [root@db01 ~]# vim /etc/my.cnf [mysqld] autocommit=0
1)现在版本在开启事务时,不需要手工start transaction;,只要你输入的是DML语句,就会自动开启事务。 2)有些情况下事务会被隐式提交 # 隐式提交触发条件 1.执行事务没有commit时,如果使用了DDL或者DCL会自动提交上一条事务 2.执行事务没有commit时,如果你手动执行begin,会自动提交上一条事务 3.执行事务没有commit时,如果执行锁表(lock tables)或者解锁(unlock tables),会自动提交上一条事务 4.load data infile(导数据)会自动提交上一条事务 5.select for update 6.在autocommit=1的时候,会自动提交上一条事务 update .... commit; insert into .... begin; update .... create table .... delete ....
begin(或 start transaction):显式开始一个新事务,推荐begin #开启事务 savepoint:分配事务过程中的一个位置,以供将来引用 #临时存档 commit:永久记录当前事务所做的更改 #提交事务 rollback:取消当前事务所做的更改 #回滚 roolback to savepoint:取消在 savepoint 之后执行的更改 #回到存档点 release savepoint:删除 savepoint 标识符 #删除临时存档 set autocommit:为当前连接禁用或启用默认 autocommit 模式 #临时开关自动提交 PS:永久开启或关闭autocommit,则在配置文件(my.cnf)插入一行: autocommit=1 # 开启状态 autocommit=0 # 关闭状态
事务并发带来的问题 前面讲到了事务的隔离性,如果要提升系统的吞吐量,当有多个任务需要处理时,应当让多个事务同时执行,这就是事务的并发。既然事务存在并发执行,那必然产生同一个数据操作时的冲突问题,来看一下都会出现什么问题。 # 1.更新丢失 Lost Update,当两个事务更新同一行数据时,双方都不知道对方的存在,就有可能覆盖对方的修改。比如两个人同时编辑一个文档,最后一个改完的人总会覆盖掉前面那个人的改动。 # 2.脏读 --》原因 -- 设置安全级别低导致的 Dirty Reads,一个事务在执行时修改了某条数据,另一个事务正好也读取了这条数据,并基于这条数据做了其他操作,因为前一个事务还没提交,如果基于修改后的数据进一步处理,就会产生无法挽回的损失。 # 3.不可重复读 Non-Repeatable Reads,同样是两个事务在操作同一数据,如果在事务开始时读了某数据,这时候另一个事务修改了这条数据,等事务再去读这条数据的时候发现已经变了,这就是没办法重复读一条数据。 # 4.幻读 Phantom Read,与上方场景相同,事务一开始按某个查询条件没查出任何数据,结果因为另一个事务的影响,再去查时却查到了数据,这种就像产生幻觉了一样,被称作幻读。 # 1、当设置事务的安全隔离级别低的的时候,相应的安全级别低,处理效率就比较高 # 2、当设置事务的安全隔离级别高的的时候,相应的安全级别高,处理效率就比较低
首先,更新丢失这种问题应该是由应用层来解决的,因为数据库没有办法控制用户不去更新某条数据。但是另外三个问题是可以得到解决的,既然有方案解决解决它不就好了,干嘛还要设置这么多隔离级别呢? 刚才说了,如果我们要性能好、吞吐量提升,那就不得不付出一些代价,如果要做到完全没有副作用,那么就只需要让事务排队执行就好了,一个一个执行绝对不会出现脏读幻读的问题,但是这样会导致数据库处理的非常慢。那怎么办呢?官方唯一能做的就是给你提供各种级别的处理方式,由你根据具体业务场景选择,于是就有了隔离级别。 # 1、读未提交 Read uncommitted 读未提交其实就是事务没提交就可以读,很显然这种隔离级别会导致读到别的还没提交的数据,一旦基于读到的数据做了进一步处理,而另一个事务最终回滚了操作,那么数据就会错乱,而且很难追踪。总的来说说,读未提交级别会导致脏读。 # 2、读提交 Read committed 顾名思义就是事务提交后才能读,假设你拿着银行卡去消费,付钱之前你看到卡里有2000元,这个时候你老婆在淘宝购物,赶在你前面完成了支付,这个时候你再支付的时候就提示余额不足,但是分明你看到卡里的钱是够的啊。 这就是两个事务在执行时,事务A一开始读取了卡里有2000元,这个时候事务B把卡里的钱花完了,事务A最终再确认余额的时候发现卡里已经没有钱了。很显然,读提交能解决脏读问题,但是解决不了不可重复读。 Sql Server,Oracle的默认隔离级别是Read committed。 # 3、可重复读 Repeatable read 看名字就看出来了,它的出现就是为了解决不可重复读问题,事务A一旦开始执行,无论事务B怎么改数据,事务A永远读到的就是它刚开始读的值。那么问题就来了,假设事务B把id为1的数据改成了2,事务A并不知道id发生了变化,当事务A新增数据的时候却发现为2的id已经存在了,这就是幻读。 MySQL的默认隔离级别就是Repeatable read。 # 4、串行化 serializable 这个就是最无敌的存在了,所有的事务串起来一个个执行,因为没有并发的场景出现了,什么幻读、脏读、不可重复读统统都不存在的。但是同样的,基本并发能力会非常差。最终,到底什么隔离级别完全要根据自己的业务场景选择,没有最好的,只有最适合的。 事务隔离级别越严格,越消耗计算机性能,效率也越低,通常情况下,设置为允许不可重复读就可以解决大多数的问题了。
脏读:比如有两个事务并行执行操作同一条数据库记录,A事务能读取到B事务未提交的数据。
# 临时有效 mysql数据库事务开关 开启自动提交事务:set autocommit = 1; 关闭自动提交事务:set autocommit = 0 ; 查看事务开关:show variables like '%autocommit%';
# 全局的 ---此处演示我们设置全局的 mysql> set global transaction isolation level read uncommitted; mysql> show variables like '%iso%'; +-----------------------+------------------+ | Variable_name | Value | +-----------------------+------------------+ | transaction_isolation | READ-UNCOMMITTED | | tx_isolation | READ-UNCOMMITTED | # 退出重新进入 # 当前会话 set session transaction isolation level read uncommitted;
# 1、关闭自动提交事务 set autocommit = 0; # 2、查询指定用户 select * from jiaoyi where id=1; # 3、修改指定用户余额 update jiaoyi set money = 500;
---------- 窗口B ------------------------------------------ # 1、关闭自动提交事务 set autocommit = 0; # 2、查询指定用户 select * from jiaoyi where id=1; +------+--------+-------+ | id | name | money | +------+--------+-------+ | 1 | qiudao | 500 | +------+--------+-------+ 1 row in set (0.00 sec) 很显然,事务A修改了数据后还没有提交事务,此时事务B可以读取到事务A没有提交的事务的数据。这就是脏读,脏读情况下的数据是不可取的,所以一般没有数据库事务级别设置为允许脏读。 # 总结:脏读就是指事务A读取到事务B修改但未提交事务的数据。 (事务B未执行commit的时候,但是事务A却读取到了)
学习完脏读后,我们再来看看什么是不可重复读。比如事务A在同一事务中多次读取同一记录,此时事务B修改了事务A正在读的数据并且提交了事务,但是事务A读取到了事务B所提交的数据,导致两次读取数据不一致。
# 全局的 ---此处演示我们设置全局的 set global transaction isolation level read committed; # 当前会话 set session transaction isolation level read committed;
---------- 窗口A ------------------------------------------ # 1、关闭自动提交事务 set autocommit = 0; # 2、查询指定用户 select * from jiaoyi where id = 1; 此时窗口2执行事务 # 1、查询指定用户 select * from jiaoyi where id = 1; # 2、提交事务 commit;
窗口1先是开启事务,查询指定用户信息,然后窗口2开启事务,查询数据指定用户,修改数据,提交事务,然后再回到窗口1,查询指定用户信息;窗口2操作内容如下
# 1、关闭自动提交事务 set autocommit = 0; # 2、查询指定用户 select * from jiaoyi where id = 1; # 3、修改指定用户余额 update money set money = 300 where id=1; # 4、提交事务 commit; 事务A在两次查询中,查询的数据不一样,这就是不可重复读。Mysql默认采用的就是不可重复读的隔离级别,用一句话总结,不可重复读就是事务A读取到事务B已提交事务的数据,导致两次读取数据信息不一致。
上面我我们学习了一下什么不可重复读,在mysql数据库中,不可重复读是不被允许的。
# 全局的 ---此处演示我们设置全局的 set global transaction isolation level repeatable read; # 当前会话 set session transaction isolation level repeatable read;
# 1、关闭自动提交事务 set autocommit = 0; select * from jiaoyi where id = 5; # 此时窗口1未查询到id为5的数据,正准备进行插入时,窗口2插入了一条id为5的数据。 # 2、没有查询到结果,进行插入 insert into jiaoyi values (5, 'cm',200); # 3、再查询id为5的 select * from money where id = 5; commit;
# 1、关闭自动提交事务 set autocommit = 0; # 2、插入一条记录 insert into jiaoyi values (5,'cm',200); # 3、提交事务 commit; 在上述事务A中,不提交事务的情况下,插入id为5的记录会一直报错主键冲突,但是再怎么查询id为5的记录都查询不到;这是因为在MySql的设计中,事务中查询的是被修改前的日志。即Undo log。
#查看隔离级别 mysql> show variables like '%iso%'; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | tx_isolation | READ-UNCOMMITTED | +---------------+------------------+ 1 row in set (0.00 sec) #配置隔离级别 [root@db01 ~]# vim /etc/my.cnf [mysqld] transaction_isolation=read-uncommit
redo,顾名思义“重做日志”,是事务日志的一种。
# 1)作用 在事务ACID过程中,实现的是“ D ”持久化的作用。 REDO:记录的是,内存数据页的变化过程 特性:WAL(Write Ahead Log)日志优先写 # 2)REDO工作过程 执行步骤: update t1 set num=2 where num=1; 1)首先将t1表中num=1的行所在数据页加载到内存中buffer page 2)MySQL实例在内存中将num=1的数据页改成num=2 3)num=1变成num=2的变化过程会记录到,redo内存区域,也就是redo buffer page中 提交事务执行步骤: commit; 1)当敲下commit命令的瞬间,MySQL会将redo buffer page写入磁盘区域redo log 2)当写入成功之后,commit返回ok
undo,顾名思义“回滚日志”,是事务日志的一种。 在事务ACID过程中,实现的是“A”原子性的作用。当然CI的特性也和undo有关 # PS: undo buffer cache 会实时的将数据写入磁盘,也就是 是否执行了 commit 事务id:txid 日志版本号:lsn 物理备份时可以看到
# 锁,顾名思义就是锁定的意思,修改数据时锁住数据 # 在事务ACID特性过程中,“锁”和“隔离级别”一起来实现“I”隔离性的作用。
数据 id=1 事务1 set id=2 事务2 set id=3 #1.创建一个表 create table test(id int); #2.插入数据 insert into test values(1); #3.开启两个窗口开启事务 begin; update test set id=2 where id=1; begin; update test set id=3 where id=1;
排他锁:保证在多事务操作时,数据的一致性。(在我修改数据时,其他人不得修改) 共享锁:保证在多事务工作期间,数据查询时不会被阻塞。 乐观锁:多事务操作时,数据可以被同时修改,谁先提交,谁修改成功。 悲观锁:多事务操作时,数据只有一个人可以修改。
1)只阻塞修改类操作(排它锁),不阻塞查询类操作(共享锁) 2)乐观锁的机制(谁先提交谁为准)
MyISAM:表级锁 InnoDB:行级锁
1)四种隔离级别: 1.rc: read committed 允许事务查看其他事务所进行的已提交更改 2.ru: read uncommitted(独立提交),未提交读,允许事务查看其他事务所进行的未提交更改; 3.rr: repeatable read 可重复读 InnoDB 的默认级别 #commit提交以后可能看不到数据变化,必须重新连接 4.serializable:串行化:,将一个事务的结果与其他事务完全隔离 #如果一个事务没有提交,查询也不能查了 # 我改微信头像时你不能看我的信息,我看你朋友圈的时候你不能发朋友圈、不能看朋友圈 2)修改隔离级别 #查看隔离级别 mysql> show variables like '%iso%'; #修改隔离级别为RU [mysqld] transaction_isolation=read-uncommit mysql> use oldboy mysql> select * from stu; mysql> insert into stu(id,name,sex,money) values(2,'li4','f',123); #修改隔离级别为RC [mysqld] transaction_isolation=read-commit
1.脏读:(RU级别会发生) 一个事务执行了,去没有提交,被其他人读取到了值,可是事务回滚了,那刚被独到的数据被称为脏数据 2.幻读:(RR级别可以解决这个问题) # 可理解为:操作端的当前状态 事务将数据库中所有数据都删除的时候,但是事务B就在这个时候新插入了一条记录,当事务A删除结束后发现还有一条数据,就好像发生了幻觉一样。这就叫幻读。 # 说白了,就是当前用户的当前数据还没刷新到最新,或者刷新了一部分,且有残留没刷新完全,导致数据的不一致 3.不可重复读: # 被操作端的当前状态 执行一个事务读取两次数据,在第一次读取某一条数据后,有一个事务修改了读到的数据并提交,第一个事务再次读取该数据,两次读取便得到了不同的结果。 # 说白了,就是当前用户的当前数据还没刷新到最新,导致数据的不一致