一、事务——ACID特征与使用
事务的四大特征:
A——原子性:事务是最小的单位,不可以再分割;
C——一致性:事务要求,同一事务中的sql语句,必须保证同时成功或者同时失败;
I——隔离性:事务1和事务2之间是具有隔离性的;(之前未提及!)
D——持久性:事务一旦结束(commit,rollback),就不可以返回。
事务开启:
1、修改默认提交: set autocommit=0;
2、begin;
3、start transaction;
事务提交:
commit;
事务手动回滚:
rollback;
二、事务——脏读
事务的隔离性:
1、read uncommitted; 读未提交的
2、read committed; 读已经提交的
3、repeatable read; 可以重复读
4、serializable;(可串行化的) 串行化
1-read uncommitted
如果有事务a,和事务b。
a事务对数据进行操作,在操作的过程中,事务没有被提交,但是b可以看见a操作的结果。
bank数据库 user表
insert into user values(3,'xiaoming',1000);
insert into user values(4,'taobaodian',1000);
mysql> select * from user;
+----+------------+-------+
| id | name | money |
+----+------------+-------+
| 1 | a | 900 |
| 2 | b | 1200 |
| 3 | xiaoming | 1000 |
| 4 | taobaodian | 1000 |
+----+------------+-------+
4 rows in set (0.10 sec)
如何查看数据库的隔离级别?
mysql 8.0:
——系统级别的(默认隔离级别:REPEATABLE-READ )
select @@global.transaction_isolation;
——会话级别的
我的不是8.0的!
select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
—— 如何修改隔离级别?
设置当前事务模式为read uncommitted(未提交读):
set session transaction isolation level read uncommitted;
转账:小明在淘宝店卖鞋子:800块钱。
小明——>成都ATM
淘宝店——>广州ATM
start transaction;
update user set money = money-800 where name = 'xiaoming';
update user set money = money+800 where name = 'taobaodian';
mysql> select * from user;
+----+------------+-------+
| id | name | money |
+----+------------+-------+
| 1 | a | 900 |
| 2 | b | 1200 |
| 3 | xiaoming | 200 |
| 4 | taobaodian | 1800 |
+----+------------+-------+
4 rows in set (0.00 sec)
——给淘宝店打电话,说你去查一下,是不是到账
——淘宝店在广州查账
——发货
——淘宝店晚上请女朋友吃好吃的
——1800
mysql> rollback;
Query OK, 0 rows affected (0.16 sec)
mysql> select * from user;
+----+------------+-------+
| id | name | money |
+----+------------+-------+
| 1 | a | 900 |
| 2 | b | 1200 |
| 3 | xiaoming | 1000 |
| 4 | taobaodian | 1000 |
+----+------------+-------+
——结账的时候发现钱不够
mysql> select * from user;
+----+------------+-------+
| id | name | money |
+----+------------+-------+
| 1 | a | 900 |
| 2 | b | 1200 |
| 3 | xiaoming | 1000 |
| 4 | taobaodian | 1000 |
+----+------------+-------+
——如果两个不同的地方都在进行操作,如果事务a开启之后,他的
数据可以被其他事务读取到
——这样就会出现(脏读)
——脏读:一个事务读到了另外一个事务没有提交的数据,就叫做脏读
——实际开发是不允许脏读出现的!!!!!!!!
二、read committed; 读已经提交的
set global transaction isolation level read committed;
select @@global.transaction_isolation;
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
1 row in set (0.00 sec)
——修改隔离级别为READ-COMMITTED:
mysql> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED |
+-----------------------+
bank数据库 user表
小张:银行的会计
start transaction;
select * from user;
+----+------------+-------+
| id | name | money |
+----+------------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | xiaoming | 1000 |
| 4 | taobaodian | 1000 |
+----+------------+-------+
小张出去上厕所去了。。。抽烟
小王:
start transaction;
insert into user values(5,'c',100);
commit;
+----+------------+-------+
| id | name | money |
+----+------------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | xiaoming | 1000 |
| 4 | taobaodian | 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;
select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+
——在REPEATABLE-READ隔离级别下又会出现什么问题?
select * from user;
+----+------------+-------+
| id | name | money |
+----+------------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | xiaoming | 1000 |
| 4 | taobaodian | 1000 |
| 5 | c | 100 |
+----+------------+-------+
——张全蛋—成都
start transaction;
——王尼玛—北京
start transaction;
——张全蛋—成都
insert into user values(6,'d',1000);
mysql> select * from user;
+----+------------+-------+
| id | name | money |
+----+------------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | xiaoming | 1000 |
| 4 | taobaodian | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
+----+------------+-------+
——王尼玛—北京
insert into user values(6,'d',1000);
mysql> select * from user;
+----+------------+-------+
| id | name | money |
+----+------------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | xiaoming | 1000 |
| 4 | taobaodian | 1000 |
| 5 | c | 100 |
+----+------------+-------+
mysql> insert into user values(6,'d',1000);
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
——这种现象就叫做幻读
——事务a操作和事务b同时操作一张表,事务a提交的数据,也不能被事务b读到,就可能造成幻读。
(这其实是一个select和insert之前时间误差问题:a先select之后b再insert,结果a插不进去
和b插的相同的key去了。。。。。)
四、serializable;(可串行化的) 串行化
set global transaction isolation level serializable;
——修改隔离级别为串行
select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| SERIALIZABLE |
+-----------------------+
mysql> select * from user;
+----+------------+-------+
| id | name | money |
+----+------------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | xiaoming | 1000 |
| 4 | taobaodian | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
+----+------------+-------+
——张全蛋—成都
start transaction;
——王尼玛—北京
start transaction;
——张全蛋—成都
mysql> insert into user values(7,'zhao',1000);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from user;
+----+------------+-------+
| id | name | money |
+----+------------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | xiaoming | 1000 |
| 4 | taobaodian | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
| 7 | zhao | 1000 |
+----+------------+-------+
——王尼玛—北京
mysql> select * from user;
+----+------------+-------+
| id | name | money |
+----+------------+-------+
| 1 | a | 800 |
| 2 | b | 1200 |
| 3 | xiaoming | 1000 |
| 4 | taobaodian | 1000 |
| 5 | c | 100 |
| 6 | d | 1000 |
| 7 | zhao | 1000 |
+----+------------+-------+
——张全蛋—成都
start transaction;
插入这句的时候这句会卡主!!!因为这是串行化了!
必须等待对方commit提交!
mysql> insert into user values(8,'wang',1000);
——当user表被另外一个事务操作的时候,其他事务里面的写操作,
是不可以进行的。
——进入排队状态(串行化),直到王尼玛那边事务结束之后,张全蛋这个
的写入操作才会执行。
——在没有等待超时的情况下。
——王尼玛—北京
mysql> commit;
Query OK, 0 rows affected (0.06 sec)
——张全蛋—成都
Query OK, 1 rows affected (7.06 sec)
——串行化问题是:性能特差!!!
READ-UNCOMMITTED(问题越多) > READ-COMMITTED(出现不可重复问题) > REPEATABLE-READ(出现幻读问题) > SERIALIZABLE;
——隔离级别越高,性能越差!
mysql默认隔离级别是 REPEATABLE-READ
SQL语句高级(大结局)——ACID和隔离性(隔离级别)
最新推荐文章于 2023-12-21 18:35:23 发布