是否使用嵌套选择原子操作进行更新?

我需要先选择(假设)数据库中的 10000 行并返回它们.一次可能会有更多的客户端执行此操作.我想出了这个查询:

update v set v.batch_Id = :batchId来自 tblRedir v内部联接 (选择前 10000 个 ID来自 tblRedir其中 batch_Id 为空按日期升序排序) v2 上 v.id=v2.id

这是一个由更新和嵌套选择组成的操作.两个查询都在同一个表 (tblRedir) 上工作.这个想法是首先用唯一的batchId标记行，然后通过

返回

select * from tblRedir where batch_id = :batchId

(batchid 是每次更新的唯一标识符(例如时间戳或 guid))

我的问题:

我认为操作使用嵌套选择更新是原子的——这意味着每个客户端都会收到他自己唯一的一组数据(没有其他客户端收到他的数据子集).>

但是看起来我错了 - 在某些情况下，有些客户端没有收到任何数据，因为可能他们首先两者执行选择然后然后两者执行更新(所以第一个客户端没有标记的行).

这个操作是否是原子操作?

<小时>

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

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

解决方案

SELECT 在读取的行上放置共享锁，然后可以在 READ COMMITED 隔离模式下解除这些锁.

UPDATE 将更新锁稍后提升为排他锁.直到交易结束，它们才会被解除.

您应该在放置锁后立即保留它们.

您可以通过将事务隔离级别设为 REPEATABLE READ 来实现，这将保留共享锁直到事务结束，并防止 UPDATE 部分锁定这些行.

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

WITH q AS(选择前 10000 个 *从 mytable WITH (ROWLOCK, READPAST)WHERE batch_id 为空订购者日期)更新 qSET 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.