September 24, 2018 at 10:42 am
Thanks in advance for your help.
I have AGs set up with "per DTC support" on SQL Server 2017 with CU6.
When I execute the following as a single batch, it fails with the error:
Msg 3996, Level 16, State 1, Line 16
Snapshot isolation level is not supported for distributed transaction. Use another isolation level or do not use distributed transaction.
However, when I run Section A and Section B in separate batches, I do not get any errors. The second batch does return snapshot isolation.
So this is telling me that when I create the temp table in the same batch that I use it, it's considered a distributed transaction for some reason, but not when I insert to it?
CODE EXAMPLE:
--SECTION A
set transaction isolation level snapshot
set implicit_transactions off
set nocount on
drop table #SSIDs
create table #SSIDs (SSID bigint PRIMARY KEY CLUSTERED, sts tinyint)
create nonclustered index ix1 on #ssids (sts)
truncate table #SSIDs
--SECTON B
begin transaction
insert #SSIDs (SSID, sts)
select SSID, 0
from mydb.dbo.mytable
where ([timeout] is not null and DateDiff(mi, LastRequest, getdate()) > [Timeout])
or ([timeout] is null and DateDiff(mi, LastRequest, getdate()) > 20)
SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id = @@SPID
commit
September 27, 2018 at 7:48 pm
I have not tried to repro (I don't have a mydb.dbo.mytable). Seems buggy. I see no enlisted distributed tran and I see no linked server. Have you tried simplifying this further? For example, test using mydb and not referencing mydb in the select statement, and test not creating ix1.
October 2, 2018 at 12:22 pm
Just FYI, worked with MS on this and they recommended Trace flag 3451, which changes the scope of the DTC transaction from per database to per instance.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply