I don't think everyone understands how this works and the downsides.Ĭonsider that NOLOCK is NOT a hint. I have worked with many customers and see NOLOCK all over the place. I think there is a place for using this, but I agree people need to know that the drawbacks can outweigh the benefits. The goal of this tip was to show people the danger of using nolock in production and the issue with dirty reads. As you mentioned, Microsoft lists this as a hint in their list of hints. Great, thank you for this clear explanation. I have never liked NOLOCK in Hints and as you know SQL has deprecated those features for a while now. Hi Kawthar, Microsoft recommends using READUNCOMMITED going forward if you need this functionality. Is it it true SQL Server has special feature that when table is in updating process and not commit yet, it will even block SELECT statement to read data unit commit is complete? Why does SQL Server choose this unusual implementation? My understanding is most Databases default to READCOMMITED isolation level and when SELECT statement reads data from the table which is in updating process but not commit yet, the "read" will not be blocked and DB will return the "old" data. Whole batch of statements instead of modifying each query. NOLOCK) hint and the UPDATE will still function as normal. In the example below, the two SELECT statements will use the READUNCOMMITED SELECT statements that are performed from when this is turned on until it is turned The isolation level will apply the READUNCOMMITTED to all You can also set the Isolation Level for all queries instead of using the NOLOCK To learn more about these issues read this article: So for the insert you will get more records and for the delete
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |