SQL Pocket Guide, 2nd 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 (SQL Server, MySQL, PostgreSQL)

SQL Server, MySQL, and PostgreSQL default to an autocommit mode in which each statement you execute is treated as a transaction in and of itself.

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 the next section, "Starting a Transaction,” 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

Databases differ in the syntax they support to begin a transaction. The following subsections show you how to begin transactions in Oracle, SQL Server, MySQL, and PostgreSQL. DB2 does not implement a SQL statement to explicitly begin a transaction.

Starting a transaction (Oracle) ...