I am working on a second-sealed-bid auction web app. I have an Auction table to keep auction items on record, a Bid table to keep the bids, and a ProxyBid table to keep trace of the highest bids that users have ever placed. Bid and ProxyBid are both associated with Auction, there could be multiple bid records for a given user id and auction id, whereas there can only be one record for a given user id and an auction id. Auction table has a Foreign Key which refers to Bid table, so that I can keep track of the current bid of each auction item.
I have multiple users may place a bid on the same auction item at the same time, below is the stored procedure that places a bid:
ALTER PROCEDURE [dbo].[PlaceABid]
@UserId NVARCHAR(128),
@AuctionId INT,
@BiddingPrice DECIMAL(16,2)
AS
BEGIN
IF NOT EXISTS (SELECT TOP 1 Id FROM dbo.AspNetUsers WHERE Id = @UserId)
THROW 50000, 'User could not be found.', 1; -- this can stop the execution, and level is 16
IF NOT EXISTS (SELECT TOP 1 Id FROM dbo.Auction WHERE Id = @AuctionId AND Closed = 0 AND EndingDate < GETDATE())
THROW 50000, 'Auction could not be found.', 1;
BEGIN TRY
BEGIN TRANSACTION; -- TODO: consider other islolation level
SET NOCOUNT ON;
DECLARE @CurrentPrice DECIMAL(16,2),
@HighestBiddingPrice DECIMAL(16,2),
@HighestBidderId NVARCHAR(128),
@StartingPrice DECIMAL(16,2);
SELECT TOP 1 @CurrentPrice = BiddingPrice FROM dbo.Bid WHERE AuctionId = @AuctionId ORDER BY BiddingPrice DESC, Id DESC;
SELECT TOP 1 @HighestBiddingPrice = BiddingPrice, @HighestBidderId = UserId FROM dbo.ProxyBid WHERE AuctionId = @AuctionId ORDER BY BiddingPrice DESC;
SELECT TOP 1 @StartingPrice = StartingPrice FROM dbo.Auction WHERE Id = @AuctionId;
IF @BiddingPrice <= @CurrentPrice
BEGIN
ROLLBACK TRANSACTION;
THROW 50000, 'Bidding is lower than the current bid.', 1;
END
IF @HighestBiddingPrice IS NULL -- nobody has placed any bids on the auction
BEGIN
INSERT INTO dbo.ProxyBid (AuctionId, UserId, BiddingPrice, CreatedTime, BidTime) VALUES (@AuctionId, @UserId, @BiddingPrice, GETDATE(), GETDATE());
IF @@ROWCOUNT < 1 -- indicate that the insertion has failed
BEGIN
ROLLBACK TRANSACTION;
THROW 50000, 'Failed in inserting bid record into ProxyBid table.', 2;
END
INSERT INTO dbo.Bid (AuctionId, BidderId, BiddingPrice, BiddingTime) VALUES (@AuctionId, @UserId, @StartingPrice, GETDATE());
IF @@ROWCOUNT < 1 -- indicate that the insertion has failed
BEGIN
ROLLBACK TRANSACTION;
THROW 50000, 'Failed in inserting bid record into Bid table.', 2;
END
UPDATE dbo.Auction SET CurrentBidId = SCOPE_IDENTITY(), CountBids = CountBids + 1 WHERE Id = @AuctionId;
IF @@ROWCOUNT < 1 -- indicate that the update has failed, AuctionId is invalid
BEGIN
ROLLBACK TRANSACTION;
THROW 50000, 'Failed in updating the auction record.', 2;
END
END
ELSE
BEGIN
IF @BiddingPrice <= @HighestBiddingPrice --
BEGIN
INSERT INTO dbo.Bid (AuctionId, BidderId, BiddingPrice, BiddingTime)
VALUES (@AuctionId, @UserId, @BiddingPrice, GETDATE())
,(@AuctionId, @HighestBidderId, @BiddingPrice, GETDATE());
IF @@ROWCOUNT < 2 -- indicate that the insertion has failed
BEGIN
ROLLBACK TRANSACTION;
THROW 50000, 'Failed in inserting bid record into Bid table.', 3;
END
UPDATE dbo.Auction SET CurrentBidId = SCOPE_IDENTITY(), CountBids = CountBids + 2 WHERE Id = @AuctionId;
IF @@ROWCOUNT < 1 -- indicate that the update has failed, AuctionId is invalid
BEGIN
ROLLBACK TRANSACTION;
THROW 50000, 'Failed in updating the auction record.', 3;
END
END
ELSE
BEGIN
IF EXISTS (SELECT TOP 1 CreatedTime FROM dbo.ProxyBid WHERE AuctionId = @AuctionId AND UserId = @UserId)
BEGIN
UPDATE dbo.ProxyBid SET BiddingPrice = @BiddingPrice, BidTime = GETDATE() WHERE AuctionId = @AuctionId AND UserId = @UserId;
END
ELSE
BEGIN
INSERT INTO dbo.ProxyBid (AuctionId, UserId, BiddingPrice, CreatedTime, BidTime) VALUES (@AuctionId, @UserId, @BiddingPrice, GETDATE(), GETDATE());
END
IF @@ROWCOUNT < 1 -- indicate that the update/insertion has failed
BEGIN
ROLLBACK TRANSACTION;
THROW 50000, 'Failed in updating the bid or inserting new bid record into the ProxyBid table.', 4;
END
INSERT INTO dbo.Bid (AuctionId, BidderId, BiddingPrice, BiddingTime)
VALUES (@AuctionId, @HighestBidderId, @HighestBiddingPrice, GETDATE())
,(@AuctionId, @UserId, @HighestBiddingPrice, GETDATE());
IF @@ROWCOUNT < 2 -- indicate that the insertion has failed
BEGIN
ROLLBACK TRANSACTION;
THROW 50000, 'Failed in inserting bid record into Bid table.', 4;
END
UPDATE dbo.Auction SET CurrentBidId = SCOPE_IDENTITY(), CountBids = CountBids + 2 WHERE Id = @AuctionId;
IF @@ROWCOUNT < 1 -- indicate that the update has failed, AuctionId is invalid
BEGIN
ROLLBACK TRANSACTION;
THROW 50000, 'Failed in updating the auction record.', 4;
END
END
END
COMMIT TRANSACTION;
RETURN 0
END TRY
BEGIN CATCH -- if the execution reaches here, the return value is below 0
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorLine INT = ERROR_LINE();
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
--PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
--PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
END
Its logic can be simplified as:
if nobody has placed a bid on the auction item in question
{
insert a record into ProxyBid
insert a record into Bid
update the auction item record, making it reference to the latest Bid record
}
else
{
if the price that the user is bidding at is no higher than the current highest bidding price
{
insert two records into Bid (note: because it is second sealed bid auction)
update the auction item record, making it reference to the latest Bid record
}
else
{
if the user already has a record in ProxyBid
{
insert a record into ProxyBid
}
else
{
update the existing record in ProxyBid
}
insert two records into Bid
update the auction item record, making it reference to the latest Bid record
}
}
I enclose if-else blocks inside try-catch block and transaction block. I am new to SQL, and I am always curious that, would it be better to do it in the other way, which is to enclose one try-catch block and transaction block inside each if-else block.
The other thing is, the control flow depends on the highest bidding price at the execution time. So, if another user inserts another record with higher price in middle of the execution, the data will be inconsistent. Will the default isolation level of transaction prevent that from happening?
My 3rd question is, is there a better way to handle error/exception, and pass them to client code such as C#?
Thanks a lot.