第八篇：数据库事务 - 图1

一 事务介绍

1、什么是事务:
事务(Transaction)，顾名思义就是要做的或所做的事情，数据库事务指的则是作为单个逻辑工作单元执行的一系列操作（SQL语句）。这些操作要么全部执行，要么全部不执行。
2、为什么需要事务
把一系列sql放入一个事务中有两个目的：

  1. 1、为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
  2. 2、当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

当一个事务被提交给了DBMS（数据库管理系统），则DBMS需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中，如果事务中有的操作没有成功完成，则事务中的所有操作都需要被回滚，回到事务执行前的状态（要么全执行，要么全都不执行）;同时，该事务对数据库或者其他事务的执行无影响，所有的事务都好像在独立的运行。
但在现实情况下，失败的风险很高。在一个数据库事务的执行过程中，有可能会遇上事务操作失败、数据库系统/操作系统失败，甚至是存储介质失败等情况。这便需要DBMS对一个执行失败的事务执行恢复操作，将其数据库状态恢复到一致状态（数据的一致性得到保证的状态）。为了实现将数据库状态恢复到一致状态的功能，DBMS通常需要维护事务日志以追踪事务中所有影响数据库数据的操作。
经典的银行转账例子

  1. 1、为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
  2. 2、当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

事务管理是每个数据库（oracle、mysql、db等）都必须实现的。

二 事务的4个特性

这四个特性通常称为ACID特性

  1. # 1、原子性（Atomicity）
  2. 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
  4. # 2、一致性（Consistency）
  5. 事务应确保数据库的状态从一个一致状态转变为另一个一致状态，例如转账行为中，一个人减了50元，另外一个人就应该加上这50元，而不能是40元。
  6. 其他一致状态的含义是数据库中的数据应满足完整性约束,例如字段约束不能为负数，事务执行完毕后的该字段也同样不是负数
  8. # 3、隔离性（Isolation）
  9. 多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
  11. # 4、持久性（Durability）
  12. 一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。

举例说明ACID
用一个常用的“A账户向B账号汇钱”的例子来说明如何通过数据库事务保证数据的准确性和完整性。熟悉关系型数据库事务的都知道从帐号A到帐号B需要6个操作：
1、从A账号中把余额读出来（500）。
2、对A账号做减法操作（500-100）。
3、把结果写回A账号中（400）。
4、从B账号中把余额读出来（500）。
5、对B账号做加法操作（500+100）。
6、把结果写回B账号中（600）。

  1. # 1、原子性：
  2. 保证1-6所有过程要么都执行，要么都不执行。一旦在执行某一步骤的过程中发生问题，就需要执行回滚操作。 假如执行到第五步的时候，B账户突然不可用（比如被注销），那么之前的所有操作都应该回滚到执行事务之前的状态。
  4. # 2、一致性
  5. 在转账之前，A和B的账户中共有500+500=1000元钱。在转账之后，A和B的账户中共有400+600=1000元。也就是说，数据的状态在执行该事务操作之后从一个状态改变到了另外一个状态。同时一致性还能保证账户余额不会变成负数等。
  7. # 3、隔离性
  8. 在A向B转账的整个过程中，只要事务还没有提交（commit），查询A账户和B账户的时候，两个账户里面的钱的数量都不会有变化。
  9. 如果在A给B转账的同时，有另外一个事务执行了C给B转账的操作并提交了，虽然A给B转账的事务里看不到最新修改的数据，但是当两个事务都提交完的时候，B账户里面的钱应该是A转给B的钱加上C转给B的钱再加上自己原有的钱。
  11. # 4、持久性
  12. 一旦转账成功（事务提交），两个账户的里面的钱就会真的发生变化（会把数据写入数据库做持久化保存）！
  14. # 强调：原子性与隔离性
  15. 一致性与原子性是密切相关的,原子性的破坏可能导致数据库的不一致，数据的一致性问题并不都和原子性有关。
  16. 比如刚刚的例子，在第五步的时候，对B账户做加法时只加了50元。那么该过程可以符合原子性，但是数据的一致性就出现了问题。
  18. 因此，事务的原子性与一致性缺一不可。

