==mysql事务
==什么是事务
mysql中,事务其实是一个最小的不可分割的工作单位。事务能够保证一个业务的完整性。比如:
银行转账:
用户 a->-100
update user set money=money-100 where name=‘a’;
用户 b->+100
update user set money=money+100 where name=‘b’;
实际的程序中,如果只有一条语句执行成功了,而另外一个没有执行成功?(a账号扣了100,但是b账号没有收到钱)
出现数据前后不一致。
多条 sql 语句,可能会有同时成功的要求,要么同时失败(这就是事务要解决的问题)
==mysql 中如何控制事务?
1.mysql 默认开启事务的(自动提交)。
mysql> select @@autocommit;
±-------------+
| @@autocommit |
±-------------+
| 1 |
±-------------+
1 row in set
==默认事务开启的作用是什么?
当我们去执行一个 sql 语句的时候,效果会立即体现出来,且不能回滚。
mysql> create database bank;
Query OK, 1 row affected
mysql> use bank;
Database changed
mysql> create table user(
id int primary key,
name varchar(20),
money int);
Query OK, 0 rows affected
mysql> insert into user values(1,‘a’,1000);
Query OK, 1 row affected
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
±—±-----±------+
1 row in set
会立即显现出来
==事务回滚(rollback):撤销 sql 语句执行效果
mysql> rollback;
Query OK, 0 rows affected
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
±—±-----±------+
1 row in set
数据还在所有回滚失败,证明它是不可以回滚的
那怎么撤销 sql 语句呢
==我们可以把默认事务设置为0,也就是 设置 sql 自动提交为 false
mysql> set autocommit=0;
Query OK, 0 rows affected
mysql> select @@autocommit;
±-------------+
| @@autocommit |
±-------------+
| 0 |
±-------------+
1 row in set
==上面操作,关闭了 mysql 的自动提交(commit);
mysql> insert into user values(2,‘b’,1000);
Query OK, 1 row affected
其实这里看到是一个临时的效果,它不是真实的,不是在我们的数据库里面,只是一张虚拟的表
(相当于把user表复制了一份(虚拟表),在虚拟表中呈现,真实的表是没有b这条数据的所以我们 rollback的时候是可以撤销的)
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
| 2 | b | 1000 |
±—±-----±------+
2 rows in set
mysql> rollback;
Query OK, 0 rows affected
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
±—±-----±------+
1 row in set
那如果说我们想让 b 这条数据提交怎么办呢
再一次插入数据
mysql> insert into user values(2,‘b’,1000);
Query OK, 1 row affected
手动提交
mysql> commit;
Query OK, 0 rows affected
再撤销,是不可以撤销的(体现了事务的特性—持久性)
mysql> rollback;
Query OK, 0 rows affected
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
| 2 | b | 1000 |
±—±-----±------+
2 rows in set
要注意的:
==自动提交?@@autocommit =1
==手动提交?commit;
==事务回滚?rollback;
自动提交和手动提交是不可以撤销的那么问题来了
==如果说这个转账:
update user set money=money-100 where name=‘a’;
update user set money=money+100 where name=‘b’;
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 900 |
| 2 | b | 1100 |
±—±-----±------+
2 rows in set
mysql> rollback;
Query OK, 0 rows affected
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
| 2 | b | 1000 |
±—±-----±------+
2 rows in set
==事务给了我们一个反悔的机会
==手动开启事务(begin_start_transaction)
mysql> set autocommit=1;
Query OK, 0 rows affected
mysql> select @@autocommit;
±-------------+
| @@autocommit |
±-------------+
| 1 |
±-------------+
1 row in set
不需要手动输入 commit 了,但是不可以撤销
但是如果我还是想撤销怎么办
可以通过输入 begin; 或者 start transaction; 都可以帮我们手动开启一个事务
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
| 2 | b | 1000 |
±—±-----±------+
2 rows in set
mysql> update user set money=money-100 where name=‘a’;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set money=money+100 where name=‘b’;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 900 |
| 2 | b | 1100 |
±—±-----±------+
2 rows in set
事务回滚
mysql> rollback;
Query OK, 0 rows affected
没有被撤销(因为我们把自动提交设置成了1,会帮我们提交)
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 900 |
| 2 | b | 1100 |
±—±-----±------+
2 rows in set
==手动开启事务(1)
begin;
update user set money=money-100 where name=‘a’;
update user set money=money+100 where name=‘b’;
手动开启一个事务
mysql> begin;
Query OK, 0 rows affected
进行转账
mysql> update user set money=money-100 where name=‘a’;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set money=money+100 where name=‘b’;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 800 |
| 2 | b | 1200 |
±—±-----±------+
2 rows in set
事务回滚
mysql> rollback;
Query OK, 0 rows affected
被撤销
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 900 |
| 2 | b | 1100 |
±—±-----±------+
2 rows in set
==手动开启事务(2)
start transaction;
update user set money=money-100 where name=‘a’;
update user set money=money+100 where name=‘b’;
手动开启事务
mysql> start transaction;
Query OK, 0 rows affected
mysql> update user set money=money-100 where name=‘a’;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set money=money+100 where name=‘b’;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 800 |
| 2 | b | 1200 |
±—±-----±------+
2 rows in set
事务回滚
mysql> rollback;
Query OK, 0 rows affected
被撤销
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 900 |
| 2 | b | 1100 |
±—±-----±------+
2 rows in set
如果我们想让它生效,手动输入commit (手动提交)
事务开启之后,一旦commit 提交,就不可以回滚(也就是当前这个事务在提交的时候就结束了)
手动开启事务
mysql> start transaction;
Query OK, 0 rows affected
mysql> update user set money=money-100 where name=‘a’;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set money=money+100 where name=‘b’;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
手动提交
mysql> commit;
Query OK, 0 rows affected
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 800 |
| 2 | b | 1200 |
±—±-----±------+
2 rows in set
事务回滚
mysql> rollback;
Query OK, 0 rows affected
没有被撤销
mysql> select *from user;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 800 |
| 2 | b | 1200 |
±—±-----±------+
2 rows in set
==事务的三大特征:
A 原子性:事务是最小的单位,不可以在分割。
B 一致性:事务要求,同一事务中的 sql 语句,必须保证同时成功或者同时失败。
I 隔离性:事务1 和 事务2 之间是具有隔离性的。
D 持久性: 事务一旦结束(进行(commit,rollback)其中一个),就不可以返回。
事务开启:
1.修改默认提交 set autocommit=0;
2.begin;
3.start transaction;
事务手动提交:
commit;
事务手动回滚:
rollback;
==事务隔离性:
- read uncommitted; 读未提交的
- read committed; 读已经提交的
- repeatable read; 可以重复读
- serializable; 串行化
==1. read uncommitted
如果有事务 a ,和事务 b,
a 事务对数据进行操作,在操作的过程中,事务没有被提交,但是 b 可以看见 a 操作的结果。
==这样会出现上面问题呢?
insert into user values(3,‘小明’,1000);
insert into user values(4,‘淘宝店’,1000);
mysql> select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
±—±-------±------+
4 rows in set
小明去淘宝店买鞋子,鞋子要800块钱,给淘宝店转账,淘宝店查看钱是否到账
==如何查看数据库的隔离级别?
mysql 8.0 版本:
select @@global.transaction_isolation;(系统级别的)
select @@transaction_isolation;(会话级别的)
mysql 5.几 版本:
select @@global.tx_isolation;(系统级别的)
select @@tx_isolation;(会话级别的
系统级别的
REPEATABLE-READ是 mysql 默认隔离级别
mysql> select @@global.tx_isolation;
±----------------------+
| @@global.tx_isolation |
±----------------------+
| REPEATABLE-READ |
±----------------------+
1 row in set
会话级别的
mysql> select @@tx_isolation;
±----------------+
| @@tx_isolation |
±----------------+
| REPEATABLE-READ |
±----------------+
1 row in set
==如何修改隔离级别?
mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected
mysql> select @@global.tx_isolation;
±----------------------+
| @@global.tx_isolation |
±----------------------+
| READ-UNCOMMITTED |
±----------------------+
1 row in set
转账:小明在淘宝店买鞋子:800块钱
小明->成都 ATM
淘宝店->广州 ATM
开启事务(转账事务)
mysql> start transaction;
Query OK, 0 rows affected
mysql> update user set money=money-800 where name=‘小明’;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set money=money+800 where name=‘淘宝店’;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
±—±-------±------+
4 rows in set
==小明给淘宝店打电话,说你去查一下,是不是到账了
==淘宝店在广州查账
mysql> select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
±—±-------±------+
4 rows in set
==发货
==晚上请女朋友吃好吃的1800
==1800
==小明->成都
mysql> rollback;
Query OK, 0 rows affected
mysql> select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
±—±-------±------+
4 rows in set
==结账的时候发现钱不够(他只能看到自己的,这里只是打个比方)淘宝店他不是程序员所以他不知道怎么回事
mysql> select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
±—±-------±------+
4 rows in set
==如果俩个不同的地方,都在进行操作,如果事务a (小明开启的事务)开启之后,他的数据可以被其他事务(被淘宝店事务读取到回滚,所以钱变回了1000)读取到。
==这样就会出现(脏读)
==脏读:一个事务读到了另外一个事务没有提交的数据,就叫脏读
==实际开发是不允许脏读出现的。
==2. read committed;
查看隔离级别
mysql> select @@global.tx_isolation;
±----------------------+
| @@global.tx_isolation |
±----------------------+
| REPEATABLE-READ |
±----------------------+
1 row in set
修改隔离级别为 read committed
mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected
查看隔离级别
mysql> select @@global.tx_isolation;
±----------------------+
| @@global.tx_isolation |
±----------------------+
| READ-COMMITTED |
±----------------------+
1 row in set
小张:银行会计(做报表的)
开启一个事务
start transaction;
mysql> select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
±—±-------±------+
4 rows in set
小张出去上厕所
小王:
start transaction;
开了一个户
insert into user values(5,‘c’,100);
commit;
mysql> select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
±—±-------±------+
5 rows in set
小张上厕所回来了
mysql> select avg(money) from user;
±-----------+
| avg(money) |
±-----------+
| 820.0000 |
±-----------+
1 row in set
发现money 的平均值不是1000,变少了
==虽然我只能读到另外一个事务提交的数据,但还是会出现问题,就是
读取同一个表的数据,发现前后不一致
这种现象称为不可重复读现象:在 read committed 出现
==3. repeatable read; 可以重复读
修改隔离级别
mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected
mysql> select @@global.tx_isolation;
±----------------------+
| @@global.tx_isolation |
±----------------------+
| REPEATABLE-READ |
±----------------------+
1 row in set
==在 repeatable read 隔离级别下又会出现什么问题呢?
mysql> select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
±—±-------±------+
5 rows in set
张三->成都
start transaction;
王五->北京
start transaction;
张三->成都
insert into user values(6,‘d’,1000);
commit;
mysql> select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
±—±-------±------+
6 rows in set
王五->北京
mysql> select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
±—±-------±------+
5 rows in set
mysql> insert into user values(6,‘d’,1000);
1062 - Duplicate entry ‘6’ for key ‘PRIMARY’
==这种现象叫做幻读(王五这边查看的时候明明没有6号,但是却说已经存在)
事务 a 和事务 b 同时操作一张表,事务 a 提交的数据,也不能被事务 b 读到,就可能会造成幻读。
==4. serializable; 串行化
修改隔离级别为串行化
mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected
mysql> select @@global.tx_isolation;
±----------------------+
| @@global.tx_isolation |
±----------------------+
| SERIALIZABLE |
±----------------------+
1 row in set
张三->成都
mysql> select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
±—±-------±------+
6 rows in set
start transaction;
王五->北京
start transaction;
张三->成都
insert into user values(7,‘赵铁柱’,1000);
commit;
mysql> select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
| 7 | 赵铁柱 | 1000 |
±—±-------±------+
7 rows in set
王五->北京
mysql> select * from user;
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
| 7 | 赵铁柱 | 1000 |
±—±-------±------+
7 rows in set
张三->成都
start transaction;
insert into user values(8,‘王小花’,1000);
sql语句被卡住了
mysql> insert into user values(8,‘王小花’,1000);
那为什么会被卡住呢?
因为我这个隔离级别为串行化,
当 user 表被另外一个事务操作的时候,其他事务里面的写操作,是不可以进行。进入排队状态(串行化)(也就是当王五在查询的时候,张三是不能进行写入操作,如果进行写入操作的话就会进入等待状态(张三))
直到王五那边的事务提交之后,张三才可以执行了(在没有等待超时的情况下)
王五->北京
commit;
张三->成都
insert into user values(8,‘王小花’,1000);
mysql> insert into user values(8,‘王小花’,1000);
Query OK, 1 row affected
==串行化问题是,性能特差!!
性能:read uncommitted > read committed > repeatable read > serializable
隔离级别越高性能越差
隔离级别越低出现的问题就越多(read uncommitted什么问题都会出现, read committed出现不可重复问题,repeatable read出现幻读)
mysql默认级别是:repeatable read