fish-404
commented
2 years ago A case forgot to commit transactions to database
I have a query like codes below.
This stored procedure is called by .Net. When I test the function in .Net application, the exception will be captured in .Net application.
Exception message like below:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
When I realize the mistake, I have tried many times, both in .Net application and SQL Server Management Studio (2018). (In SSMS, the output statement will successfully output the result in Results tab, but shows the error message in Messages tab.)
Then I find the tables used in this transaction are locked. When I only select top 1000 without order desc, it can select the result. But when I select top 1000 with order desc, it will be running for a long time.
When I close the .Net application, the transaction was not committed (based on the data not changed in the transaction).
When I close the EXEC ... tab (which execute the forged commit query), SSMS will pop a warning window:
There are uncommitted transactions. Do you wish to commit these transactions?
I have tested the both the Yes and No choices.
If I click Yes, the transactions are committed.
If I click No, the transactions aren't committed.
After I close the tab, my locked table will be released, then I can query successfully.
begin try
-- some process
begin transaction
update ...
output ...
insert ...
-- I missing this commit statement below
commit transaction
end try
begin catch
if (xact_state()) = -1
begin
rollback transaction;
;throw
end;
-- this statement I want to compare to 1, but mistake write to -1, but since the throw statement let the mistake can't be triggerd
if (xact_state()) = 1
begin
commit transaction;
end;
end catch;SO about this problem : https://stackoverflow.com/questions/4896479/what-happens-if-you-dont-commit-a-transaction-to-a-database-say-sql-server/73455374
Reference: