Select blocking a Truncate stamemet

Question

Post reply

Select blocking a Truncate stamemet

vsamantha35

SSChampion

Points: 11551

August 17, 2023 at 1:16 pm

Go to Answer

#4263649

Hi All,

I am seeing a SELECT blocking a TRUNCATE statement. THE SELECT is running over 4 hours and it is waitype is AYSN_NETWORK_IO. Isolation level is default read committed isolation level.

Why or when does a SELECT takes "Sch-S” lock? I see this lock being acquired using sp_whoisactive. Truncate is waiting for="Sch-M" lock.

How to fix this blocking?

Below are the sql stmts involved in blocking.

blocking process

=================

SELECT
"C_B_REF_ORG"."ROWID_OBJECT",
"C_B_REF_ORG"."DUNS_NMBR",
"C_B_REF_ORG"."CONTROL_OWN_TXT",
"C_B_REF_ORG"."GLBL_DUNS_NMBR",
"C_B_REF_ORG"."PARENT_DUNS",
"C_B_REF_ORG"."ORG_START_YEAR",
"C_B_REF_ORG"."PRMRY_US_SIC_CODE",
"C_B_REF_ORG"."IMPORT_IND",
"C_B_REF_ORG"."EXPORT_IND",
"C_B_REF_ORG"."MINORITY_IND",
"C_B_REF_ORG"."FEMALE_IND",
"C_B_REF_ORG"."INCORP_YEAR",
"C_B_REF_ORG"."SMALL_BUSINESS_IND",
"C_B_REF_ORG"."HQ_DUNS",
"C_B_REF_ORG"."DOMESTIC_DUNS",
"C_B_REF_ORG"."FAM_HIERARCHY_LVL",
"C_B_REF_ORG"."OUT_OF_BUS_IND",
"C_B_REF_ORG"."FAM_TREE_COUNT",
"C_B_REF_ORG"."OPS_STATUS_TXT",
"C_B_REF_ORG"."STANDALONE_IND",
CASE
WHEN "C_B_REF_ORG".STANDALONE_IND = 'true' THEN 'SINGLE LOCATION'
WHEN 0 < (SELECT Count(*)
FROM C_B_REF_ORG_FAM_ROLE
WHERE REF_ORG_ID = "C_B_REF_ORG".ROWID_OBJECT
AND Upper(FAM_ROLE_MEMBER_TXT) IN ( 'HEADQUARTERS', 'PARENT' )) THEN 'HEADQUARTERS'
WHEN 0 < (SELECT Count(*)
FROM C_B_REF_ORG_FAM_ROLE
WHERE REF_ORG_ID = "C_B_REF_ORG".ROWID_OBJECT
AND Upper(FAM_ROLE_MEMBER_TXT) IN ('BRANCH', 'DIVISION')) THEN 'BRANCH'
WHEN 0 < (SELECT Count(*)
FROM C_B_REF_ORG_FAM_ROLE
WHERE REF_ORG_ID = "C_B_REF_ORG".ROWID_OBJECT
AND FAM_ROLE_MEMBER_TXT = 'Subsidiary'
AND Upper(FAM_ROLE_MEMBER_TXT) NOT IN ('HEADQUARTERS', 'PARENT', 'BRANCH','DIVISION')) THEN 'SINGLE LOCATION'
END AS LOCATION_TYP,
CASE WHEN 0 < (SELECT Count(*)
FROM C_B_REF_ORG_FAM_ROLE
WHERE REF_ORG_ID = C_B_REF_ORG.ROWID_OBJECT AND FAM_ROLE_MEMBER_TXT='Subsidiary' and HUB_STATE_IND =1)
THEN 'Subsidiary'
END AS FAM_ROLE_MEMBER_TXT,
CASE WHEN 0 < (SELECT Count(*)
FROM C_B_REF_ORG_FAM_ROLE
WHERE REF_ORG_ID = C_B_REF_ORG.ROWID_OBJECT AND FAM_ROLE_MEMBER_TXT IN ( 'Headquarters', 'Parent' ) and HUB_STATE_IND =1)
THEN 1 ELSE 0
END AS U_IS_HQ,
"C_B_REF_ORG"."NON_MARTKETABLE_REASON_TXT"
FROM
"dbo"."C_B_REF_ORG" "C_B_REF_ORG" INNER JOIN
"dbo"."DNB_INCR_REF_ORGS" "DNB_INCR_REF_ORGS"
ON "C_B_REF_ORG"."ROWID_OBJECT" = "DNB_INCR_REF_ORGS"."REF_ORG_ID" --where duns_nmbr='116894185'

blocked process

=================

TRUNCATE TABLE "dbo"."DNB_INCR_REF_ORGS"

Capture

Thanks,

Sam

Steve Jones - SSC Editor

SSC Guru

Points: 736494
More actions
August 18, 2023 at 7:46 pm
Answer
#4264892

The SELECT needs a schema-lock because it has a plan to read the table. The table structure cannot change while the SELECT is reading, hence the lock.
As Johan says, kill the query
August 22, 2023 at 4:29 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

vsamantha35 SSChampion Points: 11551 More actions · Answer 1

Link to estimated plan

https://www.brentozar.com/pastetheplan/?id=rktPkssh2

ratbak Ten Centuries Points: 1382 More actions · Answer 2

Are your database & the referenced tables/columns using case-sensitive collation? If not, get rid of the UPPER() functions in your where clauses to ensure sargability.