Brief analysis of With (NOLOCK) in SQL Server (to the hermit of Xiaoxiang)

Blog Source: http://www.cnblogs.com/kerrycode/p/3946268.html

Concept Introduction

Developers like to use with (NOLOCK) in SQL scripts, with (NOLOCK) is actually one of the table hints (table_hint). It is equivalent to readuncommitted. The specific function functions are as follows (from MSDN):

1: Specifies that dirty reads are allowed. No shared locks are published to prevent other transactions from modifying the data read by the current transaction, and exclusive locks set by other transactions do not prevent the current transaction from reading the locked data. Allowing dirty reads can result in more concurrent operations, but at the cost of reading data modifications that are later rolled back by other transactions. This may cause errors in your transaction, display data that has never been submitted to the user, or cause the user to see the record two times (or not see the record at all). For more information about dirty reads, non-repeatable reads, and Phantom reads, see concurrency Effects .

the 2:readuncommitted and NOLOCK hints apply only to data locks. All queries, including those with readuncommitted and NOLOCK hints, get sch-s (schema stability) locks during compilation and execution. Therefore, when the concurrent transaction holds the SCH-M (schema modification) lock on the table, the query is blocked. For example, a data definition language (DDL) operation acquires a SCH-M lock before modifying the schema information for a table. All concurrent queries, including those running with readuncommitted or NOLOCK hints, are blocked when attempting to acquire a sch-s lock. Conversely, queries that hold sch-s locks block concurrent transactions that attempt to acquire sch-m locks. For more information about lock behavior, see Lock Compatibility (Database engine).

3: readuncommitted and NOLOCK cannot be specified for tables modified by INSERT, UPDATE, or delete operations. The SQL Server query optimizer ignores the readuncommitted and NOLOCK hints that are applied to the target table of the UPDATE or DELETE statement in the FROM clause.

function and defect

There are pros and cons to using wiht (NOLOCK), so before you decide to use it, you need to understand the features and flaws of with (NOLOCK) to see if it fits your business needs, don't think it can improve performance, use it in a muddle.

1: Query is not blocked by other exclusive locks when using with (NOLOCK)

Open Session window 1, execute the following script, do not commit or rollback the transaction, the simulation transaction is really in the process of execution

BEGIN TRAN

       UPDATE TEST SET name= ' Timmy ' WHERE object_id = 1;

       --rollback

Open Session Window 2, execute the following script, you will find that the execution results have not been queried (in fact, only two records). The current session is blocked

SELECT * from TEST;

Open Session Window 3, execute the following script to view the blocking situation, you will find that session 2 is blocked by session 1, the wait type of Session 2 is lck_m_s: "Occurs when a task is waiting to acquire a shared lock"

  SELECT wt.blocking_session_id as                    Blockingsessesionid

        , Sp.program_name as                           programname

        , COALESCE (sp. Loginame, Sp.nt_username) as     HostName    

        , ec1.client_net_address as                    clientipaddress

        , Db.name as                                   DatabaseName        

        , Wt.wait_type as                              WaitType                    

        , Ec1.connect_time as                          blockingstarttime

        Wt. wait_duration_ms/1000 as                  waitduration

        , ec1.session_id as                            Blockedsessionid

        , H1. TEXT as                                   Blockedsqltext

        , H2. TEXT as                                   Blockingsqltext

  From Sys.dm_tran_locks as TL

  INNER JOIN sys.databases DB

    On db.database_id = tl.resource_database_id

  INNER JOIN sys.dm_os_waiting_tasks as WT

    On tl.lock_owner_address = wt.resource_address

  INNER JOIN sys.dm_exec_connections EC1

    On ec1.session_id = tl.request_session_id

  INNER JOIN sys.dm_exec_connections EC2

    On ec2.session_id = wt.blocking_session_id

  Left OUTER JOIN master.dbo.sysprocesses sp

    On sp.spid = wt.blocking_session_id

  Cross APPLY Sys.dm_exec_sql_text (ec1.most_recent_sql_handle) as H1

  Cross APPLY Sys.dm_exec_sql_text (Ec2.most_recent_sql_handle) as H2

At this point, View Session 1 (Session 1 of Session ID 53, before executing script 1, you can use SELECT @ @spid to view the session ID) lock information, you will find that table test (objid=1893581784) holds the lock information as shown below

