SQL Pocket Guide, 3rd Edition

Transaction Management

A transaction is a collection of operations treated as a unit. Either all operations in the unit are completed or none of them are. All commonly used databases make provisions for transactions.

When working in a transactional environment, you need to know how to begin and end a transaction. You also need to know how to specify various characteristics of a transaction—for example, whether it will update any data.

Autocommit Mode

MySQL, PostgreSQL, and SQL Server default to an autocommit mode in which each statement you execute is treated as a transaction in and of itself. (Thus, you cannot roll back a statement when the result isn’t what you expected).

You can disable autocommit in SQL Server with the following statement:

SET IMPLICIT_TRANSACTIONS ON

You can enable autocommit again using:

SET IMPLICIT_TRANSACTIONS OFF

You leave SQL Server’s and PostgreSQL’s autocommit mode whenever you issue an explicit BEGIN TRANSACTION (SQL Server) or BEGIN (PostgreSQL) statement. See “Starting a Transaction” below for details.

In MySQL, you can disable autocommit with:

SET AUTOCOMMIT=0

And you can enable it again with:

SET AUTOCOMMIT=1

You automatically leave autocommit mode whenever you issue a BEGIN or BEGIN WORK statement.

Starting a Transaction: DB2

DB2 does not implement an SQL statement to explicitly begin a transaction. When you connect and issue an SQL statement, you begin a transaction. You also begin a transaction with the first SQL statement following a COMMIT.

Starting a Transaction: ...