?
Solved

Locking

Posted on 2009-05-02
21
Medium Priority
?
232 Views
Last Modified: 2013-11-05
I am using SQL Server 2005 and within a stored procedure I wish to select a single  record from a table with some crietria and then update a value within that table.

My select is like this:

Select Top 1 * From PinNumbers Where Used = 0

I then want to update Used to 1 but am worried that between these two statements something else may happen to change the criteria.  Should I use some kind of locking to overcome this?
0
Comment
Question by:daveamour
21 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24286465
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 > 
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24286468
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.
0
 
LVL 19

Author Comment

by:daveamour
ID: 24286495
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24286524
Why not update first and then retrieve the value?  Or place it in a BEGIN /COMMT TRANSACTION block.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24286532
If it is well structured there should never be any need to lock anything.
0
 
LVL 19

Author Comment

by:daveamour
ID: 24286536
If I update first how do I retrieve - can I use scope_identity() after an update?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24286564
No.  But you can use OUTPUT
0
 
LVL 19

Author Comment

by:daveamour
ID: 24286567
Ok can you show me how, I'm having a real thicko day!
Thanks
 
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24286590
Update      PinNumbers
Set      Used = 1
OUTPUT      Inserted.PinID
Where      PinID = (Select MIN(PinID) From PinNumbers Where Used = 0)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24286605
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)
0
 
LVL 19

Author Comment

by:daveamour
ID: 24286622
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24286634
Have you tried it?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24286639
acperkins is 100% correct here, OUTPUT is the way to go.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 24286645
Here is how I tested it:

Create table #PinNumbers (
                  PinID integer,
                  PinNumber varchar(20),
                  Used bit)

SET  NOCOUNT ON

Insert #PinNumbers (PinID, PinNumber, Used) Values (1, '069784', 0)
Insert #PinNumbers (PinID, PinNumber, Used) Values (2, '083163', 0)
Insert #PinNumbers (PinID, PinNumber, Used) Values (3, '098974', 0)
Insert #PinNumbers (PinID, PinNumber, Used) Values (4, '171207', 0)
Insert #PinNumbers (PinID, PinNumber, Used) Values (5, '133010', 0)
Insert #PinNumbers (PinID, PinNumber, Used) Values (6, '099590', 0)

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

Drop Table #PinNumbers

This is the output I got:

PinID       PinNumber            Used
----------- -------------------- -----
1           069784               1
0
 
LVL 19

Author Comment

by:daveamour
ID: 24286648
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)
 
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24286668
Thanks Tim, but I hold you responsible for enlightening me to the use of the OUTPUT clause.  :)
0
 
LVL 19

Author Closing Comment

by:daveamour
ID: 31577186
Ok I get it now having had a quick google - its what used to be used in triggers pre 2005
0
 
LVL 19

Author Comment

by:daveamour
ID: 24286673
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?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24286693
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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24286699
>>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.
0
 
LVL 19

Author Comment

by:daveamour
ID: 24286704
Ok thanks
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question