The situation where one or more session are blocking one or more sessions each, and the blocked ones block some other session(s) themselves is something that can occur.<\/p>\n\n\n\n
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH\nINTO #T\nFROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T\nGO\nWITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)\nAS\n(\nSELECT SPID,\nBLOCKED,\nCAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,\nBATCH FROM #T R\nWHERE (BLOCKED = 0 OR BLOCKED = SPID)\nAND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)\nUNION ALL\nSELECT R.SPID,\nR.BLOCKED,\nCAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,\nR.BATCH FROM #T AS R\nINNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID\n)\nSELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)\/4 - 1) +\nCASE WHEN (LEN(LEVEL)\/4 - 1) = 0\nTHEN 'HEAD - '\nELSE '|------ ' END\n+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE\nFROM BLOCKERS ORDER BY LEVEL ASC\nGO\nDROP TABLE #T\nGO<\/code><\/pre>\n\n\n\n<\/p>\n","protected":false},"excerpt":{"rendered":"
The situation where one or more session are blocking one or more sessions each, and the blocked ones block some other session(s) themselves is something that can occur.<\/p>\n","protected":false},"author":1,"featured_media":104,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-38","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mssql"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[]}},"_links":{"self":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/38","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/comments?post=38"}],"version-history":[{"count":3,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/38\/revisions"}],"predecessor-version":[{"id":42,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/posts\/38\/revisions\/42"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media\/104"}],"wp:attachment":[{"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/media?parent=38"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/categories?post=38"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/130.61.57.200\/index.php\/wp-json\/wp\/v2\/tags?post=38"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}