More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
In the REPEATABLE READ isolation level, what phenomena is still possible?
Answer
Phantom rows
Explanation
The answer is Phantoms rows. A phantom row refers to the situation where you execute a DML statement that retrieves data, and another process may add new rows the result set. For example:
SET ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT * FROM table
We return the following rows:
ColumnName
-----------
row1
row2
You are guaranteed to get back at least these rows, and no other user may delete these rows (the rows are locked with a shared lock) This is what is referred to as a repeatable read. However, a user might add another row:
SELECT * FROM table
ColumnName
-----------
row1
row2
row3
To prevent phantoms, use the SERIALIZABLE isolation level.
(Continued on next question...)
