What does NOLOCK in SQLServer mean?

What does NOLOCK in SQLServer mean? (2011-04-2716: 18: 31) I have encountered this problem before, but I only want to hear from my colleagues that it is better to add NOLOCK. I tried it carefully today and finally understood it, so what is the difference between adding and not adding? Let me talk about the difference first, and then perform a test. We all know that each time a query is created

What does NOLOCK in SQLServer mean? (16:18:31) I have encountered this problem before, but I only want to hear from my colleagues that it is better to add NOLOCK. I have studied it carefully today and finally understood it, so what is the difference between adding and not adding? Let me talk about the difference first, and then perform a test. We all know that each time a query is created

NOLOCK in SQL ServerTo the endYesWhatMeaning? (16:18:31)

ReprintedBytes

I have met before, but I only want to hear from my colleagues that it is better to add NOLOCK. Now I have carefully studied and tested it, and finally understood it. Then I can add or not.To the endWhat is the difference?

Let me talk about the difference first, and then perform a test.

As we all know, each new query is equivalent to creating a session. operations performed in different query analyzer can affect the query of other sessions, in extreme cases, the query may remain congested. Even a very simple query is "very slow ".

BEGINTRAN starts a transaction.MeaningYou can execute some SQL statements after the start, and then execute COMMIT for submission or ROLLBACK for ROLLBACK. Otherwise, the above situation will occur. However, if NOLOCK is used for query, it will not be blocked because other calls are not submitted or rolled back. To sum up, use the following statement:

NOLOCK can query the current session and is not blocked by other sessions. But in this way, the "uncommitted after modification" data of other transactions is read.

Now we are testing, we must note that it is only available in multiple sessions, that is, three query analyzer windows need to be created.

Use the simplest table to create one.

Query analyzer 1: Run

SELECT * FROM dbo. test_main

Get

Id Value
1 One
2 Two
3 Three
4 Four

Run the following command:

BEGIN TRAN
Insert into test_main VALUES (5, 'five ')

One row affected

Query analyzer 2: Run

SELECT * FROM dbo. test_main

It is stuck and blocked by the previous session. No results can be found.

Supplement: What if it gets stuck? As we have already said, you only need to perform the COMMIT or rollback operation, so you can execute COMMIT in session 1.Then the query immediately displays the result.

Query analyzer 3: Run

SELECT * FROMtest_main (NOLOCK)

The result is as follows:

Id Value
1 One
2 Two
3 Three
4 Four

5 Five

However, the last row is not actually stored in the database, because the session has not been committed, we use NOLOCK to query it.

Maybe you will thinkWhatWhat about NOLOCK? After our analysis, we use NOLOCK to avoid getting stuck, so we can analyze the environment.

A table that is frequently operated, and each operation is very important. In this way, transactions are usually used for processing, because errors can be avoided,

We need to use NOLOCK when querying, otherwise there is a high chance of getting stuck. When someone else executes a transaction, You can query the transaction before it completes. With NOLOCK, this problem can be solved.