MySQL事务和隔离级别

浏览数：19 / 时间：2015年06月12日

理论部分：

事务是什么：多个操作当成一个处理单元来对待，要么同时执行，要么都不执行
事务的好处：并发执行
   1、提高吞吐量和资源利用率
   2、减少等待时间
事务的缺点：事务是可以进行同时执行的，如果两个事务同时操作了一张表就可能产生交互
如果A账户有3000块，B账户有两千块，A和B加起来一共有5000块
正常的事务：A转账500到B那么A和B都有了2500块，A和B加起来一共有5000块
不正常的事务：A转账500到B账户的过程中，如果有第二个事务进行计算了A和B两个事务的总和，A和B加起来一共有4500块。
如果A账户在减去500的时候B还没有加上，这个时候第二个事务启动了他计算了A+B=4500，并且在4500的基础上面做了一些统计更新了另外一个事务，这个时候就麻烦了，如果想解决这种情况的发生需要隔离事务。

如果想减少事务交互带来的影响，就需要使用隔离

隔离：
隔离级别：MySQL模式隔离级别是REPATABLE READ（可重读）
        READ UNCOMMITTED：读未提交（读别人尚未提交的数据）    隔离级别最低
           如果用户A删除一行数据，B用户马上可以看到数据被删除，B写入一行数据，A也马上可以看到

       READ COMMITTED：读提交（读别人已经提交的数据）
          如果A用户进行删除操作，B用户进行查看操作，那么B如果在A操作没有提交前进行过一次查看操作，数据是存在的，但是A提交之后，B在查看数据是不存在的（容易产生幻读）
       REPATABLE READ：可重读隔离级别高
           不管A进行的是什么操作，B在事务开始和结束时看到的数据都是一样的
            如果B进行操作之前表有30行，B添加了10行，那么B执行完成操作，表应该有40行，可以在B执行的过程中A删除了20行，那么B执行完成后，查看数据只有20行（还是会产生幻读）
       SERIABLIZABLE：可串行，A用户执行的时候上锁，B用户就不能执行，如果B想执行就只能等A用户放开锁
注意：如果对并发处理的要求不是很高，可以调低隔离级别，这样性能会提高性能

操作部分：
查看默认的隔离级别
mysql> SHOW GLOBAL VARIABLES LIKE ‘%iso%‘;
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

修改隔离级别的值
mysql> SET tx_isolation=‘READ-UNCOMMITTED‘;
mysql> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+

事务的执行：
START TANSACTION：启动事务
   SQL
   SQL
COMMIT：提交事务
ROLLBACK: 回滚事务

例：如果启动一个事务，在事务中删除了数据，但是没有提交事务，而是选择回滚了事务，那么删除操作就会撤销
mysql> START TRANSACTION; #启动一个事务
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze | F      |   49 |
| 10 | jerry        | M      |   50 |
+-----+--------------+--------+------+
10 rows in set (0.01 sec)

mysql> DELETE FROM tutors WHERE Tname LIKE ‘H%‘; #删除H开头的用户
Query OK, 3 rows affected (0.01 sec)

mysql> SELECT * FROM tutors;                     #查看用户已经被删除
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age |
+-----+--------------+--------+------+
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   9 | NingZhongze | F      |   49 |
| 10 | jerry        | M      |   50 |
+-----+--------------+--------+------+
7 rows in set (0.00 sec)

mysql> ROLLBACK;   #回滚事务
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM tutors;    #回滚后发现删除的用户还在
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age |
+-----+--------------+--------+------+
|   1 | HongQigong   | M      |   93 |
|   2 | HuangYaoshi | M      |   63 |
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   8 | HuYidao      | M      |   42 |
|   9 | NingZhongze | F      |   49 |
| 10 | jerry        | M      |   50 |
+-----+--------------+--------+------+

但是如果在事务启动中执行了事务的提交那么，数据就不存在了
事务的提交
mysql> START TRANSACTION;                       #启动事务
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM tutors WHERE Tname LIKE ‘H%‘; #删除H开头的用户名
Query OK, 3 rows affected (0.00 sec)

mysql> COMMIT;      #提交事务
Query OK, 0 rows affected (0.04 sec)       #用户不在了

mysql> SELECT * FROM tutors;
+-----+--------------+--------+------+
| TID | Tname        | Gender | Age |
+-----+--------------+--------+------+
|   3 | Miejueshitai | F      |   72 |
|   4 | OuYangfeng   | M      |   76 |
|   5 | YiDeng       | M      |   90 |
|   6 | YuCanghai    | M      |   56 |
|   7 | Jinlunfawang | M      |   67 |
|   9 | NingZhongze | F      |   49 |
| 10 | jerry        | M      |   50 |
+-----+--------------+--------+------+

MySQL是否自动提交事务，如果没有明确启动事务，那么是自动提交MySQL语句的
mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

建议：在支持事务的引擎上，明确使用事务，并且关闭自动提交
关闭自动提交后，在执行语句的时候，如果不启动事务，那么所有的操作就会当成一个事务来进行处理

关闭自动提交
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+

验证是否关闭了事务的自动提交
查看是否关闭事务自动提交
mysql> SELECT * FROM students;   #查看表的数据
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age | Gender | CID1 | CID2 | TID | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
| 10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)

mysql> DELETE FROM tutors WHERE Tname=‘GuoJing‘;   #删除用户GuoJing
Query OK, 0 rows affected (0.00 sec)

mysql> ROLLBACK;                                  #回滚事务
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM students;                  #用户没有删除
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age | Gender | CID1 | CID2 | TID | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
| 10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)

事务支持保存点，如果执行100个事务，执行到85个的时候，发现执行的75个操作错了，如果这个时候进行撤销，那么所有的操作都撤销了
可以执行10个操作记录一次保存点，如果发现75个操作错了，可以回到70个操作，那么前面的70次操作还是有的。

保存点：SAVEPOINT 保存点名
回滚至保存点：ROLLBACK TO 保存点名

mysql> SELECT * FROM students;   #查看数据
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age | Gender | CID1 | CID2 | TID | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
|   9 | LingHuchong |   22 | M      |   11 | NULL | NULL | 2012-04-06 10:00:00 |
| 10 | YiLin        |   19 | F      |   18 | NULL | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
10 rows in set (0.00 sec)

mysql> START TRANSACTION;   #启动一个事务
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM students WHERE SID=10; #删除SID=10的那一行
Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT a;   #记录保存点a
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM students WHERE SID=9; #删除SID=9的那一行
Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT b;                     #记录保存点b
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM students WHERE SID=8; #删除SID=8的那一行
Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT c;                     #记录保存点c
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM students;           #查看数据，就可以看到SID=8、9、10的行都删除了
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age | Gender | CID1 | CID2 | TID | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+
7 rows in set (0.00 sec)

mysql> ROLLBACK TO b; #回滚至保存点b
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM students; #查看数据SID=8的那一行回来了
+-----+--------------+------+--------+------+------+------+---------------------+
| SID | Name         | Age | Gender | CID1 | CID2 | TID | CreateTime          |
+-----+--------------+------+--------+------+------+------+---------------------+
|   1 | GuoJing      |   19 | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |
|   2 | YangGuo      |   17 | M      |    2 |    3 |    1 | 2012-04-06 10:00:00 |
|   3 | DingDian     |   25 | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |
|   4 | HuFei        |   31 | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |
|   5 | HuangRong    |   16 | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |
|   6 | YueLingshang |   18 | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |
|   7 | ZhangWuji    |   20 | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |
|   8 | Xuzhu        |   26 | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |
+-----+--------------+------+--------+------+------+------+---------------------+