Intro

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
CREATE DATABASE RECONFIGURE
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.

Examples

For more examples, see Error Handling.

Example 1

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

Example 2

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

Links that lead to off-site pages about SQL Server Transactions.



GeorgeHernandez.comSome rights reserved