Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

closed as not constructive by Paul White, Mark Storey-Smith, RolandoMySQLDBA, Mike Walsh, Derek Downey Dec 17 '12 at 15:41

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
How long was this procedure blocking your select? Are you sure it wasn't wrapped in a larger transaction (there are no autonomous transactions in SQL Server, so the commit here wouldn't truly commit an encapsulating one). –  Aaron Bertrand Dec 14 '12 at 19:51
    
I have reposted my procedure, I missed on statement before –  shawn swanson Dec 14 '12 at 19:57
1  
Can you explain the point of the procedure? To return 4 rows from a random company to the user, and then what? –  Aaron Bertrand Dec 14 '12 at 20:01
6  
So the answer to your previous question(s) was not to export/import the entire database then? Awesome. –  Mark Storey-Smith Dec 14 '12 at 20:38
5  
Well yeah, detaching the database kind of kicks out all of the users. If you keep it detached, the problem won't come back, either. –  Aaron Bertrand Dec 14 '12 at 22:17

2 Answers 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.

share|improve this answer

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
share|improve this answer
    
Do you think, changing query like this can help fixing issue? Is issue related to query written in different way? –  shawn swanson Dec 14 '12 at 20:22
    
Yes, this should reduce blocking, but I have no way of knowing how much (or why there is so much blocking in the first place). –  Aaron Bertrand Dec 14 '12 at 20:25
    
Thanks, I will try it & let u know –  shawn swanson Dec 14 '12 at 20:37
    
Is there a way to add timeout for UPDLOCK ?? –  shawn swanson Dec 14 '12 at 22:09
2  
@shawnswanson: tuning is a step by step operation. Do what Aaron advised, change your code, test, test again (not in production), then, if nothing changes for the good, come back with details. –  Marian Dec 15 '12 at 8:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.