Problem with/question about DTC and AGs and snapshot isolation.

Question

Problem with/question about DTC and AGs and snapshot isolation.

NJ-DBA

SSChampion

Points: 13834
More actions
September 24, 2018 at 10:42 am

#363492

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

SoHelpMeCodd SSCertifiable Points: 5871 More actions · Answer 1

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.

NJ-DBA SSChampion Points: 13834 More actions · Answer 2

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.