question

AkshayChikara avatar image
AkshayChikara asked

One select statement is blocking other select statement . SQL Server 2014, 2016

I have seen this many time that a select statement is blocking another select statement. These two selects are actually same queries triggered by different application users and one is blocking other. Select statements fetch data from a view (join of multiple tables) .

Did someone know why this happens as this is not a usual situation, shared locks allows other select statements to run usually.

Following is the blocked session information along with blocking session information

  Blocked_ContextInfo
  UserK,,10.10.10.46,PRD1 
  Blocking_ContextInfo
  UserC,,10.10.10.43,PRD1 
  Blocked_StatementText
  SELECT TOP 301 BUSINESS_UNIT, INVOICE, INVOICE_TYPE, BILL_TYPE_ID,
  BILL_STATUS, BILL_TO_CUST_ID, COLLECTOR, BILL_CYCLE_ID, BILLING_SPECIALIST,
  INVOICE_AMOUNT, LAST_NAME, CHARGE_FROM_DT,
  (CONVERT(CHAR(10),CHARGE_FROM_DT,121)), PO_REF FROM PS_BI_HDR_NOIVC_VW A
  WHERE BUSINESS_UNIT=@P1 AND INVOICE LIKE 'HCT%' ORDER BY BUSINESS_UNIT,
  INVOICE DESC
  Blocking_StatementText
  SELECT TOP 301 BUSINESS_UNIT, INVOICE, INVOICE_TYPE, BILL_TYPE_ID,
  BILL_STATUS, BILL_TO_CUST_ID, COLLECTOR, BILL_CYCLE_ID, BILLING_SPECIALIST,
  INVOICE_AMOUNT, LAST_NAME, CHARGE_FROM_DT,
  (CONVERT(CHAR(10),CHARGE_FROM_DT,121)), PO_REF FROM PS_BI_HDR_NOIVC_VW A
  WHERE BUSINESS_UNIT=@P1 AND INVOICE LIKE 'HCT%' ORDER BY BUSINESS_UNIT,
  INVOICE DESC
  Blocked_SessionID
  Blocking_SessionID
  Blocked_Status
  running
  Blocking_Status
  running
  Blocked_Login
  Blocked_Host
  App Server 2
  Blocking_Login
  Blocking_Host
  App Server 2
  Waiting_Time
  Blocked_WaitType
  LCK_M_S
  Blocked_WaitResource
  PAGE: 7:1:18168 
  Blocking_WaitType
  LCK_M_S
  Blocking_WaitResource
  PAGE: 7:1:18168 
  DateCreated
sql server 2014blockingsessions
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Does it happen with any values of P1 (Business_Unit), or only for specific combinations?

0 Likes 0 ·

1 Answer

· Write an Answer
AkshayChikara avatar image
AkshayChikara answered

Adding file with blocking details as blocking information is not clearly visible in above post.

blocking-details.txt


10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.