事务控制语言

事务控制语言
事务是指一组语句，要么都执行，要么都不执行
mysql> create table bank(
    -> id int primary key,
    -> name varchar(20),
    -> balance int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into bank values
    -> (1,'tom',10000),(2,'jreey',10000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

A  (Atomicity)    原子性
正常
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update bank set balance=balance-1000 where name='tom';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update bank set balance=balance+1000 where name='jreey';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |    9000 |
|  2 | jreey |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)
在另一个终端查看
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |   10000 |
|  2 | jreey |   10000 |
+----+-------+---------+
2 rows in set (0.00 sec)
回到终端提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
在另一个终端查看
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |    9000 |
|  2 | jreey |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)
异常
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> update bank set balance=balance-1000 where name='jreey';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update bank set balance=balance+1000 where name='tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select  * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |   10000 |
|  2 | jreey |   10000 |
+----+-------+---------+
2 rows in set (0.00 sec)
在另一个终端查看
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |    9000 |
|  2 | jreey |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)
mysql> rollback;                 回滚
Query OK, 0 rows affected (0.01 sec)
在另一个终端查看
mysql> select * from bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | tom   |    9000 |
|  2 | jreey |   11000 |
+----+-------+---------+
2 rows in set (0.00 sec)
查看当前事务隔离级别
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
设置隔离事务级别
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
C（consistency）一致性
I   (Isolation)      隔离性
脏读 
幻读 
可重复读
不可重复读
事务隔离级别
读未提交（READ UNCOMMITTED）
读提交 （READ COMMITTED）
可重复读 （REPEATABLE READ）默认级别
串行化 （SERIALIZABLE）

D (Durability)     持久性
SAVEPOINT应用
基本用法
使用mysql中的savepoint保存点来实现事务的部分回滚
语法：
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
使用 SAVEPOINT identifier 来创建一个名为identifier的回滚点
ROLLBACK TO identifier，回滚到指定名称的SAVEPOINT，这里是identifier
使用 RELEASE SAVEPOINT identifier 来释放删除保存点identifier
如果当前事务具有相同名称的保存点，则将删除旧的保存点并设置一个新的保存点。
如果执行START TRANSACTION，COMMIT和ROLLBACK语句，则将删除当前事务的所有保存点。