Transactions in MySQL

Transactions in MySQL are available only on tables that use the InnoDB or BDB storage engines. See Lesson 14, "Creating and Modifying Tables," for more information on how to select a specific table handler.

To begin a transaction, issue the BEGIN TRANSACTION command. Any database-manipulation statements issued after that command form part of the same transaction.

Until you issue a statement that ends a transaction, the effects of any commands that you enter are visible only within the current MySQL session. For instance, if you delete rows from a table after issuing the START TRANSACTION command, other database users can still query those rows, even though in your session they appear to be already deleted.

To commit all the changes made in a transaction, issue the COMMIT command. Doing so causes each INSERT, UPDATE , and DELETE statement that forms part of the transaction to be executed immediately and as one unit.

Autocommit Mode

By default, the system variable AUTOCOMMIT is set to 1, which instructs MySQL to process each database-manipulation statement issued immediately as a single transaction. This is the behavior you have already seen, with an INSERT, UPDATE , or DELETE committed to the database as soon as you issue the command.

Consider this simple UPDATE statement:

UPDATE PRODUCTS
SET price = 5.99
WHERE code = 'MINI';

When autocommit mode is turned on, the previous statement is executed as if you had entered the following:

BEGIN TRANSACTION;

UPDATE PRODUCTS
SET price = 5.99
WHERE code = 'MINI';

COMMIT;

By setting the value of AUTOCOMMIT to 0, you disable this feature so that you have to use COMMIT to store any changes madeor use ROLLBACK to discard changessince the beginning of the transaction.

To disable AUTOCOMMIT , use the SET command as shown:

mysql>

SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

Rolling Back a Transaction

You can also end a transaction without committing the changes with the ROLLBACK command. The result is that the database state is rolled back to how it was before you issued the START TRANSACTION command.

The following example shows how you can recover from a disastrously wrong DELETE statementremember to always include the WHERE clauseif it occurs within a transaction:

mysql>

START TRANSACTION;

Query OK, 0 rows affected (0.00 sec)

mysql>

DELETE FROM products;

Query OK, 3 rows affected (0.03 sec)

mysql>

ROLLBACK;

Query OK, 0 rows affected (0.01 sec)

If you then query the products table, you will see that it still contains records.

Transactional Table Handlers If you try to use transactions with tables that do not use the InnoDB storage engine, MySQL will not give an error when you START TRANSACTION . In the previous example, there would be a warning that ROLLBACK could not be used, but at this point it is already too late to undo the DELETE command.