SQL语句高级（大结局）——ACID和隔离性（隔离级别）

一、事务——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