Complete Guide to Transaction Rollback and Commit in SQL Server: Error Handling with TRY-CATCH

Fundamental Concepts of Transaction Management

In database systems, transactions are the core mechanism for ensuring data consistency and integrity. SQL Server provides complete transaction control capabilities through BEGIN TRANSACTION, COMMIT, and ROLLBACK statements. The ACID properties (Atomicity, Consistency, Isolation, Durability) of transactions guarantee the reliability of database operations.

Problem Scenario Analysis

In practical development, scenarios requiring execution of multiple related SQL statements are common. As shown in the user's stored procedure example, three INSERT statements insert data into different tables:

declare @QuantitySelected as char
set @QuantitySelected = '2'

declare @sqlHeader as varchar(1000)
declare @sqlTotals as varchar(1000)
declare @sqlLine as varchar(1000)

-- Construct three dynamic SQL statements
select @sqlHeader = 'Insert into tblKP_EstimateHeader (CompanyID,CompanyName,ProjectName,EstimateID,EstimateHeader,QuoteDate,ValidUntil,RFQNum,Revision,Contact,Status,NumConfigurations) select CompanyID,CompanyName,ProjectName,EstimateID,EstimateHeader,QuoteDate,ValidUntil,RFQNum,Revision,Contact,Status,NumConfigurations from V_EW_Estimate_Header where EstimateID = 2203'

select @sqlTotals = 'Insert into tblKP_Estimate_Configuration_Totals (ConfigRecId,RecId,SellQty,ConfigNum,ConfigDesc,SortOrder,OptionsInMainPrice,MarkupPctQty,SellPriceQty,RubberStamp,OptPriceQty,StatusRecid,LastUpdate_Date,LastUpdate_User,TotalCost,QuantityBracketSelected) select ConfigRecId,RecId,SellQty' + @QuantitySelected + ',ConfigNum,ConfigDesc,SortOrder,OptionsInMainPrice,MarkupPctQty' + @QuantitySelected + ',SellPriceQty' + @QuantitySelected + ',RubberStamp,OptPriceQty' + @QuantitySelected + ',StatusRecid,LastUpdate_Date,LastUpdate_User,TotalCost' + @QuantitySelected + ',' + @QuantitySelected + ' from v_EW_Estimate_Configuration_Totals where ConfigRecId = -3'

select @sqlLine = 'Insert into tblKP_Estimate_Configuration_Lines (MstrRfqRecId,RfqRecId,RfqLineRecId,CompanyId,VendorQuoteNum,LineGrp,LineNum,StatusRecId,LineDesc,LineSize,LineMatl,LineDeco,LineFinish,CopyFromRecId,PerPieceCost,IsOptional,CopyToNewRev,RecId,UnitPrice,LineQty,LinePrice,CustOrVend,SellQty1,RfqNum,ConfigLineIsOptional,ConfigLinePerPieceCost,ConfigLineRecid,SellPrice,SaleQty) select distinct MstrRfqRecId,RfqRecId,RfqLineRecId,CompanyId,VendorQuoteNum,LineGrp,LineNum,StatusRecId,LineDesc,LineSize,LineMatl,LineDeco,LineFinish,CopyFromRecId,PerPieceCost,IsOptional,CopyToNewRev,RecId,UnitPrice' + @QuantitySelected + ',LineQty' + @QuantitySelected + ', isnull(LinePrice' + @QuantitySelected + ', 0.0000),CustOrVend,SellQty' + @QuantitySelected + ',RfqNum,ConfigLineIsOptional,ConfigLinePerPieceCost,ConfigLineRecid,SellPrice' + @QuantitySelected + ',SaleQty' + @QuantitySelected + ' from v_EW_EstimateLine  where rfqlinerecid in (select RfqLineRecID from kp_tblVendorRfqConfigLine where ConfigRecID = -3)'

-- Execute three insert operations
exec(@sqlHeader)
exec(@sqlTotals)
exec(@sqlLine)

When primary key constraint violations occur, the first two statements fail while the third executes successfully, compromising data consistency. The solution is to encapsulate these operations within a transaction.

TRY-CATCH Transaction Handling Pattern

SQL Server's TRY-CATCH mechanism provides robust error handling capabilities for transaction management. The recommended implementation pattern is:

BEGIN TRY
    BEGIN TRANSACTION
        exec(@sqlHeader)
        exec(@sqlTotals)
        exec(@sqlLine)
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
END CATCH

Key Technical Analysis

Transaction Boundary Management: BEGIN TRANSACTION and COMMIT TRANSACTION define the start and end points of a transaction. Within the TRY block, all database operations either succeed completely or fail entirely.

Error Capture Mechanism: When any EXEC statement raises an error, execution flow immediately jumps to the CATCH block. The @@TRANCOUNT system function detects whether active transactions exist, ensuring rollback occurs only when necessary.

Dynamic SQL Handling: SQL construction code should reside outside transactions, adhering to the principle of "keeping transactions as short as possible." This reduces lock contention and resource occupation time.

Best Practice Recommendations

Based on discussions in reference articles, for ad-hoc operations, a more cautious approach is recommended:

DECLARE @Testing BIT = 1
BEGIN TRANSACTION
    -- Execute update operation
    UPDATE B
    SET expired = -1
    FROM GCDF_DB..BillingThirdNotice B
    INNER JOIN GCDF_DB..Certs C
    ON B.PeopleID = C.PeopleID
    WHERE C.certificationExpireDate < GETDATE()
    AND B.billMonth = 3
    AND B.processed = 0

IF @Testing = 1
    ROLLBACK TRANSACTION
ELSE
    COMMIT TRANSACTION

This method controls transaction commit or rollback through testing variables, particularly suitable for development and testing environments. Additionally, using semicolons to terminate all SQL statements is recommended to improve code readability and consistency.

Performance and Concurrency Considerations

Long-running transactions can cause significant blocking issues. In production environments, you should:

• Pre-calculate affected row counts
• Schedule transaction execution appropriately
• Monitor transaction duration
• Ensure backup strategy effectiveness

Through proper transaction design and error handling, robust and reliable database applications can be built, ensuring data remains consistent under various exceptional conditions.