A transaction is a sequence of operations which must complete in entirety or not at all. If the transaction fails for any reason, the databases involved are rolled back, i.e. returned to their original state. This insures the integrity of a database, especially for systems where multiple users and processes may try to access the same data at the same time.
Transactions in databases should pass the ACID test:
- Atomic It must be a complete entity, all-or-nothing, leaving no partial results.
- Consistent It leaves the DB in a consistent state, i.e. no business rules are forgotten.
- Isolated It must run regardless of what else is running. Often this means locking certain resources so there is no outside interference. The transaction never works on data that is in the middle of another transaction. Different levels of isolation can be set in T-SQL (with the SET TRANSACTION ISOLATION LEVEL statement) or in the DB APIs.
- Durable Once complete, its affect remains in storage memory so it would endure even if something like a system crash occurs.
Transactions on data in a SQL Server database are usually handled with T-SQL or with DB APIs. It is not good practice to mix the two.
In SQL Server there are three ways to start a transaction:
- Auto-commit. The default mode of SQL Server. Each statement commits as it completes.
- Explicit. Aka user-defined or user-specified transactions. Defined by the user with a BEGIN TRANSACTION statement.
- Implicit. Defined through either a DB API or the T-SQL SET IMPLICIT_TRANSACTIONS ON statement. A transaction is started any time one of these statements is executed:
ALTER TABLE INSERT CREATE OPEN DELETE REVOKE DROP SELECT FETCH TRUNCATE TABLE GRANT UPDATE
When transaction completes, it must end with either a COMMIT or ROLLBACK statement. If the client's connection, application, or either computer unexpectedly stops during a transaction, then the transaction is rolled back.
This paragraph from the MSDN Library is so concise that I haven't bothered to paraphrase it much:
- The @@TRANCOUNT function records the current transaction nesting level.
- Each BEGIN TRANSACTION statement increments @@TRANCOUNT by one.
- Each COMMIT TRANSACTION or COMMIT WORK statement decrements @@TRANCOUNT by one.
- A ROLLBACK WORK or a ROLLBACK TRANSACTION statement that does not have a transaction name rolls back all nested transactions and decrements @@TRANCOUNT to 0.
- A ROLLBACK TRANSACTION that uses the transaction name of the outermost transaction in a set of nested transactions rolls back all the nested transactions and decrements @@TRANCOUNT to 0.
- When you are unsure if you are already in a transaction, SELECT @@TRANCOUNT to determine if it is 1 or more. If @@TRANCOUNT is 0 you are not in a transaction.
All T-SQL statements are allowed in transactions except for the following:
|ALTER DATABASE||LOAD DATABASE|
|BACKUP LOG||LOAD TRANSACTION|
|DISK INIT||RESTORE DATABASE|
|DROP DATABASE||RESTORE LOG|
|DUMP TRANSACTION||UPDATE STATISTICS|
SQL Server can also work with MS DTC (Microsoft Distributed Transaction Coordinator) for distributed transactions. The SQL keywords of BEGIN DISTRIBUTED TRANSACTION would have to be used.
For more examples, see Error Handling.
Here is a simple example of using explicit transactions in SQL Server.
BEGIN TRANSACTION -- Do a bunch of stuff. -- Check if it's OK to commit: IF @myOK = 1 COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION
A Savepoint can be used for a partial rollback.
BEGIN TRANSACTION myTrans -- Do a bunch of stuff. -- Name a transaction for kicks or if you have multiple trans. SAVE TRANSACTION mySavepoint -- Do a bunch of stuff that may err. -- Check if it's OK to commit: IF @@ERROR <> 0 BEGIN COMMIT TRANSACTION myTrans RETURN 0 END ELSE BEGIN ROLLBACK TRANSACTION mySavepoint RETURN @@ERROR END
Links that lead to off-site pages about SQL Server Transactions.
- TRY...CATCH (Transact-SQL) [http://msdn.microsoft.com/en-us/library/ms175976.aspx]
- Transaction Statements (Transact-SQL) [http://msdn.microsoft.com/en-us/library/ms174377.aspx]
- SET XACT_ABORT (Transact-SQL) [http://msdn.microsoft.com/en-us/library/ms188792.aspx]
- XACT_STATE (Transact-SQL) [http://msdn.microsoft.com/en-us/library/ms189797.aspx]. A scalar function that reports the user transaction state of a current running request. XACT_STATE() indicates whether the request has an active user transaction, and whether the transaction is capable of being committed. Return 1 (active trans), 0 (no active trans), -1 (active trans but it is uncommittable). See also: http://stackoverflow.com/questions/16043513/sql-server-try-catch-with-xact-state.