mysql的innodb支持事务处理的
Suppose that you have started the MySQL client with the command mysql test. To create an InnoDB table, you must specify and ENGINE = InnoDB or TYPE = InnoDB option in the table creation SQL statement:
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) TYPE=InnoDB;
操作的时候
begin
rollback
commit
15.7.1. How to Use Transactions in InnoDB with Different APIs
By default, each client that connects to the MySQL server begins with autocommit mode enabled, which automatically commits every SQL statement you run. To use multiple-statement transactions, you can switch autocommit off with the SQL statement SET AUTOCOMMIT = 0 and use COMMIT and ROLLBACK to commit or roll back your transaction. If you want to leave autocommit on, you can enclose your transactions between START TRANSACTION and COMMIT or ROLLBACK. Before MySQL 4.0.11, you have to use the keyword BEGIN instead of START TRANSACTION. The following example shows two transactions. The first is committed and the second is rolled back.
shell> mysql test
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 5 to server version: 3.23.50-log
Type 'help;' or '/h' for help. Type '/c' to clear the buffer.
mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
-> TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
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> 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> 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>
In APIs like PHP, Perl DBI/DBD, JDBC, ODBC, or the standard C call interface of MySQL, you can send transaction control statements such as COMMIT to the MySQL server as strings just like any other SQL statements such as SELECT or INSERT. Some APIs also offer separate special transaction commit and rollback functions or methods.
...数据库连接...
mysql_query('BEGIN'); 开启事务
$SQL = "...";
mysql_query($SQL);
if(mysql_errno) {
print mysql_error();
mysql_query('ROLLBACK'); 出错就回卷
exit();
}
... 可以重复上述自 $SQL = "..."; 开始的过程(中间可以加入其他操作,不局限于数据库更新,但是注意,最好不要让一个事务时间过长,因为它锁定所有你用到的表,会影响其他程序使用) ...
你也可以在几条正确的sql更新语句后故意写一句错误的,看看是否回卷了。
mysql_query('COMMIT'); 能够到这里,代表上述数据库操作都没有错,正式提交执行
如果mysql版本太低还没有事务支持,只能锁定那些表来替代。
还有就是注意要建立能用事务操作的表类型。以及除了 commit 外还有什么语句能结束一个事务。看帮助吧。
锁表和事务是有区别的,而且很关键。
锁定是不能够替代事务的,事务做为一个原子操作,一旦中间任何环节出错,会自动回卷恢复到原先状态。
而锁定工作表,再释放锁定是没有这个功能的。一旦某条语句出错,那么之前的语句还是作用于数据库了,你只能手工来修正。
Suppose that you have started the MySQL client with the command mysql test. To create an InnoDB table, you must specify and ENGINE = InnoDB or TYPE = InnoDB option in the table creation SQL statement:
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) TYPE=InnoDB;
操作的时候
begin
rollback
commit
15.7.1. How to Use Transactions in InnoDB with Different APIs
By default, each client that connects to the MySQL server begins with autocommit mode enabled, which automatically commits every SQL statement you run. To use multiple-statement transactions, you can switch autocommit off with the SQL statement SET AUTOCOMMIT = 0 and use COMMIT and ROLLBACK to commit or roll back your transaction. If you want to leave autocommit on, you can enclose your transactions between START TRANSACTION and COMMIT or ROLLBACK. Before MySQL 4.0.11, you have to use the keyword BEGIN instead of START TRANSACTION. The following example shows two transactions. The first is committed and the second is rolled back.
shell> mysql test
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 5 to server version: 3.23.50-log
Type 'help;' or '/h' for help. Type '/c' to clear the buffer.
mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
-> TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
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> 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> 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>
In APIs like PHP, Perl DBI/DBD, JDBC, ODBC, or the standard C call interface of MySQL, you can send transaction control statements such as COMMIT to the MySQL server as strings just like any other SQL statements such as SELECT or INSERT. Some APIs also offer separate special transaction commit and rollback functions or methods.
...数据库连接...
mysql_query('BEGIN'); 开启事务
$SQL = "...";
mysql_query($SQL);
if(mysql_errno) {
print mysql_error();
mysql_query('ROLLBACK'); 出错就回卷
exit();
}
... 可以重复上述自 $SQL = "..."; 开始的过程(中间可以加入其他操作,不局限于数据库更新,但是注意,最好不要让一个事务时间过长,因为它锁定所有你用到的表,会影响其他程序使用) ...
你也可以在几条正确的sql更新语句后故意写一句错误的,看看是否回卷了。
mysql_query('COMMIT'); 能够到这里,代表上述数据库操作都没有错,正式提交执行
如果mysql版本太低还没有事务支持,只能锁定那些表来替代。
还有就是注意要建立能用事务操作的表类型。以及除了 commit 外还有什么语句能结束一个事务。看帮助吧。
锁表和事务是有区别的,而且很关键。
锁定是不能够替代事务的,事务做为一个原子操作,一旦中间任何环节出错,会自动回卷恢复到原先状态。
而锁定工作表,再释放锁定是没有这个功能的。一旦某条语句出错,那么之前的语句还是作用于数据库了,你只能手工来修正。