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.
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:
And you can enable it again with:
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.