案例：验证事务的一致性
准备表：

  • sql ```python create table employee( id int primary key auto_increment, name varchar(20) not null, age int(3) unsigned not null default 20 );

insert into employee(name) values (‘xio’), (‘jion’), (‘wupeiqi’), (‘yuanhao’), (‘liwenzhou’), (‘jingliyang’), (‘jinxin’), (‘成龙’), (‘歪歪’), (‘丫丫’), (‘丁丁’), (‘星星’), (‘格格’), (‘张野’), (‘程咬金’), (‘程咬银’), (‘程咬铜’), (‘程咬铁’) ; update employee set age = 18 where id <=3;

  1. 实验：
  3. |   | ** 事务一** | ** 事务二** |
  4. | --- | --- | --- |
  5. |  步骤1 |  start transaction;<br />select * from employee where name = "xio"; -- 对应的age = 18 |  start transaction;<br />select * from employee where name = "xio"; -- 对应的age = 18 |
  6. |   步骤2 |   |  update employee set age=age+1 where name = "xio";<br />commit;  -- 修改xio的年龄为19岁，并提交<br />select * from employee where name = "xio"; -- 对应的age变为19 |
  7. |   步骤3 | -- 在事务二commit之后，重新查询，发现对应的age 仍为18 <br />select * from employee where name = "xio";  |   |
  8. |   步骤4 | -- 虽然看到的age仍为18，但因为事务的一致性原则，其实此处的修改是在age=19的基础上进行的<br />update employee set age=age+1 where name = "xio";  <br /> <br />-- 查看到age变为20<br />select * from employee where name = "xio";   |   |
  9. |   步骤5 |  commit;<br />select * from employee where name = "xio";  -- age =20 |   |
  13. ---
  15. <a name="4QmWq"></a>
  16. # 三 事务的3种运行模式
  17. 隐式 == 自动<br />显式 == 手动<br /> <br />**1. 自动提交事务（隐式开启、隐式提交）**<br />每一条单独的SQL语句都在其执行完成后进行自动提交事务，即执行 SQL 语句后就会马上自动隐式执行 COMMIT 操作。如果出现错误，则进行事务回滚至之前状态。<br />SQL SERVER和MY SQL中都默认开启自动提交事务，ORACLE则显式提交事务。这三种产品都提供了各自的方式来开闭自动提交事务模式，具体如下：<br />1）MYSQL中通过下面语句来开启或关闭当前会话或全局的自动提交事务模式。
  18. ```python
  19. set session autocommit=0; -- 0是关闭，1是开启；session是设置当前会话变量，global是设置全局变量

2)SQL SERVER中使用下面语句来关闭或打开自动提交事务模式 。或者通过 Sql Server Managerment Studio，在 Tools -> Options Query Execution -> SQL Server -> ANSI 选项中勾选SET IMPLICIT_TRANSACTIONS设置开启还是关闭自动事务提交模式。

  1. SET IMPLICIT_TRANSACTIONS ON; -- ON是打开隐式事务模式或关闭自动事务模式，OFF 是关闭隐式事务模式或打开自动提交事务模式

3）ORACLE通过如下语句开启或者关闭自动提交事务模式

  1. set autocommit on; -- on是开启自动事务模式，off是关闭自动提交事务模式

2. 显式事务（显式开启、显式提交）
通过指定事务开始语句来显式开启事务来作为开始，并由以提交命令或者回滚命令来提交或者回滚事务作为结束的一段代码就是一个用户定义的显式事务。SQL SERVER、MYSQL和ORACLE都以不同的方式来开始显式事务，具体如下：
1）SQL SERVER 以BEGIN [ TRAN | TRANSACTION ] 作为开始，以COMMIT [ WORK | TRAN | TRANSACTION ] 或者 ROLLBACK [ WORK | TRAN | TRANSACTION ] 作为结束。
2）MYSQL 以START TRANSACTION | BEGIN [WORK]作为开始，以COMMIT [ WORK ] 或者 ROLLBACK [ WORK ] 作为结束。

  1. # 注意
  2. 这种方式在当你使用commit或者rollback后，事务就结束了
  3. 再次进入事务状态需要再次start transaction

3）ORACLE事务起始于第一条SQL语句的执行，不需要特别指定事务的开始和结束，一个事务结束就意味着下一事务开始。以COMMIT或不带有SAVEPOINT子句的ROLLBACK命令作为结束。

3. 隐式事务（隐式开启、显式提交）
在隐式事务中，无需使用BEGIN TRANASACTION 来开启事务，每个SQL语句第一次执行就会开启一个事务，直到用COMMIT [TRANSACTION]来提交或者ROLLBACK [TRANSACTION]来回滚结束事务。
1）SQL SERVER中使用下面语句来开启和关闭隐式事务模式。或者通过 Sql Server Managerment Studio，在 Tools -> Options Query Execution -> SQL Server -> ANSI 选项中勾选SET IMPLICIT_TRANSACTIONS设置开启还是关闭隐式事务模式。

  1. SET IMPLICIT_TRANSACTIONS ON --ON是开启，OFF是关闭

2）ORACLE默认就是隐式开启事务，显式提交事务。可以通过下面语句开启自动提交事务，以达到隐式提交事务。

  1. SET autocommit ON; -- on是开启自动事务模式，off是关闭自动提交事务模式

