How to Guarantee Atomic and Reliable Row Handling in T-SQL?

Atomic and Reliable Row Handling with T-SQL

To maintain the integrity of a booking system, it's crucial to ensure that updates and insertions are atomic and reliable. An atomic transaction guarantees that if multiple users attempt to update the same row concurrently, only one update will succeed, preventing race conditions.

Suppose you have a table named "Bookings" with a unique index on the "FlightId" column. Your goal is to create a stored procedure that updates the "TicketsBooked" column for a specific flight ID. If the row corresponding to that flight ID doesn't exist, you need to insert it.

Here's how you can achieve this atomic and reliable operation using T-SQL:

-- BEGIN TRANSACTION
BEGIN TRANSACTION;

-- Check if the row exists
IF EXISTS (SELECT * FROM Bookings WHERE FlightID = @Id)
BEGIN
    -- Update the existing row
    UPDATE Bookings
    SET TicketsBooked = TicketsBooked + @TicketsToBook
    WHERE FlightID = @Id AND TicketsMax < (TicketsBooked + @TicketsToBook);
END
ELSE
BEGIN
    -- Insert a new row
    INSERT INTO Bookings (FlightID, TicketsBooked) VALUES (@Id, @TicketsToBook);
END;

-- COMMIT TRANSACTION
COMMIT TRANSACTION;

-- Return success (TRUE)
SELECT CASE WHEN @@ERROR = 0 THEN 1 ELSE 0 END AS Success;

This stored procedure uses the BEGIN TRANSACTION and COMMIT TRANSACTION statements to define the transaction boundary. Within the transaction, it first checks if the row with the specified flight ID exists using the IF EXISTS statement. If it doesn't, the INSERT statement inserts a new row.

If the row exists, the UPDATE statement updates the "TicketsBooked" column only if the updated value doesn't exceed the "TicketsMax" limit. By using a transaction, we ensure that either the update or insertion is successfully committed, or if any error occurs, the transaction is rolled back.

Finally, the CASE statement checks the @@ERROR global variable to determine the success or failure of the transaction and returns a Boolean value (TRUE or FALSE) accordingly.

The above is the detailed content of How to Guarantee Atomic and Reliable Row Handling in T-SQL?. For more information, please follow other related articles on the PHP Chinese website!