代码之家 › 专栏 › 技术社区 › tangens

选择以使用SQL Server进行更新

read-committed-snapshot tsql sql-server-2005 sql-server sql

tangens · 技术社区 · 15 年前

我正在使用隔离级别为的Microsoft SQL Server 2005数据库 READ_COMMITTED 和 READ_COMMITTED_SNAPSHOT=ON .

现在我想使用:

SELECT * FROM <tablename> FOR UPDATE

…以便其他数据库连接在尝试访问同一行“for update”时阻塞。

我尝试过:

SELECT * FROM <tablename> WITH (updlock) WHERE id=1

…但这会阻止所有其他连接,即使是为了选择“1”以外的ID。

这是正确的提示 SELECT FOR UPDATE 如Oracle、DB2、MySQL所知?

编辑2009-10-03:

以下是创建表和索引的语句:

CREATE TABLE example ( Id BIGINT NOT NULL, TransactionId BIGINT, 
    Terminal BIGINT, Status SMALLINT );
ALTER TABLE example ADD CONSTRAINT index108 PRIMARY KEY ( Id )
CREATE INDEX I108_FkTerminal ON example ( Terminal )
CREATE INDEX I108_Key ON example ( TransactionId )

很多并行进程都会这样做 SELECT :

SELECT * FROM example o WITH (updlock) WHERE o.TransactionId = ?

编辑2009-10-05:

为了获得更好的概述,我将所有尝试过的解决方案都写在下表中:

mechanism              | SELECT on different row blocks | SELECT on same row blocks
-----------------------+--------------------------------+--------------------------
ROWLOCK                | no                             | no
updlock, rowlock       | yes                            | yes
xlock,rowlock          | yes                            | yes
repeatableread         | no                             | no
DBCC TRACEON (1211,-1) | yes                            | yes
rowlock,xlock,holdlock | yes                            | yes
updlock,holdlock       | yes                            | yes
UPDLOCK,READPAST       | no                             | no

I'm looking for        | no                             | yes

18 回复 | 直到 10 年前

Community M-A 7 年前

最近我有一个 deadlock problem 因为SQL Server锁定的次数比必需的多(第页)。你不能做任何反对它的事情。现在我们正在捕获死锁异常…我希望我能换成先知。

编辑: 同时,我们使用快照隔离,这解决了许多问题,但并不是所有的问题。不幸的是,为了能够使用快照隔离,数据库服务器必须允许使用快照隔离,这可能会导致客户站点出现不必要的问题。现在,我们不仅捕获死锁异常(当然仍然可能发生),而且还捕获并发问题,以便从后台进程(用户不能重复)重复事务。但这仍然比以前好得多。

BenMorel Sonaten 11 年前

我也有类似的问题,我只想锁定一行。据我所知, UPDLOCK 选项,sqlserver锁定它需要读取的所有行以获取该行。因此,如果不定义直接访问行的索引,则前面的所有行都将被锁定。在您的示例中:

A请注意,您有一个名为tbl的表, id 字段。你想用 id=10 . 您需要为字段ID(或所选的任何其他字段)定义索引:

CREATE INDEX TBLINDEX ON TBL ( id )

然后,只锁定所读取行的查询是:

SELECT * FROM TBL WITH (UPDLOCK, INDEX(TBLINDEX)) WHERE id=10.

如果不使用index(tblindex)选项,则sqlserver需要从表的开头读取所有行,以查找包含 ID=10 ,因此这些行将被锁定。

Christian Hayter 15 年前

不能同时进行快照隔离和阻塞读取。快照隔离的目的是防止阻塞读取。

BlueMonkMN 15 年前

尝试(向上锁定,行锁定)

Jonathan Leffler 15 年前

完整的答案可以深入研究DBMS的内部。这取决于查询引擎(它执行由SQL优化器生成的查询计划)的运行方式。

但是,一个可能的解释(至少适用于某些DBMS的某些版本-不一定适用于MS SQL Server)是,ID列上没有索引,因此任何试图使用' WHERE id = ? '在中,它最终对表进行顺序扫描,并且该顺序扫描会命中进程应用的锁。如果DBMS默认应用页级锁定,也可能会遇到问题;锁定一行会锁定整个页和该页上的所有行。

有一些方法可以揭穿这是麻烦的根源。查看查询计划;研究索引;尝试选择ID为1000000而不是1的,看看其他进程是否仍然被阻塞。

Community M-A 7 年前

也许让MVCC永久化可以解决这个问题(与仅特定批处理相反:设置事务隔离级别快照):

ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

[编辑:10月14日]

读完后: Better concurrency in Oracle than SQL Server? 而这: http://msdn.microsoft.com/en-us/library/ms175095.aspx

当读取提交快照时数据库选项设置为on, 用于支持选项的机制立即激活。什么时候? 设置读取提交快照选项,仅执行连接允许使用alter database命令在数据库中。一定没有数据库中其他打开的连接直到alter数据库完成。这个数据库不必位于单用户模式。

我得出的结论是,您需要设置两个标志,以便在给定的数据库上永久激活MSSQL的MVCC:

ALTER DATABASE yourDbNameHere SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

TFD 15 年前

好的,默认情况下,单个select将使用“read committed”事务隔离,该隔离锁定并因此停止对该集的写入。您可以使用更改事务隔离级别

Set Transaction Isolation Level { Read Uncommitted | Read Committed | Repeatable Read | Serializable }
Begin Tran
  Select ...
Commit Tran

这些在SQL Server BOL中进行了详细说明。

您的下一个问题是,默认情况下,如果您在锁事务中拥有超过2500个锁或使用超过40%的“正常”内存,SQL Server 2K5将升级这些锁。升级到第页,然后是表锁

您可以通过设置“跟踪标志”1211T关闭此升级,有关详细信息,请参阅BOL。

ewoo 14 年前

我假设您不希望任何其他会话能够在运行此特定查询时读取行…

在与(xlock,readpass)锁定提示一起使用时,在事务中包装您的select将得到您想要的结果。只需确保其他并发读取没有与(nolock)一起使用。readPast允许其他会话在其他行上执行相同的选择。

BEGIN TRAN
  SELECT *
  FROM <tablename> WITH (XLOCK,READPAST) 
  WHERE RowId = @SomeId

  -- Do SOMETHING

  UPDATE <tablename>
  SET <column>=@somevalue
  WHERE RowId=@SomeId
COMMIT

Constantin 13 年前

应用程序锁是使用自定义粒度滚动您自己的锁的一种方法,同时避免“有用的”锁升级。见 sp_getapplock .

Kamran Khan 13 年前

创建一个假更新来强制行锁。

UPDATE <tablename> (ROWLOCK) SET <somecolumn> = <somecolumn> WHERE id=1

如果你不把车锁起来,天知道会怎样。

此后 UPDATE “你可以做你的 SELECT (ROWLOCK) 以及随后的更新。

RMorrisey 15 年前

尝试使用:

SELECT * FROM <tablename> WITH ROWLOCK XLOCK HOLDLOCK

这应该使锁成为独占的,并在事务期间保持它。

erikkallen 15 年前

根据 this article ,解决方案是使用WITH(repeatableread)提示。

Michael Buen 15 年前

重新访问您的所有查询,可能您有一些查询选择不带rowlock/用于更新提示,这些查询来自您选择要更新的表。

MSSQL经常将这些行锁升级为页级锁(即使是表级锁,如果查询的字段上没有索引),请参见 explanation . 既然您要求更新,我可以假设您需要跨领域的健壮性(如财务、库存等)。所以那个网站上的建议不适用于你的问题。这只是一个洞察为什么MSSQL 升级锁 .

如果您已经在使用MSSQL2005(及更高版本),那么它们是基于MVCC的,我认为您应该不会对使用row lock/updlock提示的行级锁有任何问题。但是,如果您已经在使用MSSQL2005及更高版本,请尝试检查查询要更新的同一表的某些查询,如果它们升级锁,请检查它们的WHERE子句上的字段(如果它们有索引)。

附笔。
我使用的是PostgreSQL,它也使用MVCC进行更新,我没有遇到同样的问题。锁升级是MVCC解决的问题,所以如果MSSQL2005仍然使用在其字段上没有索引的WHERE子句升级表上的锁,我会感到惊讶。如果这(锁升级)仍然是MSSQL2005的情况,请尝试检查Where子句上的字段是否有索引。

免责声明:我上次使用的MSSQL是2000版。

user205622 15 年前

您必须在提交时处理异常并重复事务。

onupdatecascade 14 年前

问题-这种情况是否被证明是锁升级的结果(即,如果您使用探查器跟踪锁升级事件,那么这是否确实是导致阻塞的原因)?如果是这样的话,通过在实例级别启用跟踪标志来防止锁升级,有一个完整的解释和(相当极端的)解决方案。见 http://support.microsoft.com/kb/323630 跟踪标志1211

但是,这可能会产生意想不到的副作用。

如果您有意锁定一行并将其长期锁定,那么对事务使用内部锁定机制并不是最佳方法(至少在SQL Server中)。SQL Server中的所有优化都是针对短期事务的—进入、更新、退出。这就是锁升级的原因。

因此,如果目的是长时间“签出”一行,而不是事务性锁定,那么最好使用带有值的列和一个普通的ol'update语句来标记这些行是否已锁定。

jessn 13 年前

我以完全不同的方式解决了rowlock问题。我意识到SQL Server无法以令人满意的方式管理这样的锁。我选择从编程的角度通过使用互斥体来解决这个问题…等待…释放…

Gratzy 15 年前

你试过读过去吗?

当把一张表当作一个队列时,我使用了updlock和readpass。

Vladimir 13 年前

先对这一行进行简单的更新(而不真正更改任何数据)怎么样?之后,您可以继续执行“已选择要更新的”行。

UPDATE dbo.Customer SET FieldForLock = FieldForLock WHERE CustomerID = @CustomerID
/* do whatever you want */

编辑 :您当然应该在事务中包装它。

编辑2 :另一个解决方案是使用可序列化的隔离级别