3）MYSQL通过关闭自动提交事务，来达到隐式开启事务，显式提交事务的目的。

  1. SET session autocommit = 0; -- 0是关闭，1是开启；session是设置当前会话变量，global是设置全局变量

总结：MYSQL 默认为每条sql开启事务，并且会在本条sql执行完毕后自动执行commit提交，若想设置手动提交，有两种方式
方式一：直接用 SET 来改变 MySQL 的自动提交模式（下述设置均为会话级别的设置）:

  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1 开启自动提交

方式二： 手动开启的事务里默认不会自动提交

  1. # 手动开启的事务里默认不会自动提交，所以我们可以将要执行的sql语句放在我们自己手动开启的事务里
  2. start transaction;
  4. update test.t1 set id=33 where name = "jack";
  6. commit;
  8. # 注意
  9. 这种方式在当你使用commit或者rollback后，事务就结束了
  10. 再次进入事务状态需要再次start transaction

案例：

  1. create table user(
  2. id int primary key auto_increment,
  3. name char(32),
  4. balance int
  5. );
  7. insert into user(name,balance)
  8. values
  9. ('wsb',1000),
  10. ('xio',1000),
  11. ('ysb',1000);
  13. #原子操作
  14. start transaction;
  15. update user set balance=900 where name='wsb'; #买支付100元
  16. update user set balance=1010 where name='xio'; #中介拿走10元
  17. update user set balance=1090 where name='ysb'; #卖家拿到90元
  18. commit;
  20. #出现异常，回滚到初始状态
  21. start transaction;
  22. update user set balance=900 where name='wsb'; #买支付100元
  23. update user set balance=1010 where name='xio'; #中介拿走10元
  24. uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到
  25. rollback;
  26. commit;
  27. mysql> select * from user;
  28. +----+------+---------+
  29. | id | name | balance |
  30. +----+------+---------+
  31. |  1 | wsb  |    1000 |
  32. |  2 | xio  |    1000 |
  33. |  3 | ysb  |    1000 |
  34. +----+------+---------+
  35. 3 rows in set (0.00 sec)
  • pymysql实现事务处理
    1. try:
    2.   cursor.execute(sql_1)  
    3.   cursor.execute(sql_2)  
    4.   cursor.execute(sql_3)  
    5. except Exception as e:
    6.   connect.rollback()  # 事务回滚
    7.   print('事务处理失败', e)
    8. else:
    9.   connect.commit()  # 事务提交
    10.   print('事务处理成功', cursor.rowcount)# 关闭连接
    11. cursor.close()
    12. connect.close()

四 事务保存点

savepoint和虚拟机中的快照类似，用于事务中，没设置一个savepoint就是一个保存点，当事务结束时会自动删除定义的所有保存点，在事务没有结束前可以回退到任意保存点

  1. 1、设置保存点savepoint 保存点名字
  2. 2、回滚到某个保存点，该保存点之后的操作无效，rollback 某个保存点名
  3. 3、取消全部事务，删除所有保存点rollback
  5. # 注意：rollback和commit都会结束掉事务，这之后无法再回退到某个保存点

