What is Conducting a Transaction?
If you have a series of SQL statements that you want to run which involve adding, deleting, or changing data contained in InnoDB or BDB tables, but want to be sure that all SQL statements or transactions are completed successfully before committing them, there is a set of MySQL statements that you can use to do this. This is known as conducting a transaction (i.e., the set of SQL statements to execute) and being able to perform a rollback. Currently, you can only rollback transactions on InnoDB and BDB tables in MySQL.
Disabling autocommit
1. The first step for performing a transaction that you may want to rollback is to disable autocommit mode, so that you are always operating within a transaction, or to explicitly begin a transaction.
2. Autocommit mode is enabled by default. To change the autocommit just for the current connection, enter the following SQL statement:
SET SESSION autocommit = 0;
Disabling autocommit like this will only affect the current client connection and not other client connections. It will last until you enable it again by setting the variable to 1 or when you close the current session.
3. If you want to explicitly begin a transaction, which you must do if you do not disable autocommit mode, you can use the following simple statement:
START TRANSACTION;
4. Now you may enter a series of SQL statements that collectively will make up the transaction. At any point, you may undo all of the statements for the transaction by entering the following SQL statements:
ROLLBACK;
5. If you don’t want to reverse a transaction and decide to commit the SQL statements executed, enter the following SQL statement:
COMMIT;
This ends the transaction. Note that some statements are not transactional and may not be rolled back, such as data definition statements CREATE TABLE, CREATE DATABASE, ALTER TABLE, DROP TABLE, and DROP DATABASE, for example. Some of these statements, in fact, implicitly commit the current transaction.