14.5.3 Grouping DML Operations with Transactions 分组DML 事务操作
默认情况下,连接到MySQL server 开启自动提交模式, 自动提交每个SQL 语句。
这种模式的操作可能不熟悉的 如果你有其他数据库系统的经验,
使用多个语句的事务,关闭自动提交 使用SQL语句 SET autocommit = 0
每个事务 COMMIT或者ROLLBACK 适当的,
离开自动提交, 开始每个事务 使用START TRANSACTION 结束事务使用COMMIT或者ROLLBACK.
下面的例子显示两个事务,第一个是提交,下面是回滚:
shell> mysql test
mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do a transaction with autocommit turned on.
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do another transaction with autocommit turned off.
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO customer VALUES (20, 'Paul');
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM customer WHERE b = 'Heikki';
Query OK, 1 row affected (0.00 sec)
mysql> -- Now we undo those last 2 inserts and the delete.
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM customer;
+------+--------+
| a | b |
+------+--------+
| 10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>
事务在客户端语言:
在APIs比如PHP,Perl DBI,JDBC,ODBC或者标准的C 请求接口,你可以发送事务控制语句比如COMMIT给MySQL server
作为字符窜像任何其他SQL语句一样比如SELECT 或者INSERT,一些APIs 也提供单独的特别的事务提交和回滚函数。