January 31, 2011 at 5:36 am
I'm having a problem I can't quite understand along the lines of the following:
I have a commonly-accessed ASP.NET page running a SELECT query WITH (NOLOCK) from some table.
Occasionally, a scheduled process TRUNCATES that table, then re-fills it using INSERT WITH (ROWLOCK) and runs a couple of UPDATE WITH (ROWLOCK) queries on the table.
As I understand things, the SELECT WITH (NOLOCK) should, during this process, sometimes return no rows, sometimes return uncommitted rows, and sometimes return committed rows.
Instead, I end up with an ASP.NET page crash, running through the following progression:
1) Transaction (Process ID [number]) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2) Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The timeouts all happen after the transaction deadlock errors, and last until the whole TRUNCATE/INSERT/UPDATE process finishes.
Would somebody please help me understand what is happening here and why there is (seemingly?) contention between a SELECT WITH (NOLOCK) and these other queries? I thought the whole point of reading uncommitted data was that in return for accepting I might not get the "right" answer, I would avoid contention and would always be able to return *some* answer.
January 31, 2011 at 5:43 am
To answer that we're going to need to see the deadlock graph.
Turn traceflag 1222 on. That will write a deadlock graph into the error log when a deadlock is encountered. Post the graph here along with the definition of the table and the indexes on it.
DBCC TRACEON(1222,-1)
The SQL code that the ASP page runs as well as the code for the scheduled process would also be useful.
p.s. Have you considered ditching the Nolock along with the chance of inconsistent data and enabling one of the snapshot isolation levels?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2011 at 6:19 am
I would ditch all the query hints entirely. Using hints you're forcing the optimizer and the query engine to do things your way instead of letting them figure out the best way to process your data. If you're really hitting lots of issues, then you need to reexamine your approach and design, not attempt to force the engine to behave one way. Hints like that should be an absolute last-ditch method for fixing 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
January 31, 2011 at 6:24 am
Here's a question I ask myself when I feel like using hints :
Am I really smarter than the best MS engineer who had 10 000s of hours to think about this issue and trillions of test runs?
Since I usually get a resounding no I have no hints in my queries :w00t:.
January 31, 2011 at 6:30 am
Ninja's_RGR'us (1/31/2011)
Here's a question I ask myself when I feel like using hints :Am I really smarter than the best MS engineer who had 10 000s of hours to think about this issue and trillions of test runs?
Since I usually get a resounding no I have no hints in my queries :w00t:.
That's an absolutely excellent way to put it.
"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
January 31, 2011 at 7:10 am
Thank you for your advice. I didn't really mean to ask you guys to debug my code though (I can rewrite it in this instance, and I'm trying to learn how to do it myself more generally), and these query hints are more designed to tell SQL Server which queries must lock data and which can't (thinking about failure modes) than they are to optimize performance. I've set the flags so that the next time this error comes up (it comes up every 3 days due to the scheduled process), I'll catch it, if it will help to illustrate the problem.
Having said that, I think I might have been unclear though in what I'm asking. I'm trying to better learn and understand locking in SQL Server. I thought I understood concurrency control, at least on a theoretical level (I've read through the key papers on the subject). Given my understanding of how it's supposed to work, I'm getting behavior that is impossible, so I'm trying to figure out what I've misunderstood.
What I do not understand is how a combination of:
Process A:
TRUNCATE TABLE test
INSERT INTO test WITH (ROWLOCK) [stuff]
Process B:
SELECT [stuff] FROM test WITH (NOLOCK)
can ever lead Process B to terminate with error " Transaction (Process ID [number]) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
I do not see how Process B should ever be creating a transaction.
Further, let's suppose Process B actually were
BEGIN TRANSACTION
SELECT [stuff] FROM test WITH (NOLOCK)
COMMIT
If SQL Server somehow chooses to wrap it in a transaction, like the above, the WITH (NOLOCK) query hint should mean that Process B never tries to lock anything. Thus, no deadlock.
The closest thing I can think of is that the TRUNCATE is placing an IX lock on the table, and a SELECT is getting a request granted for an IS lock. Then, the TRUNCATE tries to individually grab X locks which deadlock with S locks on a row-by-row basis? The problem is that WITH (NOLOCK) should prevent the S locks from being created, hence no deadlock, right?
What am I missing here?
January 31, 2011 at 7:45 am
My guess, you're seeing Sch-M/Sch-S deadlock. Everything takes a schema stability (nolock or no nolock), and truncate table is classified a DDL and I believe that it wants a Sch-M (schema modification). Sch-M blocks Sch-S. Can't say for sure without seeing the deadlock graph though
Grant's comment stands, don't play with hints (even locking hints) unless you really, absolutely know what effect they'll have. Rowlock does not mean that SQL will only lock at a row level. It can still escalate the locks and if it does it will escalate straight to table. You'll get escalation faster with row locks than you would with page locks.
I still recommend snapshot isolation. Locking is all about concurrency and isolation, not failure modes. Snapshot isolation removes locks from reads entirely yet still gives you transactionally consistent reads (which nolock may not). So with either of the snapshot isolations your process should be able to run without impacting any of your queries, yet your queries will always show consistent data.
Every single query in SQL is a transaction. if you don't explicitly begin a transaction, there's one implicitly stated when the statement begins and either committed or rolled back when the statement completes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2011 at 4:55 pm
As I understand snapshot isolation, the tradeoff is that it creates a lot of overhead in tempdb as well as extra storage space in each row. So, mind if I ask a few basic questions about it?
First off, if I set ALLOW_SNAPSHOT_ISOLATION ON, is this something that I can readily undo with ALLOW_SNAPSHOT_ISOLATION OFF? If so, will that immediately break stored procedures containing transactions with ISOLATION LEVEL SNAPSHOT, or will those procedures revert?
Basically, is this a one-way process and why is this not the default state for databases?
January 31, 2011 at 10:38 pm
It's not the default state for one simple reason - it it were, there would be behaviour changes when someone upgraded to SQL 2005. MS doesn't want that, they don't want all the support calls.
I have a client using Read committed snapshot on their main system (OLTP, very busy). The tempDB impact is there, it's not huge. This is another 'it depends'. It becomes more important to configure tempDB properly (sensible default size, fast drives, maybe multiple files).
If you enable snapshot isolation, little changes. It just allows you to specify in your procedures SET TRANSACTION ISOLATION LEVEL Snapshot. So you could then chose which pieces use snapshot and which don't.
If you enable read committed snapshot (also a database setting), then all transactions using read committed isolation (the default) will start using a form of snapshot that is statement-level rather than transaction level (as Snapshot isolation is).
If you turn read-committed snapshot off, things will go back to the old way. If you turn snapshot off, any procedures that explicitly SET TRANSACTION ISOLATION LEVEL Snapshot will give errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2011 at 4:14 am
We've been running with Read Committed Snapshot on several systems, and while there is a bit of an impact on tempdb, the reduction in locking and blocking more than made up for that bit of resource use.
You need to plan for it and take it into account when you set up your system, but it's absolutely worth doing.
"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
February 4, 2011 at 6:10 am
I tried turning this on, and now I no longer get the transaction deadlock errors, but I simply get timeouts instead. Which again makes me think that there's a problem with locks...
Any ideas what might be going on then? My understanding was that by telling the database that it should read snapshots, I shouldn't be getting this timeout; I should just be reading what the table looked like before the update started. Some testing with other queries seemed to suggest that would be true here. But, it doesn't seem to be.
Since you asked before, the two relevant tables look like this:
CREATE TABLE [dbo].[fanSpirit](
[teamID] [bigint] NOT NULL,
[lastSeason] [float] NULL,
[thisSeason] [float] NULL,
[lastGame] [float] NULL,
[lastTVGame] [float] NULL,
[lastRivalryGame] [float] NULL,
[cupRun] [float] NULL,
[familiarPlayers] [float] NULL,
[proactiveOwner] [float] NULL,
[fanSpiritOverall] [float] NULL,
[fanSpiritNormalized] [float] NULL,
CONSTRAINT [PK_fanSpirit] PRIMARY KEY CLUSTERED
(
[teamID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[fanSpiritData](
[teamID] [bigint] NOT NULL,
[lastMatchID] [bigint] NULL,
[lastHomeMatchID] [bigint] NULL,
[lastTVMatchID] [bigint] NULL,
[lastRivalryMatchID] [bigint] NULL,
[lastCupMatchID] [bigint] NULL,
[recentMatchRevenue] [int] NULL,
[money] [bigint] NULL,
[managedLastSeason] [tinyint] NOT NULL,
[averagePlayerTenure] [float] NULL,
[transfersThisSeason] [int] NULL,
[jitterSeed] [int] NULL,
[lastUpdate] [datetime] NOT NULL,
CONSTRAINT [PK_fanSpiritData] PRIMARY KEY CLUSTERED
(
[teamID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The relevant part of the SELECT which is timing out is:
SELECT fsd.*, fs.*, t2.teamID as rivalID
FROM teams t WITH (NOLOCK)
LEFT JOIN fanSpirit fs WITH (NOLOCK) ON fs.teamID = t.teamID
LEFT JOIN fanSpiritData fsd WITH (NOLOCK) ON fsd.teamID = t.teamID
WHERE t.teamid = @teamid
and there are no issues with the teams table.
The procedure which is running during the timeout looks like:
ALTER PROCEDURE [dbo].[setFanSpirit]
AS
BEGIN
truncate table fanSpirit
truncate table fanSpiritData
INSERT INTO fanSpiritData WITH (ROWLOCK) (teamid,
lastMatchID,
lastHomeMatchID,
lastTVMatchID,
lastRivalryMatchID,
lastCupMatchID,
recentMatchRevenue,
[money],
averagePlayerTenure,
transfersThisSeason,
managedLastSeason,
jitterSeed,
lastUpdate
)
SELECT [long-running query that I can't post on a message board]
insert into fanSpirit WITH (ROWLOCK) (teamID,
lastGame,
lastTVGame,
lastRivalryGame,
cupRun,
lastSeason,
thisSeason,
proactiveOwner,
familiarPlayers)
SELECT [similar long-running query]
UPDATE f
SET fanSpiritOverall = [calculation involving rows of f]
FROM fanspirit f WITH (ROWLOCK)
UPDATE fanSpirit WITH (ROWLOCK)
SET fanSpiritNormalized = [calculation involving rows of fanSpirit]
END
February 4, 2011 at 6:23 am
I wold get rid of the ROWLOCK hints. By putting the hint in place, you're forcing the optimizer to use row locks on inserts when page locks or even table locks, could be more efficient. You're gettin slow performance, so it sounds like you're introducing inefficiency. And the long running queries are probably where you need to spend your time. Look at their execution plans to determine if you have missing indexes, etc.
"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
February 4, 2011 at 6:25 am
Locking hints override the isolation level. Take the hints out first. Which of the snapshot isolations are you using? Snapshot or read committed snapshot?
Don't assume that it's locking, check what the waits are, check what locks exist.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2011 at 6:26 am
p.s. Consider getting a consultant in if it's urgent and you aren't sure where to start.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply