SQL 2000 - 行级锁定 [英] SQL 2000 - Row Level Locking

问题描述

我们在桌子上遇到死锁，用于生成

不同类别的连续数字，例如典型条目


类别价值


TRADE_NO 1456

JOB_NO 267

.....


应用程序引用适用于它们的相关类别

并相应地更新

值。这是表非常小，占用1页。

然而，它没有索引，因为它看起来不适合这个尺寸的

表。


但是，有人可以建议是否

1.行级锁定需要索引

2.如果是小于上面的表上的索引可能会降低

的死锁率。


此外，请考虑以下但我不确定<与上述查询相关的
。


我们注意到当我们将有关数据库从SQL 6.5迁移到

SQL 2000时，使用DTS ，数据库并非严格使用SQL 2000

格式的非聚集索引（NC），即聚簇键不是
NC索引的一部分，直到聚簇索引为重建。


鉴于这一点，我应该用假指数重建这个表，然后放弃

。


我们知道过去使用的不同技术避免死锁（例如

以相同顺序访问的表等）并尽可能地实现这些实践。


我提前感谢您提供任何帮助。


谢谢


Puvendran

Hi,

We have encountered deadlock on a table which is used to generate
sequential numbers for different categories eg typical entries

Category Value

TRADE_NO 1456
JOB_NO 267
.....

The applications reference the relevant category applicable to them
and update
the Value accordingly. This is table is very small, occupying 1 page.
However, it has no index as it was not seen to be appropriate for a
table this size.

However, can someone please advise whether

1. An index is required for row level locking
2. If an index on a table as small as above is likely to reduce the
deadlock rate.

Also, please consider the following but which I am not sure is
relevant for above query.

We noted that when we migrated the database concerned from SQL 6.5 to
SQL 2000, using DTS, that the database was NOT strictly in SQL 2000
format for non clustered indexes (NC) ie the clustered key was not
part of the NC index until the clustered index was rebuilt.

Given this should I just rebuild this table with a fake index and drop
it thereafter.

We are aware of the different techniques used to avoid deadlocks (eg
tables accessed in same order etc) and have , as much as possible,
implemented those practices.

I thank you in advance for any help you may be able to offer.

Thanks

Puvendran

推荐答案

> 1.行级锁定需要索引


是的。无论表大小如何，所有表都有一个主键

总是一个好主意。这不仅是数据库中的最佳实践设计，SQL Server还将创建一个独特的索引来支持主键

约束，这样就可以让SQL Server开发出更好的执行计划for

加入并实现行级锁定。

> 1. An index is required for row level locking

Yes. It''s always a good idea for all tables to have a primary key
regardless of table size. Not only is this a best practice in database
design, SQL Server will create a unique index to support the primary key
constraint and this allows SQL Server to develop better execution plans for
joins and implement row-level locking.

2.如果表上的索引小到可能会减少
死锁率。


是的但是如果你在同一个交易中更新不同的行作为

的一部分并且以不同的顺序执行更新，你仍然会遇到问题。您可能会考虑使用IDENTITY，因为这会提高并发性。

我们注意到当我们使用DTS将有关数据库从SQL 6.5迁移到SQL 2000时，对于非聚簇索引（NC），数据库并非严格采用SQL 2000格式，即在重建聚簇索引之前，聚簇键不是NC索引的一部分。


我不确定你的意思。在SQL 2000中，聚簇索引键总是存储在非聚簇索引叶页中。这是由SQL Server自动完成的，因此你不需要做任何特别的事情来实现这一点，即使聚簇索引键不是非聚集索引，也需要
列。 SQL

Server使用非聚集索引中的聚簇索引值进行

书签查找，并在适当时覆盖查询。


-

希望这会有所帮助。


Dan Guzman

SQL Server MVP


" Puvendran" < PU ******************* @ btfinancialgroup.com>在消息中写道

news：d5 ************************** @ posting.google.c om ...

我们在桌子上遇到了死锁，用于生成不同类别的序号，例如典型的条目

类别值

TRADE_NO 1456
JOB_NO 267
....

