8.2. Defining a Transaction – MySQL Stored Procedure Programming [Book]

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, …