SQL Server 2008 - Select disjunct rows

Question

I have two concurrent processes and I have two queries, eg.:

select top 10 * into #tmp_member
from member
where status = 0
order by member_id

and then

update member
set process_status = 1
from member inner join #tmp_member m
on member.member_id=m.member_id

I'd like each process to select different rows, so if a row was already selected by the first process, then do not use that one in the second process' result list.

Do I have to play around with locks? UPDLOCK, ROWLOCK, READPAST hints maybe? Or is there a more straightforward solution?

Any help is appreciated,

cheers,

b

Currently your second query will only update the rows inserted in the temp table by your first query. You want to change the second query so it does not use the first query results? — garnertb, Jun 03 '11 at 10:58
No, he wants two statements: SELECT the entries and then UPDATE the selected entries. However, there are two processes doing these two calls. Now the problem is that process 2 might select the same 10 entries as process 1 as the select and update are not an atomic operation. He does not want process 2 to update the same entries again. — Thorsten Dittmar, Jun 03 '11 at 11:04

score 3 · Accepted Answer · edited May 23 '17 at 10:34

3

You need hints.

See my answer here: SQL Server Process Queue Race Condition

However, you can shorten your query above into a single statement with the OUTPUT clause. Otherwise you'll need a transaction too (asuming each process executes the 2 statements above one after the other)

update m
set process_status = 1
OUTPUT Inserted.member_id
from
  (
  SELECT top 10
      process_status, member_id
    from member WITH (ROWLOCK, READPAST, UPDLOCK)
    where status = 0
    order by member_id
  ) m

Summary: if you want multiple processes to

select 10 rows where status = 0
set process_status = 1
return a resultset in a safe, concurrent fashion

...then use this code.

edited May 23 '17 at 10:34

Community

1
1

answered Jun 03 '11 at 10:59

gbn

422,506
82
585
676

I came to a similar solution but I did not use ROWLOCK. It seems to work without it. Why is it necessary to use both ROWLOCK and UPDLOCK? – Balázs Németh Jun 03 '11 at 11:08
ROWLOCK = granualarity aka only lock rows (not pages etc). UPDLOCK = isolation and is required to ensure that the same row isn't selected by 2 different processes. ROWLOCK will still allow 2 different processes to select the same row. READPAST skips rows locked by another process. You *need* all three hints... – gbn Jun 03 '11 at 11:10
I understand READPAST and UPDLOCK I think. Does ROWLOCK ease the lock level of UPDLOCK then? So that it won't lock pages but only rows? That would mean it's stronger locked without ROWLOCK, but still works. Am I still missing something? :) – Balázs Németh Jun 03 '11 at 11:17
Yes you're missing soemthing... UPDLOCK affects concurrency, ROWLOCK affects granularity. Neither affects the other. ROWLOCK stops "lock escaltion* to the whole table, no matter if read, exclusive etc. UPDLOCK determines isolation on whatever is locked (row, page, table etc) – gbn Jun 03 '11 at 11:20

score 0 · Answer 2 · answered Jun 03 '11 at 11:01

Well the problem is that your select/update is not atomic - the second process might select the first 10 items in between the first process having selected and before updating.

There's the OUTPUT clause you can use on the UPDATE statement to make it atomic. See the documentation for details, but basically you can write something like:

DECLARE @MyTableVar table(member_ID INT)
UPDATE TOP (10) Members
SET 
 member_id = member_id,
 process_status = 1
WHERE status = 0
OUTPUT inserted.member_ID
INTO @MyTableVar;

After that @MyTableVar should contain all the updated member IDs.

score 0 · Answer 3 · answered Jun 03 '11 at 11:02

To meet your goal of having multiple processes work on the member table you will not need to "play around with locks". You will need to change from the #tmp_member table to a global temp table or a permanate table. The table will also need a column to track which process is managing the member row/

You will need a method to provide some kind of ID to each process which will be using the table. The first query will then be modified to exclude any entries in the table by other processes. The second query will be modified to include only those entries by this process

Does not achieve the first solution the same thing as you suggest with the help of hints? — Balázs Németh, Jun 03 '11 at 11:12

SQL Server 2008 - Select disjunct rows

3 Answers3