Shulou(Shulou.com)05/31 Report--

This article is about whether SELECT in SQL Server will block SELECT. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Preface

In SQL Server, we know that during the execution of a SELECT statement, only some intended shared locks (IS) and shared locks (S) will be applied for. For example, I use SQL Profile trace session 86 to execute the query statement SELECT * FROM dbo.TEST WHERE OBJECT_ID = 1. The process of applying for and releasing lock resources is as follows:

And from the compatibility table of the most common lock mode, we can see that IS locks are compatible with S locks, which means that SELECT queries do not block SELECT queries.

Existing authorization model

Requested mode

IS

S

U

IX

SIX

X

Intention sharing (IS)

Yes

Yes

Yes

Yes

Yes

No

Share (S)

Yes

Yes

Yes

No

No

No

Update (U)

Yes

Yes

No

No

No

No

Intention exclusion (IX)

Yes

No

No

Yes

No

No

Intention exclusive sharing (SIX)

Yes

No

No

No

No

No

Exclusive (X)

No

No

No

No

No

No

But in some special situations. You will see that the select statement actually "blocks" the SELECT operation, so does the SELECT in SQL Server actually block the SELECT operation? Let's first construct the case scenario of the test, then prepare the test data first

CREATE TABLE TEST (OBJECT_ID INT, NAME VARCHAR (8)); CREATE INDEX PK_TEST ON TEST (OBJECT_ID) DECLARE @ Index INT = 0; WHILE @ Index < 20BEGIN INSERT INTO TEST SELECT @ Index, 'kerry'; SET @ Index = @ Index + 1% end

In session window A, execute the following SQL statement to simulate that a UPDATE statement is being executed

BEGIN TRANSACTION UPDATE dbo.TEST SET NAME='Kerry' WHERE OBJECT_ID=1;-ROLLBACK

In session window B, execute the following SQL statement

SELECT * FROM dbo.TEST WHERE OBJECT_ID=1

In session window C, execute the following SQL statement

SELECT * FROM dbo.TEST WHERE OBJECT_ID=1

In my experimental scenario, the session ID of session window An is 85, the session ID of session window B is 90, and the session ID of session window C is 87, as shown below

As shown below, you can see that the select statement "blocks" the SELECT statement, that is, session 90 "blocks" session 87, and their wait events are all LCK_M_S, that is, they are all waiting for the shared lock to be acquired. You may question whether there is a problem with this SQL, so if we use SP_WHO to check, you will find that the same is true, as shown below:

As shown below, we will find that sessions with session ID 90 and 87 are waiting for a shared lock with a type of RID,Resource of 1 24171virtual 1

In fact, it should be said that sessions 87 and 90 are waiting for shared locks from RID objects. We know that shared locks and intended shared locks are compatible, so SELECT will not block SELECT, so how to explain this phenomenon? Under the guidance of Song Dashen, I roughly flipped through the book Database System Implementaion (a lot of theoretical knowledge, which seems to be quite laborious). It introduces the concepts of lock table (lock table) and Element Info, Handling Lock Requests, Handling Unlocks and so on, which is shown in an interesting figure.

In the lock table (lock table), the lock application in elements info is in a queue-like structure. FIFO mechanism, so when session 90 enters the queue first, it is waiting for the shared lock (S), and session 87 also enters the queue to wait for the shared lock (S), and it is behind session 90 (that is, the Next pointer after the elements info of session 90 points to the transaction of session 87 session). Because both sessions are blocked, the Wait field of both sessions is Yes, due to some internal mechanism Session 87 shows that the session blocking it is 90 (this is my personal guess, the actual specific reason remains to be investigated), and the actual source of blocking is session 85. When session 85 releases the exclusive lock (X), the session queue handles unlocking (Handling Unlocks) according to the following principles:

1: First-come-first-served: Grant the lock request that has been waiting the longest. This strategy guarantees no starvation, the situation where a transaction can wait forever for a lock

First-come-first-served (queue principle): grant the lock request with the longest lock waiting time, this strategy ensures that you will not starve to death (translation does not feel appropriate), that is, a transaction will not wait for the lock forever.

2. Priority to shared locks: First grant all the shared locks waiting. Then,grant one update lock, if there are any waiting. Only grant an exclusive lock if no others are waiting. This strategy can allow starvation, if a transaction is waiting for a U or X lock.

Shared lock priority, first grant all waiting shared locks (S), and then grant one of the update locks (U). If there are other types of waiting, exclusive locks are granted only when there are no other locks waiting. This policy allows transactions waiting for updated locks or exclusive locks to starve to death (end).

3. Priority to upgrading: If there is a transaction with a U lock waiting to upgrade it to an X lock, grant that first. Otherwise, follow one of the other strategies mentioned.

Lock escalation takes precedence, and if one holds a shared lock (U) waiting to upgrade the Wie exclusive lock (X), grant it an exclusive lock first, otherwise use one of the previously mentioned strategies.

According to these principles, when session 85 releases the exclusive lock (X), the scheduler (Scheduler) should grant session 90 and 87 shared locks (S) in order, and the blocking of both will disappear almost at the same time. This can or can be roughly inferred through experiments. In the above experiment, you can manually cancel the query operation for 90 sessions, and then look at the blocking situation, and you will find that session 87 is blocked by 85. The source of the blockage is 85, not 90.

Thank you for reading! This is the end of the article on "will SELECT in SQL Server block SELECT?". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.