What is the theory behind when DELETE or UPDATE query comes in a Repeatable Read Isolation Level?

Question

In Repeatable Read Isolation Level, it does not prevent other transactions from inserting new rows into the tables which have been selected in the current transaction. I want to know the theory behind when DELETE or UPDATE query comes? Consider the following example which I'm trying to solve.

Assume use of Relational Database Management System (RDBMS) and rigorous 2-Phase locking approach for concurrency control.

ID  Balance Name
1   12      A
2   18      B
10  100     C

The following timing diagram, shows the execution of two concurrent transactions based on the above Customer table, T1 and T2, both of which execute on REPEATABLE_READ transaction isolation level.

            T1                                  T2
SELECT * FROM Customer              |
WHERE Balance BETWEEN 10 AND 20;    |
                                    |       **QUERY X** 
                                    |       COMMIT; "
SELECT * FROM Customer              |
WHERE Balance BETWEEN 10 AND 20;    |

Will the Phantom anomaly be exhibited if instead of an INSERT query, one of the following SQL statements is replaced into QUERY X on T2 ? Justify your answer.

1. UPDATE Customer SET Balance = 14 WHERE Balance = 100;
2. DELETE FROM Customer WHERE Balance = 12;

I'm new to DBMS, So I'm very much appreciated for kind explanations which help me to have a better understanding of these problems.

Answer 1

You can easily test this for yourself.

Setup

CREATE TABLE dbo.Customer
(
ID INT,
Balance INT,
Name CHAR(1)
)


INSERT INTO dbo.Customer
VALUES 
(1 ,  12   ,  'A'),
(2 ,  18   ,  'B'),
(10,  100  ,  'C');

Connection 1 (leaves transaction open)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT * FROM Customer            
WHERE Balance BETWEEN 10 AND 20;  

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

Repeatable read differs from read committed (locking version) as it retains the S locks on rows matching the WHERE clause rather than releasing them as soon as the row is read.

The query against sys.dm_tran_locks shows that it holds two RID locks matching the two rows returned.

Trace flag 1200 output for the above

shows that it acquires three row S locks and keeps hold of the locks on the rows that qualify for the filter

Process 64 acquiring IS lock on OBJECT: 5:917578307:0  (class bit2000000 ref1) result: OK

Process 64 acquiring IS lock on PAGE: 5:1:256  (class bit2000000 ref0) result: OK

Process 64 acquiring S lock on RID: 5:1:256:0 (class bit2000000 ref0) result: OK

Process 64 acquiring S lock on RID: 5:1:256:1 (class bit2000000 ref0) result: OK

Process 64 acquiring S lock on RID: 5:1:256:2 (class bit2000000 ref0) result: OK

Process 64 releasing lock on RID: 5:1:256:2

If you now try in another connection

DELETE FROM Customer WHERE Balance = 12;

you will see that this is blocked by the row lock held by the original transaction. Note that this will eventually succeed when the original transaction commits and releases its locks.

Cancel that one and try

UPDATE Customer SET Balance = 14 WHERE Balance = 100;

this succeeds straight away. The original transaction never retained the S lock on that row as even though it was read (due to lack of index on Balance) it did not qualify for the WHERE clause and the lock was released per the releasing lock output above.