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. The 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 who may place a bid on the same auction item at the same time. Here is the stored procedure that places a bid:ALTER PROCEDURE [dbo].[PlaceABid]     @UserId NVARCHAR(128),     @AuctionId INT,    @BiddingPrice DECIMAL(16,2)ASBEGIN    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 CATCHENDIts 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 about whether it would 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 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?Is there a better way to handle error/exception, and pass them to client code such as C#?
Second-sealed-bid auction web app
sql;sql server;t sql;stored procedure