mysql事务
mysql中,事务是一个最小的不可分割的单元, 事务能偶保证一个业务的完整性。
比如银行转账:
a -> -100
update user set money=money-100 where name='a';
b -> +100
update user set money=money+100 where name='b';
在实际的语句中,要是一条成功了。另外一条失败,则出现数据前后不一致的问题。
因此对于一个事务,里的多条语句,要么同时成功,要是全部失败。
mysql 中控制事务
- mysql 默认是开始事务的(自动提交)。
select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
– 当我们执行一个sql语句时,效果会立即体现,且不能回滚。
create table user(
id int primary key,
name varchar(20),
money int
);
insert into user values(1,'a',1000);
– 事务回滚:撤销sql语句执行效果
rollback;
– 设置mysql自动提交为false(0)。
set autocommit=0;
– 上面的操作,关闭了 mysql的自动提交(commit)。
insert into user values(2,'b',1000);
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
rollback;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
+----+------+-------+
– 插入,手动提交,再回滚不可撤销。
insert into user values(2,'b',1000);
commit;
rollback;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
– 自动提交 @@autocommit=0;
– 手动提交 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 |
+----+------+-------+
– 事务提供了一个返回的机会。
– 设置事务自动提交开始。
set autocommit=1;
– 查询事务自动提交操作。
select @@autocommit;
– 手动开启事务(1)。
begin;
update user set money=money-100 where name='a';
update user set money=money+100 where name='b';
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
+----+------+-------+
rollback;
select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
– 手动开始事务(2).
start transaction;
update user set money=money-100 where name='a';
update user set money=money+100 where name='b';
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 900 |
| 2 | b | 1100 |
+----+------+-------+
rollback;
select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
+----+------+-------+
事务的四大特征:
A 原子性:事务是最小的单位,不可以再分割。
C 一致性:事务要求,同一事物的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);
select * from user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+----+-----------+-------+
– 如何查看数据库的隔离级别?
mysql 8.0
– 系统级别的
select @@global.transaction_isolation;
-- mysql默认级别
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ |
+--------------------------------+
– 会话级别的
select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| 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元。
小明 -> 成都 ATM
淘宝店 -> 广州 ATM
— 前面已经设置 隔离级别为 read uncommitted。
start transaction;
update user set money=money-800 where name='小明';
update user set money=money+800 where name='淘宝店';
select * from user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
+----+-----------+-------+
— 给淘宝店打电话,看看是否到账.看到1800,完成。
select * from user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
+----+-----------+-------+
— 这时,小明执行。
rollback;
select * from user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+----+-----------+-------+
— 出事情,淘宝店,钱没了。
— 如果在两个不同的地方,都进行操作,事务a 开启后,他的数据可以被其他事事务读到。
— 这样就会出现 脏读。
— 脏读:一个事务读到了另一个事务没有提交的数据。
2. read committed; 读己经提交的
— 修改隔离级别为:read committed
set global transaction isolation level read committed;
select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-COMMITTED |
+--------------------------------+
— user表。
— 小张:银行会计。
start transaction;
select * from user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
+----+-----------+-------+
— 小张出去上厕所。。。。抽烟。
— 小王:操作了一下。
start transaction;
insert into user values(5,'c',100);
commit;
select * from user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
+----+-----------+-------+
— 小张回来了,办事:算一下平均数。
select avg(money) from user;
+------------+
| avg(money) |
+------------+
| 820.0000 |
+------------+
— money的平均值变小了。
— 虽然只能读到另外一个事务提交之后的数据,但还是会出现对于同一张表,
— 先后读取数据不一致。
— 不可重复读现象:read committed。
3. repeatable read; 可以重复读
— 修改隔离级别。
set global transaction isolation level repeatable read;
select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ |
+--------------------------------+
— 张全蛋-成都
start transaction;
— 王尼玛-北京
start transaction;
— 张全蛋-成都
insert into user values(6,'d',1000);
commit;
select * from user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
+----+-----------+-------+
— 王尼玛-北京
select * from user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
+----+-----------+-------+
insert into user values(6,'d',1000);
ERROR 1062 (23000): Duplicate entry '6' for key 'user.PRIMARY'
— 这种现象叫做幻读!!!
— 事务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 | 1000 |
| 2 | b | 1000 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
+----+-----------+-------+
— 张全蛋-成都
start transaction;
— 王尼玛-北京
start transaction;
— 张全蛋-成都
insert into user values(7,'赵铁柱',1000);
commit;
— 王尼玛-北京
select * from user;
+----+-----------+-------+
| id | name | money |
+----+-----------+-------+
| 1 | a | 1000 |
| 2 | b | 1000 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
| 7 | 赵铁柱 | 1000 |
+----+-----------+-------+
— 张全蛋-成都
—sql语句卡住了。
insert into user values(8,'王小花',1000);
— 当user表被另一个事务操作的时候,其他事物里面的写操作,是不可以进行的。
— 进入排队状态(串行化),知道王尼玛那边事务结束之后,张全蛋这个写操作才可以继续执行。
— 前提是没有超时。
— 串行化问题:性能特差!!!
性能比较
随着隔离级别的升高,性能降低,出现的问题越少。
read uncommitted > read committed > repeatable read > serializable;
— mysql 默认的隔离级别是repeatable read。