事务
MySQL事务
1.事务简介
(1)在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
(2)事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
(3)事务用来管理 insert,update,delete 语句。
2.事务四大特征
一般来说，事务是必须满足4个条件（ACID）：
（1）原子性（Atomicity，或称不可分割性）
一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
（2）一致性（Consistency）
在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。(比如：A向B转账，不可能A扣了钱，B却没有收到)
（3）隔离性（Isolation，又称独立性）
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。（比u人：A正在从一张银行卡里面取钱，在A取钱的过程中，B不能向这张银行卡打钱）
（4）持久性（Durability）
事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
3.事务提交、回滚
-- UNSIGNED代表无符号数，不能是负数
create table user(
id int primary key auto_increment,
name VARCHAR(20),
balance DECIMAL(10,2) UNSIGNED
);
insert into user VALUES (1,'楠哥',200);
insert into user VALUES (2,'楠哥老婆',50000);
-- 转账业务，必须都成功，或者都失败，所以不能一句一句执行，万一执行了一半，断电了咋办
-- 所以要编程一个整体
-- 都成功
-- begin;
start transaction;
UPDATE user set balance = balance - 200 where id = 1;
UPDATE user set balance = balance + 200 where id = 2;
commit;
-- 都失败
start transaction;
UPDATE user set balance = balance - 200 where id = 1;
UPDATE user set balance = balance + 200 where id = 2;
rollback;
3.1、实现的原理简单介绍
mysql每执行一条语句记录一条日志，
1、start transaction，先记个日志，真正执行执行。
2、UPDATE user set balance = balance - 200 where id = 1，先记个日志，真正执行。
2.1如果此时断电了，当然不能继续执行了，过了一会来电了，启动mysql会检查日志，发现有个事务没有执行完毕，没有commit，就会安装反向的操作把他回滚了。
3、UPDATE user set balance = balance + 200 where id = 2，先记个日志，真正执行。
4、如commit，记个记录，执行，结束了，日志就能删除了。如果rollback，就会按照日志反向操作，回滚。
4.事务特性--隔离性
隔离强调的是两个或两个以上同时发生（并发）的业务同时操作一个数据库，为了让两个事务一方面能都看到、得到正确的结果，一方面还要保证一定的效率而产生的不同的隔离级别。
4.1 隔离性有隔离级别(4个)
（1）读未提交：read uncommitted
（2）读已提交：read committed
（3）可重复读：repeatable read
（4）串行化：serializable
| 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|
| Read uncommitted | √ | √ | √ |
| Read committed | × | √ | √ |
| Repeatable read | × | × | √ |
| Serializable | × | × | × |
1.脏读：（读取未提交数据）
2.不可重复读:（前后多次读取，数据内容不一致）
3.幻读:（前后多次读取，数据总量不一致）
两个并发事务
2、提交了才能读
强调的是，读的都是已经提交过的数据，强调的是单行数据的修改。
对于另一个事务而言，提交前后数据发生了变化，另一个事务中感觉能做的事情突然不能做了。
两个事务，一个先读了，读的是旧数据，应为对方虽然转账了，但是没有提交啊！
但是另一个先提交了，成了新数据，第一个事务在处理一些事情就废了。
一个加了id为11的学生，另一个也是，
3、可重复读，但是不能修改
锁了行
和第二个有点像 针对的是 插入和删除
只要你不提交读的数据都是旧的，可以重复读，都是旧的
一个事务一旦开始，其他事务就不能做修改操作。
第一个事务先开始，去修改数据。
另一个事务读，读出旧数据，
第一个事务提交。
另一个事务在读，发现读出来的不一样，就可幻觉一样，就是幻读。
4、串行化
锁了表
一个事务来了，其他的事物都靠边站，只要我不提交，别的就别想开始。
查看个设置事务的隔离级别：
SELECT @@global.tx_isolation, @@tx_isolation;
set session transaction isolation level repeatable read;
SET transaction isolation level read uncommitted;
SET transaction isolation level read committed;
set transaction isolation level repeatable read;
SET transaction isolation level serializable;
SET GLOBAL transaction isolation level read uncommitted;
SET GLOBAL transaction isolation level read committed;
set GLOBAL transaction isolation level repeatable read;
SET GLOBAL transaction isolation level serializable;
其中，SESSION 和 GLOBAL 关键字用来指定修改的事务隔离级别的范围：
SESSION：表示修改的事务隔离级别将应用于当前 session（当前 cmd 窗口）内的所有事务；
GLOBAL：表示修改的事务隔离级别将应用于所有 session（全局）中的所有事务，且当前已经存在的 session 不受影响；
如果省略 SESSION 和 GLOBAL，表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。
4.2 读未提交
- 事物A和事物B，事物A未提交的数据，事物B可以读取到
- 这里读取到的数据叫做“脏数据”，叫脏读
- 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别
简而言之第一个事务没提交，别的事物就能读，这种数据不一定是正确的因为人家可能回滚呀！
案例：
楠哥发工资了，老婆让楠哥把工资打到他老婆的账号上，但是该事务并未提交，就让老婆去查看，老婆一看真的打了钱了，高高兴兴关了网页，此时楠哥急中生智进行回滚，钱瞬间回来，一次蒙混了一个月工资。所以楠哥老婆看到的数据我们称之为“脏数据”。
必须开两个事务
use test;
SET transaction isolation level read uncommitted;
1-楠哥，转账
start transaction;
UPDATE user set balance = balance - 10000 where id = 1;
UPDATE user set balance = balance + 10000 where id = 2;
2-楠哥老婆，查账，不错，钱已到账
start transaction;
select * from user where id = 2;
commit;
3-楠哥,回马枪，回滚
rollback;
4-楠哥老婆某天查账，哎，怎么少了一万
start transaction;
select * from user where id = 2;
commit;
出现上述情况，即我们所说的脏读 ，两个并发的事务，“事务A：领导给singo发工资”、“事务B：singo查询工资账户”，事务B读取了事务A尚未提交的数据。
4.3 读已提交
A事务在本次事务中，对自己操作过的数据，进行了多次读取发现数据不一致。
简单点说就是不能让我好好的重复读，一个事务里读出来的数据都不一样，让不让人干活了。
针对的语句update和delete，会导致不可重复读
楠哥拿着工资卡去消费，系统读取到卡里确实有10200元，而此时她的老婆也正好在网上转账，把楠哥工资卡的2000元转到另一账户，并在 楠哥之前提交了事务，当楠哥扣款时，系统检查到楠哥的工资卡和上次读取的不一样了，楠哥十分纳闷，明明卡里有钱，为何......
SET transaction isolation level read committed;
1-楠哥去消费了，显示有余额，贼高兴
start transaction;
select * from user where id = 1;
2-老婆转账
start transaction;
UPDATE user set balance = balance + 10000 where id = 2;
UPDATE user set balance = balance - 10000 where id = 1;
commit;
3-楠哥查账，同一个事务里，发现钱少了。
select * from user where id = 1;
当隔离级别设置为Read committed 时，避免了脏读，但是可能会造成不可重复读。
大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle。如何解决不可重复读这一问题，请看下一个隔离级别。
4.4 可重复读
A事务在本次事务中对未操作的数据进行多次查询，发现第一次没有，第二次出现了就像幻觉一样。或者第一次有而第二次没有。针对delete和insert。
案例
楠哥的老婆在银行部门工作，她时常通过银行内部系统查看楠哥的账户信息。有一天，她正在查询到楠哥账户信息时发现楠哥只有一个账户，心想这家伙应该没有私房钱。此时楠哥在另外一家分行右开了一个账户，准备存私房钱。一次同时楠哥老婆点击了打印，结果打印出的楠哥账户居然多了一个，真实奇怪。
set transaction isolation level repeatable read;
1-楠哥去消费了，显示有余额，贼高兴
start transaction;
2-老婆查账户
start transaction;
select * from user where name = '楠哥';
3-楠哥趁机开户
insert into user values(3,'楠哥',10000);
commit;
4-老婆再查询并打印，应该发现楠哥多了一个账户
select * from user where name = '楠哥';
MySQL 通过多版本并发控制（MVCC）（快照读/一致性读）其实解决了幻读问题。
原理：事务开启后，将历史数据存一份快照，其他事务增加与删除的数据，对于当前事务来说是不可见的。
4.5 串行化
- 事务A和事务B，事务A在操作数据库时，事务B只能排队等待
- 这种隔离级别很少使用，吞吐量太低，用户体验差
- 这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发。
- 别的地方一用这个数据就不能修改删除，直到别的地方提交
SET transaction isolation level serializable;
1-楠哥
begin;
select * from user;
2-老婆
begin;
select * from user;
3-楠哥操作发现卡住了
delete from user where id = 9;
4-老婆这边一提交，那边就能操作了
commit;
5、事务实现的原理（不学，能看懂了看，看不懂拉倒）
等以后学的多了再看：
下面我首先讲实现事务功能的三个技术，分别是日志文件(redo log 和 undo log)，锁技术以及MVCC，然后再讲事务的实现原理，包括原子性是怎么实现的，隔离型是怎么实现的等等。最后在做一个总结，希望大家能够耐心看完
- redo log与undo log介绍
- mysql锁技术以及MVCC基础
- 事务的实现原理
5.1 redo log 与 undo log介绍
5.1.1、什么是redo log ?
redo log叫做重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中。假设有个表叫做tb1(id,username) 现在要插入数据（3，ceshi）
start transaction;
select balance from bank where name="zhangsan";
// 生成 重做日志 balance=600
update bank set balance = balance - 400;
// 生成 重做日志 amount=400
update finance set amount = amount + 400;
5.1.2、redo log作用是什么？
mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Boffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。
那么问题来了，如果还没来的同步的时候宕机或断电了怎么办？还没来得及执行上面图中红色的操作。这样会导致丢部分已提交事务的修改信息！
所以引入了redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据。
- 总结：redo log是用来恢复数据的，用于保障，已提交事务的持久化特性（记录了已经提交的操作）
5.1.3、什么是undo log？
undo log 叫做回滚日志，用于记录数据被修改前的信息。他正好跟前面所说的重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。
还用上面那两张表
每次写入数据或者修改数据之前都会把修改前的信息记录到 undo log。
5.1.4、undo log 有什么作用？
undo log 记录事务修改之前版本的数据信息，因此假如由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到没被修改前的状态。
- 总结：undo log是用来回滚数据的用于保障，未提交事务的原子性
5.2 mysql锁技术以及MVCC基础
5.2.1 mysql锁技术
当有多个请求来读取表中的数据时可以不采取任何操作，但是多个请求里有读请求，又有修改请求时必须有一种措施来进行并发控制。不然很有可能会造成不一致。
读写锁
解决上述问题很简单，只需用两种锁的组合来对读写请求进行控制即可，这两种锁被称为：
-
共享锁(shared lock),又叫做"读锁"
读锁是可以共享的，或者说多个读请求可以共享一把锁读数据，不会造成阻塞。 -
排他锁(exclusive lock),又叫做"写锁"
写锁会排斥其他所有获取锁的请求，一直阻塞，直到写入完成释放锁。
-
总结：通过读写锁，可以做到读读可以并行，但是不能做到写读，写写并行
5.2.2 MVCC基础
MVCC介绍
MVCC (MultiVersion Concurrency Control) 叫做多版本并发控制。一般情况下，事务性储存引擎不是只使用表锁，行加锁的处理数据，而是结合了MVCC机制，以处理更多的并发问题。Mvcc处理高并发能力最强，但系统开销 比最大（较表锁、行级锁），这是最求高并发付出的代价。
InnoDB的 MVCC ，是通过在每行记录的后面保存两个隐藏的列来实现的。这两个列， 一个保存了行的创建时间，一个保存了行的过期时间， 当然存储的并不是实际的时间值，而是系统版本号。
以上片段摘自《高性能Mysql》这本书对MVCC的定义。他的主要实现思想是通过数据多版本来做到读写分离。从而实现不加锁读进而做到读写并行。MVCC在mysql中的实现依赖的是undo log与read view;
-
undo log :undo log 中记录某行数据的多个版本的数据。
-
read view :用来判断当前版本数据的可见性
MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。不同存储引擎的MVCC实现是不同的，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。
5.2.1 MVCC具体实现分析
轻松理解MYSQL MVCC 实现机制
下面,我们通过InnoDB的MVCC实现来分析MVCC使怎样进行并发控制的.
InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的创建时间，一个保存的是行的删除时间。这里存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID)，没开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID.下面看一下在REPEATABLE READ隔离级别下,MVCC具体是如何操作的.
- 例子：
- CREATE(创建表)
create table yang(
id int primary key auto_increment,
name varchar(20));
- INSERT(插入数据)
start transaction;
insert into yang values(NULL,'yang') ;
insert into yang values(NULL,'long');
insert into yang values(NULL,'fei');
commit;
假设系统的版本号从1开始.
对应在数据中的表如下(后面两列是隐藏列,我们通过查询语句并看不到)
| id | name | 创建时间(事务ID) | 删除时间(事务ID) |
|---|---|---|---|
| 1 | yang | 1 | undefined |
| 2 | long | 1 | undefined |
| 3 | fei | 1 | undefined |
-
SELECT(查询数据)
InnoDB会根据以下两个条件检查每行记录:
(1)InnoDB只会查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的.
(2)行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除.
只有a,b同时满足的记录，才能返回作为查询结果. -
DELETE(删除数据)
InnoDB会为删除的每一行保存当前系统的版本号(事务的ID)作为删除标识.
看下面的具体例子分析:
第二个事务,ID为2;
start transaction;
select * from yang; //(1)
select * from yang; //(2)
commit;
假设1
假设在执行这个事务ID为2的过程中,刚执行到(1),这时,有另一个事务ID为3往这个表里插入了一条数据; 第三个事务ID为3;
start transaction;
insert into yang values(NULL,'tian');
commit;
这时表中的数据如下:
| id | name | 创建时间(事务ID) | 删除时间(事务ID) |
|---|---|---|---|
| 1 | yang | 1 | undefined |
| 2 | long | 1 | undefined |
| 3 | fei | 1 | undefined |
| 4 | tian | 3 | undefined |
然后接着执行事务2中的(2),由于id=4的数据的创建时间(事务ID为3),执行当前事务的ID为2,而InnoDB只会查找事务ID小于等于当前事务ID的数据行,所以id=4的数据行并不会在执行事务2中的(2)被检索出来,在事务2中的两条select 语句检索出来的数据都只会下表:
| id | name | 创建时间(事务ID) | 删除时间(事务ID) |
|---|---|---|---|
| 1 | yang | 1 | undefined |
| 2 | long | 1 | undefined |
| 3 | fei | 1 | undefined |
假设2
假设在执行这个事务ID为2的过程中,刚执行到(1),假设事务执行完事务3后，接着又执行了事务4; 第四个事务:
start transaction;
delete from yang where id=1;
commit;
此时数据库中的表如下:
| id | name | 创建时间(事务ID) | 删除时间(事务ID) |
|---|---|---|---|
| 1 | yang | 1 | 4 |
| 2 | long | 1 | undefined |
| 3 | fei | 1 | undefined |
| 4 | tian | 3 | undefined |
接着执行事务ID为2的事务(2),根据SELECT 检索条件可以知道,它会检索创建时间(创建事务的ID)小于当前事务ID的行和删除时间(删除事务的ID)大于当前事务的行,而id=4的行上面已经说过,而id=1的行由于删除时间(删除事务的ID)大于当前事务的ID,所以事务2的(2)select * from yang也会把id=1的数据检索出来.所以,事务2中的两条select 语句检索出来的数据都如下:
| id | name | 创建时间(事务ID) | 删除时间(事务ID) |
|---|---|---|---|
| 1 | yang | 1 | 4 |
| 2 | long | 1 | undefined |
| 3 | fei | 1 | undefined |
- UPDATE
InnoDB执行UPDATE，实际上是新插入了一行记录，并保存其创建时间为当前事务的ID，同时保存当前事务ID到要UPDATE的行的删除时间.
假设3
假设在执行完事务2的(1)后又执行,其它用户执行了事务3,4,这时，又有一个用户对这张表执行了UPDATE操作;第5个事务:
start transaction;
update yang set name='Long' where id=2;
commit;
根据update的更新原则:会生成新的一行,并在原来要修改的列的删除时间列上添加本事务ID,得到表如下:
| id | name | 创建时间(事务ID) | 删除时间(事务ID) |
|---|---|---|---|
| 1 | yang | 1 | 4 |
| 2 | long | 1 | 5 |
| 3 | fei | 1 | undefined |
| 4 | tian | 3 | undefined |
| 2 | Long | 5 | undefined |
继续执行事务2的(2),根据select 语句的检索条件,得到下表:
| id | name | 创建时间(事务ID) | 删除时间(事务ID) |
|---|---|---|---|
| 1 | yang | 1 | 4 |
| 2 | long | 1 | 5 |
| 3 | fei | 1 | undefined |
还是和事务2中(1)select 得到相同的结果.
浙公网安备 33010602011771号