Locking

it is better to add a 'timestamp' column to the table. the timestamp column will get updated automatically whenever you make any changes in that row. and while updating you can put this row
update PinNumbers
set ....
where Used = 0 and timeStampColumn = <Value you got from the select statement > 

If you are using Stored Procedure

then issue the SELECT statement with FOR UPDATE clause like

Select Top 1 * From PinNumbers Where Used = 0 FOR UPDATE

because FOR UPDATE Locks a record within a transaction to prevent conflicts.

Ok thanks guys, I am still a little unsure perhaps you can help me more.
My table looks like this:
PinID  PinNumber  Used
1          069784         0
2          083163         0
3          098974         0
4          171207         0
5          133010         0
6          099590         0
I want a stored procedure which will return the next Pin which is unused and then set used to 1 with no way of any concurrent issues occurring.
I have this so far:

Alter Procedure NextPin
As
Declare @PinID Int
Select @PinID = Min (PinID) From PinNumbers Where Used = 0
Update PinNumbers Set Used = 1 Where PinID = @PinID
Select * From PinNumbers Where PinID = @PinID

Why not update first and then retrieve the value?  Or place it in a BEGIN /COMMT TRANSACTION block.

If it is well structured there should never be any need to lock anything.

If I update first how do I retrieve - can I use scope_identity() after an update?

No.  But you can use OUTPUT

Ok can you show me how, I'm having a real thicko day!
Thanks
 

Update      PinNumbers
Set      Used = 1
OUTPUT      Inserted.PinID
Where      PinID = (Select MIN(PinID) From PinNumbers Where Used = 0)

On second thoughts, you want the whole row added, so:

Update      PinNumbers
Set      Used = 1
OUTPUT      Inserted.*
Where      PinID = (Select MIN(PinID) From PinNumbers Where Used = 0)


If PinID is not unique there is a chance that you could get more then one row and you should change the query as follows:

Update      PinNumbers
Set      Used = 1
OUTPUT      Inserted.*
Where      PinID In (Select MIN(PinID) From PinNumbers Where Used = 0)

I don't get that
If I have updated Used to 1 then how will selecting where it is 0 work?
PinID is the Primary Key - autonumber +1 each time

Have you tried it?

acperkins is 100% correct here, OUTPUT is the way to go.

No I haven't tried it and of course I'm not saying you are wrong or that it doesn't work  - I am just looking for an explanation.
Update      PinNumbers
Set      Used = 1
<Can anything happen here to make the following criteria change  - eg other code calling this at the exact same time>

OUTPUT      Inserted.*
Where      PinID = (Select MIN(PinID) From PinNumbers Where Used = 0)
 

Thanks Tim, but I hold you responsible for enlightening me to the use of the OUTPUT clause.  :)

Ok I get it now having had a quick google - its what used to be used in triggers pre 2005

chapmandew: While you are around did you have any thoughts on my other SQL question about union or am I being thick on that one too?

daveamour,

>>Can anything happen here to make the following criteria change<<
I think you have some misconceptions on how the OUTPUT clause works.  It is not a seperate SQL command, but rather a clause of the same SQL query.  So no, nothing can happen.

Why don't you read up on this article that Tim wrote:
Know your DML: The new OUTPUT feature in SQL Server 2005
http://articles.techrepublic.com.com/5100-10878_11-6074046.html

>>its what used to be used in triggers pre 2005<<
Not exactly.  If you are referring to the Inserted/Deleted logical tables in tables.  They are still being used in Triggers and they only bare the same name, that is all and really should not be compared.

Ok thanks