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


Part I: Stored Programming Fundamentals

Introduction to MySQL Stored Programs

MySQL Stored Programming Tutorial

Language Fundamentals

Blocks, Conditional Statements, and Iterative Programming

Using SQL in Stored Programming

Error Handling

Part II: Stored Program Construction

Creating and Maintaining Stored Programs

Transaction Management

MySQL Built-in Functions

Stored Functions

Triggers

Part III: Using MySQL Stored Programs in Applications

Using MySQL Stored Programs in Applications

Using MySQL Stored Programs with PHP

Using MySQL Stored Programs with Java

Using MySQL Stored Programs with Perl

Using MySQL Stored Programs with Python

Using MySQL Stored Programs with .NET

Part IV: Optimizing Stored Programs

Stored Program Security

Tuning Stored Programs and Their SQL

Basic SQL Tuning

Advanced SQL Tuning

Optimizing Stored Program Code

Best Practices in MySQL Stored Program Development



MySQL Stored Procedure Programming
MySQL Stored Procedure Programming
ISBN: 0596100892
EAN: 2147483647
Year: 2004
Pages: 208

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net