Transcript

How do I tell if my apps are using repeatable read and serializable isolation levels?

Well, the best way to find this is by checking your app code and your connection strings: actually looking in the application. But sometimes it’s a vendor app, and we don’t have the source code.

Sometimes it’s a legacy app, and we don’t have the source code. Sometimes there are political reasons that make it difficult to get the source code. There’s a variety of reasons. We can look for signs in the SQL Server, of whether these isolation levels are employed, but it’s not perfect.

It’s complex because it’s flexible

I can set my isolation level at the session level. I can also use hints. I can run a query where I’m using multiple isolation levels, because I’ve hinted it for one table, and maybe not hinted on another and my default is different. I can do that. So this is difficult to find, but we can see signs of this. Also that escalation in isolation, you know, that foreign key escalation, that’s difficult to see too, because my query didn’t even ask for serializable.

You can look for range lock waits

One thing I can do: when we look at sys.dm_os_wait_stats– which is wait stats since start-up, or the last time they were manually cleared– we can say, do we have any range lock waits in there? If we have range lock waits that have occured, then something’s using serializable. Whether it’s by isolation level escalation, whether it by the a holdlock hint, whether it’s by ‘set transaction isolation level serializable’.

Something’s using that.

Since this gets cleared out when everything’s taken offline I would need to check this regularly or collect this.

And this also doesn’t tell me who was doing it, it tells me that it happened.

We don’t really have a good way to look for this (the range lock waits) in Query Store. Query Store at this point– which is available in SQL Server 2016 and higher– it is using a larger wait category. So I can see a there were lock waits, but I don’t get key range locks, or anything like that. Not taken care of yet by Query Store, I don’t know if it ever will be because it’s not really a monitoring tool. It’s more of an aggregate performance tool. It keeps things aggregate to help keep them light-weight. I don’t know if we’ll ever get the detail of key range locks out of Query Store.

You can look at the session-level isolation setting for connected sessions

We can also look at all the sessions that are connected to the SQL Server, and look at what their isolation level for the session level is. Transaction isolation level 2 is read committed. I’m saying are there any sessions connected with a different isolation level. Right now, there are not.

If I set my isolation level to repeatable read when I go and run this again now it says well, here is your session, session 57, the isolation level is 3, which is repeatable read.

I can see for sessions that are connected, what is their isolation level.

You can look for isolation hints in queries in the plan cache

I could also look for queries who have hinted isolation levels. Maybe I’m looking in the Query Store in a database that’s collected query plans [by having Query Store enabled]. Maybe I’m looking in the plan cache. But if queries are using a hint as part of the query text, I do get query text in my plan cache and in Query Store. This query is hinting repeatable read. This query is using a holdlock hint which hints serializable [as does the serializable hint, they are equivalent].

Since I ran those, now those queries: if I look at my execution plan cache… I’m simply I’m doing, you know, this is not very elegant TSQL. I’m looking for all the stuff in sys.dm_exec_query_stats, and I’m getting the text and the plan, and just doing a brute search and saying– the text has one of these hints in there:

• repeatableread
• holdlock
• serializable

I have done a lower, so that I don’t have to account for the fact taht different cases could be used, so I have done a lower on the text, so that I at least only have to do my hacky search once. But is not an elegant, refined way to search. It could take a long-time to run it you have a big query cache. But at least I can find that these happen to be currently in my cache.

My cache may be volatile, right, I may miss something. I can look for these traces of it, but there’s no simple: absolutely yes / absolutely no, and these are queries to find it, because of the flexibility that we have in using it.