Select blocking issue 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 &amp; 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&lt;&gt;1 or lockstatus is null )


update tblproducts
set lockStatus='1'
where productid&gt;=@min and productid&lt;@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&gt;=@min and s.productid&lt;@min+4


if @@error &gt;0 rollback tran

commit tran


Please advise me if I have written it wrong.
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 &amp; perform final select inside the transaction?

BEGIN TRANSACTION;
  BEGIN TRY    

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

    UPDATE dbo.tblproducts
      SET lockStatus = '1' -- is this column numeric or string?
      WHERE productid &gt;= @min AND productid &lt; @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 &gt;= @min AND s.productid &lt; @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