How to Atomically Update or Insert Rows in T-SQL, Handling Maximum Booking Limits?

Ensuring Atomistic and Reliable Row Updates: Checking for Existence and Inserting if Absent

In your T-SQL stored procedure, you aim to update a row in a table, or insert it if it doesn't exist. This is crucial for a booking system, where atomicity and reliability are paramount.

Checking for Row Existence

To check if a row with the specified FlightId exists, use the EXISTS operator:

IF EXISTS (SELECT * FROM Bookings WHERE FlightID = @Id)

If the row exists, the inner query returns a non-empty result, and the IF condition evaluates to TRUE.

Inserting if Row Doesn't Exist

Inside the ELSE block, if the row doesn't exist, you can perform the insertion operation:

INSERT INTO Bookings ... (omitted)

Handling Condition Violation

Your question highlights the need to enforce a limit on maximum tickets bookable per flight. This can be achieved by checking the TicketsMax column in the UPDATE statement:

UPDATE Bookings
SET TicketsBooked = TicketsBooked + @TicketsToBook
WHERE FlightId = @Id AND TicketsMax < (TicketsBooked + @TicketsToBook)

If the condition is violated (i.e., there are already too many tickets booked), the UPDATE will fail, and you can return FALSE to indicate the operation's failure.

Transaction Management

To ensure atomicity and reliability, enclose the operations within a transaction:

BEGIN TRANSACTION

-- Perform operations (update/insert/condition check)

IF @@error = 0
BEGIN
    COMMIT TRANSACTION
    RETURN TRUE
END
ELSE
BEGIN
    ROLLBACK TRANSACTION
    RETURN FALSE
END

The @@error system variable indicates the success or failure of the database operations. If any error occurs (@@error ≠ 0), the transaction is rolled back, returning FALSE. Otherwise, the transaction is committed, and TRUE is returned to signify a successful booking.

The above is the detailed content of How to Atomically Update or Insert Rows in T-SQL, Handling Maximum Booking Limits?. For more information, please follow other related articles on the PHP Chinese website!