March 9, 2015 at 12:44 pm
i have a project server select thats selecting a single record. the holdlock ,updlock is locking up. If i change it to rowlock it runs fine. Can someone explain the difference between (ROWLOCK) VS (HOLDLOCK, UPDLOCK)
declare @proj_uid uniqueidentifier,
@res_uid uniqueidentifier,
@assn_uid uniqueidentifier
SET @proj_uid='11111111-1111-1111-1111-111111111111'
SET @res_uid='0C8832DD-3681-4238-A4DA-5342308DDBED'
SELECT
@assn_uid = ASSN_UID
FROM
dbo.MSP_ASSN_ENTERPRISE with (HOLDLOCK, UPDLOCK)
WHERE
PROJ_UID = @proj_uid AND RES_UID = @res_uid
March 9, 2015 at 6:25 pm
Snargables (3/9/2015)
i have a project server select thats selecting a single record. the holdlock ,updlock is locking up. If i change it to rowlock it runs fine. Can someone explain the difference between (ROWLOCK) VS (HOLDLOCK, UPDLOCK)declare @proj_uid uniqueidentifier,
@res_uid uniqueidentifier,
@assn_uid uniqueidentifier
SET @proj_uid='11111111-1111-1111-1111-111111111111'
SET @res_uid='0C8832DD-3681-4238-A4DA-5342308DDBED'
SELECT
@assn_uid = ASSN_UID
FROM
dbo.MSP_ASSN_ENTERPRISE with (HOLDLOCK, UPDLOCK)
WHERE
PROJ_UID = @proj_uid AND RES_UID = @res_uid
I am curious why in the case of a SELECT statement you think you need to throw any kind of lock on that row at all.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 10, 2015 at 6:54 am
UPDLOCK is telling the server that you want to take out a lock on the table as if you were getting ready to update it. Then, the HOLDLOCK hint tells it that this is part of a serializable transaction. Where as, issuing a ROWLOCK just tells SQL Server to use row level locking. Both of the other locks, especially in combination, are more restrictive locks.
And I agree. What are you trying to accomplish? Most of the time, SQL Server's locking is way more than adequate. Taking control of locking in this fashion frequently leads to issues.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2015 at 7:47 am
thanks guys. The selects is a proc in project server. So i hesitate to change it however at this point i'm not sure if i have any other option. Here's what i'm seeing.
this proc is blocking the one below it yet the spid is showing as sleeping. It blocks it for about 3-4 minuted. Once it finishes then the one that was being blocked then becomes the blocker. So for example. Spid 123 on is sleeping and running the first proc and spid 321 is running the second proc. spid 123 is blocking spid 321. Once it finishes after a few minutes then spid 321 starts running proc 1 and then blockes a new spid that is running proc 2
--proc 1
ALTER PROCEDURE [dbo].[MSP_SRA_GetData]
(
@PROJ_UID uniqueidentifier,
@ITEM_UID uniqueidentifier
)
AS
SELECT *
FROM
dbo.MSP_ASSN_ENTERPRISE
WHERE
ASSN_UID = @ITEM_UID
---proc 2 that is being blocked
ALTER PROCEDURE [dbo].[MSP_SRA_LockServerLevelSRA]
(
@proj_uid uniqueidentifier,
@res_uid uniqueidentifier,
@assn_uid uniqueidentifier OUT
)
AS
SELECT
@assn_uid = ASSN_UID
FROM
dbo.MSP_ASSN_ENTERPRISE with (HOLDLOCK, UPDLOCK)
WHERE
PROJ_UID = @proj_uid AND RES_UID = @res_uid
March 10, 2015 at 7:52 am
The blocking makes sense. The types of locks that the query is attempting to take out don't allow for sharing with others. So, it has to wait until the first query clears. By changing the hints from very restrictive exclusive locks to a shared locking scheme, you're changing the blocking behavior. Completely expected. The real question remains, what the heck is that second query doing and why?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2015 at 7:59 am
Whats stopping the first query from clearing. It's in sleeping mode and doesnt appear to be doing anything. It's doing a clustered index seek and does around 3 reads. What would be a scenerio for it to sit there in sleeping mode for 4 minutes?
Do you think it would be a bad idea to change the second proc to a rowlock table hint?
March 10, 2015 at 8:05 am
If it's doing 3 reads based on an index seek but taking 3 minutes, something else is going on. I'd look to see what the wait statistics look like around that query. That's very concerning. Use extended events to capture all the waits when the query runs. I suspect you may have some other blocking or resource issue.
Personally, I wouldn't put any locking hints on at all. As I keep saying, I don't understand why whoever wrote this think they're needed, especially as restrictive a set of hints as they had. Without knowing why they had such seriously restrictive hints, I can't suggest to you to replace it with ROWLOCK. It's just a very different style of locking than what they had, so it will not do the same thing. But, you will see less blocking because of that hint. You'll probably see less blocking by dropping the hint entirely and letting SQL Server manage the locks for you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 10, 2015 at 9:10 am
i removed the hint. I still dont understand a scenerio where a sleeping spid would block another. I was under the impression that is it's in sleeping mode it is complete
March 10, 2015 at 10:07 am
A transaction can still be open for a sleeping connection. So, while it's not actively running anything, if it's still an open transaction, it's still holding locks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply