是否更新与嵌套选择原子操作？ [英] Is update with nested select atomic operation?

问题描述

我需要先在数据库中选择10000行（返回）。可能有更多的客户端一次执行此操作。我想出了这个查询：

  update v set v.batch_Id =：batchId 
 from tblRedir v 
 inner join（
 select top 10000 id 
 from tblRedir 
其中batch_Id为null 
按日期升序排列
）v2 on v.id = v2.id 
  

这是一个由update和嵌套select组成的操作。这两个查询都在同一个表（tblRedir）上工作。这个想法是首先用唯一的batchId标记行，然后通过

标记

  select * from tblRedir其中batch_id =：batchId 
  

（batchid是每个此更新的唯一标识符（例如timestamp或guid） >

我的问题：

我认为操作 / em>是atomic - 这意味着每个客户端都接收到自己的唯一数据集（没有其他客户端接收到他的数据子集）。

我错了 - 在某些情况下，有客户端没有收到数据，因为他们可能先两个执行选择，然后然后都执行更新（所以第一个客户端没有标记的行）。

此操作是否是原子操作？

---

$ b b

我使用Sql server 2005.查询通过NHibernate运行，像这样

  session.CreateSQLQuery ...'）
  

解决方案

SELECT 将共享锁置于读取的行上，然后可以在 READ COMMITED 隔离模式中取消。

UPDATE 将更新锁稍后提升为排它锁。

$ b

你可以通过使事务隔离级别 REPEATABLE READ 来实现，它将保留共享锁直到事务结束，并且将阻止

或者，您可以将此查询重写为：

  WITH q AS 
（
 SELECT TOP 10000 * 
 FROM mytable WITH（ROWLOCK，READPAST）
 WHERE batch_id IS NULL 
 ORDER BY 
 date 
）
 UPDATE q 
 SET batch_id = @myid 
  

，这将跳过锁定的行。

I need to select first (let's say) 10000 rows in database and return them. There may be more clients that do this operation at one time. I came up with this query:

update v set v.batch_Id = :batchId 
    from tblRedir v 
    inner join (
        select top 10000 id 
            from tblRedir
            where batch_Id is null 
            order by Date asc
    ) v2 on v.id=v2.id

It is a operation that consists from update and nested select. Both the queries work on the same table (tblRedir). The idea is that the rows are first marked by a unique batchId and then returned via

select * from tblRedir where batch_id = :batchId

(the batchid is a unique identifier (e.g. timestamp or guid) for each this update)

My question:

I thought that the operation update with nested select is atomic - that means that every client receives his own set of data that is unique (no other client received a subset of his data).

However it looks that I'm wrong - in some cases there are clients that receive no data, because probably they first both execute the select and then both execute the update (so the first client has no marked rows).

Is this operation atomic or not?

---

I work with Sql server 2005. The query is run via NHibernate like this

session.CreateSQLQuery('update....')

解决方案

SELECT places shared locks on the rows read which then can be lifted in READ COMMITED isolation mode.

UPDATE places the update locks later promoted to exclusive locks. They are not lifted until the end of the transaction.

You should make the locks to retain as soon as they are placed.

You can do it by making the transaction isolation level REPEATABLE READ which will retain the shared locks until the end of the transaction and will prevent UPDATE part from locking these rows.

Alternatively, you can rewrite your query as this:

WITH    q AS
        (
        SELECT  TOP 10000 *
        FROM    mytable WITH (ROWLOCK, READPAST)
        WHERE   batch_id IS NULL
        ORDER BY
                date
        )
UPDATE  q
SET     batch_id = @myid

, which will just skip the locked rows.

这篇关于是否更新与嵌套选择原子操作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！