如果在数据检索过程中删除记录，将会发生什么？ [英] What will happen if records are deleted during the data retrieving process?

问题描述

假设我有6条记录，我将提取大小设置为2。

Let's say I have 6 records, and I set the fetch size as 2.

[Id] [Name]
 11   A   <-- 1st fetch, start from 1 position
 21   B
 31   C   <-- 2nd fetch, start from 3 position
 41   D
 51   E   <-- 3rd fetch, start from 5 position
 61   F

如果第一位用户发出 SELECT * from tablex，第二个用户发出 DELETE FROM tablex WHERE Id =2。删除过程恰好发生在第一次获取和第二次获取之间。

If 1st user issues a "SELECT * from tablex", and 2nd user issue a "DELETE FROM tablex WHERE Id = 2. The deletion process happen just in time between the 1st fetching and the 2nd fetching.

第一次获取（A）

[Id] [Name]
 11   A   <-- 1st fetch, start from 1
 21   B

从（B）中删除后

[Id] [Name]
 11   A   <-- 1st fetch, start from 1
 31   C   
 41   D
 51   E   
 61   F

现在第二次获取（ A）应该从3开始，但是删除导致记录的位置已经改变。他从位置3开始进行第二次提取，将要提取的记录是

Now 2nd fetch for (A) should start from 3, but deletion causes position of records have been altered. If the 2nd fetch start with position 3, the records which will be fetched are

[Id] [Name]
 41   D   <-- position 3
 51   E   

而不是

[Id] [Name]
 31   C   <-- 2nd fetch, start from 3 position
 41   D

我不确定这个问题是否会发生。还是现代数据库足够智能以检测到它？还是在数据检索过程中锁定表？

I am not sure if this problem will happen or not. Or the modern db is smart enough to detect it? Or simply lock the table during data retrieving process?

推荐答案

您所要求的称为 幻影读取。大多数数据库使用锁来防止在事务中这样做。

What you ask is called "PHANTOM READ". Most database use locks to prevent this in a transaction.

某些数据库提供 READ COMMITTED （或更低的隔离级别），在这种情况下可能会发生幻像读取。.

Some database provide "READ COMMITTED" (or lower) isolation level, phantom read may happens in those case..

这篇关于如果在数据检索过程中删除记录，将会发生什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！