I have a BizTalk 2013 R2 polling WCF-SQL receive location that executes a Polled Data Available statement with a READPAST lock hint. The statement has worked flawlessly for the past several months but it stopped working after the SQL 2014 server being polled was upgraded to CU6. Now the event log is flooded with the following warning message every polling interval:
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels
I tried adding a WCF service behavior to the receive location to force a READ COMMITTED isolation level on the DTC transaction but it would appear that the polling statement is being executed outside the scope of the DTC.
I tested the same application against a separate copy of the database on a SQL 2014 CU5 server and the polling works with no warnings.
Any ideas?
Update: It looks like setting the isolation level as part of the Polled Data Available statement allows the READPAST hint to work:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; Select count(*) From dbo.Table with(READPAST) Where [Status] = 'READY'
However, I'm still concerned that SQL may no longer be honoring the isolation level set by the service behavior in the DTC transaction.
It appears that Microsoft fixed some issues with isolation levels failing to be reset when the SQL Connection is released (kb3025845). This leads me to believe that the CU6 behavior is intentional and I simply benefited from a bug. It still seems odd to me that the polled data avail. statement doesn't honor the WCF service behavior settings but that may be intentional as well.
I ended up just setting the isolation level in the PolledDataAvailableStatement to force the read committed isolation level.
Example:
set transaction isolation level read committed;
Select count(*) From dbo.Table with(READPAST) Where [Status] = 'READY'