CODEWITHSUNDEEP
sqlsql-server
dearn44
dearn44

Reputation: 3422

Error with uncommitted transaction

I have the following query that I ran in SQL Server Management Studio:

BEGIN TRANSACTION [Tran1] 
BEGIN TRY
    UPDATE SomeTable 
    SET value = 0 
    WHERE username = 'test' 
       OR [PR_RequestDate] < DATEADD(day, -2, GETDATE())

    INSERT INTO SomeTable (username, value) 
    VALUES('test', 'test')

    COMMIT TRANSACTION [Tran1]
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION [Tran1]
END CATCH  
GO

After executing it every other query that I try to run on the same table runs forever, and when I try to exist SQL Server Management Studio, I get a warning that there are uncommitted transactions.

What exactly is wrong with my query and how should I fix it?

Upvotes: 1

Views: 1061

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28900

Typos will be caught immediately,further as per Ivan suggestionmyou are not showing us entire info.You are getting uncommitted transactions error due to error in update or insert and the entire query trying to rollback..You can do the follow to get total message and trancount,,

set xact_abort on--to rollback entire batch,rollback in catch is redundant
begin try
begin tran
commit
end try
begin catch
select @@trancount--gives me number of open transactions
select error_message()
rollback tran
end catch

Upvotes: 1

Related Questions