mysql基础教程 -- 事务

事务

数据库事务（简称：事务）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。
一般来说数据库事务由以下四个特点：

• 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
• 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
• 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

事务控制语句

• BEGIN或START TRANSACTION，显式地开启一个事务。
• COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的。
• ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
• SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT。
• RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常。
• ROLLBACK TO identifier；把事务回滚到标记点。
• SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

mysql事务处理的两种方法：

1. 用 BEGIN, ROLLBACK, COMMIT来实现：

   a. BEGIN 开始一个事务
   b. ROLLBACK 事务回滚
   c. COMMIT 事务确认

2. 开启MySQL 的手动提交模式：
   SET AUTOCOMMIT=0 禁止自动提交
   SET AUTOCOMMIT=1 开启自动提交

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。

例：

• 方法1：
  以银行转账为例，

   mysql> CREATE TABLE `account` (
   	`id` bigint(20) NOT NULL AUTO_INCREMENT, 
   	`bankNo` varchar(20),
   	`username` varchar(40), 
   	`money` float, 
   	 PRIMARY KEY (`id`));
   mysql> INSERT INTO `account` VALUES(1, '110', '张三', 10000);
   mysql> INSERT INTO `account` VALUES(2, '120', '李四', 1000);
   # 新建account表，并添加数据。
   
   mysql> START TRANSACTION;                                   
   Query OK, 0 rows affected (0.00 sec)
   
   # 张三向李四转账200
   mysql> UPDATE account SET money = money - 200 WHERE bankNo = 110;
   Query OK, 1 row affected (0.00 sec)
   Rows matched: 1  Changed: 1  Warnings: 0
   
   mysql> UPDATE account SET money = money + 200 WHERE bankNo = 120;
   Query OK, 1 row affected (0.00 sec)
   Rows matched: 1  Changed: 1  Warnings: 0
   
   #提交事务
   mysql> COMMIT;                                                                                           
   Query OK, 0 rows affected (0.01 sec)
   
   #查看转账结果，转账成功。
   mysql> SELECT * FROM account;
   +----+--------+----------+-------+                                                                    
   | id | bankNo | username | money |                                                                       
   +----+--------+----------+-------+                                                                        
   |  1 | 110    | 张三     |  9800 |                                                                       
   |  2 | 120    | 李四     |  1200 |                                                                         
   +----+--------+----------+-------+                                                                         
   2 rows in set (0.00 sec)             
   
   # 张三再向李四转账200
   mysql> START TRANSACTION;                                   
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> UPDATE account SET money = money - 200 WHERE bankNo = 110;
   Query OK, 1 row affected (0.00 sec)
   Rows matched: 1  Changed: 1  Warnings: 0
   
   #假定更新李四余额的时候出错。该命令执行失败
   mysql> UPDATE account SET money = money + 200 WHERE bankNo = 120;
   
   mysql> ROLLBACK;
   Query OK, 0 rows affected (0.00 sec)
   
   # 查看张三和李四的账户余额,转账不成功。
   mysql> SELECT * FROM account;
   +----+--------+----------+-------+                                                                    
   | id | bankNo | username | money |                                                                       
   +----+--------+----------+-------+                                                                        
   |  1 | 110    | 张三     |  9800 |                                                                       
   |  2 | 120    | 李四     |  1200 |                                                                         
   +----+--------+----------+-------+                                                                         
   2 rows in set (0.00 sec)           
  

• 方法2：
  仍然以转帐为例：

    mysql> CREATE TABLE `account` (
   		`id` bigint(20) NOT NULL AUTO_INCREMENT, 
   		`bankNo` varchar(20),
   		`username` varchar(40) ,  
   		`money` float,
   		 PRIMARY KEY (`id`));
   mysql> INSERT INTO `account` VALUES(1, '110', '张三', 10000);
   mysql> INSERT INTO `account` VALUES(2, '120', '李四', 1000);
   # 新建account表，并添加数据。
   
   #禁止自动提交
   mysql> SET AUTOCOMMIT= 0;
   Query OK, 0 rows affected (0.00 sec) 
   
   # 张三向李四转账200
   mysql> UPDATE account SET money = money - 200 WHERE bankNo = 110;
   Query OK, 1 row affected (0.00 sec)
   Rows matched: 1  Changed: 1  Warnings: 0
   
   mysql> UPDATE account SET money = money + 200 WHERE bankNo = 120;
   Query OK, 1 row affected (0.00 sec)
   Rows matched: 1  Changed: 1  Warnings: 0
   
   #提交事务
   mysql> COMMIT;                                                                                           
   Query OK, 0 rows affected (0.01 sec)
   
   #查看转账结果，转账成功。
   mysql> SELECT * FROM account;
   +----+--------+----------+-------+                                                                    
   | id | bankNo | username | money |                                                                       
   +----+--------+----------+-------+                                                                        
   |  1 | 110    | 张三     |  9800 |                                                                       
   |  2 | 120    | 李四     |  1200 |                                                                         
   +----+--------+----------+-------+                                                                         
   2 rows in set (0.00 sec) 
   
   # 当转账过程出现错误时，仍然使用ROLLBACK回滚。
  

注意

• 事务提交之后，进行回滚事务是不起作用的，因为事务日志在提交事务的同时已经被清除。

• mysql支持的主流存储引擎中Innodb是支持事务的，Myisam不支持事务。

• 事务只支持DML语句，也即insert，delete，update三种操作。

    # 创建表t1
    mysql> create table t1 (id bigint(20), name varchar(20));
    Query OK, 0 rows affected (0.06 sec)
   
   mysql> show tables;
   +---------------+
   | Tables_in_tmp |
   +---------------+
   | t1            |
   +---------------+
   1 row in set (0.00 sec)
   # 开始事务
   mysql> begin;
   Query OK, 0 rows affected (0.00 sec)
   
   # 删除表t1，drop操作属于DDL语句
   mysql> drop table t1;
   Query OK, 0 rows affected (0.04 sec)
   
   #回滚
   mysql> rollback;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> show tables;
   Empty set (0.00 sec)
   # 尽管rollback，但是表t1还是删除掉了。
  

总结

本文总结了数据库事务的基本概念与特性。同时通过案例讲解了mysql事务的使用。