Transaction Isolation Level when Querying a View
-
I was hoping to get some confirmation on specific details regarding how SQL Server deals with queries involving a view which targets a different database (on the same instance of the server), with respect to transaction isolation level.
I have two databases, DB1 and DB2 on the same SQL Server instance. DB1 has a table called T_Data.
DB2 has a view called T_Data which does a select of all columns from db1.dbo.T_Data.
DB1 is a production database and has Snapshot Isolation enabled, and Read Committed Snapshot enabled.
DB2 has neither of the Snapshot options enabled and is used for reporting.
There is a stored procedure in DB2 that does a query similar to the following:
set transaction isolation level read uncommitted
select [fields] from Local_Table
LEFT OUTER JOIN T_Data on [ join condition ]Therefore this query is joining with the T_Data "view" in DB2, which references DB1.
My question is this: will SQL Server carry through the "Read Uncommitted" isolation level set in the stored procedure all the way through to querying DB1.T_Data via the View, or not?
The reason I ask is that executing the stored procedure is causing a massive performance hit to users interacting with DB1, even though we are not seeing spikes in CPU or DIsk usage on the server, which leads me to believe there is contention occurring (ie. locking contention).
But that "shouldn't" be happening if READ UNCOMMITTED is being used, should it?
- Kevin Frey - Tuesday, November 28, 2017 8:40 PM
Read uncommitted and nolock only ignores other locks on the objects which can lead to incorrect data being returned. It does not prevent locks being taken!
You are probably better off replicating that table into reporting, to me this looks like a broken design.
- Eirikur Eiriksson - Tuesday, November 28, 2017 10:26 PMRead uncommitted and nolock only ignores other locks on the objects which can lead to incorrect data being returned. It does not prevent locks being taken!
You are probably better off replicating that table into reporting, to me this looks like a broken design.
At this stage fixing the design is not an option which is open to me -- it is not my design so to start with I just want to understand the implications.
Regarding NOLOCK, I have to admit I was unaware that it still acquired locks. I would presume that it cannot acquire any exclusive locks, but nor does it need to because it is doing a SELECT operation.
Is your inference then that NOLOCK might prevent the query from "blocking" against existing locks, but the locks acquired by the NOLOCK'ed query do not prevent contention for other writers? In other words:
a NOLOCK query won't be blocked by writers, BUT
writers can be blocked by a NOLOCK query?
- Kevin Frey - Wednesday, November 29, 2017 1:26 AM
All queries take locks of some sort - including select statements.
A NOLOCK hint in a query means it can do "dirty" (uncommitted) reads and an yes, exclusive locks won't block that query.
It also will not take out shared locks, not that it won't take out any locks. It will still take out sch-s locks.
Here is an interesting read if you are trying to understand it better:
Bad habits : Putting NOLOCK everywhereSue
- Kevin Frey - Wednesday, November 29, 2017 1:26 AM
No.
Read uncommitted does not take shared locks, so it does not block writers and it is not blocked by writers.
It will block and be blocked by a schema modification (eg ALTER TABLE)In exchange you may get incorrect number of rows returned (too many or too few)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - Eirikur Eiriksson - Tuesday, November 28, 2017 10:26 PMRead uncommitted and nolock only ignores other locks on the objects which can lead to incorrect data being returned. It does not prevent locks being taken!
It ignores locks because it does not take the shared locks that a select would normally take. It's not possible for a query to take locks and ignore incompatible locks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
OK, so Eirikur's comment about the query taking locks is technically correct but misleading (at least to me) because the Read Uncomitted query might be taking out schema locks but not data related locks such as TAB, PAG, RID ?
Can anyone explain why then a query on this view, using READ UNCOMMITTED, would cause such an impact on our production database if no locks (of consequence) are taken?
-
When you say a performance hit, what exactly are you talking about? What is occurring while the stored procedure is running?
-
It could be compilation for the stored procedure. Or it could be a lot of other things.
Have you been querying the locking when this performance hit happens? You mentioned no CPU or Disk spikes but what else are you seeing in the database when this happens? Have you looked at blocking, long running queries or processes, memory pressure, the server itself, things timing out, etc - just so many things.
If you don't have them, you may want to try running some of Glenn Berry's diagnostic queries. If you are fairly certain it is locking, there are several related to that which you could run:
SQL Server Diagnostic Information Queries for November 2017Those are the latest as of this writing on November 30 2017. Someone tomorrow or in five years will post or complain about this not being current.
Sue
-
I have yet to determine the specifics of what the performance hit is, but basically other users simply start to suffer very poor query performance and/or timeouts (this is via the web service supporting the application of course).
The database is small enough that it is effectively running from within memory (which explains the very low disk IO).
I will try the Glenn Berry diagnostic queries to see if they shed any light.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply