一、

1、

#事务
    一个或一组SQL要么全部执行，要么全部不执行

 

#事务的ACID属性
#原子性（Atomicity）
        一个事务是一个整体 不可拆分 要么都成功 要么都失败

#一致性（Consistency）
       事务必须使数据库从一个一致性状态，变换到另一个一致性状态
            也就是本来两个人金额总和为200，转账后也得是200

#隔离性（Isolation）
    两个事务相互隔离，并发执行的各个事务不能相互干扰

#持久性（Durability）
    一个事务一但被提交，它对数据库中的数据的改变是永久性的

事务的ACID属性

 

#start transaction;  开启事务

update user set balance = balance-100 where name = 'hby';
update user set balance = balance-100 where name = 'pdun';   

#rollback;  #全部撤销（回滚）

#commit;
注意：commit前可以回滚，一旦执行了commit，将无法回滚

------------------------------------------------------------------------

#start transaction;   

update user set balance = balance-100 where name = 'hby';
update user set balance = balance+100 where name = 'pdun';   

#savepoint p;     #创建保存点，可以选择回滚到此处，注意：一定取名字

update user set balance = balance+100 where name = 'pdun';  

#rollback to p;

 

 

 

#脏读，幻读，不可重复读都有


mysql> select @@tx_isolation;       #查看隔离级别
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)


#设置隔离级别set session transaction isolation level
mysql>  set session transaction isolation level read uncommitted;  #读不提交


mysql> select * from user;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | pdun  |    1000 |
|  2 | Moker |    1000 |
+----+-------+---------+
2 rows in set (0.00 sec)


mysql> start transaction;               #开启事务，
Query OK, 0 rows affected (0.00 sec)

#修改但未commit
mysql> update user set balance = balance+100 where name = 'pdun';  



-------------------------------------------
#此时打开另一个终端2，同样把隔离基别设置为最轻等级
mysql> start transaction;               #开启事务，
mysql> select * from user;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | pdun  |    1100 |      #发现数据已经改了，但是终端1并未提交，这就是脏读
|  2 | Moker |    1000 |
+----+-------+---------+

#如果终端1 rollback，回滚了
mysql> select * from user;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | pdun  |    1000 |             #数据又变回来了，这就称为不可重复读和幻读
|  2 | Moker |    1000 |
+----+-------+---------+
2 rows in set (0.00 sec)

uncommitted等级

 

#没有脏读，存在幻读与不可重复读

#开启终端1
#设置隔离级别
mysql> set session transaction isolation level read committed;

mysql> select *from user;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | pdun  |    1000 |
|  2 | Moker |    1000 |
+----+-------+---------+
2 rows in set (0.00 sec)

mysql> start transaction;   #开启事务
Query OK, 0 rows affected (0.00 sec)

mysql>  update user set balance = balance+100 where name = 'Moker';
#没有commit提交

----------------------------------
#开启终端2

mysql> select * from user;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | pdun  |    1000 |       #没有脏读
|  2 | Moker |    1000 |
+----+-------+---------+

#如果终端1此时提交了

mysql> select * from user;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | pdun  |    1000 |
|  2 | Moker |    1100 |     #终端2事务并没提交，却改变了，出现了幻读
+----+-------+---------+
2 rows in set (0.00 sec)

read committed级别

 

不存在脏读和不可重复读，存在脏读

开启终端1
#修改隔离等级
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>  select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

mysql> select *from user;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | pdun  |    1100 |           #查看的一共两条数据，按理说应该两行收影响
|  2 | Moker |    1100 |
+----+-------+---------+
2 rows in set (0.00 sec)

mysql> start transaction;

mysql>  update user set balance = balance+100;  #这一步先不运行


---------------------------------
#开启终端2
mysql> set session transaction isolation level repeatable read;

mysql>  start transaction;
Query OK, 0 rows affected (0.12 sec)

mysql>  insert into user(name,balance)
    ->  values
    ->  ('Bingo',1000);
Query OK, 1 row affected (0.00 sec)

mysql> commit;        #插入一个字段并提交
Query OK, 0 rows affected (0.13 sec)    

-----------------------------------------------------
mysql>  update user set balance = balance+100;  #运行这一步
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0    #3行受影响，这就是幻读

repeatable read等级