Jump Start: Tracking Transactions

My "Using Transactions" article prompted some questions from readers about how transactions work, when and where the data is written, and whether the data is put into the database before the transaction is committed.

SQL Server and SQL Server Express databases are stored in two types of files. Database data is stored in data files, which have an .mdf extension. Transactions are stored in transaction log files, which have an .ldf extension. The data is written to both the transaction log file and the data file.

When you use a BEGIN TRANSACTION statement to start a transaction, you essentially mark a set of related data in the transaction log. SQL Server can use that transaction log marker to pull the data out of the data file in the event of a ROLLBACK TRANSACTION operation.

The sample code below can help illustrate how transactions work. The code contains a multistatement transaction that consists of two INSERT statements.

USE Northwind

GO

BEGIN TRANSACTION MyTempRegion;

INSERT INTO Region (RegionID, RegionDescription)

 VALUES (5, 'Middle');

INSERT INTO Territories (TerritoryID, TerritoryDescription, RegionID)

 VALUES (55555, 'Chicago', 5);

-- The rows are added

SELECT TerritoryDescription, RegionDescription

FROM Territories INNER JOIN Region

ON Territories.RegionID = Region.RegionID

WHERE Territories.RegionID = 5

ROLLBACK TRANSACTION MyTempRegion;

-- The rows are gone

SELECT TerritoryDescription, RegionDescription

FROM Territories INNER JOIN Region

ON Territories.RegionID = Region.RegionID

WHERE Territories.RegionID = 5

The code first starts a transaction, which inserts data into two tables. The transaction then executes a SELECT statement. If you run the code, the results of that SELECT statement will show that data has been written to the database--no COMMIT statement is needed to write the data to the database. Because the INSERT and SELECT statements are within a transaction, the data is also written to the transaction log file and marked as a transaction.

Next, the ROLLBACK TRANSACTION statement is run, which causes the data that's marked in the transaction log to be pulled out of the database. Finally, another SELECT statement is executed. If you run the code, the second SELECT statement will show that the data that had originally been added to the tables is no longer there.

Comments

Plain text