A transaction is a single unit of work. Any single SQL statement is considered to be a single unit of work whether it affects only a single row or multiple rows within a table. In SQL Server, a transaction is implicit (due to the nature of the statements executed) or explicit (as defined by the user). An explicit transaction is a grouping of SQL statements surrounded by the transaction delimiters: BEGIN TRANSACTION, COMMIT TRANSACTION, and optionally, one of the following statements. In creating explicitly defined transactions, the following statements are used:
All transactions must be enclosed within BEGIN TRANSACTION...COMMIT TRANSACTION statements. The outermost pair actually creates and commits the transaction; the inner pairs track the nesting level. When a nested transaction is encountered, the global variable @@TRANCOUNT is incremented.
Naming nested transactions with a transaction_name has little effect on the outermost transaction. Only the first (outermost) transaction name is registered with the system, and a rollback to any other name (other than a valid savepoint name) generates an error. None of the statements executed before the rollback are rolled back when this error occurs. No portion of the transaction is committed until the outermost COMMIT TRANSACTION is issued. Normally, this apparent transaction nesting occurs as stored procedures or triggers with BEGIN...COMMIT pairs call each other.
Although transactions can be nested, they have little effect on the behavior of ROLLBACK TRANSACTION statements, except for generating errors if not handled properly. The syntax allows stored procedures and triggers to be called from within transactions if they contain BEGIN TRANSACTION and COMMIT TRANSACTION statements.
Grouping a large number of Transact-SQL statements into one long-running transaction can negatively affect recovery time and cause concurrency problems. If SQL Server fails during such a transaction, recovery time can increase because SQL Server must first undo the transaction. Wherever possible, it is wise to break 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.
In stored procedures and triggers, the number of BEGIN TRANSACTION statements must match the number of COMMIT TRANSACTION statements. A procedure or trigger that contains unpaired BEGIN TRANSACTION and COMMIT TRANSACTION statements produces an error message when it is executed.
To define a transaction, place Transact-SQL statements and/or stored procedures between the statements BEGIN TRANSACTION and COMMIT TRANSACTION. Within a user-defined (or explicit) transaction, there are some important rules to follow:
| ALTER DATABASE | DISK INIT | LOAD TRANSACTION |
| CREATE DATABASE | DROP <objname> | RECONFIGURE |
| CREATE INDEX | DUMP TRANSACTION | SELECT INTO |
| CREATE PROCEDURE | GRANT or REVOKE | TRUNCATE TABLE |
| CREATE TABLE | LOAD DATABASE | UPDATE STATISTICS |
| CREATE VIEW |
Note Additionally, you cannot use the sp_dboption system stored procedure to set database options or use any system procedures that change the master database inside user-defined transactions.
Important Statements executed with calls to remote stored procedures are not rolled back with ROLLBACK TRANSACTION, and they do not depend on COMMIT TRANSACTION to be executed. In other words, statements executed on a remote server through remote stored procedures happen outside the scope of the local user-defined transaction.
No explicit permission is necessary to define a transaction. By default, all users can define a transaction. All statements used within a transaction follow their specific permission rules.