How not to select same row when selecting random

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How not to select same row when selecting random

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2010-12-30 : 04:01:50

I have a table with tens of thousands of records which has pre-generated codes. I give one randomly selected code to a user each time. I select one code and immediately update it as "used". My problem is every once in a while two users get the same code. Is there a guaranteed way to not give two users the same code?

This is how I select and update the selected code:


SELECT TOP 1 @code = code FROM MyCodes WITH (NOLOCK) WHERE some conditions
 AND 0.01 >= CAST(CHECKSUM(NEWID(), code) & 0x7fffffff AS FLOAT) / CAST (0x7fffffff AS INT);

UPDATE MyCodes SET used = 1 WHERE code = @code;

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-30 : 05:08:30

My first idea:
take away that "WITH (NOLOCK)"

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-12-30 : 05:39:14

Just add a where clause


SELECT TOP 1 @code = code FROM MyCodes WITH (NOLOCK) WHERE some conditions
 AND 0.01 >= CAST(CHECKSUM(NEWID(), code) & 0x7fffffff AS FLOAT) / CAST (0x7fffffff AS INT);
where used!=1

PBUH

kensai
Posting Yak Master

172 Posts

Posted - 2010-12-30 : 05:52:30

quote:

Originally posted by Sachin.Nand

Just add a where clause


SELECT TOP 1 @code = code FROM MyCodes WITH (NOLOCK) WHERE some conditions
 AND 0.01 >= CAST(CHECKSUM(NEWID(), code) & 0x7fffffff AS FLOAT) / CAST (0x7fffffff AS INT);
where used!=1

PBUH

Forgot to add, I already check for used != 1.

Kristen
Test

22859 Posts

Posted - 2010-12-30 : 06:29:05

1. Do NOT use NOLOCK. If you have READs blocking WRITEs then use READ_COMITTED_SNAPSHOT instead (set that on the database and take ALL the NOLOCK statements out, they will be breaking all your code)

2. Use the UPDATE statement to also get the PK of the record so that the operation is atomic:


UPDATE TOP 1 MyCodes
SET used = 1,
    @code = code 
WHERE ... some conditions ...
      AND used <> 1
      AND 0.01 >= CAST(CHECKSUM(NEWID(), code) & 0x7fffffff AS FLOAT) / CAST (0x7fffffff AS INT)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-12-30 : 12:07:27

Wouldn't you need a transaction around the SELECT and UPDATE statements? Otherwise, a second user could win the race condition and select the same record.

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-30 : 13:10:50

quote:
Originally posted by Kristen

1. Do NOT use NOLOCK. If you have READs blocking WRITEs then use READ_COMITTED_SNAPSHOT instead (set that on the database and take ALL the NOLOCK statements out, they will be breaking all your code)
[/code]

Better not use use Read Committed or Repeatable Read either then.

quote:
Originally posted by Bustaz Kool

Wouldn't you need a transaction around the SELECT and UPDATE statements? Otherwise, a second user could win the race condition and select the same record.

If you do it in one statement like Kristen showed, then you should be safe. Also, a transaction won't necessarily help. But, locking the table or setting an application lock should.

Kristen
Test

22859 Posts

Posted - 2010-12-30 : 13:38:33

"locking the table or setting an application lock should"

Indeed, but might cause the "resource" to be blocked / "sluggish" to respond I think?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-30 : 13:49:24

quote:
Originally posted by Kristen

"locking the table or setting an application lock should"

Indeed, but might cause the "resource" to be blocked / "sluggish" to respond I think?

Agreed, I didn't state that very well...

If using the "one-shot" approach that you showed things should be fine. However, if it were to be done in two steps, then something other than a transaction would need to be employed to prevent race conditions.

Kristen
Test

22859 Posts

Posted - 2010-12-30 : 13:54:00

"something other than a transaction would need to be employed to prevent race conditions."

Gotcha :) using a ROWVERSION column for optimistic locking, for example.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-30 : 14:12:58

quote:
Originally posted by Kristen

"something other than a transaction would need to be employed to prevent race conditions."

Gotcha :) using a ROWVERSION column for optimistic locking, for example.

Actually, if using a table to get the random row in one step and then updating that same table in another step you'd need to do something to prevent another process from reading that same table/row/value before you updated it. In this example, using a transaction, you could swap out the NOLOCK with a TABLOCKX or READPAST and UPDLOCK hint. You should also be able to use an app lock (sp_getapplock) to enforce a critical section. But, that's probably more overhead.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources