title: 数据库-MySQL的事务
date: 2021-11-21 20:44:39
tags:
数据库-MySQL的事务
mysql中，事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。
比如银行转账，a用户的前转给b用户100元
a -> -100
update user set money = money - 100 where name = ‘a’;
b -> +100
update user set money = money + 100 where name = ‘b’;
实际的程序中，如果只有一条语句执行成功了而另一条没有执行，则会出现数据前后不一致，所以对于多条sql语句，可能会有需要同时成功的要求，要么就同时失败。
mysql中如何控制事务
1.mysql默认是开启事务的（自动提交）
select @@autocommit;
值为1就是自动提交
±-------------+
| @@autocommit |
±-------------+
| 1 |
±-------------+
默认事务开启的作用是什么？
当我们去执行一个sql语句的时候，效果就会立即体现出来，且不能回滚。
show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| pet |
| selecttest |
| sys |
| testjoin |
| user |
±-------------------+
create database bank;
insert into user values(1, ‘a’, 1000);
select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
±—±-----±------+
插入一条数据后就立马生效了，不能回滚，相当于默认就提交了
事务回滚：撤销sql语句执行效果
rollback；
rollback;
Query OK, 0 rows affected (0.00 sec)
select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
±—±-----±------+
可以进行回滚的话，需要设置mysql自动提交为false
set autocommit = 0;
关闭了mysql的自动提交（commit）
select @@autocommit;
±-------------+
| @@autocommit |
±-------------+
| 0 |
±-------------+
insert into user values(2, ‘b’, 1000);
select * from user;
这里看到的效果是一个临时的效果，还没有真实地发生在数据库里面，只是在一个虚拟的表里
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
| 2 | b | 1000 |
±—±-----±------+
rollback;
select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
±—±-----±------+
如果想要让虚拟的表提交怎么做？用commit语句
再一次插入数据
insert into user values(2, ‘b’, 1000);
因为把自动提交关闭了，这里是手动提交数据
commit;
再撤销是不可以撤销的（事务的持久性，就是一旦之前提交了commit，就会持久地产生效果，下面的rollback就不会产生效果）
rollback;
select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
| 2 | b | 1000 |
±—±-----±------+
自动提交？@@autocommit = 1;
手动提交？commit;
事务回滚?rollback;
update user set money = money - 100 where name = ‘a’;
update user set money = money + 100 where name = ‘b’;
select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 900 |
| 2 | b | 1100 |
±—±-----±------+
rollback;
select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
| 2 | b | 1000 |
±—±-----±------+
事务给我们提供了一个返回的机会
其他方式：
手动开启事务
begin；或start transaction；这两个sql语句都可以帮我们手动开启一个事务；
set autocommit = 1;
select @@autocommit;
±-------------+
| @@autocommit |
±-------------+
| 1 |
±-------------+
select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
| 2 | b | 1000 |
±—±-----±------+
update user set money = money - 100 where name = ‘a’;
update user set money = money + 100 where name = ‘b’;
select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 900 |
| 2 | b | 1100 |
±—±-----±------+
rollback; //事务回滚
select * from user; //发现rollback并没有撤销效果
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 900 |
| 2 | b | 1100 |
±—±-----±------+
begin;
update user set money = money - 100 where name = ‘a’;
update user set money = money + 100 where name = ‘b’;
select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 800 |
| 2 | b | 1200 |
±—±-----±------+
rollback;
select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 900 |
| 2 | b | 1100 |
±—±-----±------+
start transaction;
update user set money = money - 100 where name = ‘a’;
update user set money = money + 100 where name = ‘b’;
select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 800 |
| 2 | b | 1200 |
±—±-----±------+
rollback;
select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 900 |
| 2 | b | 1100 |
±—±-----±------+
事务开启后一旦commit提交，就不可以回滚了（就是当前这个事务在提交的时候就结束了）
commit;
select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 900 |
| 2 | b | 1100 |
±—±-----±------+
rollback;
select * from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 900 |
| 2 | b | 1100 |
±—±-----±------+
事务的四大特征：ACID
A原子性：事务是最小的单位，不可以再分割
C一致性：事务要求，同一事务中的sql语句，必须保证同时成功或者同时失败
I隔离性：事务1和事务2之间是具有隔离性的
D持久性：事务一旦结束（commit，rollback二选一），就不可以返回。
事务开启：
三种方式
1.修改默认提交 set autocommit = 0;
2.begin；
3.start transaction；
事务手动提交：
commit；
事务手动回滚:
rollback;
事务的隔离性：
1.read uncommtited;----读未提交的
2.read committed;----读已经提交的
3.repeatable read;----可以重复读
4.serializable;----串行化
1.read uncommitted;----读为提交的
如果有事务a和事务b，a事务对数据进行操作，在操作的过程中，事务没有被提交，但b可以看到a操作的结果。
insert into user values(3, ‘小明’, 1000);
insert into user values(4, ‘淘宝店’, 1000);
select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
±—±-------±------+
如何查看数据库的隔离级别？（系统不同语句不同）
mysql 8.0：这两种都可以
select @@global.transaction_isolation; //系统级别的
或者
select @@transaction_isolation; //会话级别的
±-------------------------------+
| @@global.transaction_isolation |
±-------------------------------+
| REPEATABLE-READ |
±-------------------------------+
mysql默认隔离级别REPEATABLE-READ
mysql 5.x：
select @@global.tx_isolation; //系统级别的
或者
select @@tx_isolation; //会话级别的
如何修改隔离级别？
set global transaction isolation level read uncommitted;
select @@global.transaction_isolation;
±-------------------------------+
| @@global.transaction_isolation |
±-------------------------------+
| READ-UNCOMMITTED |
±-------------------------------+
小明在淘宝店买了一双800元的鞋子，需要转账，小明在成都，淘宝店在广州
update user set money = money - 800 where name = ‘小明’;
update user set money = money + 800 where name = ‘淘宝店’;
select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
±—±-------±------+
给淘宝店打电话说你去查一下是不是到账了，查了一下确实到账了
rollback;
之后钱就回来了
select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
±—±-------±------+
如果两个不同的地方都在进行操作，如果事务a开启之后，它的数据可被其他事务读取到，这样就会出现脏读（一个事务读到了另一个事务没有提交的数据），就像上面的例子，实际开发是不允许脏读出现的。
2.read committed;----读已经提交的
set global transaction isolation level read committed;
select @@global.transaction_isolation;
±-------------------------------+
| @@global.transaction_isolation |
±-------------------------------+
| READ-COMMITTED |
±-------------------------------+
一个事务只能读取另一个事务已经提交的数据
小张是银行的会计
start transaction;
select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
±—±-------±------+
小张出去了
另一个城市的小王插入了一个数据并提交了
start transaction;
insert into user values(5, ‘c’, 100);
select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
±—±-------±------+
commit;
小张回来了需要算平均值，发现平均值不是他走之前的数据该有的平均值了
select avg(money) from user;
±-----------+
| avg(money) |
±-----------+
| 820.0000 |
±-----------+
虽然我只能读到另一个事务提交的数据，但是还是会出现问题，就是读取同一个表的数据，发现前后不一致，这就是不可重复读现象（read committed）
3.repeatable read;----可以重复读（这是mysql默认模式）
set global transaction isolation level repeatable read;
select @@global.transaction_isolation;
±-------------------------------+
| @@global.transaction_isolation |
±-------------------------------+
| REPEATABLE-READ |
±-------------------------------+
select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
±—±-------±------+
张全蛋–成都
start transaction;
王尼玛–北京
start transaction;
张全蛋–成都
insert into user values(6, ‘d’, 1000);
王尼玛–北京
select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
±—±-------±------+
张全蛋–成都
commit;
王尼玛–北京，这边还是没有
select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
±—±-------±------+
张全蛋–成都，但是这边有
select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
±—±-------±------+
王尼玛–北京，再去插入就会报错
insert into user values(6, ‘d’, 1000); //报错
ERROR 1062 (23000): Duplicate entry ‘6’ for key ‘user.PRIMARY’
王尼玛这边明明没有6号却显示6号已经存在
这种现象叫做幻读，就是事务a和事务b同时操作一张表，事务a提交的数据也不能被事务b读到，就造成了幻读
4.serializable;----串行化
set global transaction isolation level serializable;
select @@global.transaction_isolation;
±-------------------------------+
| @@global.transaction_isolation |
±-------------------------------+
| SERIALIZABLE |
±-------------------------------+
select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
±—±-------±------+
张全蛋–成都
start transaction;
王尼玛–北京
start transaction;
张全蛋–成都
insert into user values(7, ‘赵铁柱’, 1000);
王尼玛–北京
select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
±—±-------±------+
张全蛋–成都
commit;
select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
| 7 | 赵铁柱 | 1000 |
±—±-------±------+
王尼玛–北京
select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 900 |
| 2 | b | 1100 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
| 7 | 赵铁柱 | 1000 |
±—±-------±------+
张全蛋–成都
insert into user values(8, ‘王小花’, 1000);
此时发现sql语句会卡住不执行，为什么会卡住？
当user表被另外一个事务操作的时候，其他事务里面的写操作是不可以进行的，因为王尼玛–北京start transaction;开启了事务后，王尼玛进行了user表的操作select * from user;这两个事务是串行的，必须等王尼玛这边committed；提交了，张全蛋这边才可以继续操作（前提是王尼玛执行的语句没有等待超时）
串行化的问题：性能特差
性能比较：隔离级别越高性能越差，mysql默认隔离级别是repeatable read;
read uncommtited;>read committed>repeatable read;>serializable;----串行化
