question

Sagar Bhargava avatar image
Sagar Bhargava asked

Locking issues

Hi All, I am facing an issue with below Stmt2 and it looks to be in a running state forever. Stmt1 completes OK. Is this because Stm1 is already holding a Table lock on Table1 and will not release until the commit transaction? Will adding a table lock hint to the second Insert likely to resolve the issue. BEGIN TRANSACTION -- Stmt1 INSERT INTO Table1 WITH (TABLOCK, HOLDLOCK) (Col1, Col2) SELECT DISTINCT Col1 from Table2 -- Stmt2 INSERT INTO Table1 WITH(Col1, Col2) SELECT DISTINCT Col2 from Table2 COMMIT TRANSACTION
locking
10 |1200

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

1 Answer

·
JohnM avatar image
JohnM answered
Yes, it's because of the TABLOCK & HOLDLOCK. As per the documentation, it specifies that if you use both of those table hints together, the table lock is held until the transaction continues. *TABLOCK: Specifies that the acquired lock is applied at the table level. The type of lock that is acquired depends on the statement being executed. For example, a SELECT statement may acquire a shared lock. By specifying TABLOCK, the shared lock is applied to the entire table instead of at the row or page level. If HOLDLOCK is also specified, the table lock is held until the end of the transaction.* Reference: https://msdn.microsoft.com/en-us/library/ms187373.aspx This would prevent the second statement from acquiring it's exclusive lock that it needs to insert the data. Adding an additional table hint to the second query really won't help since the first statement has acquired a lock on the entire table, as you've specified. If anything, you would probably want to remove the table hints from the first query and let SQL Server manage the concurrency. Also, if the second statement is in the same session as the first, it'll always wait until the first statement is completed. That's how SQL Server will process it. If you want the second statement to execute in parallel, you'd have to put it into it's own session. Hope that helps!
1 comment
10 |1200

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

Thanks John for the explanation. There is a SP on a sharepoint server which is running these. I have asked for more information why this is setup the way it is and would be interesting to found out what they had in mind.
0 Likes 0 ·

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.