run job with uncommitted transaction - how to find the transaction? is it still active?

Question

By mistake I have run the job I just put into production, but the COMMIT TRANSACTION was commented out.

when I look at the job log, it printed that it did my updates with no problem.

when I run

dbcc opentran

it says:

No active open transactions. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

no locks or blocks are in place, but where is my transaction?

the code for my job is here: with the commit transaction commented out

--================================================================
--RUN THE UPDATE
--================================================================

BEGIN TRANSACTION T1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SELECT @@TRANCOUNT

BEGIN TRY

UPDATE DEStock.DBO.ItemStock 
SET     QtyOnOrder = 0 ,
        DueDate = NULL
FROM DEStock.DBO.ItemStock T
INNER JOIN TABLEBACKUPS.DBO.__RADHE R
        ON T.ITEMNO = R.ITEMNO

print cast ( @@rowcount  as varchar) + ' updating DEStock.DBO.ItemStock '


-- no updates for france
--UPDATE FRStock.DBO.ItemStock 
--SET     QtyOnOrder = 0 ,
--        DueDate = NULL
--FROM FRStock.DBO.ItemStock T
--INNER JOIN TABLEBACKUPS.DBO.__RADHE R
--        ON T.ITEMNO = R.ITEMNO

--print cast ( @@rowcount  as varchar) + ' updating FRStock.DBO.ItemStock '

-- us is on a different schedule
--UPDATE USStock.DBO.ItemStock 
--SET     QtyOnOrder = 0 ,
--        DueDate = NULL
--FROM USStock.DBO.ItemStock T
--INNER JOIN TABLEBACKUPS.DBO.__RADHE R
--        ON T.ITEMNO = R.ITEMNO

--print cast ( @@rowcount  as varchar) + ' updating USStock.DBO.ItemStock '

UPDATE EUStock.DBO.ItemStock 
SET     QtyOnOrder = 0 ,
        DueDate = NULL
FROM EUStock.DBO.ItemStock T
INNER JOIN TABLEBACKUPS.DBO.__RADHE R
        ON T.ITEMNO = R.ITEMNO

print cast ( @@rowcount  as varchar) + ' updating EUStock.DBO.ItemStock '   

--COMMIT TRANSACTION T1

END TRY
BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    WHILE @@TRANCOUNT > 0
         ROLLBACK TRANSACTION

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH

the table used in the join comes from here:

how to insert a list of varchar values, into a single column table?

Answer 1

Non-committed transactions will be automatically rolled-back if the session is terminated.

You can easily test this by:

USE tempdb;

CREATE TABLE dbo.TestTran
(
    ID INT NOT NULL
        CONSTRAINT PK_TestTran
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

BEGIN TRAN
INSERT INTO dbo.TestTran DEFAULT VALUES;
SELECT * FROM dbo.TestTran;

Results will show a row inserted into dbo.TestTran.

Now, disconnect and reconnect the session.

--disconnect and reconnect

USE tempdb;

SELECT * FROM dbo.TestTran;

The results from the above SELECT statement will return no results since the non-committed transaction was automatically rolled back when the session was disconnected.

This is a primary tenet of all ACID-compliant database management systems.

Atomicity

A transaction is a unit of work in which a series of operations occur between the BEGIN TRANSACTION and END TRANSACTION statements of an application. A transaction executes exactly once and is atomic — all the work is done or none of it is.

Once you disconnect the session (or it completes without a COMMIT TRAN), the transaction will rollback to ensure none of the work has been done since the BEGIN TRAN statement.

Answer 2

Uncommitted transactions are held open until the connection is terminated, and then they are rolled-back. So in connection-pooling scenarios, they will he held open longer since the connection has not terminated.

For a SQL Agent job, it might not be using connection pooling, so the distinction between Session and Connection might not be obvious.