事务
在mysql中,事务是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。
事务的应用场景举例:
银行转账:A给B转100快,B收到A的100块
A-100: update user set money = money -100 where name = 'a';
B+100:update user set money = money +100 where name = 'b';
在实际开发中,若上两条sql语句只有一句执行成功。另一条执行失败,就会出现数据前后不一致的效果,导致出错。所以在实际开发过程中,要求两条sql语句是同时成功,或者同时失败的。这就是事务存在的意义。
1.mysql中是怎样控制事务的?
通过select @@autocommit;
查询一下当前状态,可以看到autocommit(自动提交)为1,代表此时是开启事务状态的。
在mysql中默认是开启事务的
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
默认开启事务的作用:当我们执行一条sql语句时,效果会立刻显现出来,且不能回滚。
2.什么是回滚?
上面提到的回滚(roolback):撤销sql语句的效果。
我们先创建一个表,再插入数据,来看一下回滚
mysql> create database bank;
Query OK, 1 row affected (0.00 sec)
mysql> use bank;
Database changed
mysql> create table user(
-> id int primary key,
-> name varchar(20),
-> money int
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into user values(1,'a',1000);
Query OK, 1 row affected (0.01 sec)
插入数据a之后查看我们的表
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
1 row in set (0.00 sec)
进行回滚操作,撤销上条sql语句
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
在查看发现并没有回滚
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
1 row in set (0.00 sec)
这就是上条说的自动提交为1,则不能回滚的效果。因为autocommit 默认为1所以当我们执行完一条sql语句后就自动提交了,不支持回滚操作。
3.我就想回滚,怎么办
将mysql的自动提交设置为0;
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
此时自动提交已经关闭
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
在插入数据看一遍效果
插入数据b
mysql> insert into user values (2,'b',1000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
可以看到此时回滚操作成功了,插入b信息的sql语句被撤销了
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
1 row in set (0.00 sec)
此时,插入的数据b,select后的表只是一个虚拟表,是一个临时的效果,并没有真实的发生在我们的数据库中,若rollback则数据b不见。
若想将数据b插入实际的数据库表中,我们可以使用commit;该操作之后,rollback;会失效。
commit;
可以理解为手动提交,提交之后的数据具有持久性,不可撤销。
再次插入数据
mysql> insert into user values (2,'b',1000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
手动提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
再次回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
可以看到回滚失效,因为commit后数据具有持久性
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
自动提交:@@autocommit = 1;
手动提交:commit;
事务回滚:rollback;
一旦提交,不可回滚
4.再谈转账栗子
A-100: update user set money = money -100 where name = 'a';
B+100:update user set money = money +100 where name = 'b';
- 先关闭mysql的自动提交
- 执行两条转账语句,此时select的表为虚拟表
- 当我发现啊转账有问题:只有一条sql语句执行成功时(中途断网,断电,弱网环境等等原因),我就rollback;
- 当转账没问题时,就commit提交
- 此时,转账才是真实的提交了,具有持久性,在真正的数据表中产生效果。
所以说:事务给我们提供了一个返回的机会!
5.不改变自动提交默认值,其他可以使用rollback的方法?
mysql为我们提供了两种手动打开一个事务的方法
begin;
start transaction;
仍以转账为例:现将我们的事务恢复默认值,采用手动开启一个事务的方法进行回滚
mysql> set @@autocommit = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
使用begin开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
进行转账
mysql> update user set money = money -100 where name = 'a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set money = money +100 where name = 'b';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看表,此时为虚拟表
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
+----+------+-------+
2 rows in set (0.00 sec)
进行回滚
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
- 手动开始事务写在需要事务的语句之前,此时回滚生效。
- 此时,事务语句仍是在虚拟表中进行的,因为我们手动开启了事务,所以仍需要commit提交才能真实生效。
- commit提交之后,该事务自动关闭,回滚失效。
6.事务的四大特征(面试常问)
ACID
- 原子性:事务是最小的单位,不可以再分割,要么全部执行,要么一条都不执行。
- 一致性:事务执行后,数据要处于合法情况(转账中,A转B多少,B的钱加多少)
- 隔离性:事务1与事务2之前,是具有隔离性,相互执行是不干扰的
- 持久性:事务一旦结束,就不可返回。commit之后不能在rollback;
隔离性是重点,后续重点说一下。
7.事务开启的方式
1.修改默认提交:set commit = 0;
2.begin;手动开启事务
3.star transaction;手动开启事务
事务手动提交:commit;让虚拟的效果真实产生
事务手动回滚:rollback;让虚拟的 效果被撤销
8.事务的四大特征----隔离性
如何查看事务的隔离界别?
select @@tx_isolation;
查看会话级别的隔离性
这里我们可以发现,mysql的默认隔离级别是REPEATABLE-READ
(可重复读)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
如何修改隔离级别?
修改隔离级别:set transaction isolation level read uncommitted;
这里以read uncommitted(读未提交)举例,实际开发中可以根据现实情况,选择四种隔离级别中的任意一个。
mysql> set transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
隔离级别分为四种:
1.read uncommitted
;读未提交----->导致脏读
2.read committed
; 读以提交------>导致不可重复读
3.repeatable read
; 可以重复读------>导致幻读
3.serializable
; 串行化------>可解决上述问题,但性能很差
1.read uncommitted;读未提交----->导致脏读
- 若有事务A/B,A对事务数据进行操作,在操作过程中,A还没提交,但是B看见了A的操作。
- 用转账举个栗子:(A、B,是两个事务,所以我们开两个cmd进行操作,模拟)
小明有1000块,淘宝也有100块
mysql> insert into user values (3,'小明',1000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values (4,'淘宝店',1000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+----+--------+-------+
4 rows in set (0.00 sec)
小明在淘宝店买东西,花了800,淘宝店涨了800
开启事务A,进行转账和收账操作,但未提交
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set money = money -800 where name = '小明';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set money = money +800 where name = '淘宝店';
Query OK, 1 row affected (0.00 sec)
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 (0.00 sec)
小明让淘宝店家去查账,店家在另一台终端B(事务B)一查,果然到账了,就去请女朋友吃饭了,但是结账时却发现800块么见了??
原因:小明在自己的终端(事务A)中进行了回滚操作。相当于钱并没有真实的转账(并没有真实的改变数据库中信息,未提交没有持久性)
小明进行rollback操作,钱又回来了,淘宝店家在自己的终端一查,发现钱也不见了
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+----+--------+-------+
4 rows in set (0.00 sec)
这就是read uncommitted(读未提交)所带来的问题
- 如果两个不同的终端,都对同一个数据库进行操作,如果事务a开启后未提交,但他的数据就被其他事务读取到,这样就会出现 脏读
脏读:一个事务读到了另一个事务没有提交的数据,就叫做脏读。在实际开发中是不允许出现的。
2.read committed ;读已提交----->导致不可重复读
我们先将数据库的隔离界别修改为read committed;
mysql> set transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
举个栗子:
小张要算客户钱的平均值,他打开自己的终端查看了一下数据;然后他就去上厕所了
mysql> select * from user;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+----+--------+-------+
4 rows in set (0.00 sec)
此时小王打开了自己的终端,干了另一件事,新插入了一个客户的信息
mysql> insert into user values (5,'c',1000);
Query OK, 1 row affected (0.01 sec)
mysql> insert into user values (6,'d',800);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 1000 |
| 6 | d | 800 |
+----+--------+-------+
6 rows in set (0.00 sec)
但小张不知道啊,小张回来了一算平均值,发现跟预期的1000不一样,这就有问题了
mysql> select avg(money) from user;
+------------+
| avg(money) |
+------------+
| 966.6667 |
+------------+
1 row in set (0.01 sec)
这里虽然我们可以克服脏读的影响,但是会导致读取同一个表中的数据前后不一致的问题,造成不可重复读现象,这在现实中是不允许的
- read committed ;虽然能解决脏读问题,但是会带来不可重复读问题
3.repeatable read;可以重复读------>导致幻读
将隔离级别设置为可以重复读
mysql> set transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
举个栗子:
小张在成都对数据库进行操作,开启事务之后向数据库表中插入数据7
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user values(7,'e',666);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 1000 |
| 6 | d | 800 |
| 7 | e | 666 |
+----+--------+-------+
7 rows in set (0.00 sec)
小王在上海对同一个数据库进行操作,看一下表,发现没有数据7,所以开启事务准备插入数据7
mysql> select * from user;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 1000 |
| 6 | d | 800 |
+----+--------+-------+
6 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
插入数据7,却发现数据7已经存在了
mysql> insert into user values(7,'e',666);
ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'
这种情况就是幻读:事务啊和事务b同时对一张表进行操作,A提交的数据,也不能被B读到,造成了幻读现象。
4、serializable;串行化
修改当前数据库隔离级别为串行化
mysql> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| SERIALIZABLE |
+--------------------------------+
1 row in set (0.00 sec)
串行化:当A开启事务时,B不能对该事务进行操作,B的sql语句会卡住,进入等待状态;当a的事务被提交之后,B才能再对该表进行操作,前提是B没有超过等待时间,在a提交之后,之前被卡主的B的sql语句很快就会被执行了。
9.小结:
- 串行化可以解决脏读,幻读,不可重复读问题,但是串行化性能特别差!
- 隔离级别越高,性能越差:
- 性能: read uncommitted>read committed>repeatable read>serializable
- mysql的默认隔离级别是 :repeatable read(可重复读)