MS-SQL Server Implicit_transaction on table

Question

I'm trying to experiment with implicit_transaction on sql server. However, after executing:

SET IMPLICIT_TRANSACTIONS ON;

INSERT INTO [Interface].[dbo].[Log] ([String], [Time], [theUser]) VALUES ((SELECT [String] FROM [Interface].[dbo].[Log] WHERE [ID] = '128'), null, 'ROW3');

SET IMPLICIT_TRANSACTIONS OFF;

However When I go to do a SELECT, the query hangs.

What did I do wrong?

Answer 1

From the documentation:

Transactions that are automatically opened as the result of this setting being ON must be explicitly committed or rolled back by the user at the end of the transaction. Otherwise, the transaction and all of the data changes it contains are rolled back when the user disconnects. After a transaction is committed, executing one of the statements above starts a new transaction.

So, in effect, you did this:

begin transaction;
INSERT INTO [Interface].[dbo].[Log] ([String], [Time], [theUser]) 
VALUES ((SELECT [String] FROM [Interface].[dbo].[Log] WHERE [ID] = '128'), null, 'ROW3');
--no commit or rollback, so transaction is still open

In short, you have an open transaction that you need to do something with.