应用程序参考适用于他们的相关类别
并相应地更新值。这个表非常小，占据了1页。
然而，它没有索引，因为它看起来不适合这个尺寸的
表。

然而，可以有人请告知是否

1.行级锁定需要索引
2.如果表上的索引小到可能会降低死锁率。

另外，请考虑以下但我不确定是否与上述查询相关。

我们注意到当我们从SQL 6.5迁移相关数据库时使用DTS的SQL 2000，数据库严格来说不是非聚集索引（NC）的SQL 2000格式，即聚簇键不是NC索引的一部分，直到聚簇索引重建了。

鉴于此，我应该用假索引重建这个表，然后放弃它。

我们知道使用的不同技术避免死锁（例如以相同顺序访问的表等）并且尽可能多地使用死锁可能，
实施了这些做法。

我提前感谢您提供任何帮助。

谢谢
Puvendran

2. If an index on a table as small as above is likely to reduce the
deadlock rate.
Yes but you can still have problems if you update different rows as part of
the same transaction and perform updates in different sequence. You might
consider using IDENTITY since this will improve concurrency.
We noted that when we migrated the database concerned from SQL 6.5 to
SQL 2000, using DTS, that the database was NOT strictly in SQL 2000
format for non clustered indexes (NC) ie the clustered key was not
part of the NC index until the clustered index was rebuilt.
I''m not sure what you mean here. In SQL 2000, the clustered index keys are
always stored in non-clustered index leaf pages. This is done automatically
by SQL Server so you don''t need to do anything special to make this happen,
even if clustered index keys are not non-clustered index columns. SQL
Server uses the clustered index values from the non-clustered index for
bookmark lookups and to cover queries when appropriate.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Puvendran" <pu*******************@btfinancialgroup.com> wrote in message
news:d5**************************@posting.google.c om... Hi,

We have encountered deadlock on a table which is used to generate
sequential numbers for different categories eg typical entries

Category Value

TRADE_NO 1456
JOB_NO 267
....

The applications reference the relevant category applicable to them
and update
the Value accordingly. This is table is very small, occupying 1 page.
However, it has no index as it was not seen to be appropriate for a
table this size.

However, can someone please advise whether

1. An index is required for row level locking
2. If an index on a table as small as above is likely to reduce the
deadlock rate.

Also, please consider the following but which I am not sure is
relevant for above query.

We noted that when we migrated the database concerned from SQL 6.5 to
SQL 2000, using DTS, that the database was NOT strictly in SQL 2000
format for non clustered indexes (NC) ie the clustered key was not
part of the NC index until the clustered index was rebuilt.

Given this should I just rebuild this table with a fake index and drop
it thereafter.

We are aware of the different techniques used to avoid deadlocks (eg
tables accessed in same order etc) and have , as much as possible,
implemented those practices.

I thank you in advance for any help you may be able to offer.

Thanks

Puvendran

pu ******************* @ btfinancialgroup.com （Puvendran）在留言中写道：< d5 ******* *******************@posting.google。 com> ...

pu*******************@btfinancialgroup.com (Puvendran) wrote in message news:<d5**************************@posting.google. com>...

我们在桌面上遇到了死锁，用于为不同类别生成序列号，例如典型条目

类别价值

TRADE_NO 1456
JOB_NO 267
....

申请参考适用于他们的相关类别然而，它没有索引，因为它看起来不适合这个尺寸的
表。

然而，可以有人请告知是否

1.行级锁定需要索引
2.如果表上的索引小到可能会降低死锁率。

另外，请考虑以下但我不确定是否与上述查询相关。

我们注意到当我们从SQL 6.5迁移相关数据库时使用DTS的SQL 2000，数据库严格来说不是非聚集索引（NC）的SQL 2000格式，即聚簇键不是NC索引的一部分，直到聚簇索引重建了。

鉴于此，我应该用假索引重建这个表，然后放弃它。

我们知道使用的不同技术避免死锁（例如以相同顺序访问的表等）并且尽可能多地使用死锁可能，
实施了这些做法。

我提前感谢您提供任何帮助。

谢谢
Puvendran

Hi,

We have encountered deadlock on a table which is used to generate
sequential numbers for different categories eg typical entries

Category Value

