Database Reference
In-Depth Information
becomes part of the current transaction. To end the transaction and begin the next
one, execute a
COMMIT
or
ROLLBACK
statement:
mysql>
CREATE TABLE t (i INT) ENGINE = InnoDB;
mysql>
SET autocommit = 0;
mysql>
INSERT INTO t (i) VALUES(1);
mysql>
INSERT INTO t (i) VALUES(2);
mysql>
COMMIT;
mysql>
SELECT * FROM t;
+------+
| i |
+------+
| 1 |
| 2 |
+------+
To turn auto-commit mode back on, use this statement:
mysql>
SET autocommit = 1;
Transactions have their limits because not all statements can be part
of a transaction. For example, if you execute a
DROP
DATABASE
state‐
ment, don't expect to restore the database by executing a
ROLLBACK
.
17.3. Performing Transactions from Within Programs
Problem
You're writing a program that must implement transactional operations.
Solution
Use the transaction abstraction provided by your language API, if it has such a thing.
If it doesn't, use the API's usual statement-execution mechanism to execute the trans‐
actional SQL statements directly.
Discussion
To perform transactional processing from within a program, use your API language to
detect errors and take appropriate action. This recipe provides general background on
doing this. The next recipes provide language-specific details for the MySQL APIs for
Perl, Ruby, PHP, Python, and Java.
Every MySQL API supports transactions, even if only in the sense that you can explicitly
execute transaction-related SQL statements such as
START
TRANSACTION
and
COMMIT
.