![]() ![]() There is another related issue, if you have a view that is comprised of SELECT statements with NOLOCK, and you try to perform DML against the view: For those people, the only way to be sure you are immune from the issue is to remove NOLOCK from the target of any UPDATE statements. ![]() So if you are in a shop that waits for full service packs, you may be vulnerable to this issue until SQL Server 2012 Service Pack 2 is released (and I am fairly confident that will happen long before a new service pack is released for 2008 or 2008 R2, which hits end of mainstream support later this year). There is a fix available, but currently only through a cumulative update (no base service pack or release level among these three versions has the fix). KB #2878968 : FIX: Nonclustered index corruption may occur when you run a complex UPDATE statement together with a NOLOCK hint against a table in SQL Server 2008, SQL Server 2008 R2 or SQL Server 2012.This problem is described in the following Knowledge Base article: Yes, you read that right: using this hint in a DML statement can corrupt an index in certain versions of SQL Server 2008, 2008 R2, and 2012. Nobody wants to carry forward code that has the potential to break through no other change than an upgrade. This means that, while your code does not raise any errors today, it may start to fail in a future version of SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them." "Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server. You can also find the following quote in the topic Table Hint (Transact-SQL): "Specifying NOLOCK or READUNCOMMITTED in the FROM clause of an UPDATE or DELETE statement when applied to the target table of the statement." ![]() In the SQL Server Books Online topic, Deprecated Database Engine Features in SQL Server 2005, you can find the following deprecation notice: SELECTs which also used NOLOCK, allowing for dirty reads in either scenario - again, this was true with or without the hint on the UPDATE). And yes, other statements were still blocked while the update was running (unless they were Sys.dm_tran_locks) is identical, with or without the hint. I ran some tests on SQL Server 2008 and confirmed that the locking behavior (at least according to So code that uses this hint is implying some sort of non-blocking behavior that doesn't actually happen (and, frankly, isn't possible, when you think about it). NOLOCK has been ignored in this scenario since SQL Server 2005. I'll briefly explain three reasons these NOLOCK hints shouldn't be in your DML statements: If SQL Server doesn't raise an error message, then this must be okay, right? This depends on your definition of "okay" and, more importantly, how future-proof you want your code to be. The NOLOCK and READUNCOMMITTED lock hints are not allowed for target tables of INSERT, UPDATE, DELETE or MERGE statements.Īnd in spite of the wording of the message, the same error is not raised in any of the other patterns I demonstrated above. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |