Deadlock on insert into single table???
Ranga Gopalan
Hi,
Your table probably has a clustered index. In case of a small table with a
clustered index, deadlocks are definitely possible (and do occur often) if
several processes are inserting records into the table simultaneously.
The deadlock is due to contention on the index pages due to page splits as
more and more rows are added to the table. This is caused by the different
locking mechanism used for locking index pages (resource locks) as compared
to locking data pages. In case of page splits the process which has passed
down the index tree to reach the data page needs to get a lock on the
higher level page which could be locked by another process descending the
index tree (I hope this makes sense).
Hope this is useful.
Ranga.
============================================================
Ranga Gopalan *** My opinions only, not my
employer's ***
Stratus Professional and Technical Services,
Stratus Computer Inc, Marlboro, MA Email:
Ranga_...@stratus.com
============================================================
Jason Kissinger <jki...@nmsmn.com> wrote in article
<32E39F...@nmsmn.com>...
> We're having processes being chosen as a deadlock victim and don't
> understand how this can happen. Here's the situation:
>
> Sybase 11.0.1 - 4 processor sparc
> Data is being inserted into a single table with no insert triggers. We
> have 4 insertion points on 4 partitions.
> We are using 4 simultaneous processes to do these insertions.
>
> How can you have a deadlock situation with single record inserts, it
> seems that this would result in a block, not a deadlock.
>
> It is my understanding that a deadlock results when two processes are
> each trying to lock something that the other needs to complete its
> transaction. Then one must be chosen as a victim so that the other can
> continue.
>
> How can this happen with single inserts into a single table? Or is my
> understanding of a deadlock incorrect?
>
> Any help would be appreciated!
>
Brenda Muller
Jason Kissinger wrote:
>
> We're having processes being chosen as a deadlock victim and don't
> understand how this can happen. Here's the situation:
>
> Sybase 11.0.1 - 4 processor sparc
> Data is being inserted into a single table with no insert triggers. We
> have 4 insertion points on 4 partitions.
> We are using 4 simultaneous processes to do these insertions.
>
> How can you have a deadlock situation with single record inserts, it
> seems that this would result in a block, not a deadlock.
>
> It is my understanding that a deadlock results when two processes are
> each trying to lock something that the other needs to complete its
> transaction. Then one must be chosen as a victim so that the other can
> continue.
>
> How can this happen with single inserts into a single table? Or is my
> understanding of a deadlock incorrect?
>
> Any help would be appreciated!
I could imagine two processes deadlocking with each doing a single
insert to the same table if that table had a clustered index on it. One
process goes down the B-tree, looking for the insert point, getting
shared locks on pages along the way, while another process comes down
the same B-tree, getting shared locks on the same pages. Neither can
promote to an exclusive lock.
If you do have a clustered index on that table, you might try dropping
it to see if what I've described above is the nature of your problem.
If you drop the clustered index and stop seeing deadlocks, I would
either a) stick with non-clustered and try to combat insert performance
problems with nice sized data caches, or b) try to find another key for
your clustered index that would give you more random insert points.
Good luck,
--
Brenda S. Muller
Sr. Consultant, Miaco Corporation / U S West
"I am a part of all that I have met;
Yet all experience is an arch wherethro'
Gleams the untravell'd world, whose margin fades
For ever and for ever when I move."
- Tennyson
Jason Kissinger
Jason Kissinger
There are no clustered indexes on this table (can't make clustered
indexes on partitioned tables). We do have nonclustered indexes, does
this idea of index page/data page locking occur for nonclustered
indexes? If so, any possible solutions? I recall something about
limiting the number of rows per index page or a percentage fill on a
page, this might decrease the liklihood of contention??
Thanks for the help.
Ranga Gopalan wrote:
>
> Hi,
>
> Your table probably has a clustered index. In case of a small table with a
> clustered index, deadlocks are definitely possible (and do occur often) if
> several processes are inserting records into the table simultaneously.
>
> The deadlock is due to contention on the index pages due to page splits as
> more and more rows are added to the table. This is caused by the different
> locking mechanism used for locking index pages (resource locks) as compared
> to locking data pages. In case of page splits the process which has passed
> down the index tree to reach the data page needs to get a lock on the
> higher level page which could be locked by another process descending the
> index tree (I hope this makes sense).
>
> Hope this is useful.
>
> Ranga.
snip...
Scott Gray
In article <32E3B9...@nmsmn.com>,
Jason Kissinger <jki...@nmsmn.com> wrote:
>There are no clustered indexes on this table (can't make clustered
>indexes on partitioned tables). We do have nonclustered indexes, does
>this idea of index page/data page locking occur for nonclustered
>indexes? If so, any possible solutions? I recall something about
>limiting the number of rows per index page or a percentage fill on a
>page, this might decrease the liklihood of contention??
This sort of thing has been known to happen even with a single user
performing an insert on tables with an excessive number of non-clustered
indexes...I don't know if table partitioning would throw another wrench
into the works as well, but you may want to try dropping a couple of
your non-clustered indexes as well. Odds are, your contention is
coming from competition on the index pages (unfortunately, the indexes
aren't partitioned :().
-scott
--
Scott C. Gray gr...@voicenet.com "my keybard is brken"
Sybase Professional Services scott...@sybase.com
http://www.voicenet.com/~gray/sqsh.html
Jason Kissinger
Thanks. I dropped all nonclustered indexes (5 of them) and had no
problem with deadlocks. It looks like performance would be harmed too
severely to keep the table without any indexes, so it looks like I'll
just have to catch the deadlock and resubmit the query.
On a side note - we've just started using sqsh for the last couple of
weeks replacing isql. I don't know how I got by without it, especially
the ability to pipe and redirect and binding vi to it makes editing so
much quicker. Thanks for the great software!!
Anthony Mandic
Jason Kissinger wrote:
>
> We're having processes being chosen as a deadlock victim and don't
> understand how this can happen. Here's the situation:
>
> Sybase 11.0.1 - 4 processor sparc
> Data is being inserted into a single table with no insert triggers. We
> have 4 insertion points on 4 partitions.
> We are using 4 simultaneous processes to do these insertions.
>
> How can you have a deadlock situation with single record inserts, it
> seems that this would result in a block, not a deadlock.
I can imagine two possible scenarios. Actually, these are
the only two. The blocking that leads to the deadlocks can
happen on either the data pages or the index pages. Since
your table is partitioned, you can't have a clustered index
on the table. What this really means, from your perspective,
is that your data is going into a heap table in each of your
partitions. In other words, any insertions are always going
to happen on the last data page of each partition. With a
high insertion rate, this would lead to hot spot contention
on these last data pages. Partitioning may not really help
since you can't be guaranteed that each of your 4 processes
will write to any particular partition. This is compounded
by the problem that indexes aren't partitioned. That is, it
is a whole. So all your 4 processes are competing for the
one index. The more indexes you have on the table, the more
contention you'll get on the index pages.
Your best bet would be to experiment both with your table
layout and tuneable server options until you find a suitable
combination that reduces the rate of deadlocks. Try altering
the number of processes, the number of partitions, the number
of indexes etc, and look at the configuration parameters that
deal with deadlocks (i.e. 'deadlock checking period' and
'deadlock retries'). Also look at caching the indexes and data
into their own pools. The only other issue that might be of
concern would be the physical layout of data, indexes and
logs over disks and controllers. A slow or contentious I/O
subsystem might be compounding the problem.
-am
Brenda Muller
Anthony Mandic wrote:
Ok - sorry, I spaced out on the partitioning thing (actually didn't read
the post very thoroughly). I can see how the deadlocks could occur on
non-clustered index pages (same data structure as a clustered index -
just smaller therefore less of a chance of deadlocks occuring).
However, I don't really see how a deadlock could occur on a data page
during an insert to a heap table. I would think only an exclusive lock
would be grabbed on this page. Why would SQL Server grab a shared
lock? It knows where it's inserting - it's not looking for anything on
the page.
With regards to SMP increasing the likelihood of deadlocks, I can see
this only in the respect that it's possible for multiple processes to be
trying to grab the index pages simultaneously. So I see your point
there. But I don't think SMP in general would increase the likelihood
of deadlocks (assuming a good data model and indexes), since processors
don't really share tasks, they just migrate them around. What do you
think about that?
But in general I think you're right - his problems are a result of
over-indexing, and the contention is occuring on the non-clustered
B-tree pages.
Thanks,
Anthony Mandic
Brenda Muller wrote:
> Ok - sorry, I spaced out on the partitioning thing (actually didn't read
> the post very thoroughly). I can see how the deadlocks could occur on
> non-clustered index pages (same data structure as a clustered index -
> just smaller therefore less of a chance of deadlocks occuring).
Actually, more of a chance of deadlocks occuring. Since its smaller,
the keys of records populate more per index page. Thus, the more
activity, the higher the likelihood.
> However, I don't really see how a deadlock could occur on a data page
> during an insert to a heap table. I would think only an exclusive lock
> would be grabbed on this page. Why would SQL Server grab a shared
> lock? It knows where it's inserting - it's not looking for anything on
> the page.
You are correct. But hot-spot contention is always an issue with
insertions in a heap table. Especially since the orignal poster
was running in an SMP environment. In this case, I think transactions
would be less likely to be serialized. However, I was thinking more
along the lines of a combination of the two factors - index insert
and help table insert. Sorry I didn't make it clearer.
> With regards to SMP increasing the likelihood of deadlocks, I can see
> this only in the respect that it's possible for multiple processes to be
> trying to grab the index pages simultaneously. So I see your point
> there. But I don't think SMP in general would increase the likelihood
> of deadlocks (assuming a good data model and indexes), since processors
> don't really share tasks, they just migrate them around. What do you
> think about that?
True. But they could also be trying to write to the same data pages
as well. Unfortunately, the documentation isn't clear on what
happens with multiple partitioned table inserts in an SMP environment.
Since the insert transactions won't be as serialized as with a
single-engined server, I think it would be best to be pessimistic.
> But in general I think you're right - his problems are a result of
> over-indexing, and the contention is occuring on the non-clustered
> B-tree pages.
Yes, I saw one of his followups. He has 5 non-clustered indexes.
With 4 processes doing inserts its easy to imagine at least one
transaction updating one of the indexes whilst another is in the
process of doing the same to one of the other ones. This is basically
why Sybase recommend no more than 2 or 3 indexes on a table in an
SMP environment. This used to be documented in the Admin manual
under the topic 'Special Considerations for SQL Server in an SMP
Environment' for earlier versions of the servers. For System XI
its now in the SQL Server Performance and Tuning Guide, in chapter
17 under the topic 'Multiprocessor Application Design Guidelines',
where there is an excellent discourse on the issues involved.
-am
Brenda Muller
Hi Anthony,
Thank you for your response.
>
> Actually, more of a chance of deadlocks occuring. Since its smaller,
> the keys of records populate more per index page. Thus, the more
> activity, the higher the likelihood.
Hmmm... I'm still trying to work this one out. A clustered index is
sparse, whereas a non-clustered is dense, so in an SMP environment with
dense indexes you have an increased likelihood that multiple processes
will try to scan the same index page, rather than with a sparse index,
where the index entries are not as likely to exist on one page.
However, when updating a clustered index, you would be attempting to
lock *more* pages than with a non-clustered index for a single insert.
And unless you have a very random key for clustering, I can see a high
likelihood of deadlocks. Non-clustered index B-trees are smaller,
shorter, and more compact than clustered index B-trees. Clustered
indexes are bigger and leggier. And because maintaining a clustered
index B-tree frequently involves the significant overhead of page
splitting, you also have a higher chance of holding locks on those pages
longer. Anyway... that's the way it seems to me. However, it does
seem as if most people seem to think that deadlocks will occur more
frequently on non-clustered index pages, as opposed to clustered.
Certainly I seem to have heard more about these non-clustered deadlocks
than clustered ones. So, I guess I don't really understand. Do you
have any more ideas?
--
Brenda S. Muller
Sr. Consultant, Miaco Corporation / U S West
"For whatsoever from one place doth fall,
is with the tide unto another brought.
For there is nothing lost that may be found
if sought."
-- ??
Pablo Sanchez
In article <32E50A...@nmsmn.com>, Jason Kissinger <jki...@nmsmn.com> writes:
> Thanks. I dropped all nonclustered indexes (5 of them) and had no
> problem with deadlocks. It looks like performance would be harmed too
> severely to keep the table without any indexes, so it looks like I'll
> just have to catch the deadlock and resubmit the query.
>
> On a side note - we've just started using sqsh for the last couple of
> weeks replacing isql. I don't know how I got by without it, especially
> the ability to pipe and redirect and binding vi to it makes editing so
> much quicker. Thanks for the great software!!
>
>
Coming in late into this conversation... I've run some benches doing
table partitioning and using a *single* non-clustered index... the
results were what you saw, excessive deadlocks. I posted that
information to this group a while ago and a kind Sybase employee
picked up on it and has submitted a bug regarding the locking
strategies to "Index Engineers"... have you tried running your bench
with a clustered index? I know that it sounds counter intuitive but
I get really great results using clustered (and obviously no table
partitions)...
--
Pablo Sanchez | Ph # (415) 933.3812 Fax # (415) 933.2821
pa...@sgi.com | Pg # (800) 930.5635 -or- pab...@pager.sgi.com
===============================================================================
I am accountable for my actions. http://reality.sgi.com/pablo/Sybase_FAQ
Karen Paulsell
In article <32E56B...@comdyn.com.au.no_spam> Anthony Mandic <a...@comdyn.com.au.no_spam> writes:
>Jason Kissinger wrote:
>>
>> We're having processes being chosen as a deadlock victim and don't
>> understand how this can happen. Here's the situation:
>>
>> Sybase 11.0.1 - 4 processor sparc
>> Data is being inserted into a single table with no insert triggers. We
>> have 4 insertion points on 4 partitions.
>> We are using 4 simultaneous processes to do these insertions.
>>
>> How can you have a deadlock situation with single record inserts, it
>> seems that this would result in a block, not a deadlock.
>
Thanks to a similar situation described a few months ago in this
news group by Pablo, I did some investigation on the problem.
He had a partitioned heap table, and a nonclustered index on
an identity column. While one might expect this situation to remove
insert contention from the 'last page' of the heap to the 'last
page' of the leaf level of the nonclustered index, what it did
was introduce a deadlock situtation whenever that last page filled and
needed to split.
I can explain this situation really easily on a whiteboard, with a few
rectangles and arrows, and the letters "S" for shared lock and "E" for
exclusive lock. I'm not sure I'll do so well in prose, but here's an
attempt.
When we're inserting rows into the nonclustered index, we get shared
address locks on the index pages as we're descending the index tree
looking for the place to insert the row, and then an exclusive lock on
the leaf page for the actual row insert. This does not cause any
problems as long as there is room on the page for the new row.
But when the page fills up, and there's not room on the page, we need
to split the page. When that happens, we need to insert a row in the
parent page for that leaf level page, so we hold the exclusive locks on
the just-filled page, and the newly allocated leaf level page, and
attempt to get an exclusive lock on the parent. We can't get the
exclusive lock on the parent: other inserts are holding shared locks on
it, waiting to get the exclusive lock on the leaf level. This
produces the deadlock. What I've generally seen, with 20 or so
processes doing inserts simulataneously, is several deadlocks at
this point, and then the split succeeds, and successful inserts
again until the next page split, and then several deadlocks again.
Several of our really experienced engineers have looked at this
situation, and have proposed a solution. I don't know the current
status or projected availability of this work.
If one of the five indexes that the original poster mentioned is on an
ascending key, just eliminating that one index could sharply reduce the
deadlocking. Using max rows per page would be the one of the worst
things to do: it would increase the number of page splits on the
index.
If the problem is coming from indexes that are not on an ascending key,
I think that using fillfactor on the nonclustered indexes to reduce
page splits could sharply reduce the number of deadlocks.
I hope this explanation is clear enough; it's much easier on a
whiteboard...
Jason Kissinger
First off, thanks for everyone's suggestions and insight into our
problem.
Here what we tried:
Tried reducing the number of nonclustered indexes (from 5 to 1) but that
didn't seem to decrease the occurence of deadlocks (the one remaining
index was on an ascending unique key, that might have been the problem;
still had a hotspot).
Tried increasing the "deadlock checking period" and "deadlock retries"
with no change in
occurence.
Finally took Pablo's advice and unpartitioned the tables and created a
clustered index on a key that wasn't ascending (avoiding a hotspot).
The number of deadlocks dropped dramatically! Just out of curiousity,
we tried it with a clustered index on an ascending unique key and had
nearly 20 deadlocks per 1000 transactions (was previously 1 per 1000 and
with Pablo's advice got it down to 1 per 50,000).
Jason Kissinger wrote:
>
> We're having processes being chosen as a deadlock victim and don't
> understand how this can happen. Here's the situation:
>
> Sybase 11.0.1 - 4 processor sparc
> Data is being inserted into a single table with no insert triggers. We
> have 4 insertion points on 4 partitions.
> We are using 4 simultaneous processes to do these insertions.
>
> How can you have a deadlock situation with single record inserts, it
> seems that this would result in a block, not a deadlock.
>
Brenda Muller
Karen Paulsell wrote:
<snip>
>
> But when the page fills up, and there's not room on the page, we need
> to split the page. When that happens, we need to insert a row in the
> parent page for that leaf level page, so we hold the exclusive locks on
> the just-filled page, and the newly allocated leaf level page, and
> attempt to get an exclusive lock on the parent. We can't get the
> exclusive lock on the parent: other inserts are holding shared locks on
> it, waiting to get the exclusive lock on the leaf level. This
> produces the deadlock. What I've generally seen, with 20 or so
> processes doing inserts simulataneously, is several deadlocks at
> this point, and then the split succeeds, and successful inserts
> again until the next page split, and then several deadlocks again.
>
> Several of our really experienced engineers have looked at this
> situation, and have proposed a solution. I don't know the current
> status or projected availability of this work.
>
> If one of the five indexes that the original poster mentioned is on an
> ascending key, just eliminating that one index could sharply reduce the
> deadlocking. Using max rows per page would be the one of the worst
> things to do: it would increase the number of page splits on the
> index.
>
> If the problem is coming from indexes that are not on an ascending key,
> I think that using fillfactor on the nonclustered indexes to reduce
> page splits could sharply reduce the number of deadlocks.
>
Karen,
Thanks for your explanation - it's very clear, even without the
whiteboard. I do have just one more pesky question (unfortunately I
always seem to have one more question). I would think that this problem
would have shown up before, regardless of table partitioning. What does
it really have to do with partitioning? Or does it? In your
description of what's going on, you don't specify that it's a problem
with partitioning, but it seems to be implied by other's posts. It
would seem to me to be more of a problem with very high volumes of
inserts to the same table with a monotonic non-clustered index in
general.
Pablo Sanchez
In article <32E845...@comdyn.com.au.no_spam>, Anthony Mandic <a...@comdyn.com.au.no_spam> writes:
> Pablo Sanchez wrote:
> >
> I recall that post. If I remember correctly, I think you
> stated that it was some sort of history table with a lot
> of data in it. But I can't recall now how many partitions
> you had and how many processes were doing inserts.
I tried something like 20/100 partitions... I was also slamming as
much data as possible into the table....
> [ fewer deadlocks seen in Sys 11 ]
>
Hmmmm.... what's your transaction rate on this table?
> > I posted that
> > information to this group a while ago and a kind Sybase employee
> > picked up on it and has submitted a bug regarding the locking
> > strategies to "Index Engineers"... have you tried running your bench
> > with a clustered index? I know that it sounds counter intuitive but
> > I get really great results using clustered (and obviously no table
> > partitions)...
>
> If it was a bug that you encountered, then that could explain
> the situation. I'm running the latest System XI EBF. Since your
> post was some months ago, I think I could be safe in assuming
> that you were benchmarking under an earlier version of the
> server.
That's correct... it was 11.0.1
> Have you tried it again under the latest EBF?
Haven't had a chance... may be soemthing I ought to try when I get a
breath.... did you do your work on 11.0.2[.1]???
Pablo Sanchez
In article <32E8CF...@netmail.mnet.uswest.com>, Brenda Muller <bmu...@netmail.mnet.uswest.com> writes:
>
> Karen,
>
> Thanks for your explanation - it's very clear, even without the
> whiteboard. I do have just one more pesky question (unfortunately I
> always seem to have one more question). I would think that this problem
> would have shown up before, regardless of table partitioning. What does
> it really have to do with partitioning? Or does it? In your
> description of what's going on, you don't specify that it's a problem
> with partitioning, but it seems to be implied by other's posts. It
> would seem to me to be more of a problem with very high volumes of
> inserts to the same table with a monotonic non-clustered index in
> general.
>
Brenda (and Karen)
1) I've seen this problem outside of table partitioning (Sys 10)
2) Karen, that was an excellent explanation... would it help if some
of us customers reported the problem to Sybase TS to get a bug ID
assigned? Lemme know... thx!
Pablo Sanchez
In article <32E7D4...@nmsmn.com>, Jason Kissinger <jki...@nmsmn.com> writes:
>
> Finally took Pablo's advice and unpartitioned the tables and created a
> clustered index on a key that wasn't ascending (avoiding a hotspot).
> The number of deadlocks dropped dramatically! Just out of curiousity,
> we tried it with a clustered index on an ascending unique key and had
> nearly 20 deadlocks per 1000 transactions (was previously 1 per 1000 and
> with Pablo's advice got it down to 1 per 50,000).
>
What does "sp_sysmon" say is your transaction rate? I would think
that your rate is quite high even with the hotspot on the last
page... that's what I've seen... it *seems* counterintuitive but
that's what I've seen...
Manoj Samel
Jason Kissinger wrote:
>
> There are no clustered indexes on this table (can't make clustered
> indexes on partitioned tables). We do have nonclustered indexes, does
> this idea of index page/data page locking occur for nonclustered
> indexes? If so, any possible solutions? I recall something about
> limiting the number of rows per index page or a percentage fill on a
> page, this might decrease the liklihood of contention??
>
>
> Ranga Gopalan wrote:
> >
> > Hi,
> >
> > Your table probably has a clustered index. In case of a small table with a
> > clustered index, deadlocks are definitely possible (and do occur often) if
> > several processes are inserting records into the table simultaneously.
> >
> > The deadlock is due to contention on the index pages due to page splits as
> > more and more rows are added to the table. This is caused by the different
> > locking mechanism used for locking index pages (resource locks) as compared
> > to locking data pages. In case of page splits the process which has passed
> > down the index tree to reach the data page needs to get a lock on the
> > higher level page which could be locked by another process descending the
> > index tree (I hope this makes sense).
> >
> > Hope this is useful.
> >
> > Ranga.
> snip...
control rows per page. Unfortunately, fillfactor is not maintained
once the index is created. System XI has introduced max_rows_per_page
clause for table and indexes and it can really help in some cases.
HTH,
Manoj
Anthony Mandic
Pablo Sanchez wrote:
>
> I tried something like 20/100 partitions... I was also slamming as
> much data as possible into the table....
How many processes/engines?
> > [ fewer deadlocks seen in Sys 11 ]
> >
>
> Hmmmm.... what's your transaction rate on this table?
I haven't got SQL Monitor up and running yet on the new
server, but the stats I kept from the old one would do
just as well since the characteristics haven't changed
all that much, from a transactional perspective. The layouts
are quite different, so some of the performance characteristics
would have changed. Here is an extracted summary of one of
the recorded stats from late 1995 -
SCSI disk M - data
Part. Device Reads Writes Req. Ref. %
a a (a) 31666 27038 106965 10239 9.572
b b (a) 26554 74210 218697 82227 37.598
d c (b) 37 888 1304 9 0.690
e d (b) 54 2592 5768 2635 45.683
f e (b) 1765 562 4677 486 10.391
g f 5956 1522 14651 585 3.993
h master 62 904 1479 0 0.0
______________________________________________________
66094 107716 353541 96181 27.205
SCSI disk N - data
Part. Device Reads Writes Req. Ref. %
a a (b) 72594 83510 361876 121003 33.438
b b (b) 18597 22904 75080 9817 13.053
d c (a) 138 2776 6224 2705 43.461
e d (a) 263 856 2345 635 27.079
f e (a) 909 168 2064 0 0.0
g e (c) 992 424 2860 367 12.832
______________________________________________________
93493 110638 450449 134527 29.865
IPI disk - logs
Part. Device Reads Writes Req. Ref. %
a a 43 154950 232242 362 0.156
b b 61 233052 352044 3318 0.943
d c 128 159850 238468 76 0.032
e d 12 69840 104782 0 0.0
f e 4 16166 24223 0 0.0
g f 0 1020 1528 0 0.0
_____________________________________________________
248 634878 953287 3756 0.394
The fields of interest are disk M, partition d and disk N,
partition d. The data pages for the table were on N and the
index was on M. You can see from the above that the percentage
difference between the requests and refusal rate (as recorded
by SQL Monitor) was a high 43% on the data pages but less than
1% on the index pages. The overall reads and writes were
negligible compared to the whole disk, however. This did
result in a lot of deadlocks for us, but interesting, the
tables on disk M partition e didn't raise any with its 45%
rate. Of course, it had a low number of reads and writes too.
So it appears to me that the issue here is one of contention
in an SMP environment. 3 engines with about 200 users. And,
of course, the slow hardware we had didn't help. The new
setup is a lot better.
There were no problems with logging for the databases related
to the disks I've cited too, but I thought I'd throw in a
sample of the log disks because we were using IPI drives
for our log devices at the time. I recall about 3 years ago
someone from Sybase recommended avoiding the use of IPI
drives for logs in this newsgroup. I can't recall why now,
but as you can see, we had no problems with them.
> > I'm running the latest System XI EBF. Since your
> > post was some months ago, I think I could be safe in assuming
> > that you were benchmarking under an earlier version of the
> > server.
>
> That's correct... it was 11.0.1
>
> > Have you tried it again under the latest EBF?
>
> Haven't had a chance... may be soemthing I ought to try when I get a
> breath.... did you do your work on 11.0.2[.1]???
Like I said - the latest - 11.0.2.1, EBF 671X.
-am
Karen Paulsell
In article <32E897...@baan.com> Manoj...@baan.com writes:
>If you are using version 10 or below, you can use fillfactor to
>control rows per page. Unfortunately, fillfactor is not maintained
>once the index is created. System XI has introduced max_rows_per_page
>clause for table and indexes and it can really help in some cases.
However, if the index deadlocks are occuring on page splits,
and I think many of them are doing exactly that, then setting
max rows per page to reduce the number of rows per page will
make the problem worse, it seems to me, because you wind up
splitting pages more often.
Why I think we deadlock on page splits: index searches acquire
shared locks going down the index tree. Data mods on index pages
lock a single page. No problems there.
Page splits on index pages cause deadlocks in two ways (at least):
* If two adjacent pages are splitting 'at once', they each need
to acquire a lock on the other in order to update the
next/previous page pointers.
* When an index page is split, the parent page needs to be updated
with an insert of the pointer to the new page. It acquires this
lock by going back up the tree, holding the lock on the
splitting page and the newly allocated page, and attempting
to acquire an exclusive lock on the parent page. While it's trying
to lock "up", it encounters incompatible locks coming "down".
Index "shrinks", I think, will have the same problems.
These deadlocks are most likely to be encountered if the combination of
index keys and inserts/deletes concentrates the data modifications in a
narrow slice of the index tree. The partitioned table + index on the
identity column is the easiest to reproduce, and many of you seem to
have done it. I'm sure there are other real world examples. Which
sometimes makes me glad that I don't have to work in the real world, I
only write the docs. (With a wry grin, and thanks to you all for
helping me make them a better match to what our users really face!)
Anthony Mandic
Karen Paulsell wrote:
>
> Thanks to a similar situation described a few months ago in this
> news group by Pablo, I did some investigation on the problem.
> He had a partitioned heap table, and a nonclustered index on
> an identity column. While one might expect this situation to remove
> insert contention from the 'last page' of the heap to the 'last
> page' of the leaf level of the nonclustered index, what it did
> was introduce a deadlock situtation whenever that last page filled and
> needed to split.
Now is that the last data page or the last index page?
[...]
> If one of the five indexes that the original poster mentioned is on an
> ascending key, just eliminating that one index could sharply reduce the
> deadlocking. Using max rows per page would be the one of the worst
> things to do: it would increase the number of page splits on the
> index.
What about the fact that he was running this in an SMP
environment where the concurrency would be higher than
with just one engine?
> If the problem is coming from indexes that are not on an ascending key,
> I think that using fillfactor on the nonclustered indexes to reduce
> page splits could sharply reduce the number of deadlocks.
But fillfactor is only useful when a index is first created.
Or do you mean the configurable option available under
System XI?
-am
Karen Paulsell
In article <5cb931$q...@mew.corp.sgi.com> no-spam-is-good-spam writes:
>
>1) I've seen this problem outside of table partitioning (Sys 10)
>
>2) Karen, that was an excellent explanation... would it help if some
> of us customers reported the problem to Sybase TS to get a bug ID
> assigned? Lemme know... thx!
Pablo, as a result of your posting, I filed bug 121306, describing the
problem with partitioned tables and indexes on partitioned tables. That
info is below. I'm not sure what the effects would be if customers who
have encountered this problem, or similar index deadlocks, reported the
situation to tech support. Bret might have a better idea on that score.
I'll just say that following the mail thread that developed internally
about this bug has led me to think that there is a possible solution
short of row level locking) and that some customer pressure might
increase the priority of this bug.
bug_id
-----------
121306
User created a table with 16 partitions, and an index on an
identity column. With lots of inserts going on, he experienced
a high rate of deadlocking on index pages. (He wouldn't have
been surprised by lock contention on the index, but was
surprised by the deadlocks.)
This is very easy to reproduce:
* Create a heap table, with 16 partitions, one idenity column
* Create a nonclustered index on the identity column
* With several (I used 20) sessions, do inserts:
insert part_table (c2, c3, c4) values ("A", "B", 9194)
go
insert...
go
insert...
go
etc.
With 20 users, each doing 30 inserts, sp_sysmon showed 40
index deadlocks.
The deadlocks were always between the latest index page and
the preceding level of the index. If there was only a root
and leaf level, the deadlock was between the root and leaf
level; with more rows, the deadlocks were between the leaf
and intermediate level.
Pablo Sanchez
In article <32ED9D...@comdyn.com.au.no_spam>, Anthony Mandic <a...@comdyn.com.au.no_spam> writes:
>
> I haven't got SQL Monitor up and running yet on the new
> server, but the stats I kept from the old one would do
> just as well since the characteristics haven't changed
> all that much, from a transactional perspective.
how about running sp_sysmon to give db tx?
> SCSI disk M - data
>
> Part. Device Reads Writes Req. Ref. %
> d c (b) 37 888 1304 9 0.690
>
> SCSI disk N - data
>
> Part. Device Reads Writes Req. Ref. %
> d c (a) 138 2776 6224 2705 43.461
>
>
> The fields of interest are disk M, partition d and disk N,
> partition d. The data pages for the table were on N and the
> index was on M. You can see from the above that the percentage
> difference between the requests and refusal rate (as recorded
> by SQL Monitor) was a high 43% on the data pages but less than
> 1% on the index pages.
Interesting numbers... swaggering a guess at this table it would
appear that we may do an insert into the table and thus the index
information gets updated, then we perform some updates to the table
(but not to columns that are part of the index?). Does that
characterize the application with respect to this table?
> So it appears to me that the issue here is one of contention
> in an SMP environment. 3 engines with about 200 users. And,
> of course, the slow hardware we had didn't help. The new
> setup is a lot better.
I'm not sure I'd reach that conclusion just yet only because I don't
know the application well enough... assuming that I characterized
the transactions on that table correctly then the above supposition
may not be true. But before we get into the nitty gritty perhaps we
should talk about what the application is doing with this particular
table... rather than letting this fool trying to "guess"... :-)
> > Haven't had a chance... may be soemthing I ought to try when I get a
> > breath.... did you do your work on 11.0.2[.1]???
>
> Like I said - the latest - 11.0.2.1, EBF 671X.
Although reading Karen's post, it seems that this bug is still
outstanding...
In article <5ck98a$g...@fyi.sybase.com>, kar...@sybase.com writes:
=================================================================
bug_id
-----------
121306
User created a table with 16 partitions, and an index on an
identity column. With lots of inserts going on, he experienced
a high rate of deadlocking on index pages. (He wouldn't have
been surprised by lock contention on the index, but was
surprised by the deadlocks.)
--
Pablo Sanchez
In article <32ED8F...@comdyn.com.au.no_spam>, Anthony Mandic <a...@comdyn.com.au.no_spam> writes:
> > If one of the five indexes that the original poster mentioned is on an
> > ascending key, just eliminating that one index could sharply reduce the
> > deadlocking. Using max rows per page would be the one of the worst
> > things to do: it would increase the number of page splits on the
> > index.
>
> What about the fact that he was running this in an SMP
> environment where the concurrency would be higher than
> with just one engine?
Actually I did this on a uniprocessor machine...
Pablo Sanchez
For high performance environments the following bug needs to be fixed.
You may consider logging a case with Sybase Tech Support to give the
bug the visibility and priority that it needs. As soon as
SupportPlus Online Services comes back online:
DB-Library: Unexpected EOF from SQL Server.
I'll give it a whirl... :-)
In article <5ck98a$g...@fyi.sybase.com>, kar...@sybase.com writes:
>
> bug_id
> -----------
> 121306
> User created a table with 16 partitions, and an index on an
> identity column. With lots of inserts going on, he experienced
> a high rate of deadlocking on index pages. (He wouldn't have
> been surprised by lock contention on the index, but was
> surprised by the deadlocks.)
--
Bret Halford
In article <5ck98a$g...@fyi.sybase.com>, kar...@sybase.com (Karen Paulsell) writes:
|> In article <5cb931$q...@mew.corp.sgi.com> no-spam-is-good-spam writes:
|> >
|> I'm not sure what the effects would be if customers who
|> have encountered this problem, or similar index deadlocks, reported the
|> situation to tech support. Bret might have a better idea on that score.
|> I'll just say that following the mail thread that developed internally
|> about this bug has led me to think that there is a possible solution
|> short of row level locking) and that some customer pressure might
|> increase the priority of this bug.
Sybase Engineering does factor the number of open cases associated with each
bug into the priority of getting that bug fixed. So if you are encountering
a problem that matches this bug description, it wouldn't hurt to have
a case open with that bugid attached. However, if you are not actually
hitting the bug (ie you can reproduce it if you try, but you are not really
hitting it during your production or development efforts), it would be a bad
idea to open a case on it as this could divert attention from potentially
more serious problems.
--
---------------------------------------------------------------------
| Bret Halford Imagine my disappointment __|
| Sybase Technical Support in learning the true nature __|
| 6400 S. Fiddlers Green Circle of rec.humor.oracle... __|
| Englewood, CO 80111-4954 USA |
============================================================
Chris Jones
kar...@sybase.com (Karen Paulsell) wrote:
>In article <32E56B...@comdyn.com.au.no_spam> Anthony Mandic <a...@comdyn.com.au.no_spam> writes:
>>>
>>> We're having processes being chosen as a deadlock victim and don't
>>> understand how this can happen. Here's the situation:
>>>
>>> Sybase 11.0.1 - 4 processor sparc
>>> Data is being inserted into a single table with no insert triggers. We
>>> have 4 insertion points on 4 partitions.
>>> We are using 4 simultaneous processes to do these insertions.
>>>
>>> How can you have a deadlock situation with single record inserts, it
>>> seems that this would result in a block, not a deadlock.
>>
>news group by Pablo, I did some investigation on the problem.
>He had a partitioned heap table, and a nonclustered index on
>an identity column. While one might expect this situation to remove
>insert contention from the 'last page' of the heap to the 'last
>page' of the leaf level of the nonclustered index, what it did
>was introduce a deadlock situtation whenever that last page filled and
>needed to split.
>
>rectangles and arrows, and the letters "S" for shared lock and "E" for
>exclusive lock. I'm not sure I'll do so well in prose, but here's an
>attempt.
>
>When we're inserting rows into the nonclustered index, we get shared
>address locks on the index pages as we're descending the index tree
>looking for the place to insert the row, and then an exclusive lock on
>the leaf page for the actual row insert. This does not cause any
>problems as long as there is room on the page for the new row.
>
>to split the page. When that happens, we need to insert a row in the
>parent page for that leaf level page, so we hold the exclusive locks on
>the just-filled page, and the newly allocated leaf level page, and
>attempt to get an exclusive lock on the parent. We can't get the
>exclusive lock on the parent: other inserts are holding shared locks on
>it, waiting to get the exclusive lock on the leaf level. This
>produces the deadlock. What I've generally seen, with 20 or so
>processes doing inserts simulataneously, is several deadlocks at
>this point, and then the split succeeds, and successful inserts
>again until the next page split, and then several deadlocks again.
>
>Several of our really experienced engineers have looked at this
>situation, and have proposed a solution. I don't know the current
>status or projected availability of this work.
>
>ascending key, just eliminating that one index could sharply reduce the
>deadlocking. Using max rows per page would be the one of the worst
>things to do: it would increase the number of page splits on the
>index.
>
>I think that using fillfactor on the nonclustered indexes to reduce
>page splits could sharply reduce the number of deadlocks.
>
>whiteboard...
I know it's not the same problem BUT it is sort of relevent :
We set up a heap table with NO indexes. The table has a high insert rate by numerous users. The table was designed to remove any
chance of blocking locks - it was padded to 1 row per page. Guess what ; blocking page locks all over the place. The reason, hmmm
, well, Sybase TS described it as follows :
Process looks at last page in chain to see if it can place new row on it ; it places an ex page lock at this point. It can;t so it
goes to the next (empty) page. Meanwhile A. N. Other process goes to the last page to attempt an insert - it can't because it's
locked.
I suppose 11 sovles this but heck , that's years of "you can fake row level locking" out the window.
Chris
************************************************
Chris Jones
E-mail : chr...@outlands.demon.co.uk
************************************************