MySQL Stored Procedure Programming

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.

Tip

Wherever possible, ...