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
To enable transactions, allowing multiple SQL statements to be
executed before a
ROLLBACK is performed, you must
take one of the following two steps:
Set the MySQL
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
START TRANSACTION statement in
any transactional stored program.
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,
SET autocommit will have no
START TRANSACTION, on the
other hand, implicitly commits any currently outstanding changes in
your session, terminating the existing transaction and starting a new
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
Wherever possible, ...