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?
Best Answer
Non-committed transactions will be automatically rolled-back if the session is terminated.
You can easily test this by:
Results will show a row inserted into
dbo.TestTran
.Now, disconnect and reconnect the session.
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.
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 theBEGIN TRAN
statement.