Defining a Transaction

The default behavior of MySQL is to perform a COMMIT after the execution of each individual SQL statement, effectively turning every statement into an individual transaction. This approach is inadequate for most complex applications.

To enable transactions, allowing multiple SQL statements to be executed before a COMMIT or ROLLBACK is performed, you must take one of the following two steps:

• Set the MySQL autocommit property or variable to 0. The default setting for AUTOCOMMIT is 1.
• Explicitly initiate a transaction with the START TRANSACTION statement.

Since it is dangerous to assume that the MySQL environment is running with the necessary transaction setting, you should generally include either a SET AUTOCOMMIT=0 or START TRANSACTION statement in any transactional stored program.

The SET autocommit=0 statement simply ensures that MySQL will not implicitly issue a COMMIT after every SQL statement. Note, however, that if you have already initiated a transaction, issuing SET autocommit will have no effect. START TRANSACTION, on the other hand, implicitly commits any currently outstanding changes in your session, terminating the existing transaction and starting a new one.

We recommend that you leave nothing to chance when programming transactions in MySQL stored programs. Therefore, we suggest that you always explicitly commence a transaction with a START TRANSACTION statement and explicitly end your transaction with a COMMIT or ROLLBACK.

Wherever possible, define explicitly the beginning and end of every transaction with START TRANSACTION and COMMIT/ROLLBACK statements. Place the START TRANSACTION statement at the beginning of your transaction, and terminate it with either COMMIT or ROLLBACK. If your program ends with conditional logic as part of its error handling, you may, in fact, need to use both of these statementsin different branches of your IF or CASE statement.

Example 8-1 shows a transaction implemented in a stored procedure using a SET AUTOCOMMIT statement.

Example 8-1. Commencing a transaction using SET AUTOCOMMIT

CREATE PROCEDURE tfer_funds
 (from_account int, to_account int,tfer_amount numeric(10,2))
BEGIN
 SET autocommit=0;

 UPDATE account_balance
 SET balance=balance-tfer_amount
 WHERE account_id=from_account;

 UPDATE account_balance
 SET balance=balance+tfer_amount
 WHERE account_id=to_account;

 COMMIT;
END;

Example 8-2 shows an example of defining a transaction using START TRANSACTION.

Example 8-2. Commencing a transaction using START TRANSACTION

CREATE PROCEDURE tfer_funds
 (from_account int, to_account int,tfer_amount numeric(10,2))
BEGIN
 START TRANSACTION;


 UPDATE account_balance
 SET balance=balance-tfer_amount
 WHERE account_id=from_account;

 UPDATE account_balance
 SET balance=balance+tfer_amount
 WHERE account_id=to_account;

 COMMIT;
END;

As we've said, transactions normally complete when either a COMMIT or a ROLLBACK statement is executed. However, be aware that some statementsusually Data Definition Language (DDL) statementscan cause implicit COMMITs. The statements that implicitly commit, and should therefore be avoided when a transaction is active, include the following:

ALTER FUNCTION	ALTER PROCEDURE	ALTER TABLE
BEGIN	CREATE DATABASE	CREATE FUNCTION
CREATE INDEX	CREATE PROCEDURE	CREATE TABLE
DROP DATABASE	DROP FUNCTION	DROP INDEX
DROP PROCEDURE	DROP TABLE	UNLOCK TABLES
LOAD MASTER DATA	LOCK TABLES	RENAME TABLE
trUNCATE TABLE	SET AUTOCOMMIT=1	START TRANSACTION