Cons:
1. It will produce dirty reading
2. Only applicable to select query statements
Pros:
1. Some files say that SQL query efficiency with WITH (NOLOCK) can be increased by 33%.
2. Can be used for inner join statements
Dirty read: A user makes a modification to a resource, and another user happens to read the modified record, and then the first user gives up the modification, and the data returns to the previous modification, these two different results are dirty reading.
Details:
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 Dirty Read.
For example:
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 that is in multiple phases (e.g. transactions across multiple tables - >e.g. withdrawal systems), WITH (NOLOCK) ignores 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
|
Posted on 2020-4-3 10:06:04
|
|
|
Posted on 2020-4-5 14:21:36
