This is similar to the READ UNCOMMITTED transaction isolation level, that allows the query to see the data changes before committing the transaction that is changing it. In other words, the WITH (NOLOCK) table hint retrieves the rows without waiting for the other queries, that are reading or modifying the same data, to finish its processing. In addition to that, no deadlock will occur against the queries, that are requesting the same data from that table, allowing a higher level of concurrency due to a lower footprint. In this way, the query will consume less memory in holding locks against that data. The WITH (NOLOCK) table hint is used to override the default transaction isolation level of the table or the tables within the view in a specific query, by allowing the user to retrieve the data without being affected by the locks, on the requested data, due to another process that is changing it. The default transaction isolation level in SQL Server is the READ COMMITTED isolation level, in which retrieving the changing data will be blocked until these changes are committed. One of the more heavily used table hints in the SELECT T-SQL statements is the WITH (NOLOCK) hint. The table hints can be added to the FROM clause of the T-SQL query, affecting the table or the view that is referenced in the FROM clause only. For everyone else, I stop at WITH (READPAST) and then only with a solid business argument.SQL Server table hints are a special type of explicit command that is used to override the default behavior of the SQL Server query optimizer during the T-SQL query execution This is accomplished by enforcing a specific locking method, a specific index or query processing operation, such index seek or table scan, to be used by the SQL Server query optimizer to build the query execution plan. For some DBAs who think they need/want to use it and can show a valid business case for it, I allow WITH (READUNCOMMITTED) (the synonym that shows the true nature of the table hint) and then only for ad hoc one-off queries - never for production code. I even see them in queries related to financial or regulatory matters, where NOLOCK is, always and forever, a very, very bad idea and in some jurisdictions, can lead to civil and even criminal charges due to improper reporting. I spend part of each day ripping NOLOCK out of queries. After that, your NOLOCK tables will eventually give you dirty reads. A future DBA may remove all these restraints for perfectly valid business reasons. Even if you have stopped all write activity (you think!) through constraints, triggers, permissions, setting the database readonly etc, etc., there is no guarantee that these things may not change in the future. Just because you are “only reading” a table, does not mean no one else is writing to them. “In case you have many tables and some of them are read only, you can set the NOLOCK hint”
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |