I am using remote connections to connect a client and server, after 6 months of working smoothly a transaction got stuck, probably because there was a cut in the connection while the transaction was running.
How can I prevent a transaction to get stuck in the case of a connection lost? Isn't SQL supposed to cancel the transaction if not finished in some time?
UPDATE:
I am using the default SQL Server isolation (Read commited) and this is the way to replicate it:
I tried SET XACT_ABORT is ON as suggested but no luck, problem remains, this is the sequence of events to replicate this issue:
Set a breakpoint in the middle of the transaction and start debugging
Once the transaction reached the breakpoint, disconnect the computer from network (simulating there was an abnormal disconnection)
Continue debugging the process and wait for .NET SqlClient to throw the error (No network)
Plug PC back to network (simulating internet connection has returned)
SQL Server does not finish or rollback the transaction, therefore tables used in the first middle of the transaction are locked
You Need to SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
Check out his link for More information
!https://learn.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017