Selecting unique record - SQL Server Forums

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)

Selecting unique record

Author

Topic

Heinduplessis
Starting Member

23 Posts

Posted - 2009-06-24 : 08:13:49

Hi All

I have a table with a list of job numbers:

 
JobID JobCode Issued IssuedTo
1     AAA     1      Mike
2     AAB     0      null
3     AAC     0      null
4     AAD     0      null

etc

I have a web application having to get the next available job number (where issued = 0).

This is the simple way to allocate the next job:


set @JobID=select top 1 JobID from Job where Issued=0

update Job set Issued=1, IssuedTo='John' where JobID=@JobID

However, on a threaded web application, one job is allocated to more than one person because there is nothing to prevent another select from the Job table before the update executes.

I've changed the code to this:


begin transaction
update Job set Issued=1, IssuedTo='John' where JobID in (
  select top 1 JobID from Job where Issued=0)

select max(JobID) from Job where IssuedTo='John'
commit

However now I get repeated deadlocks happening here, which is a frustration to handle and debug.

What would you recommend is the best way to issue JobIDs from the Job table?

Heinduplessis
Starting Member

23 Posts

Posted - 2009-06-24 : 10:28:20

I should add that another process is adding jobs in the mean time.

It's basically just a first come first serve problem. I'd hate to have to code a special semaphore.

Heinduplessis
Starting Member

23 Posts

Posted - 2009-06-29 : 09:42:10

Found answer on SQLServercentral.com:

http://www.sqlservercentral.com/Forums/Topic742300-338-1.aspx

Hope it helps somebody!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-29 : 09:48:23

For those not having an account over at SSC

UPDATE  f
SET     Issued = 1
OUTPUT  inserted.JobId
FROM    (
            SELECT TOP 1 JobID,
                         Issued
            FROM         @t
            WHERE        Issued = 0
            ORDER BY     JobID
        ) AS f

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources