| Author |
Topic |
|
lesnic
Starting Member
8 Posts |
 Posted - 2006-05-11 : 04:12:04
|
| We have experienced problems since moving to sp4. We have a clustered 64-bit system running build 2187 and are suddenly seeing deadlocks between a process inserting rows and a process reading rows from this table. One spid is taking shared page locks and the other is deadlocking after it has taken an IX lock on a page and requesting an IX lock on another page which has a shared lock from the first process. The first process is requesting further shared locks on the page which has the IX lock.Can it be that in sp4 the shared page locks are not being released?Up to sp3 we have NEVER had a deadlock at page level as lock escalation is disabled.Can anybody please help or at least tell me how locking behaviour has changed in sp4?We have also had 2 cases of severe problems with blocking on a another DB with 2 processes at the heads of blocking chains, both of which are waiting for PAGELATCH_SH on pages in tempdb. Any changes, gotchas in this area?Les |
|
|
Kristen
Test
22859 Posts |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
lesnic
Starting Member
8 Posts |
Posted - 2006-05-11 : 10:04:34
|
| Thanks for the post Kristen. I know how to deal with row-level deadlocks but how come reads require to hold page locks on more than one page at a time. Lock escalation is disabled but still the query is taking an S lock on one page , holding on to it and reuesting another page lock?Les |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-11 : 11:00:40
|
| Any chance you have comparison between non-identical datatypes in a JOIN? That seems to be one of the big CPU killers with SP4 (I think there is a workaround to disable that behaviour if it turns out to be that).Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-05-11 : 11:24:25
|
| do you mean deadlock or blocking? -ec |
|
|
|
lesnic
Starting Member
8 Posts |
Posted - 2006-05-11 : 12:48:15
|
| Thanks Kristen,I thought I hadn't but this coalesce would be 1:DECLARE @resultBalance decimal(14,2)SELECT @resultBalance = a.LastCalculatedBalance + -- table def decimal(14,2)COALESCE((SELECT SUM(p.PostingAmount) -- table def decimal(14,2) FROM dbo.Posting p (INDEX(RefAccount56)) --index on AccountId (20k values) bigint, IsAmountIncludedInBalance --tinyint with < 1% of values in table = 0WHERE p.IsAmountIncludedInBalance = 0 AND p.AccountId = a.accountId),0.0) -- THIS line might be considered to be the incorrect data type in a join???Les |
|
|
|
lesnic
Starting Member
8 Posts |
Posted - 2006-05-11 : 12:49:16
|
| To eyechart:I mean deadlocks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-11 : 13:02:19
|
| Might be worth having a look at the Query Plan to see if it is going to do something bizarre, or not!Particularly if you still have an SP3 system somewhere and you could compare the equivalent Query PlanKristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-05-11 : 13:05:22
|
quote:
Originally posted by lesnic
To eyechart:I mean deadlocks
just to be clear, a deadlock means that two processes are waiting on each other to complete. Since this will never happen, SQL kills one of the processes and rolls it back.Is this what you are talking about? Or do you mean that you are now seeing a higher than usual amount of database blocking (normal occurance btw) since upgrading to SP4.-ec |
|
|
|
lesnic
Starting Member
8 Posts |
Posted - 2006-05-12 : 04:24:26
|
| To Eyechart,I am well-versed in diagnosing and fixing deadlocks. We have a very high volume OLTP system (2M txns /day) which has been running for nearly 2 years. Yes we get deadlocks but up until now they have always between rowlocks on clustered and/or non-clustered indexes.We have lock escalation disabled and I have monitored the system since sp4. There is no lock escalation. Despite this we are no seeing deadlocks as described above.Les |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-05-12 : 11:49:36
|
quote:
Originally posted by lesnic
To Eyechart,I am well-versed in diagnosing and fixing deadlocks. We have a very high volume OLTP system (2M txns /day) which has been running for nearly 2 years. Yes we get deadlocks but up until now they have always between rowlocks on clustered and/or non-clustered indexes.We have lock escalation disabled and I have monitored the system since sp4. There is no lock escalation. Despite this we are no seeing deadlocks as described above.
ok, don't get too worked up. Many people here confuse their terms here when discussing deadlocks and locking/blocking. Since you only have 6 posts to your name, we have no idea what experience level you have. Anyway, I'm just making sure that deadlock/blocking confusion isn't happening here.Now, getting back to the deadlocks. Have you enabled trace flag 1204?-ec |
|
|
|
|
Problems with SQL Server 200 sp4 (deadlocks)