Non-Repeatable Read vs Phantom Read - Real World Scenario

Question

About the mentioned 2 Data concurrency problems below is what I understand.

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.
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;

score 6 · Accepted Answer · answered Feb 15 '23 at 17:48

Non-repeatable read is also sometimes referred to as "inconsistent analysis", which IMO is a more descriptive term. Here's a verbal example I sometimes use (without including any real tables etc in the mix):

Imagine you want to place an order. You check that the items are in stock (SELECT). Then then you place the order (INSERT, UPDATE, whatever has to be done). Now, without repeatable read, somebody might change the state of your products in stock balance after the SELECT so the items are no longer in stock. I.e., the data was modified after the SELECT but before the modifications. The customer will be disappointed since the product was supposed to be in stock, but because inconsistent analysis, the products will not ship as advertised.

Non-Repeatable Read vs Phantom Read - Real World Scenario

1 Answers1