技术咨询：Inconsistent Analysis与Non-Repeatable Reads的差异

Great question—this is such a common confusion because a lot of old or inconsistent database resources mix up the terminology, so I totally get why you're stuck! Let's break this down clearly, using concrete examples to highlight the core differences.

首先澄清术语的常见混用 #

First, note that Inconsistent Analysis (sometimes called Inconsistent Retrieval) is an older, broader term, while Non-Repeatable Reads is a more specific case standardized in modern SQL. The key difference boils down to what exactly is being read, and how the data changes.

1. Non-Repeatable Reads（不可重复读）：单行数据的更新/删除导致的不一致 #

This scenario strictly involves the exact same row of data being read twice in one transaction, with another transaction modifying or deleting that row and committing in between. The result is two different values (or a missing row) for the same record.

Example: #

• Transaction A runs: SELECT salary FROM employees WHERE id=1; → Returns 5000
• Transaction B runs: UPDATE employees SET salary=6000 WHERE id=1; and commits
• Transaction A runs the same select again: SELECT salary FROM employees WHERE id=1; → Returns 6000

Here, the focus is on a single, specific row whose value has changed. That's a non-repeatable read.

2. Inconsistent Analysis（不一致分析）：范围/聚合查询的整体结果不一致 #

This term (more common in older docs) refers to a transaction running range queries or aggregate functions multiple times, with other transactions inserting, updating, or deleting rows in that range between reads. The inconsistency is in the entire dataset's summary or composition, not just a single row.

Example 1 (Aggregation): #

• Transaction A runs: SELECT SUM(salary) FROM employees WHERE department='Engineering'; → Returns 50000
• Transaction B runs: INSERT INTO employees (id, name, salary, department) VALUES (100, 'Alice', 7000, 'Engineering'); and commits
• Transaction A runs the sum query again → Returns 57000

Example 2 (Range Query): #

• Transaction A runs: SELECT * FROM employees WHERE department='Engineering'; → Gets 10 rows
• Transaction B runs: DELETE FROM employees WHERE id IN (5, 6); and commits
• Transaction A runs the same select again → Gets 8 rows

In both cases, the issue isn't a single row's value changing—it's the entire set of matching rows (or their aggregated result) being different. This is inconsistent analysis.

Quick Cheat Sheet to Tell Them Apart #

• Non-Repeatable Reads = Single row focus: Same row, different values on repeat reads
• Inconsistent Analysis = Dataset focus: Same range/aggregate, different results on repeat reads

Also, note that modern SQL standards often refer to the "dataset range" case as Phantom Reads—but many older sources use Inconsistent Analysis interchangeably with that term.

内容的提问来源于stack exchange，提问作者Elizabeth