Transaction Management

Part:   1   2  3  4 

This chapter describes:

• What is a transaction?
• How does MySQL support of transaction management?
• What are transaction isolation levels.
• How does MySQL hide uncommitted changes from other client sessions?
• How does MySQL supports transaction isolation levels?
• MySQL transaction isolation level tests - read uncommitted, read committed, and repeatable read.

All notes in chapter are based MySQL 5.0 server.

What Is A Transaction

Transaction - A sequence of executions of SQL statements that can be treated as a single unit in which all data changes can be committed or cancelled as a whole.

Most database servers offer two transaction management modes:

• Auto Commit On: Each SQL statement is a transaction. Data changes resulted from each statement are automatically committed.
• Auto Commit Off: Transactions are explicitly started and ended by the client program. Data changes are not committed unless requested by the client program.

Most database server supports the following statements for transaction management:

• Commit Statement - To commit all changes in the current transaction.
• Rollback Statement - To rollback all changes in the current transaction.
• Start Transaction Statement - To start a new transaction.

MySQL Support of Transaction Management

MySQL support of transaction management follows the following rules:

• Only two storage engines support transaction management: InnoDB and BDB.
• The default storage engine, MyISAM, doesn't support transaction management.
• To force a table to use a non-default storage engine, you must specify the engine name in the "create table" statement.

Statements related to transaction management:

SET AUTOCOMMIT = 0 | 1;
START TRANSACTOIN;
COMMIT;
ROLLBACK;

Note that:

• SET AUTOCOMMIT = 1 - Turns on the auto-commit option. It also commits and terminates the current transaction.
• SET AUTOCOMMIT = 0 - Turns off the auto-commit option. It also starts a new transaction
• By default, auto-commit option is turned on when a new session is established.
• COMMIT - Commits the current transaction.
• ROLLBACK - Rolls back the current transaction.
• START TRANSACTION - Commits the current transaction and starts a new transaction.

(Continued on next part...)

Part:   1   2  3  4