Ongoing purging of active records causes deadlocks

#11

On Jul 23, 5:59 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

bobdu... (AT) gmail (DOT) com (bobdu... (AT) gmail (DOT) com) writes:
- Sorry again, the type >= is just a way of getting more data, those
queries are typically = based, and theres never more than 20 unique
values for type.

For a query like:

SELECT LoadID, Type, CreateDate, OtherDate, ObjectID
FROM loads
WHERE Type = @P0
ORDER BY loads.LoadID DESC

An index on (Type, LoadID DESC) appears to be a good idea. If the
query is selective as you say, the optimizer would use the index and
bookmark lookups. And with the ID in the index, the result is already
sorted.

We can try the clustered index, but i'm NOT convinced why this is
going to resolve the page lock issue... i've checked the execution
plan for some of our queries and it looks like it sorts first, then
does a clustered index scan of the ID. Is this really going to have
more page locks than if the index was on the date?

The sort is usually performed at the end of the query plan - you should
read it from right to left. Sorts can appear earlier in the plan, if
the optimizer settles for a merge join, which requires sorted input.

I cannot guarantee that the clustered index on CreateDate saves the day,
but I think it will be difficult without it. The way the query is written,
without a parameter, the optimizer will assume a 30% hit-rate which is far
beyond what is good NC-index seek + bookmark lookup. Of course, you could
force the index with an index hint, but I don't think that would be a
good thing.

What is a possible alternative, though, is that the purge first performs:

SELECT @loadid = MAX(LoadID)
FROM loads
WHERE CreateDate < dateadd(SECOND, -40, CURRENT_TIMESTAMP)

And the issues "DELETE loads WHERE LoadID <= @loadid". For this to be
successful, you still need an index on (CreateDate, LoadID DESC).

One final thing that i don't understand at the db level is transaciton
models - using jdbc we're normally using READ_COMMITTED - we could use
SERIALIZABLE but i'm not sure how either of these translates at the db
level. If someone has a pointer to this info with respect to SQL
server (and/or oracle) i'd love to hear it.

READ_COMMITTED means that once you have read data, you release the lock
for it. Thus, if you read the same data twice in the same transaction,
you can get different result.

SERIALIZABLE means that you do not tolerate that anyone adds or changes
rows that could affect data you have read. I don't think serializable
will work here. First of all, things will slow down, because they are
just that: serialized. Second, serializable appears to often lead to
deadlocks, because SQL Server is not very good at always taking the
range locks in the same order, or so it seems.

There are a few more possibilities: On SQL 2005, you can use any of the
two varities of SNAPSHOT isolation. With this isolation level, readers
read from the version store, and cannot block writers. Yes, just like
on Oracle. But watch out! If you issue a SELECT COUNT(*) while the purge
is running, do you want the number of rows before or after the purge?
Snapshot will give you the former, that is old and out-dated data.

Then you can go for READ UNCOMMITTED, and do dirty reads. Then your
SELECT COUNT(*) will give you something between before and after. Finally
there is the READPAST hint which means that you skip locked rows. In
this case two, you may also get something in between before and after,
although it's more likely to be closer to after.

...and a completely different approach is to use partitioned tables
(requires SQL 2005 Enterprise). In that case, you would only know and
then alter your partition scheme and partition function. A purge would
be a pure metadata operation. Switch out the old data, and then drop
that table. In this case, you would purge far less often, but when you
do it, it would be snap.

--
Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

One other thing that i don't understand - whats with the DESC
indexes? I would understand for queries where you'r doing a DESC
orderby, but what other purpose to they serve?

#12

bobdurie (AT) gmail (DOT) com (bobdurie (AT) gmail (DOT) com) writes:

Quote:

Thank you very much for the insight!! More followups:

For this query/purge idead:
SELECT @loadid = MAX(LoadID)
FROM loads
WHERE CreateDate < dateadd(SECOND, -40, CURRENT_TIMESTAMP);
DELETE loads WHERE LoadID <= @loadid;

Why would i need an index on CreateDate,LoadiD ? Is it just for the
first query, or would the DELETE actually need it too?

Only for the first query, obviously.

Quote:

I'd be tempted to do the first query with READ_UNCOMMITTED transaction
level, then do the DELETE on another default transaction isolation level
connection.

Most likely, it will scan the table which is not good for overall
performance. But, yes, from a purely functional standpoint, you can
run with READ UNCOMMITTED.

Quote:

One other thing that i don't understand - whats with the DESC
indexes? I would understand for queries where you'r doing a DESC
orderby, but what other purpose to they serve?

For the index on (Type, LoadID), I added DESC since you had that in
the ORDER BY clause. For the suggested index on (CreateDate, LoadID)
I will have to admit that I don't for sure that it's useful. But I
was think that the optimizer would come in from right, and it would
be good if the first row with the matching CreateDate and the good
LoadID.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#13

