CODEWITHSUNDEEP
sql-server
gordon613
gordon613

Reputation: 2952

Data locks caused by non-committed transactions

My web application is connected to a SQL Server 2016 Express database, and we have been plagued by data locks in certain areas of the system.

My colleague noticed just today that, when a KILL process was used to kill a long-running transaction, that several transactions that had ostensibly already been committed were rolled-back.

I have checked using @vladV's script on In SQL Server, how do I know what transaction mode I'm currently using? that in fact the database seems to be in auto-commit mode.

So therefore it must be that that something in the database is opening a new transaction and not committing it.

So I found in the database four stored procedures which contain the following

SET  IMPLICIT_TRANSACTIONS  ON
... code ...
IF @@TRAN_COUNT>0 COMMIT WORK

Am I right in saying that in some/most situations such a stored procedure would leave transactions open, even after exiting the stored procedure, and that this could be the source of the data-lock problems?

And if so, then could I just remedy the code by doing 

SET IMPLICIT_TRANSACTIONS OFF

when the stored procedure exits?

Upvotes: 0

Views: 1041

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88851

Am I right in saying that in some/most situations such a stored procedure would leave transactions open

Some. Depends on what comes after. With IMPLICIT TRANSACTIONS in SQL Server, transactions are not automatically started until you run a query that reads the database.

could I just remedy the code by doing SET IMPLICIT_TRANSACTIONS OFF

No. That won't end any open transactions.

Note that COMMIT doesn't reduce the @@trancount to 0. It decrements it by 1. So if you have multiple BEGIN TRAN statements, or an explicit BEGIN TRAN after an transaction has implicitly begun, then you will need multiple COMMITs.

You might try

WHILE @@trancount > 0 COMMIT TRANSACTION

which will definitely commit any outstanding transactions.

Upvotes: 1

Related Questions