Open Session Window 4 and execute the script below. You will find that the query results will be out soon, session 4 will not be blocked by session 1.

SELECT * from TEST with (NOLOCK)

From the small example shown above, it is precisely because with the NOLOCK hint that the exclusive lock of the transaction set in Session 1 does not hinder the current transaction from reading the lock data, so session 4 is not blocked, which improves query performance at concurrency.

2:with (NOLOCK) does not publish a shared lock to prevent other transactions from modifying the data read by the current transaction, which does not give an example.

In essence with (NOLOCK) is the ability to reduce blocking by reducing the locking and non-exclusive locking effects, thereby improving concurrency performance. The so-called pros and cons of everything, with (NOLOCK) in the promotion of performance, but also produce dirty read phenomenon.

As shown below, table test has two records, and I'm ready to update the object_id=1 record, when the transaction is neither committed nor rolled back

At this point another session uses the records found with the WITH (NOLOCK) as uncommitted record values

If, for some reason, the transaction rolls back, the object_id=1 record we read is a dirty piece of data.

Dirty Read is also called invalid data read out, refers to in the database access, Transactions T1 modifies a value, and then the transaction T2 reads the value, then T1 revokes the change to that value for some reason, which results in invalid data being read by T2.

With (NOLOCK) usage scenarios

When can I use with (NOLOCK)? When not to use with (NOLOCK), this depends on your system business situation, considering the performance and business requirements to determine whether to use with (NOLOCK), such as the financial or accounting costs, such as a system, dirty read that is to produce serious problems. Critical business systems also need to be considered carefully. Generally, there are some scenarios where you can use the WITH (NOLOCK)

1: The underlying data tables, the data of these tables are seldom changed.

2: Historical data tables, the data of these tables are seldom changed.

3: The business allows dirty reads to occur in the table involved.

4: A table with large data volumes that allows dirty reads for performance reasons.

The other point is not to abuse with (NOLOCK), I found there is a strange phenomenon, many developers know with (NOLOCK), but there is no understanding of dirty reading, habitual use with (NOLOCK).

With (NOLOCK) differs from NOLOCK

In order to understand the difference between with (NOLOCK) and NOLOCK, I looked up a lot of information, we first look at the following three SQL statements what is the difference

SELECT * from TEST NOLOCK

SELECT * from TEST (NOLOCK);

SELECT * from TEST with (NOLOCK);

The above question sums up the difference between NOLOCK, (NOLOCK), with (NOLOCK):

1:nolock such a way of writing, in fact, Nolock is actually only the role of aliases, without any real effect. So don't be careless to write (NOLOCK) NOLOCK

2: (NOLOCK) and with (NOLOCK) is actually functionally the same. (NOLOCK) is just the alias of With (NOLOCK), but in SQL Server 2008 and later versions, (NOLOCK) is deprecated, and the phrase "Specify table hints without using the WITH keyword" is obsolete. See MSDN http://msdn.microsoft.com/zh-cn/library/ms143729%28sql.100%29.aspx for details

2.1 As far as online saying with (NOLOCK) does not take effect in SQL SERVER 2000, I have verified that it is a complete fallacy.

2.2 In SQL using linked server, (NOLOCK) will not take effect, with (NOLOCK) will not take effect. As shown below

Msg 4122, Level 16, State 1, line 1th

Remote table-valued function calls is not allowed.

With (NOLOCK) will not generate a lock

Many people mistakenly assume that the database library does not generate any locks after using with (NOLOCK). In essence, with NOLOCK, the database still generates SCH-S (schema-Stable) locks and db-type shared locks on the Table object, as shown below, which can query a large table in one session and then view the lock information in another session (you can also use SQL Profile View Session lock information)

Do not use wtih (NOLOCK)

using with (NOLOCK)

It can be seen from the above that the database does not generate related locks after using with (NOLOCK). By contrast, when using with (NOLOCK), the database only generates a shared lock of type DB, and a schema stability lock for the tab type.

In addition, using with (NOLOCK) does not mean that it will not be blocked by other sessions and may still generate schema change Blocking

Session 1: Execute the following SQL statement, temporarily do not commit, impersonation transaction is executing

Session 2: Execute the following statement, and you will notice that the session is blocked as shown below.

SELECT * from TEST with (NOLOCK)

Brief analysis of With (NOLOCK) in SQL Server (to the hermit of Xiaoxiang)