实验
1、设置保存点

  1. mysql> select * from employee;
  2. +----+-----------+-----+
  3. | id | name      | age |
  4. +----+-----------+-----+
  5. |  1 | xio       |  16 |
  6. |  2 | jion      |  18 |
  7. |  3 | wupeiqi   |  18 |
  8. |  4 | yuanhao   |  18 |
  9. |  5 | liwenzhou |  20 |
  10. +----+-----------+-----+
  11. 5 rows in set (0.00 sec)
  13. mysql> begin;
  14. Query OK, 0 rows affected (0.00 sec)
  16. mysql> update employee set name="XIO_NB" where id=1;
  17. Query OK, 1 row affected (0.00 sec)
  18. Rows matched: 1  Changed: 1  Warnings: 0
  20. mysql> update employee set name="JION_SB" where id=2;
  21. Query OK, 1 row affected (0.00 sec)
  22. Rows matched: 1  Changed: 1  Warnings: 0
  24. mysql> update employee set name="WXX" where id=3;
  25. Query OK, 1 row affected (0.01 sec)
  26. Rows matched: 1  Changed: 1  Warnings: 0
  28. mysql> savepoint one;  -- 保存点one
  29. Query OK, 0 rows affected (0.00 sec)
  31. mysql> select * from employee;
  32. +----+-----------+-----+
  33. | id | name      | age |
  34. +----+-----------+-----+
  35. |  1 | XIO_NB    |  16 |
  36. |  2 | JION_SB   |  18 |
  37. |  3 | WXX       |  18 |
  38. |  4 | yuanhao   |  18 |
  39. |  5 | liwenzhou |  20 |
  40. +----+-----------+-----+
  41. 5 rows in set (0.00 sec)
  43. mysql> update employee set name="yxx_sb" where id=4;
  44. Query OK, 1 row affected (0.00 sec)
  45. Rows matched: 1  Changed: 1  Warnings: 0
  47. mysql> update employee set name="lxx" where id=5;
  48. Query OK, 1 row affected (0.00 sec)
  49. Rows matched: 1  Changed: 1  Warnings: 0
  51. mysql> savepoint two;  -- 保存点two
  52. Query OK, 0 rows affected (0.00 sec)
  54. mysql> select * from employee;
  55. +----+---------+-----+
  56. | id | name    | age |
  57. +----+---------+-----+
  58. |  1 | XIO_NB  |  16 |
  59. |  2 | JION_SB |  18 |
  60. |  3 | WXX     |  18 |
  61. |  4 | yxx_sb  |  18 |
  62. |  5 | lxx     |  20 |
  63. +----+---------+-----+
  64. 5 rows in set (0.00 sec)
  66. mysql> insert into employee values(6,"xioxxx",19);
  67. Query OK, 1 row affected (0.00 sec)
  69. mysql> savepoint three;  -- 保存点three
  70. Query OK, 0 rows affected (0.00 sec)
  72. mysql> select * from employee;
  73. +----+---------+-----+
  74. | id | name    | age |
  75. +----+---------+-----+
  76. |  1 | XIO_NB  |  16 |
  77. |  2 | JION_SB |  18 |
  78. |  3 | WXX     |  18 |
  79. |  4 | yxx_sb  |  18 |
  80. |  5 | lxx     |  20 |
  81. |  6 | xioxxx  |  19 |
  82. +----+---------+-----+
  83. 6 rows in set (0.00 sec)
  84. mysql> insert into employee values(7,"xio666",20);
  85. Query OK, 1 row affected (0.00 sec)
  87. mysql> savepoint four;  -- 保存点four
  88. Query OK, 0 rows affected (0.00 sec)
  90. mysql> select * from employee;
  91. +----+---------+-----+
  92. | id | name    | age |
  93. +----+---------+-----+
  94. |  1 | XIO_NB  |  16 |
  95. |  2 | JION_SB |  18 |
  96. |  3 | WXX     |  18 |
  97. |  4 | yxx_sb  |  18 |
  98. |  5 | lxx     |  20 |
  99. |  6 | xioxxx  |  19 |
  100. |  7 | xio666  |  20 |
  101. +----+---------+-----+
  102. 7 rows in set (0.00 sec)

回退到指定保存点，注意一旦回退到某个保存点，该保存点之后的操作都撤销了包括保存点，例如

  1. mysql> rollback to three;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> select * from employee;
  5. +----+---------+-----+
  6. | id | name    | age |
  7. +----+---------+-----+
  8. |  1 | XIO_NB  |  16 |
  9. |  2 | JION_SB |  18 |
  10. |  3 | WXX     |  18 |
  11. |  4 | yxx_sb  |  18 |
  12. |  5 | lxx     |  20 |
  13. |  6 | xioxxx  |  19 |
  14. +----+---------+-----+
  15. 6 rows in set (0.00 sec)
  17. mysql> rollback to four;  -- 保存点four不复存在
  18. ERROR 1305 (42000): SAVEPOINT four does not exist

可以跨越n个保存点

  1. mysql> rollback to one;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> select * from employee;
  5. +----+-----------+-----+
  6. | id | name      | age |
  7. +----+-----------+-----+
  8. |  1 | XIO_NB    |  16 |
  9. |  2 | JION_SB   |  18 |
  10. |  3 | WXX       |  18 |
  11. |  4 | yuanhao   |  18 |
  12. |  5 | liwenzhou |  20 |
  13. +----+-----------+-----+
  14. 5 rows in set (0.00 sec)

回退所有

  1. mysql> rollback;
  2. Query OK, 0 rows affected (0.01 sec)
  4. mysql> select * from employee;
  5. +----+-----------+-----+
  6. | id | name      | age |
  7. +----+-----------+-----+
  8. |  1 | xio       |  16 |
  9. |  2 | jion      |  18 |
  10. |  3 | wupeiqi   |  18 |
  11. |  4 | yuanhao   |  18 |
  12. |  5 | liwenzhou |  20 |
  13. +----+-----------+-----+
  14. 5 rows in set (0.00 sec)

五 事务的使用原则

  1. 保持事务短小
  2. 尽量避免事务中rollback
  3. 尽量避免savepoint
  4. 显式声明打开事务
  5. 默认情况下，依赖于悲观锁，为吞吐量要求苛刻的事务考虑乐观锁
  6. 锁的行越少越好，锁的时间越短越好