I used to see my senior developers use WITH (NOLOCK) when querying in SQL Server and wonder why they use. Now i explored it and found that it's useful to improve the performance in executing the query . However there is a disadvantage in using it. The disadvantage is that one may not be sure that they are getting the data which is currently being updated in the Table, ie, Without lock protection, you cannot be guaranteed that the data isn't changing during the time that the query is running. I referred this link and found it pretty useful.
To improve SQL query performance, generally speaking, the first consideration is to create an index. In fact, in addition to the establishment of indexes, when we place SQL commands, adding a section WITH (NOLOCK) to the syntax can improve the phenomenon of datasets being LOCKed in an environment with a large number of online queries, thereby improving the query performance.
However, one thing to note is that SQL SELECT with (NOLOCK) may cause a dirty read.
For example:
Because SQL Server performs the corresponding lock consistency check. To improve the overall database query performance, add WITH (NOLOCK) to the end of the table name in your SELECT syntax, although (NOLOCK) is also acceptable, but Microsoft still recommends adding WITH.
In addition to simple SELECT, you can also use SELECT syntax with JOIN. But DELETE, INSERT, UPDATE, and other commands that require transactions are not good...
Some files say that SQL query efficiency can be increased by 33% with WITH (NOLOCK).
Adding WITH (NOLOCK) tells SQL Server that our SELECT command does not need to consider the transaction lock state of the current table, so the performance will be significantly improved, and the lock phenomenon of the database system will be significantly reduced (including dead lock).
One thing to note is that because WITH (NOLOCK) does not take into account the transaction lock of the current table, so when there is some data in multiple phases of a transaction (e.g. a transaction across multiple tables - > such as a withdrawal system), WITH (NOLOCK) will ignore the data that is currently processing the transaction process...
To put it bluntly, that is, when using NoLock, it allows reading data that has been modified but the transaction has not yet been completed. Therefore, if you need to consider the real-time integrity of transaction data, you should think carefully about using WITH (NOLOCK).
If you don't need to consider transactions, WITH (NOLOCK) may be a useful reference.
Note 1: WITH ( < table_hint > )
Specifies the table scan, one or more indexes used by the query optimizer,
Or by the query optimizer to leverage this data table and use the lock mode for this statement.
Note 2: WITH (NOLOCK) is equivalent to READ UNCOMMITTED
Finally, let's talk about a few small differences between nolock and with(nolock):
1: Synonyms in SQL05, only with (nolock) is supported;
2: with (nolock) is written very easily and then specify the index.
When querying statements across servers, you cannot use with (nolock), only nolock
When querying on the same server, both with (nolock) and nolock can be used
Like what
SQL code
select * from [IP].a.dbo.table1 with (nolock) This will prompt you with the error select * from a.dbo.table1 with (nolock) This will work |
Posted on 1/8/2016 5:58:31 PM
|
|
|
