Just announced on the Microsoft Release Services blog, if you run a SELECT query with the NOLOCK hint and your query goes parallel, it can block other queries.
This is a bug, and it will be fixed soon, but it is a very big deal for people who think NOLOCK means, uh, NOLOCK.
More technical details:
- While one transaction is holding an exclusive lock on an object (Ex. ongoing table update), another transaction is executing parallelized SELECT (…) FROM SourceTable, using the NOLOCK hint, under the default SQL Server lock-based isolation level or higher. In this scenario, the SELECT query trying to access SourceTable will be blocked.
- Executing a parallelized SELECT (…) INTO Table FROM SourceTable statement, specifically using the NOLOCK hint, under the default SQL Server lock-based isolation level or higher. In this scenario, other queries trying to access SourceTable will be blocked.
If you haven’t already installed CU6, don’t.
If you have installed it, Microsoft recommends that you leave it in place unless you experience this exact issue, at which point you’d need to uninstall CU6.
To know when a fix comes out, watch the CU6 download page, or subscribe to SQLServerUpdates.com and we’ll email you.
UPDATE 2016/05/31 – It’s back! Microsoft has released a fixed CU6.
31 Comments. Leave new
Also, an interesting discussion in the comments on that Releases Services blog page. Who knew that cumulative updates weren’t actually cumulative?
Whoa! I didn’t know that! That’s crazy!
The definition from MS seems to be (** added by me to highlight the caveat):
“Our updates are cumulative in nature and they include all the updates required for the components **that the CU applies to** since the last Service Pack.”
So, presumably, if a given CU has no new updates to say SSIS (or possibly subcomponents), then all SSIS updates in previous CUs could/would be missing from that CU.
I can imagine this just increases the patchwork of SQL Servers out there in the wild, i.e. all servers running the same CU level are not necessarily equal.
I did a quick straw poll around me, and everyone was of the view that a CU contains updates in all previous CU’s. It’s another SQL Server Myth.
I just posted a reply on our Blog and on this post as well. Cumulative Updates for all server side components are cumulative since the last SP and every CU contains all the fixes for the database engine, integration services, analysis services and reporting services since the last CU. What you mention is factually incorrect and could lead to confusion.
Updates for client drivers have never been cumulative and are provided only with the Cumulative Update which released the fix. We understand that it is not intuitive for our customers to locate the latest version of the standalone MSIs when they are not included cumulatively in subsequent CUs, so we are currently migrating servicing of many MSIs to a different model where a single Microsoft.com/downloads page will always host the latest version of the particular MSI. All subsequent KB articles describing resolved issues will point to this single site.
Calling them Cumulative Updates implies that the latest Cumulative Update will contain any and all changes/fixes/updates that were included in all of the previous Cumulative Update. If updates for client drivers were going to be striped from the updates when the next Cumulative update was going to be put together, they never should have been included in the first place.
The assumption by most people is that installing the latest service pack and the latest cu, will get you the same end result as installing the latest service pack, and each and every CU that has been released, which it turns out is not the case.
This is one of those times where it’s important to realize that words have meaning, and using the right word is important
Thanks for the clarification.
To be fair, I did say “presumably” as I was just as confused as some other readers by some of the earlier information.
Based on previous feedback, we had made the additional MSIs available in addition to the cumulative update for the server components. The cumulative update is used to patch server side components like database engine, analysis services, integration services and reporting services. We understand your feedback and we are working on a central download page for all the client component updates. KB822499 talks about how we package our updates.
The SQL 2014 SP1 CU6 download page says:
“This Cumulative Update includes all fixes from all previous SP1 Cumulative Updates, therefore it can be installed to resolve issues fixed in any previous SP1 CU”.
Doh!
Client drivers (such as ODBC, SNAC, OLEDB etc) are packaged and released independently from server cumulative updates. The latest version of the client driver includes all the previous fixes hence they are cumulative in their own nature.
Cumulative updates for all server components are cumulative in nature. Updates for client drivers have never been cumulative and are provided with the Cumulative Update which released the fix. Another example is we provide updated client drivers like our ODBC driver and Native Client on the Microsoft Download page. These download links always have the latest version of the driver. We understand that it is not intuitive for our customers to locate the latest version of the standalone MSIs when they are not included cumulatively in subsequent CUs, so we are currently migrating servicing of many MSIs to a different model where a single Microsoft.com/downloads page will always host the latest version of the particular MSI. All subsequent KB articles describing resolved issues will point to this single site.
Amit, I think this is a much better approach. I don’t think these separate MSIs should ever have been part of the Cumulative Update packages; the KB articles should say “this has a dependency on such and such MSI, so make sure you install that too” (or first or after, if there is any relevance to the order).
I assume this doesn’t affect using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED?
Mark – you, sir, win the Orange Heart of Courage in the Face of Database Bugs.
Does this also apply to SQL Server 2014 RTM CU 13? Asking for a friend…
sarcasm alert: “You should plan to install a CU with the same level of confidence you plan to install SPs (Service Packs) as they are released. This is because CU’s are certified and tested to the level of SP’s.”
http://dbareactions.com/post/104152413324/when-i-ask-the-developer-if-she-actually-tested
Hello,
To address the feedback in the comment section, this issue is restricted to SQL Server 2014 SP1 CU6 only. When we understood the issue was ongoing, we started our investigations and narrowed to either parallel SELECTs running with NOLOCK, on a table previously locked with an exclusive lock, or running a parallel SELECT INTO with NOLOCK, under READ COMMITTED or higher lock-based isolation level (so READ UNCOMMITTED is not affected).
Should there be more questions, it would be good to also post them in the release blog, so they can be answered faster, and for the benefit of all users going to the release post. We are working to release an updated CU6 as soon as possible.
Thank you!
Pedro, are you saying that this would be affected:
SELECT … INTO … FROM … WITH (NOLOCK);
But this would not:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT … INTO … FROM …;
? Not sure I follow how that is possible … in all of my testing those two forms result in the exact same behavior.
On the second scenario described in the note at https://support.microsoft.com/en-us/kb/3144524 (quoted by Brent in this post), we found that the lowest lock-based isolation level does not result in blocking. Under the same conditions, using NOLOCK can result in blocking.
On the first scenario (parallel SELECT INTO) would have the same behavior in either of your examples, because the semantics are different in the code. Running that SELECT INTO with MAXDOP 1 hint does not block others.
We are working to release a refreshed CU ASAP. HTH
Readers – just to introduce Pedro, he’s on the release team at Microsoft.
(Because if I don’t say that, everybody probably assumes the commenters around here are hobos. Which they often are, but they’re really smart hobos.)
Quit touching my pork beans!
Why do you have to use that term: hobos? We are residentially challenged travelers!
I think the term is “consultants” 🙂
Has there been a blog post about NOLOCK that I missed? If isolation levels settings are the best way to avoid locks, what the hell was NOCLOCK created for in the first place?
There have been reams of blog posts about NOLOCK 😉
It’s a tool in the toolbox. One that a lot of folks thinks is the magical tool for a lot of problems. It isn’t. But every so often? I’ll still go there after careful consideration and discussion (I hope that doesn’t make me be hated). SQL Server started playing with Optimistic Concurrency models in SQL Server 2005 with the snapshot isolation levels. This became another tool in the toolbox. But “NOLOCK” came out well before then. And it exists in many/most DBMS in some form.
Often abused. Often misused. Blogged about a lot 🙂
Isaac, there may be cases where you only want the hint to apply to certain tables in a query or to certain queries in a batch/transaction. I don’t know that either is a common requirement, and for the former I haven’t actually tested the scope of the impact, but the latter is certainly feasible.
And in either case (using NOLOCK or SET TRANSACTION ISOLATION LEVEL), you can be vulnerable to all the nasty and dangerous things that come with it (see http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/).
Updated.
Details https://support.microsoft.com/en-us/kb/3144524
Download https://www.microsoft.com/en-us/download/details.aspx?id=51186
Hi,
Have anyone noticed links for previous CUs are gone? Right now I’m only able to download the latest CU6. Old CUs are supposed to be at Microsoft Windows Update Catalog, but I can’t find any.
Hello Leomar,
I’ll just quote here my reply to the same question in our release blog. We now recommend ongoing, proactive installation of CU’s as they become available. Therefore, as announced back in March, we are now placing the latest CU per mainstream supported baseline (2012 SP2/SP3 and 2014 RTM/SP1 today) on microsoft.com/downloads, just as is done for Service Packs today.
Previously released CUs will still be available for download from the Windows Update Catalog (starting shortly), or the hotfix sever for CUs released prior to January 2016 (so you can file a request with CSS to obtain a previous CU).
You can read more about the Incremental Servicing Model announcements here: https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism/
Thank you!
Pedro,
Thanks for the quick reply, but there are any date for CUs become available again? In my opinion it’s not reasonable to use CSS to download an update that was once generally available. I know I can count on that, but c’mon… Should it really be necessary?