Transactions allow you to define the boundaries of activity that will be considered atomic. You do so by enclosing the statements that you want to participate in the transaction in a BEGIN TRAN/COMMIT TRAN block. Note that in SQL Server the default behavior is to consider a statement that does not appear in an explicit transaction block to be its own transaction–as if you enclosed that statement alone in a BEGIN TRAN/COMMIT TRAN block.

Whenever you submit a change to the database, SQL Server first checks whether the pages that need to be affected already reside in cache. If they do, the pages are modified in cache. If they don’t, they’re first loaded from disk into the cache and modified there. SQL Server records the changes in the database’s transaction log. Once in a while, a process called checkpoint flushes changed pages ("dirty pages") from cache to the data portion of the database on disk. However, SQL Server will flush only changed pages for which the change was already recorded in the transaction log.

This architecture allows SQL Server to maintain transactions. If a transaction is rolled back–either explicitly or as a result of a failure–SQL Server has in the transaction log all the information it needs to undo the changes that were not committed. SQL Server might also use the information from the transaction log for roll-forward recovery capabilities, not just rollback operations. During a roll forward recovery phase, SQL Server replays committed transactions that were recorded in the transaction log but that had not been applied to the data portion of the database yet. This activity takes place, for example, when SQL Server starts up. For every database, SQL Server looks for the last checkpoint recorded in the transaction log. All transactions that were committed after the last checkpoint was recorded will be rolled forward (which is known as the redo phase). All open transactions for which a COMMIT TRAN was not recorded in the transaction log will be rolled back (which is known as the undo phase). Note that in SQL Server 2000 the database becomes available only after both phases are finished. SQL Server 2005 makes the database available as soon as the redo phase finishes.

To demonstrate the different aspects of working with transactions in this chapter, I’ll use the tables T1 and T2, which you create and populate in the testdb database by running the code in Example 9-1.

SET NOCOUNT ON;
IF DB_ID('testdb') IS NULL
 CREATE DATABASE testdb;
GO
USE testdb;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
 DROP TABLE dbo.T1;
IF OBJECT_ID('dbo.T2') IS NOT NULL
 DROP TABLE dbo.T2;
GO

CREATE TABLE dbo.T1
(
 keycol INT         NOT NULL PRIMARY KEY,
 col1   INT         NOT NULL,
 col2   VARCHAR(50) NOT NULL
);

INSERT INTO dbo.T1(keycol, col1, col2) VALUES(1, 101, 'A'),
INSERT INTO dbo.T1(keycol, col1, col2) VALUES(2, 102, 'B'),
INSERT INTO dbo.T1(keycol, col1, col2) VALUES(3, 103, 'C'),

CREATE TABLE dbo.T2
(
 keycol INT         NOT NULL PRIMARY KEY,
 col1   INT         NOT NULL,
 col2   VARCHAR(50) NOT NULL
);

INSERT INTO dbo.T2(keycol, col1, col2) VALUES(1, 201, 'X'),
INSERT INTO dbo.T2(keycol, col1, col2) VALUES(2, 202, 'Y'),
INSERT INTO dbo.T2(keycol, col1, col2) VALUES(3, 203, 'Z'),
GO

The contents of T1 and T2 are shown in Table 9-1 and Table 9-2, respectively.

As a basic example, the following code issues two INSERT statements in a single transaction:

BEGIN TRAN
  INSERT INTO dbo.T1(keycol, col1, col2) VALUES(4, 101, 'C'),
  INSERT INTO dbo.T2(keycol, col1, col2) VALUES(4, 201, 'X'),
COMMIT TRAN

You enclose the two statements in a single transaction if you want the whole unit to be atomic–either both must succeed or both must fail. If your server fails for some reason, and only the first INSERT statement is recorded in the transaction log, the recovery process will roll back the partial transaction. On the other hand, if the transaction is recorded in the transaction log in full but not yet flushed to the data portion of the database (committed after the last checkpoint), the recovery process will redo it.

Note that SQL Server doesn’t automatically roll back a transaction as a result of any failure. For example, constraint violations or lock timeout expirations leave a transaction open by default. You can write exception-handling code to determine whether you want to explicitly roll the transaction back or commit it. If you want all errors to cause a rollback of a transaction, set the XACT_ABORT session option to ON. Note that when exception handling is used, the XACT_ABORT option has a different effect. I’ll discuss exception handling in the next chapter.