2

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;

1 Answer 1

6

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.

5
  • Thank you for the answer. So you mean script like this (Added to end of my question) is a valid real-world scenario where same select statement has to run twice? Commented Feb 16, 2023 at 0:15
  • 1
    Yes. It doesn't even have to be a second SELECT, just the fact that the modification(s) are operating in a no longer valid state (since the first SELECT). Commented Feb 16, 2023 at 10:41
  • But if the second SELECT is removed, how that script is considered as non-repeatable read scenario, cause there is no reading of same record twice no? Commented Feb 16, 2023 at 14:30
  • 1
    That is why I prefer to call it "inconsistent analysis" instead. Commented Feb 16, 2023 at 14:46
  • Aha!!! So, the non-repeatable read doesn't have a meaning to a real-world usage of same query has to run twice within the same transaction. Like you said, It's more like, **commited ** row value read by one transaction has a possibility of to be changed by another transaction after reading it. Commented Feb 16, 2023 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.