Select blocking issue [closed]

Question

The database was not responding to simple select statements like

select * from table

I noticed in sys.dm_os_waiting_tasks that one session is blocking, blocking session was pointing to a stored procedure which use lock & Tran like below

    BEGIN TRAN

declare @min numeric ,@com numeric

select top 1  @com=companyid from tblcompanies
order by newid()



select @min=min(productid) from tblproducts(UPDLOCK)
where companyid=@com and (lockstatus<>1 or lockstatus is null )


update tblproducts
set lockStatus='1'
where productid>=@min and productid<@min+4

select distinct top 4 s.productname,s.productid,s.companyid,r.Type,r.requestType,r.UserID
from tblcompanydetail rd inner join tblproducts s
on rd.productid=s.productid
inner join tblcompanies r
on r.companyid=s.companyid
where s.productid>=@min and s.productid<@min+4


if @@error >0 rollback tran

commit tran

Please advise me if I have written it wrong.

Answer 1

Here's how I would re-write this. Various comments inline. These changes may help reduce blocking but it's really hard to see how even the original would block for very long unless these are all heaps and/or every table access yields a scan...

DECLARE @min INT, @com INT; -- these are INTs, right?

SELECT TOP 1 @com = companyid 
  FROM dbo.tblcompanies -- always use schema prefix
  ORDER BY NEWID(); -- random company? really?

-- Minimize transaction duration. Any real need to select the 
-- random company & perform final select inside the transaction?

BEGIN TRANSACTION;
  BEGIN TRY    

    SELECT @min = MIN(productid) 
      FROM dbo.tblproducts WITH (UPDLOCK)
      WHERE companyid = @com 
      AND (lockstatus <> 1 OR lockstatus IS NULL);

    UPDATE dbo.tblproducts
      SET lockStatus = '1' -- is this column numeric or string?
      WHERE productid >= @min AND productid < @min + 4; -- values guaranteed contiguous?

    COMMIT TRANSACTION;

    SELECT -- don't believe DISTINCT or TOP are required here
      s.productname, s.productid,   s.companyid,
      r.Type,        r.requestType, r.UserID  
    FROM dbo.tblproducts AS s
    INNER JOIN dbo.tblcompanies AS r
      ON r.companyid = s.companyid
    WHERE s.productid >= @min AND s.productid < @min + 4
    AND EXISTS -- since no columns from rd are needed
    ( 
      SELECT 1 FROM dbo.tblcompanydetail
      WHERE productid = s.productid
    );

  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
  END CATCH

Answer 2

This is not a direct answer to the question, but a wrap-up package for our OP to read about locking and blocking in the SQL Server environment. These are great questions from our site with lots of good info, articles and great comments.

Can I be automatically notified of prolonged blocking in SQL server?

Database locking issues?

Justify NOT using (nolock) hint in every query

SQL Server - what isolation level for non-blocking select statements?

SQL Server: what should be done when a process is blocking another process?

sql server concurrency performance

What is blocking and how does it happen?

It's an extensive subject, maybe not very easy to grasp, but in the end, understanding it will provide only benefits for you and your databases.

PS: I ordered the questions purely on random order, not on relevance. All of them are nice (and a must, from my POV) to read.