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 <p>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.</p><p><strong>Transaction Management</strong></p><p>To ensure atomicity and reliability, enclose the operations within a transaction:</p><pre class="brush:php;toolbar:false">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!