With (NOLOCK)

Transferred from:http://www.cnblogs.com/henw/archive/2011/07/22/2113580.html

With (NOLOCK)

Disadvantages:

1. Dirty reads are generated

2. Only applicable with SELECT query statements

Advantages:

1. Some documents say that the efficiency of SQL queries added with (NOLOCK) can be increased by 33%.

2. Can be used for inner JOIN statements

Dirty read: A user made a change to a resource, at this time another user just read the modified record, and then, the first user discard the changes, the data back to the changes, these two different results are dirty read.

Detailed content:

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 (dirty Read).

For example:

SELECT COUNT (UserID)
From EMPLOYEE with (NOLOCK)
JOIN Working_group with (NOLOCK)
On EMPLOYEE. UserID = Working_group. Userid
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

With (NOLOCK)