天道酬勤,学无止境

更新 + WITH (ROWLOCK) + CTE(UPDATE + WITH (ROWLOCK) + CTE)

问题

我找不到任何关于 T-SQL 语句语法的文档:我需要对 CTE 结果进行WITH (ROWLOCK) UPDATE。

类似于:(因此更新将是 top1000 table1.col2。在 table1 的行上更新期间的语句WITH (ROWLOCK)至关重要)

    ;WITH CTE AS 
    ( 
        SELECT TOP(1000) table1.col2
        FROM  table1 INNER JOIN table2 ON table1.id = table2.id    
    ) 
    UPDATE CTE WITH (ROWLOCK)
    SET col2 = 1

上面的语句可能在语法上是正确的,但是如果有人会找到这样的例子,请给我一个链接。

但是:我的完整 SQL 如下所示。 在执行期间我收到错误:

为表“table1”指定了冲突的锁定提示。 这可能是由为视图指定的冲突提示引起的。

为什么我不能使用WITH (NOLOCK)进行选择并WITH (ROWLOCK)进行更新?

;WITH CTE AS 
( 
    SELECT TOP(5) table1.col2
    FROM table1 WITH (NOLOCK) INNER JOIN table2 WITH (NOLOCK) ON table1.id = table2.id 
    WHERE table1.col3 = 2
    ORDER BY table1.id    
) 
UPDATE CTE WITH (ROWLOCK)
SET col2 = 1
回答1

NOLOCK不适用于查询中引用要修改的表的部分。 在 SQL Server 更新语句中,在测试时对每一行进行简单的 U 锁定。 这是一种避免死锁的机制。 它可以防止多次更新每个 S 锁定一行以进行读取,然后尝试对其进行 X 锁定。

你不能让 U 型锁消失 AFAIK。 但是您可以通过自连接将 U 锁定的行数减少到绝对最小值:

update t1
set ...
from T t1 with (rowlock)
where t1.ID in (select TOP 5 ID from T t2 with (nolock) where ... order by ...)

这会增加一点开销,但它允许您使用NOLOCK进行读取。

