Transactions by George Hernandez

Intro
Examples

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

Example 1

Here is a simple example of using explicit transactions in SQL Server.

BEGIN TRANSACTION myTrans--Do a bunch of stuff. --Check if it's OK to commit:
IF @myOK = 1
  COMMIT TRANSACTION myTrans
ELSE
  ROLLBACK TRANSACTION myTrans

Example 2

A Savepoint can be used for a partial rollback.

BEGIN TRANSACTION myTrans--Do a bunch of stuff that is certain. 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

Page Modified: (Hand noted: 2007-10-13 19:44:50Z) (Auto noted: 2007-11-17 06:43:49Z)