On Jul 24, 6:16 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:

Quote:

bobdu... (AT) gmail (DOT) com (bobdu... (AT) gmail (DOT) com) writes:
Thank you very much for the insight!! More followups:

For this query/purge idead:
SELECT @loadid = MAX(LoadID)
FROM loads
WHERE CreateDate < dateadd(SECOND, -40, CURRENT_TIMESTAMP);
DELETE loads WHERE LoadID <= @loadid;

Why would i need an index on CreateDate,LoadiD ? Is it just for the
first query, or would the DELETE actually need it too?

Only for the first query, obviously.

I'd be tempted to do the first query with READ_UNCOMMITTED transaction
level, then do the DELETE on another default transaction isolation level
connection.

Most likely, it will scan the table which is not good for overall
performance. But, yes, from a purely functional standpoint, you can
run with READ UNCOMMITTED.

One other thing that i don't understand - whats with the DESC
indexes? I would understand for queries where you'r doing a DESC
orderby, but what other purpose to they serve?

For the index on (Type, LoadID), I added DESC since you had that in
the ORDER BY clause. For the suggested index on (CreateDate, LoadID)
I will have to admit that I don't for sure that it's useful. But I
was think that the optimizer would come in from right, and it would
be good if the first row with the matching CreateDate and the good
LoadID.

--
Erland Sommarskog, SQL Server MVP, esq... (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I've tried adding the appropriate indexes, and it does seem to help.
Just to clarify though, is the theory behind adding indexes simply
that the less of the table the select queries scan, the less lock
contention there will be, and hence fewer deadlocks? None of those
sql server locking/blocking articles talked directly about adding
indexes as a solution to deadlocks, and i've found in the past adding
indexes to frequently updated fields actually CAUSED deadlocks.
Theses fields aren't updated though, so i think i'm ok.

If my above theory assumption is true, when does it become cumbersome
to continually add indexes? If my table has N unique queries, does it
need N indicies to ensure no deadlocks? This doesn't seem right...

#14

bobdurie (AT) gmail (DOT) com (bobdurie (AT) gmail (DOT) com) writes:

Quote:

I've tried adding the appropriate indexes, and it does seem to help.
Just to clarify though, is the theory behind adding indexes simply
that the less of the table the select queries scan, the less lock
contention there will be, and hence fewer deadlocks?

Right. It's really two-dimensional, space and time. Less space is
scanned in less time, which means that the risk for two processes to
collide in time or space is reduced. But if there is action enough on
the system, there can still be deadlocks.

Quote:

None of those sql server locking/blocking articles talked directly about
adding indexes as a solution to deadlocks, and i've found in the past
adding indexes to frequently updated fields actually CAUSED deadlocks.

It's correct, that adding indexes also adds new oppurtunities for deadlocks.
And it does not only have to be index on updated columns. Say that one of
the tables you purge has 50 columns, and equally many indexes. Say
further that there is only the same queries as for the Loads table. There
would be a lot more risk for deadlock on this table than on Loads, because
both the INSERT and the purge needs to update these indexes.

Quote:

If my above theory assumption is true, when does it become cumbersome
to continually add indexes? If my table has N unique queries, does it
need N indicies to ensure no deadlocks? This doesn't seem right...

Usually, there is a big overlap between queries, so the same index can
serve several queries. Also, it matters a lot how often queries are run.
But for frequently running queries like yours, you should make sure that
all queries has a suitable index. Not necessarily the most optimal, but
you should definitely make sure that queries descend to scanning
tables.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#15

On Jul 23, 9:03 am, "bobdu... (AT) gmail (DOT) com" <bobdu... (AT) gmail (DOT) com> wrote:

Quote:

We can try the clustered index, but i'm NOT convinced why this is
going to resolve the page lock issue... i've checked the execution
plan for some of our queries and it looks like it sorts first, then
does a clustered index scan of the ID. Is this really going to have
more page locks than if the index was on the date?

If your table is clustered on the criteria you use for purging, then
your historical data and your current data are physically stored on
different pages (if you provide some grace period between current and
historical). Also your purging touches as little pages as possible and
as such runs faster. This does not guarantee you will not have
deadlocks, but it might decrease their probability. Note that index
entries for current and historical data in NCIs will still intertwine,
so you still may get deadlocks.

Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Ongoing purging of active records causes deadlocks

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: Ongoing purging of active records causes deadlocks - 07-24-2007 , 06:41 AM

Re: Ongoing purging of active records causes deadlocks - 07-24-2007 , 05:16 PM

Re: Ongoing purging of active records causes deadlocks - 07-25-2007 , 03:31 PM

Re: Ongoing purging of active records causes deadlocks - 07-25-2007 , 03:51 PM

Re: Ongoing purging of active records causes deadlocks - 07-25-2007 , 09:29 PM