SQL TCL Scripting for Automation

• COMMIT accepts a valid unit; ROLLBACK undoes it.
• Use SET XACT_ABORT ON and TRY/CATCH for reliable server-side error handling.
• An inner COMMIT does not commit the outer transaction.
• Design retries with an idempotency key because connection loss can hide a successful commit.

1. What Is TCL?

TCL means Transaction Control Language. It controls the boundary and outcome of a transaction.

SQL Server starts and ends transactions according to session settings and statements. Every application should know who owns the transaction: the server procedure, the client connection or a higher-level coordinator.

2. ACID Properties in Automation Data

A transaction cannot correct a bad process model. Constraints, keys, validation and idempotency still define what “consistent” means.

3. Autocommit, Implicit and Explicit Transactions

SELECT @@OPTIONS AS [SessionOptions];
DBCC USEROPTIONS;

-- Explicitly control this setting for predictable scripts.
SET IMPLICIT_TRANSACTIONS OFF;

4. BEGIN TRANSACTION and COMMIT

BEGIN TRANSACTION;

UPDATE [dbo].[tblEvent]
SET [Temp_Act] = 949.250
WHERE [ID] = 1250;

IF @@ROWCOUNT <> 1
BEGIN
    ROLLBACK TRANSACTION;
    THROW 50020, 'Expected one event row.', 1;
END;

COMMIT TRANSACTION;

Start the transaction immediately before the first required statement. Validate while it is open, commit promptly and return a clear outcome to the caller.

5. ROLLBACK TRANSACTION

BEGIN TRANSACTION;

DELETE FROM [dbo].[tblEvent]
WHERE [ChargeNo] = 'CH-LAB-ROLLBACK';

SELECT @@ROWCOUNT AS [RowsDeletedInsideTransaction];

ROLLBACK TRANSACTION;

SELECT COUNT_BIG(*) AS [RowsAfterRollback]
FROM [dbo].[tblEvent]
WHERE [ChargeNo] = 'CH-LAB-ROLLBACK';

A full rollback undoes data changes and releases transaction resources. Changes to local variables and table variables are not undone, so do not treat their content as proof that a database change committed.

6. Define the Automation Unit of Work

Group statements only when the business rule requires them to succeed together. This example records a transition and closes the previous event.

DECLARE @PreviousEventID int = 1249;

BEGIN TRANSACTION;

INSERT INTO [dbo].[tblEvent]
    ([DT], [SQF_No], [ChargeNo], [Event_From], [Event_To], [Temp_Set], [Temp_Act], [Fan_Status])
VALUES
    (SYSUTCDATETIME(), 2, 'CH-2026-0050', 'Heating', 'Soaking', 950.000, 949.250, 1);

UPDATE [dbo].[tblEvent]
SET [Event_To] = 'Soaking'
WHERE [ID] = @PreviousEventID AND [SQF_No] = 2;

IF @@ROWCOUNT <> 1
BEGIN
    ROLLBACK TRANSACTION;
    THROW 50021, 'Previous event not found.', 1;
END;

COMMIT TRANSACTION;

Do not include unrelated report queries, file writes, email calls or operator confirmations inside this database transaction.

7. TRY/CATCH with SET XACT_ABORT ON

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO [dbo].[tblEvent]
        ([DT], [SQF_No], [ChargeNo], [Event_From], [Event_To])
    VALUES
        (SYSUTCDATETIME(), 2, 'CH-2026-0051', 'Load', 'Heating');

    UPDATE [dbo].[tblEvent]
    SET [Fan_Status] = 1
    WHERE [ID] = 1250;

    IF @@ROWCOUNT <> 1
        THROW 50022, 'Expected one target event.', 1;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;
    THROW;
END CATCH;

XACT_ABORT ON causes many runtime errors to terminate and roll back a transaction. TRY/CATCH ensures your code inspects transaction state, cleans up and preserves the original error with THROW.

8. XACT_STATE and @@TRANCOUNT

SELECT
    XACT_STATE() AS [TransactionState],
    @@TRANCOUNT AS [TransactionCount];

Use XACT_STATE() to decide whether a transaction can commit. Use @@TRANCOUNT to understand ownership/nesting, not to prove durability.

9. SAVE TRANSACTION and Partial Rollback

A savepoint marks a location inside an active transaction.

BEGIN TRANSACTION;

INSERT INTO [dbo].[tblEvent]
    ([DT], [SQF_No], [ChargeNo], [Event_From], [Event_To])
VALUES
    (SYSUTCDATETIME(), 2, 'CH-SAVEPOINT', 'Load', 'Heating');

SAVE TRANSACTION [BeforeOptionalCorrection];

UPDATE [dbo].[tblEvent]
SET [Temp_Act] = 9999.000
WHERE [ChargeNo] = 'CH-SAVEPOINT';

-- Undo only the optional correction.
ROLLBACK TRANSACTION [BeforeOptionalCorrection];

-- The INSERT before the savepoint remains part of this transaction.
COMMIT TRANSACTION;

10. Nested Transaction Behavior

BEGIN TRANSACTION;       -- @@TRANCOUNT = 1
BEGIN TRANSACTION;       -- @@TRANCOUNT = 2

COMMIT TRANSACTION;      -- @@TRANCOUNT = 1; not yet finally committed
COMMIT TRANSACTION;      -- @@TRANCOUNT = 0; outer transaction completes

An inner COMMIT only decrements @@TRANCOUNT. A ROLLBACK TRANSACTION without a savepoint rolls back the entire outer transaction and sets the count to zero.

11. Transaction Isolation Levels

Isolation controls which concurrent changes a transaction can observe and how reads interact with writes.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
-- Required statements
COMMIT TRANSACTION;

Do not select an isolation level by name alone. Test correctness, blocking, version-store cost and throughput with the actual acquisition/report workload.

12. Row-Versioning Isolation

READ_COMMITTED_SNAPSHOT changes read-committed behavior to use row versions for many reads. SNAPSHOT gives a transaction-consistent view when enabled and requested.

Avoid using NOLOCK as a universal “performance fix.” It permits dirty and inconsistent reads and does not solve every form of blocking.

13. Locks and Blocking

Writers acquire locks to protect changes. Another session may wait until the transaction commits or rolls back.

SELECT
    r.[session_id], r.[status], r.[command],
    r.[blocking_session_id], r.[wait_type], r.[wait_time],
    r.[transaction_id]
FROM sys.dm_exec_requests AS r
WHERE r.[session_id] <> @@SPID
  AND (r.[blocking_session_id] <> 0 OR r.[wait_type] IS NOT NULL);

Reading this DMV requires appropriate diagnostic permissions. Solve blocking by shortening transactions, indexing predicates and reducing unnecessary work—not by killing sessions blindly.

14. Deadlocks and Bounded Retry

A deadlock occurs when sessions wait in a cycle. SQL Server chooses a victim and rolls that transaction back.

• Access tables and keys in a consistent order.
• Keep transactions short.
• Create indexes that avoid wide scans.
• Capture and analyze the deadlock graph.
• Retry the complete unit only for recognized transient errors.
• Use a bounded retry count with delay/jitter.
• Make the operation idempotent before retrying.

15. Why Long Transactions Hurt Runtime

16. Transactions Inside Stored Procedures

A simple procedure that owns its unit can use this pattern:

CREATE OR ALTER PROCEDURE [dbo].[usp_RecordTwoEvents]
    @SQF_No int,
    @ChargeNo varchar(50)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT dbo.tblEvent (DT, SQF_No, ChargeNo, Event_From, Event_To)
        VALUES (SYSUTCDATETIME(), @SQF_No, @ChargeNo, 'Load', 'Heating');

        INSERT dbo.tblEvent (DT, SQF_No, ChargeNo, Event_From, Event_To)
        VALUES (DATEADD(MILLISECOND, 1, SYSUTCDATETIME()), @SQF_No, @ChargeNo, 'Heating', 'Soaking');

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;
END;
GO

If callers may already own a transaction, use a reviewed composable pattern with the entry @@TRANCOUNT and a savepoint. Do not improvise nested commits.

17. Transactions from ODBC, ADO or Python

A client transaction belongs to one live connection. Every command in the unit must use that same connection and transaction object.

Open connection
Begin transaction on that connection
Try:
    Execute parameterized INSERT
    Execute parameterized UPDATE
    Validate returned ID and affected-row counts
    Commit
Catch:
    Roll back if transaction is active
    Record the original error
Finally:
    Close/dispose the transaction and connection

Do not place GO inside an application command. Do not open a second connection for one of the statements and assume it joins the first local transaction.

18. Connection Loss and Idempotency

The network can fail after SQL Server commits but before the client receives confirmation. Retrying a plain INSERT can create a duplicate.

-- Example source-generated event key protected by a UNIQUE constraint.
INSERT INTO [dbo].[tblEventInbox]
    ([SourceEventID], [ReceivedUtc], [Payload])
VALUES
    (@SourceEventID, SYSUTCDATETIME(), @Payload);

Generate a stable source event ID before the first attempt, enforce uniqueness and return the existing result when a retry presents the same key. The key must identify the same logical event, not a new attempt.

19. Local vs Distributed Transactions

A local transaction covers one SQL Server connection/resource manager. Accessing linked servers or multiple transactional resources can promote the unit to a distributed transaction that depends on additional infrastructure and has different failure modes.

• Prefer one database-owned procedure when possible.
• Avoid network calls inside the transaction.
• Use an outbox/message pattern for reliable cross-system workflows.
• Test coordinator outages and recovery if distributed transactions are unavoidable.
• Remember: savepoint rollback is not supported in distributed transactions.

20. Verify Transaction Behavior

-- Verify no transaction remains open in the current session.
SELECT XACT_STATE() AS [XactState], @@TRANCOUNT AS [TranCount];

-- Verify the logical unit.
SELECT [ID], [DT], [SQF_No], [ChargeNo], [Event_From], [Event_To]
FROM [dbo].[tblEvent]
WHERE [ChargeNo] = @ChargeNo
ORDER BY [DT], [ID];

-- During a controlled test, inspect the oldest active transaction.
DBCC OPENTRAN (N'SQF_DB');

21. Common TCL Errors and Fixes

BEGIN TRANSACTION;
INSERT dbo.tblEvent (DT, SQF_No, ChargeNo, Event_From, Event_To)
VALUES (SYSUTCDATETIME(), 2, 'CH-TCL-LAB', 'Load', 'Heating');
SELECT XACT_STATE() AS XactState, @@TRANCOUNT AS TranCount;
ROLLBACK TRANSACTION;