Is it appropriate to add a nolock after each table in the query?

Background: Currently involved in a project, I found a problem, is that everyone in the write query, in order to performance, often in the back of the table with a nolock, or with (NOLOCK), the purpose is to query is not locked table, so as to improve the speed of the query. This may be a lot of friends in the development of the encounter, but if the system in each query statements in the table are added Nolock, do you agree to do so? Before I answer this question, let me say a few questions:

What is concurrent access: multiple users at the same time have access to the same resource, and if a user changes 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, then, the first user to discard the changes, the data back to the change before, the two different results is dirty read.

2: Not repeatable read, a user's one operation is a transaction, this transaction reads the same record two times, if another user modifies the data after the first reading, and then the second read is exactly the same as the other user's modified data, which results in a different record for two reads, if locked in the transaction This record can be avoided.

3: Phantom reading, refers to the user read a batch of records, users two times to query the same condition of a group of records, the first query, there are other users of this batch of data have been modified, the method may be modified, deleted, new, second query, will find the first query of the record entries are not in the second query results, or The second query is not in the content of the first query.

Why do I add a NOLOCK logo behind the query's table? 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 that resolves concurrent access.

1> locks, each transaction relies on a resource that requests different types of locks, and it can prevent other transactions from modifying the resource in a way that could cause a transaction request lock to go wrong. When a transaction no longer relies on a locked resource, the lock is freed.

Type of Lock: 1: Table type: Lock 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: Lock 8K database page.

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

1. From the perspective of the database system: divided into exclusive locks (i.e. exclusive locks), shared locks and update locks

1: Sharing (S): Operations that do not change or do not update data (read-only operations), commonly used for example, select statements.

2: Update (U): for updatable resources. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and subsequent resource updates that might occur.

3: Row It (X): For data modification operations, such as INSERT, UPDATE, or DELETE. Make sure that you do not have multiple updates for the same resource at the same time.

2. From the programmer's point of view: divided into optimistic lock and pessimistic lock.

1: Optimistic Lock: Rely entirely on the database to manage the lock work.

2: Pessimistic Lock: The programmer himself manages data or lock handling on objects.

The general programmer at the sight of what locks and so on, feel particularly complex, of course, the professional DBA is the entry level knowledge. The good news is that 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 analyzes the SQL statements to be executed through Query Analyzer to determine which resources the statement will access, what to do, and then automatically allocate the locks needed to manage with the set isolation level.

2>: Row versioning: When the isolation level based on row versioning is enabled, the database engine maintains the version of each modified row. Instead of using a lock to protect all reads, an application can specify that the transaction uses the row version to view the data that exists at the start of a transaction or query. By using row versioning, the likelihood that a read operation will block other transactions is greatly reduced. That is, the equivalent of all tables in the query will be added Nolock, the same will produce dirty read, but the difference is in a unified management place. Speaking of the isolation level based on row versioning, it is necessary to say the concept of isolation level.

The use of isolation levels: control the application of the lock, that is, what scenario the application of the lock mechanism.

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