A database transaction groups one or more data-changing T-SQL statements into one logical unit: all changes commit together, or all are undone with a rollback. Its core ACID properties are atomicity, consistency, isolation, and durability. section 7.1
Mistake #26: assuming an error automatically rolls back a transaction. A stored procedure can fail after making an earlier change, leaving an open transaction behind. Check SELECT @@TRANCOUNT; when no transaction should remain open, it should be 0. Roll back the outstanding transaction before continuing. section 7.1
Sales order insert failure
An order procedure inserts a header and a detail row inside a transaction. Renaming the detail table makes the detail insert fail. SQL Server reports both the invalid object and a mismatched transaction count, because the transaction remains open rather than being rolled back automatically. section 7.1 section 7.1
Listing 7.1 Creating the dbo.InsertSalesOrder stored procedure
CREATE PROCEDURE dbo.InsertSalesOrder
@SalesOrderNumber NVARCHAR(12),
@SalesOrderDate DATE,
@SalesPersonID INT,
@SalesAreaID INT,
@CustomerID INT,
@SalesOrderDeliveryDueDate DATE,
@SalesOrderDeliveryActualDate DATE,
@CurrierUsedForDelivery NVARCHAR(32),
@ProductID INT,
@Quantity INT
AS
BEGIN
BEGIN TRANSACTION
DECLARE @CustomerPrefix NVARCHAR(12) ;
SET @CustomerPrefix = (
SELECT SUBSTRING(CustomerCompanyName,1,3)
FROM dbo.Customers
WHERE CustomerID = @CustomerID
) ;
INSERT INTO dbo.SalesOrderHeaders
VALUES (
@CustomerPrefix + @SalesOrderNumber
, @SalesOrderDate
, @SalesPersonID
, @SalesAreaID
, @CustomerID
, @SalesOrderDeliveryDueDate
, @SalesOrderDeliveryActualDate
, @CurrierUsedForDelivery
) ;
INSERT INTO dbo.SalesOrderDetails (
ProductID
, Quantity
, SalesOrderNumber
)
VALUES (
@ProductID
, @Quantity
, @CustomerPrefix + @SalesOrderNumber
) ;
COMMIT
END
Inspect the order procedure to see a transaction wrapped around related header and detail inserts—the kind of work that must succeed or be undone as a group. section 7.1
Use transactions defensively:
- Put related updates that must stand or fall together in the same transaction. section 7.1
- Handle failures explicitly with TRY...CATCH and rollback logic rather than relying on an automatic rollback. section 7.1
- Avoid deleting a large number of rows in one transaction; split the work into batches to reduce the risk of filling the transaction log. section 5.9
