mysql事务
mysql中,事务其实是一个最小的不可分割的工作单元,事务能够保证一个业务的完整性。
比如我们的银行转账:
a->-100
update user set money=money-100 where name=‘a’;
b->+100
update user set money=money+100 where name=‘b’;
实际的程序中,如果只有一条语句执行成功了,而另一条没有执行成功,就会出现数据前后不一致。
update user set money=money-100 where name=‘a’;
update user set money=money+100 where name=‘b’;
多条sql语句,可能会有同时成功的要求,要么就同时失败。
mysql中如何控制事务
mysql是默认开启事务大的(自动提交)。
select @@autocommit;
默认事务开启的作用是什么?
当我们去执行一个sql语句的时候,效果会立即体现出来,而且不能回滚。
create database bank;
create table user(
id int primary key,
name varchar(20),
money int
);
insert into user values(1,‘a’,1000);
事务回滚:撤销sql语句执行效果。
rollback;
设置mysql自动提交为false
set autocommit=0;
上面的操作关闭了mysql的自动提交(commit)
insert into user values(2,‘b’,1000);
rollback;
手动提交数据commit,手动提交数据之后,再撤销,是不可以撤销的。(持久性)
commit;
事务自动提交?@@autocommit=1
事务手动提交?commit
事务回滚?rollback
如果说这时候转账:
update user set money=money-100 where name=‘a’;
update user set money=money+100 where name=‘b’;
rollback;
事务给我们提供了一个返回的机会。
set autocommit=1;
begin 或者start transaction 都可以帮我们手动开启一个事务;
手动开启事务回滚(一)
begin;
update user set money=money-100 where name=‘a’;
update user set money=money+100 where name=‘b’;
手动开启事务回滚(二)
start transaction;
update user set money=money-100 where name=‘a’;
update user set money=money+100 where name=‘b’;
事务开启之后,一旦commit提交,就不可以回滚(也就是当前的这个事务在提交的时候就结束了)。
事务四大特征
A原子性:事务是最小的单位,不可以再分割。
C一致性:事务要求,同一事物中的sql语句,必须保证同时成功或者同时失败。
I隔离性:事务1和事务2之间是具有隔离性的。
D持久性:事务一旦结束(commit),就不可以返回。
事务开启的三种方式:
1、修改默认提交 set autocommit=0;
2、begin;
3、start transaction
事务手动提交:
commit;
事务手动回滚:
rollback;
事务隔离性
1、read uncommitted 读未提交的;
2、read committed 读已经提交的;
3、repeatable read 可以重复读;
4、serializable 串行化;
read uncommitted
事务a和事务b:a事务对数据进行操作,在操作的过程中,事务没有被提交,但是b可以看见a操作的结果。
bank数据库,user类
insert into user values(3,‘小明’,1000);
insert into user values(4,‘淘宝店’,1000);
如何查看数据库的隔离级别:
–系统级别的:
select @@global.transaction_isolation;
–会话级别的:
select @@transaction_isolation;
如何修改隔离级别?
set global transaction isolation level read uncommitted;
脏读
小明在淘宝店买鞋子800块:
小明-》成都 ATM;
淘宝店-》广州 ATM;
start transaction;
update user set money=money-800 where name=‘小明’;
update user set money=money+800 where name=‘淘宝店’;
–给淘宝店打电话,说你去查一下是不是到账了。
–淘宝店在广州查账
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 1000 |
| 2 | b | 1000 |
| 3 | 小明 | 200 |
| 4 | 淘宝店 | 1800 |
±—±-------±------+
小明在成都进行回滚操作。
rollback;
淘宝店再查账:
±—±-------±------+
| id | name | money |
±—±-------±------+
| 1 | a | 1000 |
| 2 | b | 1000 |
| 3 | 小明 | 1000 |
| 4 | 淘宝店 | 1000 |
±—±-------±------+
4 rows in set (0.01 sec)
淘宝店读到了小明未提交的数据,这是脏读,是不被允许的。
–如果不同的地方,都在进行操作,如果事务a开启之后,他的数据可以被其他事务读到。
–这样就会出现脏读。
–脏读:一个事务读到了另外一个事务没有提交的数据,就叫做脏读。
–实际开发不允许脏读出现。
不可重复读
read committed; 读已经提交的;
set global transaction isolation level read committed;
bank数据库 user表
小张:银行的会计,在做财务报表
start transaction;
select* from user;
期间小张有事出去了;
小王:
start transaction;
insert into user values(5,‘c’,100);
commit;
±—±-------±------+
| 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的平均值应该为1000,变少了。
–虽然我只能读到另外一个事务提交的数据,但还是会出现问题,就是:
–读取同一个表的数据,发现前后不一致。
–不可重复读现象:read committed;
幻读
repeatable read; 可以重复读;
set global transaction isolation level repeatable read;
在repeatable隔离级别下,又会出什么问题。
–张全蛋-成都
start transaction;
–王呢玛-北京
start transaction;
–张全蛋-成都
insert into user values(6,‘d’,1000);
commit;
±—±-------±------+
| 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 |
±—±-------±------+
王尼玛查询发现没有id为6的数据。
insert into user values(6,‘d’,1000);
ERROR 1062 (23000): Duplicate entry ‘6’ for key ‘user.PRIMARY’
–这种现象就叫做幻读:
–事务a和事务b同时操作一张表,事务a提交的数据,也不能被b读到,就可以造成幻读。
串行化
set global transaction isolation level serializable;
select @@global.transaction_isolation;
–张全蛋-成都
start transaction;
insert into user values(7,‘赵铁柱’,100);
–王呢玛-北京
start transaction;
insert into user values(8,‘王小花’,200);
–发现王尼玛在进行插入操作的时候,sql语句卡住了,因为张全蛋没进行commit操作,所以王尼玛处于等待状态。
–当user表被另外一个事务操作的时候,其它事务里面的写操作,是不可以进行的。
–进入排队状态,直到张全蛋那边提交了,事务结束之后,王尼玛的写入操作才会执行。
–在没有等待超时的情况下。
—串行化的问题是,性能特差。
隔离级别越高,性能越差,mysql默认隔离级别是repeatable_read;