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.
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:
And you can enable it again with:
You automatically leave autocommit mode whenever you issue a BEGIN or BEGIN WORK statement.
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.