更新嵌套选择原子操作？(Is update with nested select atomic operation?)

更新嵌套选择原子操作？(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 where batch_Id is null order by Date asc ) v2 on v.id=v2.id

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

select * from tblRedir where batch_id = :batchId

（batchid是每个此更新的唯一标识符（例如时间戳或guid））

我的问题：

我认为使用嵌套select的操作更新是原子的 - 这意味着每个客户端都会收到自己的唯一数据集（没有其他客户端收到他的数据子集）。

然而，看起来我错了 - 在某些情况下，有客户端没有收到数据，因为他们可能首先执行select， 然后都执行更新（因此第一个客户端没有标记的行）。

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

---

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

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

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将共享锁放在读取的行上，然后可以在READ COMMITED隔离模式下将其READ COMMITED 。

UPDATE将更新锁稍后提升为独占锁。 在交易结束之前它们不会被解除。

放置锁后，应尽快锁住它们。

您可以通过设置事务隔离级别REPEATABLE READ ，它将保留共享锁直到事务结束，并阻止UPDATE部分锁定这些行。

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

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

，它只会跳过锁定的行。

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.