考虑对读取使用快照隔离。 NOLOCK存在某些问题,例如查询随机中止。

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • UPDATE + WITH (ROWLOCK) + CTE
    I can't find any documentation about syntax for T-SQL statement: I need to make an WITH (ROWLOCK) UPDATE on a CTE result. Something like: (so updated will be top1000 table1.col2. Statement WITH (ROWLOCK) during an UPDATE on rows of table1 is crucial) ;WITH CTE AS ( SELECT TOP(1000) table1.col2 FROM table1 INNER JOIN table2 ON table1.id = table2.id ) UPDATE CTE WITH (ROWLOCK) SET col2 = 1 The above statement is probably syntactically correct, however if someone will find such example, please give me a link. BUT: my full SQL looks like below. During execute I get error: Conflicting locking hints
  • SQL Server 2005中的原子UPSERT(Atomic UPSERT in SQL Server 2005)
    问题 在SQL Server 2005中执行原子“ UPSERT”(如果存在,则进行UPDATE,否则进行INSERT)的正确模式是什么? 我在SO上看到很多代码(例如,请参阅检查是否存在一行,否则请插入),其代码由以下两部分组成: UPDATE ... FROM ... WHERE <condition> -- race condition risk here IF @@ROWCOUNT = 0 INSERT ... 或者 IF (SELECT COUNT(*) FROM ... WHERE <condition>) = 0 -- race condition risk here INSERT ... ELSE UPDATE ... 其中<condition>将是自然键的评估。 上述方法似乎都不能很好地处理并发。 如果我不能让两行具有相同的自然键,则上述所有情况似乎都存在在竞争条件场景中插入具有相同自然键的行的风险。 我一直在使用以下方法,但是我很惊讶没有在人们的回应中看到它,因此我很奇怪它出了什么问题: INSERT INTO <table> SELECT <natural keys>, <other stuff...> FROM <table> WHERE NOT EXISTS -- race condition risk here? ( SELECT 1 FROM
  • 将SQL Server用作具有多个客户端的数据库队列(Using SQL Server as a DB queue with multiple clients)
    问题 给定一个充当队列的表,我如何最好地配置该表/查询,以便多个客户端同时从该队列进行处理? 例如,下表指示工作人员必须处理的命令。 工作程序完成后,它将把处理后的值设置为true。 | ID | COMMAND | PROCESSED | | 1 | ... | true | | 2 | ... | false | | 3 | ... | false | 客户端可能会像这样获得一条要执行的命令: select top 1 COMMAND from EXAMPLE_TABLE with (UPDLOCK, ROWLOCK) where PROCESSED=false; 但是,如果有多个工作程序,则每个工作程序都会尝试获取ID = 2的行。 只有第一个获得悲观锁,其余的将等待。 然后其中一个将进入第3行,依此类推。 什么样的查询/配置将允许每个辅助客户端分别获得不同的行并同时进行处理? 编辑: 有几个答案建议使用表本身记录进程中状态的各种方式。 我认为单笔交易不可能做到这一点。 (即,如果在提交txn之前没有其他工作人员看到状态,更新状态有什么意义?)也许建议是: # start transaction update to 'processing' # end transaction # start transaction process the command update to
  • SQL Server:使用 ORDER BY 更新表(SQL Server: UPDATE a table by using ORDER BY)
    问题 我想知道在更新表时是否可以使用 order by 子句。 我正在更新表格并设置一个连续的数字,这就是更新顺序很重要的原因。 使用以下 sql 语句,我能够在不使用游标的情况下解决它: DECLARE @Number INT = 0 UPDATE Test SET @Number = Number = @Number +1 现在我想做的是一个 order by 子句,如下所示: DECLARE @Number INT = 0 UPDATE Test SET @Number = Number = @Number +1 ORDER BY Test.Id DESC 我读过:How to update and order by using ms sql 这个问题的解决方案不能解决排序问题——它们只是过滤应用更新的项目。 保重,马丁 回答1 不。 没有记录在案的 100% 支持的方式。 有时存在一种用于计算运行总计的方法,称为“古怪更新”,它表示如果满足某些条件,它可能会按照聚簇索引的顺序进行更新,但据我所知,这完全取决于经验观察,而不是任何保证。 但是您使用的是哪个版本的 SQL Server? 如果 SQL2005+ 你也许可以用row_number和 CTE 做一些事情(你可以更新 CTE) With cte As ( SELECT id,Number, ROW_NUMBER()
  • T-SQL 是更新限制原子的子查询吗?(T-SQL Is a sub query for an Update restriction Atomic with the update?)
    问题 我在 MS Sql Server 2008 R2 中有一个简单的队列实现。 这是队列的本质: CREATE TABLE ToBeProcessed ( Id BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL, [Priority] INT DEFAULT(100) NOT NULL, IsBeingProcessed BIT default (0) NOT NULL, SomeData nvarchar(MAX) NOT null ) 我想自动选择按优先级排序的前 n 行和 IsBeingProcessed 为 false 的 id 并更新这些行以表示它们正在处理。 我以为我会使用 Update、Top、Output 和 Order By 的组合,但不幸的是,您不能在 Update 语句中使用 top 和 order by。 因此,我创建了一个 in 子句来限制更新,并且该子查询按顺序执行(见下文)。 我的问题是,这整个语句是原子的,还是需要将其包装在事务中? DECLARE @numberToProcess INT = 2 CREATE TABLE #IdsToProcess ( Id BIGINT NOT null ) UPDATE ToBeProcessed SET ToBeProcessed.IsBeingProcessed = 1
  • SQL Threadsafe UPDATE TOP 1 for FIFO Queue
    I have a table of invoices being prepared, and then ready for printing. [STATUS] column is Draft, Print, Printing, Printed I need to get the ID of the first (FIFO) record to be printed, and change the record status. The operation must be threadsafe so that another process does not select the same InvoiceID Can I do this (looks atomic to me, but maybe not ...): 1: WITH CTE AS ( SELECT TOP(1) [InvoiceID], [Status] FROM INVOICES WHERE [Status] = 'Print' ORDER BY [PrintRequestedDate], [InvoiceID] ) UPDATE CTE SET [Status] = 'Printing' , @InvoiceID = [InvoiceID] ... perform operations using
  • 使用SQL Server选择更新(SELECT FOR UPDATE with SQL Server)
    问题 我正在使用隔离级别READ_COMMITTED和READ_COMMITTED_SNAPSHOT=ON的Microsoft SQL Server 2005数据库。 现在我要使用: SELECT * FROM <tablename> FOR UPDATE ...以便其他数据库连接在尝试访问同一行“ FOR UPDATE”时阻塞。 我试过: SELECT * FROM <tablename> WITH (updlock) WHERE id=1 ...但是这会阻止所有其他连接,即使选择“ 1”以外的ID也是如此。 如Oracle,DB2,MySql所知,哪个是执行SELECT FOR UPDATE的正确提示? 编辑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 (
  • 如何在不使用500,000+行表中的截断表的同时有效地删除行(How to efficiently delete rows while NOT using Truncate Table in a 500,000+ rows table)
    问题 假设我们有30列和500,000行的表Sales 。 我想删除表中的400,000(那些在其中"toDelete='1'" )。 但是我有一些限制: 该表是“经常”读/写的,我不想花很长的时间“删除”很长时间并将表锁定太长时间我需要跳过事务日志(例如使用TRUNCATE ),但是在执行"DELETE ... WHERE..." (我需要提出一个条件)的同时,却没有找到任何方法可以执行此操作... 任何建议将欢迎您转变为 DELETE FROM Sales WHERE toDelete='1' 到更分区的东西,可能还没有事务日志。 回答1 调用DELETE FROM TableName将在一个大事务中完成整个删除。 这很贵。 这是另一种将批量删除行的选项: deleteMore: DELETE TOP(10000) Sales WHERE toDelete='1' IF @@ROWCOUNT != 0 goto deleteMore 回答2 我将在这里留下答案,因为我能够测试用于批量删除和更新的不同方法(我必须先更新然后删除125 + mio行,服务器具有16GB RAM,Xeon E5-2680 @ 2.7GHz,SQL Server 2012)。 TL; DR :始终通过主键进行更新/删除,而不会通过任何其他条件进行更新/删除。 如果您不能直接使用PK,请创建一个临时表
  • ORDER BY and WITH(ROWLOCK, UPDLOCK, READPAST)
    I need to set up a queue system using some SQL tables, like the one described here. That being, and since I need to filter queued items by different critera, inside a stored procedure I am using BEGIN TRANSACTION CREATE TABLE #Temp (ID INT, SOMEFIELD INT) INSERT INTO #Temp SELECT TOP (@Something) TableA.ID, TableB.SomeField FROM TableA WITH (ROWLOCK, READPAST) INNER JOIN TableB WITH (UPDLOCK, READPAST) WHERE Condition1 INSERT INTO #Temp SELECT TOP (@Something) TableA.ID, TableB.SomeField FROM TableA WITH (ROWLOCK, READPAST) INNER JOIN TableB WITH (UPDLOCK, READPAST) WHERE Condition2 (...)
  • SQL Server进程队列竞争条件(SQL Server Process Queue Race Condition)
    问题 我有一个订单队列,可通过存储过程由多个订单处理器访问。 每个处理器传递一个唯一的ID,该ID用于锁定接下来的20个订单以供自己使用。 然后,存储过程将这些记录返回给要处理的订单处理器。 在某些情况下,多个处理器能够检索相同的“ OrderTable”记录,此时它们将尝试同时对其进行操作。 这最终会导致在该过程的后期引发错误。 我的下一个操作步骤是允许每个处理器抓取所有可用的订单,然后仅循环处理这些处理器,但是我希望简单地使这段代码线程安全,并允许处理器在需要时抓取记录。 如此明确-任何想法我为什么会遇到这种比赛情况以及如何解决问题。 BEGIN TRAN UPDATE OrderTable WITH ( ROWLOCK ) SET ProcessorID = @PROCID WHERE OrderID IN ( SELECT TOP ( 20 ) OrderID FROM OrderTable WITH ( ROWLOCK ) WHERE ProcessorID = 0) COMMIT TRAN SELECT OrderID, ProcessorID, etc... FROM OrderTable WHERE ProcessorID = @PROCID 回答1 编辑: 我用谷歌搜索了我的答案:“使用READPAST和UPDLOCK在SQL Server中处理数据队列”。
  • SQL Server ROWLOCK over a SELECT if not exists INSERT transaction
    I have upgraded from SQL Server 2005 to 2008. I remember that in 2005, ROWLOCK simply did not work and I had to use PAGELOCK or XLOCK to achieve any type of actual locking. I know a reader of this will ask "what did you do wrong?" Nothing. I conclusively proved that I could edit a "ROWLOCKED" row, but couldn't if I escalated the lock level. I haven't had a chance to see if this works in SQL 2008. My first question is has anyone come across this issue in 2008? My second question is as follows. I want to test if a value exists and if so, perform an update on relevant columns, rather than an
  • 如何获得序列中的下一个数字(How to get the next number in a sequence)
    问题 我有一张这样的桌子: +----+-----------+------+-------+--+ | id | Part | Seq | Model | | +----+-----------+------+-------+--+ | 1 | Head | 0 | 3 | | | 2 | Neck | 1 | 3 | | | 3 | Shoulders | 2 | 29 | | | 4 | Shoulders | 2 | 3 | | | 5 | Stomach | 5 | 3 | | +----+-----------+------+-------+--+ 如何在Model 3的Stomach之后插入带有下一个seq的另一条记录。因此这是新表的外观: +----+-----------+------+-------+--+ | id | Part | Seq | Model | | +----+-----------+------+-------+--+ | 1 | Head | 0 | 3 | | | 2 | Neck | 1 | 3 | | | 3 | Shoulders | 2 | 29 | | | 4 | Shoulders | 2 | 3 | | | 5 | Stomach | 5 | 3 | | | 6 | Groin | 6 | 3 | | +----+-----
  • 是否可以在SQL Server中强制行级别锁定?(Is it possible to force row level locking in SQL Server?)
    问题 我可以看到如何在SQL Server中关闭行级和页面级锁定,但是我找不到强制SQL Server使用行级锁定的方法。 有没有一种方法可以强制SQL Server使用行级别锁定而不使用页面级别锁定? 回答1 您可以使用ROWLOCK提示,但是AFAIK SQL可能决定升级它(如果它在资源上不足) 从doco: ROWLOCK指定通常采用页锁或表锁时采用行锁。 在以SNAPSHOT隔离级别运行的事务中指定时,除非ROWLOCK与其他需要锁定的表提示(例如UPDLOCK和HOLDLOCK)结合使用,否则不会获取行锁定。 和 获取行级锁的锁提示ROWLOCK,UPDLOCK和XLOCK可能会将锁放置在索引键上,而不是实际的数据行上。 例如,如果表具有非聚集索引,并且使用锁定提示的SELECT语句由覆盖索引处理,则将在覆盖索引中的索引键上而不是基表的数据行上获取锁。 最后,这给出了有关SQL Server 2005中锁升级的相当深入的解释,而SQL Server 2008中已更改了锁升级。 也有非常深入的内容:锁定数据库引擎(在线书籍中) 所以,一般 UPDATE Employees WITH (ROWLOCK) SET Name='Mr Bean' WHERE Age>93 应该没问题,但是根据索引和服务器上的负载,它可能最终升级为页面锁。 回答2 使用ALTER / CREATE
  • 如何执行行锁定?(How to perform a row lock?)
    问题 我想锁定一条记录,然后没人可以更改该记录。 当我释放锁时,人们可能会更改记录。 在记录被锁定的同时,我想向用户显示一条警告,指出该记录已被锁定并且不允许更改。 我怎样才能做到这一点? 我已经尝试了所有的IsolationLevel级别,但是没有一个具有我想要的行为。 某些隔离级别会等到锁定释放后再进行更改。 我不希望这样,因为在锁定记录的那一刻不允许更新。 我该怎么做才能锁定记录并拒绝所有更改? 我使用SQL Server 2008 回答1 假设这是MS SQL Server,则可能需要UPDLOCK ,并可能与ROWLOCK结合使用(表提示)。 我很难找到描述该理论的不错的文章,但是这里有个简单的例子: SELECT id From mytable WITH (ROWLOCK, UPDLOCK) WHERE id = 1 该语句将在事务期间将更新锁置于行上(因此,重要的是要知道事务何时结束)。 由于更新锁与排他锁不兼容(需要更新记录),因此这将防止任何人在事务结束之前更新该记录。 请注意,其他尝试修改此记录的进程将被阻塞,直到事务完成为止,但是一旦事务结束,它们将继续执行其请求的任何写操作(除非它们被超时或作为死锁进程被杀死)。 如果希望防止这种情况,那么您的其他进程需要使用其他提示,以便在检测到不兼容的锁时中止操作,或者在更改了记录后跳过该记录。 另外,在等待用户输入时
  • SELECT FOR UPDATE with SQL Server
    I'm using a Microsoft SQL Server 2005 database with isolation level READ_COMMITTED and READ_COMMITTED_SNAPSHOT=ON. Now I want to use: SELECT * FROM <tablename> FOR UPDATE ...so that other database connections block when trying to access the same row "FOR UPDATE". I tried: SELECT * FROM <tablename> WITH (updlock) WHERE id=1 ...but this blocks all other connections even for selecting an id other than "1". Which is the correct hint to do a SELECT FOR UPDATE as known for Oracle, DB2, MySql? EDIT 2009-10-03: These are the statements to create the table and the index: CREATE TABLE example ( Id
  • 在INSERT语句中使用ROWLOCK(SQL Server)(Using ROWLOCK in an INSERT statement (SQL Server))
    问题 在复制大量数据并将其插入到同一表中的插入语句上使用ROWLOCK是否明智? 前任) INSERT INTO TABLE with (rowlock) (id, name) SELECT newid, name FROM TABLE with (nolock) WHERE id = 1 是否有人对如何改进此语句提出建议,如我所见,当SQL Server繁忙时,它将以为SQL Server返回的超时查询结束。 回答1 您最好将子查询结果存储在临时表中,然后再插入。 回答2 如果您要插入大量数据,而您遇到读/写器问题(锁定,超时),则可能应将插入内容分成几部分(前100位之类),直到完成所有数据为止。 如果不这样做,即使您声明了行锁,也可能会发生锁升级,在这种情况下,SQL Server将在您插入数据时获取表锁。 另一个不错的选择是使用SNAPSHOT隔离,如果您有足够的可用空间,这将是完美的选择。 对于第一个选项,请阅读此处Q http://support.microsoft.com/kb/323630 回答3 如果要插入大量数据,例如1000行或更多,则可以考虑使用临时表或表变量。 首先将行插入到临时表或变量中,然后执行插入到final_table中,从临时表中选择*效果很好。 如果您需要更多行,则只需将插入内容放在光标内,并每1000行进行一次迭代,直到完成总行数为止。
  • default_batch_fetch_size 推荐值(default_batch_fetch_size recommended values)
    问题 我正在阅读一些 hibernate 教程并被困在 default_batch_fetch_size 上。 阅读专家评论“Hibernate 可以用于性能敏感的应用程序吗?” 清楚地解释了重要性,但我试图理解为什么链接中使用的推荐值是 4、8、16 或 32。 问候塔伦 回答1 我们在生产中使用hibernate.default_batch_fetch_size = 100 。 因此,在某些情况下,我们有 3 个查询而不是 300 个,因此这对我们的数据库来说是一个很好的性能提升。 回答2 概括: 启用批量提取后,Hibernate 会准备大量查询:这些查询占用大量内存,不能被垃圾处理。 批量大小为 1000 将占用大约 150 Mo 内存。 因此,最好使用较低的一般批量大小(如 10、20 或 40),只需使用 @BatchSize 注释为特定集合设置更大的批量大小。 细节: 此处解释了获取批量大小 了解 Hibernate 中的 @BatchSize ,“hibernate.default_batch_fetch_size”是通用参数,而“@BatchSize”注释允许覆盖特定关联上的通用参数。 但是这些解释并没有真正回答“为什么官方文档推荐值 4、8 或 16”的问题? 显然,现代数据库可以在 IN 子句中处理超过 16 个值的查询,并且在 IN 子句中使用比方说
  • Using ROWLOCK in an INSERT statement (SQL Server)
    Would it be wise to use ROWLOCK on an insert statement that is copying large amounts of data and inserting it into the same table? Ex) INSERT INTO TABLE with (rowlock) (id, name) SELECT newid, name FROM TABLE with (nolock) WHERE id = 1 Does anybody have recommendations on how to improve this statement, as I see when SQL Server gets busy it will end in Timeout Query returned for SQL Server.
  • SQL merge statements on tables with foreign keys and resolving deadlocks
    I have a couple of MERGE statements that I execute inside a transaction from within ADO.NET code. The first table's Id will be assigned automatic when inserting into the table. The second table does have a Foreign-key constraint, that's why I have this select in my insert statement. The matching is actually based on some natural key because the surrogate keys are not exposed outside the application. The MERGE statements look like these. merge MyTable with (rowlock, updlock) as t using #someTempTable as s on (t.[VarcharColumn] = s.[VarcharColumn]) when not matched by target then insert (...)
  • 带有外键和解决死锁的表上的 SQL 合并语句(SQL merge statements on tables with foreign keys and resolving deadlocks)
    问题 我在 ADO.NET 代码中的事务中执行了几个 MERGE 语句。 第一个表的 ID 将在插入表时自动分配。 第二个表确实有一个外键约束,这就是我在插入语句中有这个选择的原因。 匹配实际上是基于一些自然键,因为代理键没有暴露在应用程序之外。 MERGE 语句如下所示。 merge MyTable with (rowlock, updlock) as t using #someTempTable as s on (t.[VarcharColumn] = s.[VarcharColumn]) when not matched by target then insert (...) values (...) when matched then update set ... ; merge SecondTable with (rowlock, updlock) as t using #otherTempTable as s on (t.[] = s.[]) when not matched by target then insert ([OtherColumn],[MyTable_Id]) values (s.[OtherColumn], (select Id from MyTable where MyTable.[VarcharColumn] = s.[VarcharColumn]