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:

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

Related

Категории