TRADE_NO 1456
JOB_NO 267
....

The applications reference the relevant category applicable to them
and update
the Value accordingly. This is table is very small, occupying 1 page.
However, it has no index as it was not seen to be appropriate for a
table this size.

However, can someone please advise whether

1. An index is required for row level locking
2. If an index on a table as small as above is likely to reduce the
deadlock rate.

Also, please consider the following but which I am not sure is
relevant for above query.

We noted that when we migrated the database concerned from SQL 6.5 to
SQL 2000, using DTS, that the database was NOT strictly in SQL 2000
format for non clustered indexes (NC) ie the clustered key was not
part of the NC index until the clustered index was rebuilt.

Given this should I just rebuild this table with a fake index and drop
it thereafter.

We are aware of the different techniques used to avoid deadlocks (eg
tables accessed in same order etc) and have , as much as possible,
implemented those practices.

I thank you in advance for any help you may be able to offer.

Thanks

Puvendran

Puvendran，


据我所知，不需要索引来实现行级

锁定，并且你清楚地知道桌子上的索引这个大小是无意义的（我怀疑优化器会选择使用它）。


任何索引的存在都可能会增加任何僵局的趋势，因为它会延长交易时间（指数需要更新指数和b
指针需要更新）数据），以及

死锁避免技术之一是保持交易尽可能短的时间。


当然，尽管如此，测试这一切会很简单吗？只需

实现行级锁定，有和没有索引，看看

发生了什么。


我是不确定我理解你的第二个观察 - 群集密钥

不是NC索引的一部分？也许其他有更多经验的人比我自己能够回答这个问题。

Puvendran,

As far as I am aware an index is not required to implement row-level
locking, and as you are clearly aware an index on a table this size is
pointless (I doubt the optimizer would ever choose to use it).

The presence of any index would probably increase any tendency towards
deadlocking, since it will lengthen the transaction time (the index
pointers need to be updated as well as the data), and one of the
deadlock avoidance techniques is to keep transactions as short as
possible.

Surely, though, it would be simple to test all this out? Just
implement row-level locking, with and without an index, and see what
happens.

I''m not sure I understand your second observation - the clustered key
was not part of the NC index? Maybe someone else with more experience
of SQL Server than myself can answer this one.

Philip Yale（ph ******* *@btopenworld.com）写道：

Philip Yale (ph********@btopenworld.com) writes:

据我所知，实现行级
锁定不需要索引，因为你清楚地知道一个表上的索引大小是没有意义的（我怀疑优化器会选择使用它）。


一个索引在Puvendran的表中肯定是有用的，因为如果有
没有索引，SQL Server将不得不放一个共享表锁在桌子上

能够找到要更新的行。一旦找到了行，那个锁定可能会被释放（虽然我不知道这是否真的发生了），

但是只要独家锁定在更新的行上，

下一个想要一个不同项目的序列号的人将被阻止，

因为他无法得到表锁定。

任何索引的存在都可能会增加任何死锁的趋势，因为它会延长交易时间（索引指针需要更新以及数据），并且其中一个避免死锁技术是为了使交易尽可能短。

As far as I am aware an index is not required to implement row-level
locking, and as you are clearly aware an index on a table this size is
pointless (I doubt the optimizer would ever choose to use it).
An index is definitely helpful in a table as Puvendran''s, since if there
is no index, SQL Server will have to put a shared table lock on the table
to be able to find the row to update. Once the row(s) is located, that lock
can possibly be released (although I don''t know if that really happens),
but as long as as the exclusive lock is held on the updated row, the
next guy that wants a sequence number for a different item wil be block,
because he can''t get the table lock.
The presence of any index would probably increase any tendency towards
deadlocking, since it will lengthen the transaction time (the index
pointers need to be updated as well as the data), and one of the
deadlock avoidance techniques is to keep transactions as short as
possible.

我会期望索引键是稳定的，不会更新，所以

这不会是问题。


-

Erland Sommarskog，SQL服务器MVP， so****@algonet.se


联机丛书SQL服务器SP3在
http://www.microsoft.com/sql/techinf...2000/books .asp

这篇关于SQL 2000 - 行级锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！