What will happen if records are deleted during the data retrieving process?

Question

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

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.

First fetch (A)

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

After deletion from (B)

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

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

instead of

[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?

Answer 1

In MSSQL cannot be such problem, because locking accured while result set retrived. I don't know what about another RDBMS.

Answer 2

What happens will depend, in part, on the isolation levels in force in the two transactions.

If the fetching process is running at serializable isolation, then the DELETE operation will fail. If the fetching process is running at dirty read (read uncommitted) isolation, then almost anything is possible, but most likely, it would read the records that are not deleted.

Answer 3

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

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