Acrobatics field-Why is the SELECT statement blocked by another select?

Many newly-introduced DBAs, when caught blocking, ask the question "Why is this SELECT statement blocked by that select, not a shared lock?" ”

Let's do a little test, first prepare some test data:

--======================================--Preparing test DataSELECTrow_number () Over(ORDER  by object_id) asRid,name asC1 intoTB003 fromSys.all_columnsGOCREATE UNIQUE CLUSTERED INDEXIdx_rid onTB003 (RID)

Note that the RID in the table created above is a unique clustered index, so if we follow the RID to update or query, the lock will be added.

Start by opening a transaction to modify the data:

-- ============================== -- open Transaction, modify a data not submitted -- to hold the X lock on the line for a long time . BEGIN TRAN UPDATE TB003 SET C1='ABC'WHERE RID=

Then open two separate answers, for data query:

SELECT *  from WHERE RID=

You can see that two queries are running, have delayed returning data, and use Dm_exec_request to view blocking:

-- ==================================== -- To view the blocking situation of the SQL being executed SELECT R.session_id,r.command,r.blocking_session_id,r.wait_type,r.wait_resource  from  as R WHERE r.session_id> r.session_id<>@ @SPID

Query Result:

Then someone began to question, why select Block select?

In fact, this is just a first served problem, session 62 execution Request lock key:10:72057594043695104 (8C752D5F60D8), found that the lock is occupied by session 59, and then start, session 63 This time out, also request the same lock, found that the lock was 59 occupied, Also began to wait, just because the session 62 has done the sofa waiting, so honestly moved a bench sit, and so on, because the hate session 62 first to occupy the sofa, so the session 62 as their blocking object, expect this session 62 was killed early, let himself sit on the sofa ....

Above tease force, just tell you small partners, blocking_session_id this value a bit not reliable, mainly also depends on resources, who really hold resources who is really blocking others!

If you want to see the true source of blocking, you can use the following script:

--=================================================================--View the blocking chain withT1 as (        SELECTs.session_id,ISNULL(RS.BLOCKING_SESSION_ID,0) asblocking_session_id,CAST("'  as NVARCHAR( $)) asBlockstep,0  asBlocknum from     [SYS].[dm_exec_sessions]  asS with(NOLOCK) Left JOIN [SYS].[dm_exec_requests]Rs with(NOLOCK) ons.session_id=rs.session_idWHEREs.session_idinch (                SELECTrs1.blocking_session_id from    [SYS].[dm_exec_requests]RS1) and ISNULL(RS.BLOCKING_SESSION_ID,0)= 0        UNION  All        SELECTrs.session_id, rs.blocking_session_id,CAST((' -'                        + CAST(rs.blocking_session_id as NVARCHAR( $))                        +T1. Blockstep) as NVARCHAR( $)) asBlockstep,1 +T1. Blocknum asBlocknum from     [SYS].[dm_exec_requests]RSINNER JOINT1 onrs.blocking_session_id=t1.session_id)SELECTsession_id, blocking_session_id, ( Case  whenT1. Blockstep= "'             Then 'KILL' + CAST(t1.session_id as NVARCHAR( $))            ELSET1. BlockstepEND) asBlockstep, Blocknum fromT1

View Code

Execution Result:

--===============================================

-Nothing big on the things, get some popular science posts, in order to send pictures, wow!!!

Acrobatics field-Why is the SELECT statement blocked by another select?