同福

鬼谷子叔叔
2019-02-01 15:10:40 30

【MySQL】事务处理的使用（InnoDB）

介绍

概念

所谓的事务处理，就是把一连串的sql操作放到一个独立环境下一次性执行，执行过程与其他进程互不影响，执行失败可以要求全部撤销，执行成功可以提交写入

准备

先决条件

MySQL的事务处理这么优秀，自然是有一定条件的，那就是必须使用InnoDB引擎类型的数据存储结构才可以使用事务机制

InnoDB类型在高版本的MySQL当中已经成为默认数据表引擎类型，如果我们使用的数据表引擎类型还是旧版本的MyISAM类型的话，就需要通过命令转换一下

转换引擎类型

可以使用下面的语句转换数据表引擎类型

ALTER TABLE [数据表名称] ENGINE = InnoDB;

然后通过下面的语句查看数据表引擎类型

SHOW CREATE TABLE [数据表名称];

+-------+-------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                            |
+-------+-------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL DEFAULT '',
  `loginCNT` int(11) DEFAULT '0',
  `viewCNT` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------+

最后的ENGINE=InnoDB就表明我们的数据表已经是InnoDB引擎类型了

使用

基本使用

插入数据

我们插入一行测试数据

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

mysql> insert into test (name) values ('zhang3');
Query OK, 1 row affected (0.07 sec)

mysql> select * from test;
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       0 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

更新字段

我们更新test表的loginCNT字段为1，最后使用rollback撤销更新

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

mysql> update test set viewCNT = 1 where name = 'zhang3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test where name = 'zhang3';
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       1 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where name = 'zhang3';
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       0 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

这时我们会发现数据实际并没有真的写入到数据库当中

也就是说，在start transaction之后，我们进入了一个独立环境内，这个环境的更新只有在这个环境内才看得到，外面是看不到的。

如果要把整个更新保存起来，需要执行commit命令。

如果不想保存整个更新，可以使用rollback撤销掉。

高级使用

事务嵌套案例1

我们在第一层事务里更新数据

再第二层事务里再次更新数据

然后在第二层事务里提交数据

最后在第一层事务里回滚数据

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

mysql> update test set viewCNT = viewCNT + 1 where name = 'zhang3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from test where name = 'zhang3';
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       1 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

mysql> update test set viewCNT = viewCNT + 1 where name = 'zhang3';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test where name = 'zhang3';
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       2 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where name = 'zhang3';
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       2 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where name = 'zhang3';
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       2 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

实践证明transaction并不识别所谓的第几层，遇到commit命令就会把前面的更新操作写入数据库了

事务嵌套案例2

我们在第一层事务内更新数据

再在第二层事务内更新数据

再在第二层事务内回滚数据

再在第一层事务内回滚数据

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

mysql> update test set viewCNT = viewCNT + 1 where name = 'zhang3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> update test set viewCNT = viewCNT + 1 where name = 'zhang3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test where name = 'zhang3';
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       4 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test where name = 'zhang3';
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       3 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where name = 'zhang3';
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       3 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

实践证明只有第二层的rollback起到了作用，第一层的rollback并没有起到回滚的作用

事务与触发器

事务嵌套总结

请先看下面的执行结果

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

mysql> select * from test where name = 'zhang3';
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       3 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

mysql> update test set viewCNT = viewCNT + 1 where name = 'zhang3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test where name = 'zhang3';
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       4 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

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

mysql> select * from test where name = 'zhang3';
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       4 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

大家应该注意到了吧，在事务内再次启动事务系统会自动提交前一个事务，也就是说事务是不能嵌套使用的

请再看下面的执行结果

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

mysql> select * from test where name = 'zhang3';
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       4 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

mysql> update test set viewCNT = viewCNT + 1 where name = 'zhang3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> exit
Bye
mysql> select * from test where name = 'zhang3';
+----+--------+----------+---------+
| id | name   | loginCNT | viewCNT |
+----+--------+----------+---------+
|  1 | zhang3 |        0 |       4 |
+----+--------+----------+---------+
1 row in set (0.00 sec)

大家可以看明白吧，在事务内进行的操作如果不进行commit或者rollback动作，直接退出的话，更新数据自动放弃保存

事务与触发器

事务这么神奇，那么如果我们结合触发器（trigger）使用又会怎么样呢？如果触发了trigger，做了相应操作后，事务又回滚了，会不会有问题？

下面我们就来测试一下

首先创建一个日志表

mysql> create table log (id int not null auto_increment, data text null, primary key (id));
Query OK, 0 rows affected (0.01 sec)

建立触发器，每当test表更新了就记录下当时的loginCNT和viewCNT的变化值

mysql> delimiter $$;
mysql> 
mysql> create trigger t1 after update test for each row begin
    -> insert into log (data) values (concat(NEW.name, ',', NEW.loginCNT, ',', NEW.viewCNT));
    -> end;
    -> 
    -> $$
    -> 
    -> delimiter ;

接着我们试试之前我们的设想会是怎么样的结果吧

更新一下test表，日志里会忠实的记录当时的数据值

mysql> update test set viewCNT = viewCNT + 1 where name = 'zhang3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from log;
+----+------------+
| id | data       |
+----+------------+
|  1 | zhang3,0,6 |
+----+------------+
1 row in set (0.00 sec)

通过事务更新一下test，然后马上回滚一下

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

mysql> update test set viewCNT = viewCNT + 1 where name = 'zhang3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from log;
+----+------------+
| id | data       |
+----+------------+
|  1 | zhang3,0,6 |
+----+------------+

你会神奇的发现，触发器貌似没有工作一样！？

其实，我们在事务内进行更新操作之后，在事务内的log表里已经有了新记录，只不过我们最后执行的rollback把这条新记录也回滚了

至此，我们对MySQL的InnoDB的事务处理有了一定的了解了吧！