Description of the WITH (NOLOCK) option when SQL Select

Source: Internet
Author: User

I used to see my senior developers use with (NOLOCK) when querying in SQL Server and wonder why they use. Now I explored it's 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, the one of May and not being sure that they was getting the data which is currently being updated in the Table, ie, without lock protection, you cannot being guaranteed that the data isn ' t changing during the time that's the query is Running. I referred this link and found it pretty useful.
To improve the query performance of SQL, we generally consider the index as the first consideration. In fact, in addition to the establishment of the index, when we are under the SQL command, in the syntax of adding a section with (NOLOCK) can improve the environment of large online query data set by lock, thereby improving the performance of the query.
However, it is important to note that the SQL select with (NOLOCK) may cause dirty Read.

For example:

 

SELECT COUNT (UserID)
from EMPLOYEE with (NOLOCK)
JOIN working_group with (NOLOCK)
On EMPLOYEE. UserID = Working_group. UserID

Because SQL Server performs a corresponding lock consistency check. To improve the performance of the overall database query, add with (NOLOCK) after the table name in your select syntax, although (NOLOCK) is also possible, but Microsoft recommends that you add with.
In addition to simple select, a select syntax with join is also available. But delete, INSERT, update these need to transaction instruction is not ...
Some files say that the efficiency of SQL queries added with (NOLOCK) can be increased by 33%.
Add with (NOLOCK) to tell SQL Server, our select instruction does not need to consider the current table transaction lock state, so the performance will be significantly improved, and the database system lock phenomenon will be significantly reduced ( Contains dead Lock).
It is important to note that because with (NOLOCK) does not take into account the current table's transaction lock, there are certain materials that are in multiple phase transactions (for example, transaction transactions across multiple table--such as the withdrawal system). With (NOLOCK) will let the current processing of the transaction process data is ignored ...
Speak a little vernacular, that is, when using NOLOCK, it allows you to read data that has been modified but not yet completed. So if there is a need to consider the real-time integrity of transaction transactional data, using with (NOLOCK) takes a good look.
If you do not need to consider transaction,with (NOLOCK) may be a useful reference.
Note 1:with (< table_hint >)
Specifies the table scan, one or more indexes that are used by the query optimizer,
This data table is used by the query optimizer, as well as using lockdown mode for this statement.
Note 2:with (NOLOCK) is equivalent to READ UNCOMMITTED

Finally, there are several small differences between Nolock and with (NOLOCK):

Synonyms in 1:sql05, only supported with (NOLOCK);

The 2:with (NOLOCK) notation is very easy to specify the index again.

You cannot use the WITH (NOLOCK) only nolock when querying statements across servers

With the same server query, with (NOLOCK) and Nolock are available

Like what

SQL Code
SELECT * FROM [Ip].a.dbo.table1 with (NOLOCK) This will prompt with the error select * from A.dbo.table1 with (NOLOCK) so that you can

Description of the WITH (NOLOCK) option when SQL Select

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.