In Oracle, a transaction is started automatically when an insert, update, or delete operation is performed. An application must issue a COMMIT command to save all changes to the database. If a COMMIT is not performed, all changes are rolled back or undone automatically.
By default, Microsoft SQL Server automatically performs a COMMIT statement after every insert, update, or delete operation. Because the data is automatically saved, you are unable to roll back any changes. You can use implicit or explicit transaction modes to change this default behavior.
The implicit transaction mode, allowing SQL Server to behave like Oracle, is activated with the SET IMPLICIT_TRANSACTIONS ON statement. If this option is ON and there are no outstanding transactions, every SQL statement automatically starts a transaction. If there is an open transaction, no new transaction is started. The open transaction must be committed by the user explicitly with the COMMIT TRANSACTION statement for the changes to take effect and for all locks to be released.
An explicit transaction is a grouping of SQL statements surrounded by the following transaction delimiters:
In the following example, the English department is changed to the Literature department. Note the use of the BEGIN TRANSACTION and COMMIT TRANSACTION statements.
| Oracle | Microsoft SQL Server |
|---|---|
| INSERT INTO DEPT_ADMIN.DEPT (DEPT, DNAME) VALUES ('LIT', 'Literature') / UPDATE DEPT_ADMIN.CLASS SET MAJOR = 'LIT' WHERE MAJOR = 'ENG' / UPDATE STUDENT_ADMIN.STUDENT SET MAJOR = 'LIT' WHERE MAJOR = 'ENG' / DELETE FROM DEPT_ADMIN.DEPT WHERE DEPT = 'ENG' / COMMIT / |
BEGIN TRANSACTION INSERT INTO DEPT_ADMIN.DEPT (DEPT, DNAME) VALUES ('LIT', 'Literature') UPDATE DEPT_ADMIN.CLASS SET DEPT = 'LIT' WHERE DEPT = 'ENG' UPDATE STUDENT_ADMIN.STUDENT SET MAJOR = 'LIT' WHERE MAJOR = 'ENG' DELETE FROM DEPT_ADMIN.DEPT WHERE DEPT = 'ENG' COMMIT TRANSACTION GO |
All explicit transactions must be enclosed within BEGIN TRANSACTION...COMMIT TRANSACTION statements. The SAVE TRANSACTION statement functions in the same way as the Oracle SAVEPOINT command, setting a savepoint in the transaction that allows partial rollbacks.
Transactions can be nested one within another. If this occurs, the outermost pair creates and commits the transaction, and the inner pairs track the nesting level. When a nested transaction is encountered, the @@TRANCOUNT function is incremented. Usually, this apparent transaction nesting occurs as stored procedures or triggers with BEGIN…COMMIT pairs calling each other. Although transactions can be nested, they have little effect on the behavior of ROLLBACK TRANSACTION statements.
In stored procedures and triggers, the number of BEGIN TRANSACTION statements must match the number of COMMIT TRANSACTION statements. A stored procedure or trigger that contains unpaired BEGIN TRANSACTION and COMMIT TRANSACTION statements produces an error message when executed. The syntax allows stored procedures and triggers to be called from within transactions if they contain BEGIN TRANSACTION and COMMIT TRANSACTION statements.
Wherever possible, break large transactions into smaller transactions. Make sure each transaction is well-defined within a single batch. To minimize possible concurrency conflicts, transactions should not span multiple batches nor wait for user input. Grouping many Transact-SQL statements into one long-running transaction can negatively affect recovery time and cause concurrency problems.
When programming with ODBC, you can select either the implicit or explicit transaction mode by using the SQLSetConnectOption function. An ODBC program’s selection of one or the other depends on the AUTOCOMMIT connect option. If AUTOCOMMIT is ON (the default), you are in explicit mode. If AUTOCOMMIT is OFF, you are in implicit mode.
If you are issuing a script through SQL Server Query Analyzer or other query tools, you can either include the explicit BEGIN TRANSACTION statement shown previously, or start the script with the SET IMPLICIT_TRANSACTIONS ON statement. The BEGIN TRANSACTION approach is more flexible, and the implicit approach is more compatible with Oracle.