不可重复读和幻影读的区别是什么?
我读过维基百科上的隔离(数据库系统)文章，但我有一些怀疑。在下面的例子中，将会发生什么:不可重复读取和幻影读取?
# # # #事务
SELECT ID, USERNAME, accountno, amount FROM USERS WHERE ID=1
# # # #输出:
1----MIKE------29019892---------5000
# # # #事务B
UPDATE USERS SET amount=amount+5000 where ID=1 AND accountno=29019892;
COMMIT;
# # # #事务
SELECT ID, USERNAME, accountno, amount FROM USERS WHERE ID=1
另一个疑问是，在上面的示例中，应该使用哪个隔离级别?,为什么?
当前回答
Non-repeatable read(fuzzy read) is that a transaction reads the same row at least twice but the same row's data is different between the 1st and 2nd reads because other transactions update the same row's data and commit at the same time(concurrently). Phantom read is that a transaction reads the same table at least twice but the number of the same table's rows is different between the 1st and 2nd reads because other transactions insert or delete rows and commit at the same time(concurrently).
我用MySQL和2个命令提示符尝试了不可重复读取和幻影读取。
对于不可重复读和幻像读的实验，我设置read COMMITTED隔离级别发生不可重复读和幻像读:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
并且，我用id和名称创建了person表，如下所示。
人表:
| id | name |
|---|---|
| 1 | John |
| 2 | David |
首先，对于不可重复的读取，我在MySQL查询中执行了以下步骤:
| Flow | Transaction 1 (T1) | Transaction 2 (T2) | Explanation |
|---|---|---|---|
| Step 1 | BEGIN; |
T1 starts. | |
| Step 2 | BEGIN; |
T2 starts. | |
| Step 3 | SELECT * FROM person WHERE id = 2;2 David |
T1 reads David. |
|
| Step 4 | UPDATE person SET name = 'Tom' WHERE id = 2; |
T2 updates David to Tom. |
|
| Step 5 | COMMIT; |
T2 commits. | |
| Step 6 | SELECT * FROM person WHERE id = 2;2 Tom |
T1 reads Tom instead of David after T2 commits.*Non-repeatable read occurs!! |
|
| Step 7 | COMMIT; |
T1 commits. |
第二，对于幻影读取，我用MySQL查询执行了以下步骤:
| Flow | Transaction 1 (T1) | Transaction 2 (T2) | Explanation |
|---|---|---|---|
| Step 1 | BEGIN; |
T1 starts. | |
| Step 2 | BEGIN; |
T2 starts. | |
| Step 3 | SELECT * FROM person;1 John 2 David |
T1 reads 2 rows. | |
| Step 4 | INSERT INTO person VALUES (3, 'Tom'); |
T2 inserts the row with 3 and Tom to person table. |
|
| Step 5 | COMMIT; |
T2 commits. | |
| Step 6 | SELECT * FROM person;1 John 2 David 3 Tom |
T1 reads 3 rows instead of 2 rows after T2 commits.*Phantom read occurs!! |
|
| Step 7 | COMMIT; |
T1 commits. |
其他回答
Non-repeatable read(fuzzy read) is that a transaction reads the same row at least twice but the same row's data is different between the 1st and 2nd reads because other transactions update the same row's data and commit at the same time(concurrently). Phantom read is that a transaction reads the same table at least twice but the number of the same table's rows is different between the 1st and 2nd reads because other transactions insert or delete rows and commit at the same time(concurrently).
我用MySQL和2个命令提示符尝试了不可重复读取和幻影读取。
对于不可重复读和幻像读的实验，我设置read COMMITTED隔离级别发生不可重复读和幻像读:
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
并且，我用id和名称创建了person表，如下所示。
人表:
| id | name |
|---|---|
| 1 | John |
| 2 | David |
首先，对于不可重复的读取，我在MySQL查询中执行了以下步骤:
| Flow | Transaction 1 (T1) | Transaction 2 (T2) | Explanation |
|---|---|---|---|
| Step 1 | BEGIN; |
T1 starts. | |
| Step 2 | BEGIN; |
T2 starts. | |
| Step 3 | SELECT * FROM person WHERE id = 2;2 David |
T1 reads David. |
|
| Step 4 | UPDATE person SET name = 'Tom' WHERE id = 2; |
T2 updates David to Tom. |
|
| Step 5 | COMMIT; |
T2 commits. | |
| Step 6 | SELECT * FROM person WHERE id = 2;2 Tom |
T1 reads Tom instead of David after T2 commits.*Non-repeatable read occurs!! |
|
| Step 7 | COMMIT; |
T1 commits. |
第二，对于幻影读取，我用MySQL查询执行了以下步骤:
| Flow | Transaction 1 (T1) | Transaction 2 (T2) | Explanation |
|---|---|---|---|
| Step 1 | BEGIN; |
T1 starts. | |
| Step 2 | BEGIN; |
T2 starts. | |
| Step 3 | SELECT * FROM person;1 John 2 David |
T1 reads 2 rows. | |
| Step 4 | INSERT INTO person VALUES (3, 'Tom'); |
T2 inserts the row with 3 and Tom to person table. |
|
| Step 5 | COMMIT; |
T2 commits. | |
| Step 6 | SELECT * FROM person;1 John 2 David 3 Tom |
T1 reads 3 rows instead of 2 rows after T2 commits.*Phantom read occurs!! |
|
| Step 7 | COMMIT; |
T1 commits. |
不可重复读取和幻影读取都是由于一个事务T1看到了在T1完成之前提交的另一个事务T2的更改。不同之处在于，对于同一个逻辑行，不可重复读操作返回不同的值。(例如，如果主键是employee_id，那么在两个结果中，某个员工的工资可能不同。)幻影读取返回两组不同的行，但是对于出现在这两组中的每一行，列值都是相同的。
在具有不可重复读取的系统中，事务a的第二次查询的结果将反映事务B中的更新—它将看到新的金额。
在允许幻影读取的系统中，如果事务B插入ID = 1的新行，事务a将在执行第二次查询时看到新行;即幻影读是不可重复读的一种特殊情况。
这两种隔离级别之间的实现存在差异。 对于“不可重复读”，需要行锁定。 对于“幻影读取”，需要范围锁定，甚至是表锁定。 我们可以使用两相锁协议来实现这两个级别。
不可重复读是一个隔离级别，幻影读(通过其他事务读取已提交的值)是一个概念(读的类型，例如脏读或快照读)。不可重复读隔离级别允许幻影读，但不允许脏读或快照读。
