Non-Repeatable Read vs Phantom Read - Real World Scenario

About the mentioned 2 Data concurrency problems below is what I understand. 1. **Non-Repeatable Read** - is where within same transaction running the same query twice returns 2 different values for the same record, cause another transaction has updated the same record before the first transaction running the same query 2nd time. 2. **Phantom Read** - is also similar. In this case instead about the same record, this is about the result set. First transaction running the same query first time had 10 rows, but when it run the same query for the second time, now it has 11 rows, before the 2nd run another transaction has made a change (Insert/Delete) which affected that query output. And this is what I can't wrap my head around. - Why exactly **a transaction has to run the same query twice** in a **real-world** scenario? If anyone can give a real-world example from their experience, where it has to run the same query again twice in a same transaction, which could results in above scenario, would be helpful. I'm trying to know why this concurrency problem is important based on a real-world example. Does the below script on Placing order is a real-world kind of script acceptable on where we have to run the same SELECT twice?.

BEGIN TRANSACTION

IF NOT EXISTS(SELECT 1 FROM products WHERE product_id = @product_id AND available_quantity> @quantity)
BEGIN
	ROLLBACK TRANSACTION; 
    RAISERROR('Quantity is not available.');
    RETURN;
END

-- Place order
INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (@customer_id, @product_id, @quantity, GETDATE());

-- Update product quantity 
IF NOT EXISTS(SELECT 1 FROM products WHERE product_id = @product_id AND available_quantity> @quantity)
BEGIN
    ROLLBACK TRANSACTION; 
    RAISERROR('Quantity is not available.');
    RETURN;
END

UPDATE products SET available_quantity = available_quantity - @quantity WHERE product_id = @product_id;
COMMIT;
RETURN;