SQL Server with (NOLOCK) detailed

In the original: SQL Server with (NOLOCK) detailed

When we write the query, in order to performance, tend to add a nolockbehind the table, or with (nolock), the purpose is to query is not locked table, so as to improve the query speed.

What is concurrent access : More than one user accesses the same resource at the same time, and if a user modifies the resource in a concurrent user, it can have some adverse effects on other users, such as:

1: 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.

2: Non-repeatable read, a user's operation is a transaction, this transaction two times read the same record, if after the first read, there is another user modified the data, and then the second time to read the data is exactly the other user modified data, so that two read the record is different, This can be avoided if the record is locked in the transaction.

3: Phantom reading, refers to the user to read a batch of records, the user two times to query the same condition of a batch of records, the first query, there are other users of this batch of data has been modified, the method may be modified, deleted, added, the second query, you will find the first query record entries are not in the second query results, Or the entry for the second query is not in the contents of the first query.

Why do I add a nolock ID to the table after the query? To avoid the adverse effects of concurrent access, SQL Server has two control mechanisms for concurrent access: Lock, row versioning, and nolock after the table is one of the scenarios for resolving concurrent access.

1> locks, each transaction pair relies on a resource that requests a different type of lock, which prevents other transactions from modifying resources in such a way that a transaction request lock error may occur. When a transaction no longer relies on a locked resource, the lock is freed.

Type of Lock: 1: Table type: Lock the entire table, 2: Row type: Lock a row, 3: File type: Lock a database file, 4: Database type: Lock the entire database, 5: page type: Locks a database page of 8K.

The classification of locks also has a sub-method, which is divided by user and database objects:

1). From the point of view of the database system: Exclusive lock (that is, lock it), share lock and update lock

1: Share (S): For operations that do not change or do not update data (read-only operations), generally common such as SELECT statements.

2: Update (U): Used in updatable resources. Prevents common forms of deadlocks that occur when multiple sessions are read, locked, and subsequent resource updates are possible.

3: Exclusive (X): Used for data modification operations such as INSERT, UPDATE, or DELETE. Ensure that no multiple updates are made to the same resource at the same time.

2). From the programmer's point of view: There are optimistic and pessimistic locks.

1: Optimistic Lock: The job of managing locks depends entirely on the database.

2: Pessimistic Lock: The programmer manages the lock handling on the data or object itself.

When a programmer sees a lock or something, it's especially complicated, and a professional DBA is certainly an entry-level knowledge. Fortunately, programmers do not have to set up and control these locks, and SQL Server automatically manages the setting and control of locks by setting the isolation level of the transaction. The lock manager parses the SQL statements to be executed by the query parser to determine which resources the statement will access, what to do, and then automatically assigns the locks to be used in conjunction with the Set isolation level.

2>: Row versioning: When row versioning-based isolation levels are enabled, the database engine maintains the version of each row that is modified. Instead of using locks to protect all reads, applications can specify that transactions use row versions to view the data that exists at the beginning of a transaction or query. By using row versioning, the likelihood of a read operation blocking other transactions is greatly reduced. That is equivalent to all the tables in the query will be added nolock, the same will produce dirty read phenomenon, but the difference lies in a unified management of the place. When it comes to row versioning-based isolation levels, it's important to say the concept of isolation levels.

The use of isolation levels: the application of the control lock, that is, what kind of lock mechanism is applied to the scene.

The ultimate goal: to solve the problems caused by concurrent processing.

Classification of isolation levels:

1: Uncommitted read, the lowest level of isolation transaction, can only guarantee not to read the physically corrupted data;

2: Read Committed, default level of database engine;

3: Repeatable reading;

4: Serializable, the highest level of isolation transactions, and complete isolation between transactions.

Summary:NOLOCK statement execution does not emit a shared lock, allowing dirty reads, equal to the READ UNCOMMITTED transaction isolation level. nolock does improve speed when querying, but it is not without drawbacks, at least it can cause dirty reads.

nolock Usage Scenario (personal view):

1: The data volume is particularly large tables, the sacrifice of data security to improve performance can be considered;

2: Allow the dirty read phenomenon of business logic, conversely, some data integrity requirements of the more stringent scenarios are inappropriate, such as financial aspects.

3: Data is not frequently modified tables, this will save the time to lock the table to greatly speed up the query.

In summary, if you add nolockto the table after each query in the project, this is not scientific, at least a special time, not as direct as the row version control to be effective.

And there will be unexpected technical problems. There should be selective selection of the most appropriate table to discard the use of shared locks.

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

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

2:with (nolock) The 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) will prompt with an error

SELECT * from A.dbo.table1 with (nolock) so you can

SQL Server with (NOLOCK) detailed