transaction在数据库编程中是一个重要的概念,这样做可以控制对数据库操作的事务提交。
但是要想在程序中实现事务,要求数据库本身支持事务。
现在的关系型数据库,我们日常使用的mysql,oracle等等都支持事务,有的是安装后直接就支持,有的需要做一些设置。这里我就自己的经历总结了一下如何设置以及设置的步骤:
但是要想在程序中实现事务,要求数据库本身支持事务。
现在的关系型数据库,我们日常使用的mysql,oracle等等都支持事务,有的是安装后直接就支持,有的需要做一些设置。这里我就自己的经历总结了一下如何设置以及设置的步骤:
进入mysql
mysql> select * from tester;
+----+--------+--------------+--------+-------+--------+
| ID | name | address | salary | grade | sex |
+----+--------+--------------+--------+-------+--------+
| 1 | jason | shenzhen | 4500 | 6 | male |
| 2 | linkle | zhuhai | 4000 | 5 | female |
| 3 | leo | zhuhai | 3000 | 4 | male |
| 5 | steven | zhuhai_jinan | 5600 | 7 | male |
| 6 | jacky | zhuhai | 4000 | 5 | male |
+----+--------+--------------+--------+-------+--------+
5 rows in set (0.00 sec)
+----+--------+--------------+--------+-------+--------+
| ID | name | address | salary | grade | sex |
+----+--------+--------------+--------+-------+--------+
| 1 | jason | shenzhen | 4500 | 6 | male |
| 2 | linkle | zhuhai | 4000 | 5 | female |
| 3 | leo | zhuhai | 3000 | 4 | male |
| 5 | steven | zhuhai_jinan | 5600 | 7 | male |
| 6 | jacky | zhuhai | 4000 | 5 | male |
+----+--------+--------------+--------+-------+--------+
5 rows in set (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tester values(7,'jack','HongKong',8000,10,'male');
Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from tester;
+----+--------+--------------+--------+-------+--------+
| ID | name | address | salary | grade | sex |
+----+--------+--------------+--------+-------+--------+
| 1 | jason | shenzhen | 4500 | 6 | male |
| 2 | linkle | zhuhai | 4000 | 5 | female |
| 3 | leo | zhuhai | 3000 | 4 | male |
| 5 | steven | zhuhai_jinan | 5600 | 7 | male |
| 6 | jacky | zhuhai | 4000 | 5 | male |
| 7 | jack | HongKong | 8000 | 10 | male |
+----+--------+--------------+--------+-------+--------+
6 rows in set (0.00 sec)
+----+--------+--------------+--------+-------+--------+
| ID | name | address | salary | grade | sex |
+----+--------+--------------+--------+-------+--------+
| 1 | jason | shenzhen | 4500 | 6 | male |
| 2 | linkle | zhuhai | 4000 | 5 | female |
| 3 | leo | zhuhai | 3000 | 4 | male |
| 5 | steven | zhuhai_jinan | 5600 | 7 | male |
| 6 | jacky | zhuhai | 4000 | 5 | male |
| 7 | jack | HongKong | 8000 | 10 | male |
+----+--------+--------------+--------+-------+--------+
6 rows in set (0.00 sec)
按照上面的步骤rollback没有生效,即便我设置了autocommit=0。By the way,如果SET AUTOCOMMIT=0;也就是关闭了自动提交,那么任何commit或rollback语句都可以触发事务提交;如果SET AUTOCOMMIT=1;也就是开启了自动提交(默认值),那么必须要以begin或者START TRANSACTION声明事务的开始,然后再以commit或rollback语句都可以触发事务提交。