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

Background:I have found a problem in a project currently, that is, when you write a query, A nolock or with (nolock) is often added to the table for performance purposes ), the purpose is to query tables without locking them, so as to increase the query speed. This may happen to many of my friends during development. But if nolock is added to the table in every query statement in the system, do you agree to do this? Before answering this question, let me explain the following questions:

What is concurrent access:Multiple users access the same resource at the same time. If a concurrent user modifies the resource, it will have some adverse effects on other users. For example:
1: Dirty readA user modifies a resource. At this time, another user just reads the modified record. Then, the first user gives up the modification and the data is returned before the modification, the two different results are dirty reads.

2: Repeated read is not allowed.A user's operation is a transaction that reads the same record twice. If another user modifies the data after the first read, then the data read for the second time is exactly the data modified by other users. This results in different records read twice. If the transaction locks this record, this can be avoided.

3: phantom readWhen a user reads a batch of records, the user queries a batch of records under the same condition twice. After the first query, other users modify the batch of data by modifying or deleting the data, added. During the second query, some record entries in the first query are not in the results of the second query, or the entries in the second query are not in the content of the first query.

Why does the nolock mark apply to the queried table?To avoid adverse effects of concurrent access, SQL Server has two types of concurrent access control mechanisms: Lock and row version control. Adding nolock to the table is one of the solutions for concurrent access.

 
1> lockEach transaction requests different types of locks for the resources it depends on. It can prevent other transactions from modifying resources in a way that may cause transaction request locks errors. When the transaction no longer depends on the locked resource, the lock will be released.

Lock type: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: Lock the database page in 8 K.

There is also a classification of locks, which is divided by users and database objects:

1) from the perspective of the database system: exclusive locks (exclusive locks), shared locks, and update locks
1: Share (s): used for operations that do not change or do not update data (read-only operations), such as select statements.
2: Update (u): Used in updatable resources. It prevents common deadlocks when multiple sessions are reading, locking, and subsequent resource updates.
3: arrange it (x): used for data modification operations, such as insert, update, or delete. Make sure that multiple updates are not performed for the same resource at the same time.

2 ).ProgramPersonnel perspective: Optimistic locks and pessimistic locks.
1: Optimistic lock: it relies entirely on the database to manage the lock.
2: Pessimistic lock: the programmer manages the lock processing on data or objects by himself.

Generally, when programmers see locks and other things, they feel very complicated. Professional DBAs are of course entry-level knowledge. Fortunately, programmers do not need to set and control these locks. sqlserver automatically manages the setting and control of locks by setting the isolation level of transactions. The lock manager analyzes the SQL statements to be executed by the query analyzer to determine the resources and operations that the statements will access, and then automatically allocates the locks required for management at the specified isolation level.

2>: Row Version Control: When the row version control-based isolation level is enabled, the database engine maintains the version of each row modified. The application can specify that the transaction uses the row version to view the data that exists at the beginning of the transaction or query, rather than using the lock to protect all reads. By using row version control, the possibility of reading operations to block other transactions is greatly reduced. That is, it is equivalent to adding nolock to all tables during query, which also produces dirty reads, but the difference lies in a unified management. Speaking of the isolation level based on Row version control, it is necessary to describe the concept of isolation level here.

Use of isolation level:The application of the control lock, that is, the application scenario and the lock mechanism.
Ultimate Goal: Solves various problems caused by concurrent processing.

Isolation level classification:
1: uncommitted read, the lowest isolation level of the transaction, can only ensure that the physical damage data is not read;
2: committed read, default database engine level;
3: repeatable;
4: serializable; the highest level of isolation transactions, full isolation between transactions.

Conclusion: No shared lock is issued when the nolock statement is executed. Dirty read is allowed, which is equal to the read uncommitted transaction isolation level. Nolock can improve the query speed, but it does not have any disadvantages. At least it will cause dirty reads.

Use Cases of nolock (personal opinion ):

1: For tables with extremely large data volumes, you can consider sacrificing data security to improve performance;
2: the business logic that allows dirty reading is not suitable for scenarios with strict data integrity requirements, such as finance.
3: tables with infrequently modified data save the time needed to lock the table to speed up query.

To sum upIf nolock is added to the end of each query table in the project, this method is not scientific. At least it takes a lot of time and is not as effective as row version control. In addition, unexpected technical problems may exist. You should select the most suitable table to discard the use of the shared lock.

Finally, let's talk about the differences between nolock and with (nolock:

1: synonym in sql05, only with (nolock) is supported );
2: With (nolock) is easy to specify the index.