2

I've seen a lot of comparison to inconsistent analysis and dirty reads and non-repeatable reads to dirty reads but i can't seem to grasp the difference between an inconsistent (incorrect) analysis vs a non repeatable read?

Is there a better way to explain this.

My confusion is in the fact that they are both multiple reads part of a transaction where a second (or third) transaction makes updates that are committed.

Incorrect analysis - the data read by the second transaction was committed by the transaction that made the change. Inconsistent analysis involves multiple reads (two or more) of the same row and each time the information is changed by another transaction, thus producing different results each time, and hence inconsistent.

Where as

Non Repeatable Reads occur when one transaction attempts to access the same data twice and a second transaction modifies the data between the first transaction's read attempts. This may cause the first transaction to read two different values for the same data, causing the original read to be non-repeatable.

I cant quite figure out how are they different. Thank you.

Elizabeth
  • 719
  • 1
  • 14
  • 27
  • They are synonyms. In both cases, transaction A reads a row, transaction B changes it, transaction A reads it again. See https://www.coursehero.com/file/p3ra5ncs/Inconsistent-Analysis-The-inconsistent-analysis-problem-is-also-known-as-the/ – kfinity May 15 '18 at 15:07
  • @kfinity textbooks and sites list them out as separate issues with separate examples and i can't figure out the difference. – Elizabeth May 15 '18 at 15:37
  • The difference reads as one of timing - and thus a subtle difference. Incorrect analysis, the transaction is inflight, but could be rolled back. The non-repeatable read reads more that the 2nd transaction is committed. Not convinced enough though to consider this an answer. – Andrew May 15 '18 at 15:43
  • @Andrew thank you, but i thought the "transaction in in flight" uncommitted, was a dirty read that one i get. Unless i misunderstand your explanation. – Elizabeth May 15 '18 at 15:45
  • @Elizabeth - no I'd agree with you that its a dirty read. The incorrect analysis here is a bit odd. - especially when paired up with Oracle. – Andrew May 15 '18 at 15:55
  • @Andrew I'm less worried about the platform and more about the definition (logical) explanation of what is happening exactly in a step by step process of the two types of transactions. I can remove the oracle tag. I just don't know how to distinguish this. – Elizabeth May 15 '18 at 16:00
  • it seems you need research about read committed/repeatable read/read uncommitted, the words in title is not the standard calling in database scope. – yaoweijq May 16 '18 at 03:01
  • @yaoweijq I understand the difference between committed vs uncommitted differences and how that works, in this case both examples have a erroneous changes with committed transactions. – Elizabeth May 16 '18 at 17:24

1 Answers1

0

In my view, the following is an example of an inconsistent analysis, but not a non-repeatable read. The example uses a table for bank accounts:

CREATE TABLE ACCOUNT(
    NO NUMERIC(10) NOT NULL PRIMARY KEY,
    BALANCE NUMERIC(9,2) NOT NULL);
INSERT INTO ACCOUNT VALUES (1, 100.00);
INSERT INTO ACCOUNT VALUES (2, 200.00);

For performance reasons, the bank has another table that stores the sum of all account balances redundantly (therefore, this table has always only one row):

CREATE TABLE TOTAL(AMOUNT NUMERIC(9,2) NOT NULL);
INSERT INTO TOTAL SELECT SUM(BALANCE) FROM ACCOUNT;

Suppose that transaction A wants to check whether the redundant sum is indeed correct. It first computes the sum of the account balances:

START TRANSACTION; -- PostgreSQL and SQL Standard, switches autocommit off
SELECT SUM(BALANCE) FROM ACCOUNT;

Now the owner of account 1 makes a deposit of 50 dollars. This is done in transaction B:

START TRANSACTION;
UPDATE ACCOUNT SET BALANCE = BALANCE + 50 WHERE NO = 1;
UPDATE TOTAL SET AMOUNT = AMOUNT + 50;
COMMIT;

Finally, transaction A continues and reads the redundant sum:

SELECT AMOUNT FROM TOTAL;

It will see the increased value, which is different from the sum it computed (probably causing a false alarm).

In this example, transaction A did not read any table row twice, therefore this cannot be a non-repeatable read. However, it did not see a unique database state - some part of the information was from the old state before the update of transaction B, and some part from the new state after the update.

But this is certainly very related to a non-repeatable read: If A had read the ACCOUNT rows again, this would be a non-repeatable read. It seems that the same internal mechanisms that prevent non-repeatable reads also prevent this problem: One could keep read rows locked until the end of the transaction, or use multi-version concurrency control with the version at the begin of the transaction.

However, there is also one nice solution here, namely to get all data in one query. At least Oracle and PostgreSQL guarantee that a single query is evaluated with respect to only one state of the database:

SELECT SUM(BALANCE) AS AMOUNT, 'SUM' AS PART FROM ACCOUNT
UNION ALL
SELECT AMOUNT, 'TOTAL' AS PART FROM TOTAL;

In a formal model of transaction schedules with reads and writes this also looks very similar to a non-repeatable read: First A does read(x), then B does write(x), write(y), and then A does read(y). If only a single object would be involved, this would be a non-repeatable read.

StefanB
  • 89
  • 1
  • 3