Questions regarding concurrency in sql server 2000

[Maximus]

Hi
I am building an auction site using SQL server 2000 and asp.net.
My question is how does sql server work when >1 person wud try to place a bid?

Obviously, each time i need to check the current high price, and to see if the bid is the next highest. Is there a way i can use Mutex variables on a table to make sure that only a particualr thread is able to access a table to obtain such values or wud the inhenrent structure of SQL Server and the locks it places on tablse be sufficent enought to let me do my programing without any "Stale data" issues.

Thanks
Pierre

[Maximus]

thanks all for yur helpin advance

[MAK]

Lock would do it

[skhanal]

SQL lock is enough for concurrency, however if your user is selecting current bid, then when he/she submits a bid, you have to check if the earlier bid has already changed and if it is higher than the user is trying to bid.

[Maximus]

Thanks guys
I did some reasearch and came across.
sp_getapplock

and its use below in a transaction

sp_getapplock @Resource = 'mySemaphore',
@LockMode = 'Exclusive'


is this right?
I guess it wud enabel me to make sure that when i have this lock, and this is the only interface to place a bid then the thread has the most uptodate value and there will no rac condition etc to give me stale data?

[rmiao]

Ensure don't let the app hold lock too long, that will affect performance and prevent other users to submit bid.

[Rawhide]

Yeah, you definitely don't want to hold an application lock. You want more than one person to be able to bid at once right?

[skhanal]

App lock can be more restrictive than serializable, instead of locking the row you can lock the whole application

[Maximus]

Hi
Well i have made the "critical section" as short as possible, it fetches the data from the Auctions table and then does some checking and appropraitly updates the table.My code is as follows:



CREATE PROCEDURE j4d_Auction_PlaceBid
(
@AuctionID int,
@UserID int,
@Bid money,
@Increment money,
@Result int out

/*
Ouput values
1 - Success
2 - Success but outproxied
3 - Auction Over (only possible in last few milliseconds)

4- Bid too low
5 - Increment too low


*/

)

as

set nocount on

--First thing's First , GET the current time
declare @currentTime datetime
declare @currentHighBid money
declare @currentMaxBid money
declare @AuctionEndTime datetime
declare @currentBids int
declare @timeLeft int
declare @newEndTime datetime


set @CurrentTime = GETDATE()


--Step 2: We need the the following info ASAP
-- CurrentBid, CurrentMaxBid, TimeofEnding (from j4d_Auctions_Current table)

BEGIN TRANSACTION
EXEC sp_getapplock @Resource = 'mySemaphore', @LockMode = 'Exclusive' --We now have exxclusive rights
--Begin Critical Section
Select @currentHighBid = currentHighBid, @currentMaxBid = currentMaxBid, @AuctionEndTime = EndDate , @currentBids = currentBids from j4d_Auctions_Current where AuctionID = @AuctionID

--Check TimeDifference
set @timeLeft = Datediff(ms,@currentTime,@AuctionEndTime)
set @newEndTime = dateadd(s, 60, @AuctionEndTime)

if(@timeLeft < 0)
begin
set @Result = 3
end
else
begin


if(@currentBids = 0) --Bypass if its the first bid
begin
set @Result = 1
--Now to Update Prices
if(@timeLeft > 60000)
Update j4d_Auctions_Current set currentHighBidder = @UserID, currentMaxBid = @Bid , currentBids = currentBids + 1 where AuctionID = @AuctionID
else
Update j4d_Auctions_Current set currentHighBidder = @UserID, currentMaxBid = @Bid , currentBids = currentBids + 1 , EndDate = @newEndTime where AuctionID = @AuctionID

end
else
begin
if(@currentHighBid >= @Bid)
begin
set @Result = 4 --Bid Too low
end
else if(@currentMaxBid >= @Bid)
begin
set @Result = 2 --Outdone by Proxy
Update j4d_Auctions_Current set currentHighBid = @Bid + 1, currentBids = currentBids + 1 where AuctionID = @AuctionID

end
else if( (@CurrentMaxBid + @Increment) > @Bid)
begin
set @Result = 5 --Not enought increment
end

else
begin
--Success! we are the top Bidder
set @Result = 1
--Now to Update Prices
if(@timeLeft > 60000)
Update j4d_Auctions_Current set currentHighBidder = @UserID, currentHighBid = @CurrentMaxBid + 1, currentMaxBid = @Bid , currentBids = currentBids + 1 where AuctionID = @AuctionID
else
Update j4d_Auctions_Current set currentHighBidder = @UserID, currentHighBid = @CurrentMaxBid + 1, currentMaxBid = @Bid , currentBids = currentBids + 1, EndDate = @newEndTime where AuctionID = @AuctionID
end
end
end

--End Critical Section
Commit TRANSACTION


--Add to Bildding History
if(@result = 3) OR (@result = 2)
begin
Insert into j4d_Auctions_Bids(BidAmount, UserID, AuctionID, BidTime) values (@Bid, @UserID, @AuctionID, @currentTime)
end

set nocount off
GO




I apprecaite the concern that it might slow the system but isnt accuracy of the data the primary concern here? Is the above usage of applock enough without slowing the system. How would serializeable work?
AT any moment, there will be threads trying to post bids and also threads trying to get the latest value of the auction. I havent used any locks on reading which is a seperate stored procdure. i am assuming that those threads get blocked autoamtically if the "PlaceBid" s.prodecure is running.