SQL Server uses NOLOCK to boost performance

There are many articles about nolock in the blog Park, most of which are well written, for example: http://www.cnblogs.com/huangxincheng/p/4292320.html

This is a personal note that only combines individual items.

Nolock methods of use such as:

SELECT * FROM table1 with (NOLOCK)

With the "lock hint" behind, there are many kinds of locking hints, such as table level, page level, row level, and so on. Refer to the link above for details.

Nolock is the lock level: no locks are taken. Using NOLOCK can improve query performance and avoid locking data in situations where dirty reads are allowed . The keyword is the bold part.

Dirty reads mean that uncommitted data in other transactions is read, but the transaction may be rolled back.

The first thing to make clear is that SQL Server will have an exclusive lock on insert/update/delete, which means that when these operations are performed, other operations must wait. For SELECT, a shared lock is added, that is, when read, other operations are read only. To see a simple test, create a new two query window with SQL as follows:

--Query window a BEGIN tranupdate Test set c = ' 2 ' WHERE id = 2--Query window two select * from Test where id = 2

When a transaction with query window one is not committed, other queries cannot be read. This is the default isolation level for SQL Server, which ensures that dirty reads do not occur. Then we use NOLOCK to test, Query window two of SQL changed to:

--Query window two select * from Test with (NOLOCK) where id = 2

As you can see, the data can still be read even if the transaction of Query window one is not committed. When does it appear dirty to read? Very simply, when the transaction rollback, the dirty data is read.

For some systems that have strict data requirements, dirty reads are unacceptable and NOLOCK cannot be used. But many times, our query is allowed to be dirty read, because it brings many benefits.

1. Improve query performance

As you can see from the example above, NOLOCK is not affected by the lock, which means that it can read the data directly without waiting for the lock to be released.

2. Avoid locking data

This benefit can be understood better from the links above. Sometimes our select may also be time-consuming, saying that select adds a shared lock, that is, select, other transactions such as update cannot be done, and if our select executes too long, then other operations may fail due to timeouts.

SQL Server uses NOLOCK to boost performance