I've come across a problem while learning transaction isolation levels in SQL server.
The problem is that after I run this code (and it finishes without errors):
set implicit_transactions off;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN T1;
SELECT (...)
WAITFOR DELAY '00:00:5'
SELECT (...)
WAITFOR DELAY '00:00:3'
COMMIT TRAN T1;
I want to run this query:
set implicit_transactions off;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION T2;
INSERT (...)
INSERT (...)
COMMIT TRANSACTION T2;
But it just says "Executing query", and does nothing. I think it's because the lock on the tables somehow continues after the first transaction has been finished. Can someone help? Of course the selects and the inserts refer to the same tables.
Either the first tran is still open (close the window to make sure it is not), or some other tran is open (
exec sp_who2
). You can't suppress X-locks taken by DML because SQL Server needs those locks during rollback.