How exclusive lock works?

Hi @Sudip Bhatt ,

REPEATABLE READ
Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction. Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Use this option only when necessary.

How it works？

--step 1 The transaction is set to be repeatable read  
set transaction isolation level repeatable read  
--step 2 Open the transaction, display all data with id=2, the transaction is not over yet  
begin tran  
select * from zc (xlock) where id=2  
--step 3 In the same transaction, query again  
select * from zc (xlock) where id=2  

--step 4 End of transaction  
commit tran  

When the transaction isolation level is repeatable read, we query the number of rows that meet a certain condition multiple times in a transaction. The number of rows returned by different queries may be different.

Best Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Here is a longer elaboration to the question in the comment above. (Since I need more than 1000 characters, I post this as an Answer.)

In one window run this:

USE tempdb
go
DROP DATABASE IF EXISTS isolocktest
CREATE DATABASE isolocktest
go
USE isolocktest
go
CREATE TABLE sometable (id int NOT NULL,
                        name sysname NOT NULL,
                        CONSTRAINT pk_sometable PRIMARY KEY(id)
)

INSERT sometable(id, name)
   SELECT object_id, name FROM sys.objects

BEGIN TRANSACTION

SELECT name FROM sometable WITH (REPEATABLEREAD) WHERE id = 6
SELECT name FROM sometable WHERE id = 7

The output is sysclones and sysallocunits.

Now open a second query window and run this:

USE isolocktest
go
UPDATE sometable SET name = 'Paris' WHERE id = 7
UPDATE sometable SET name = 'Madrid' WHERE id = 6
go
USE tempdb

This script will hang on the second UPDATE statement, as it is being blocked by the REPEATABLEREAD hint, which is implemented with a shared lock.

Now go back to the first window and run:

SELECT name FROM sometable WHERE id = 6
SELECT name FROM sometable WHERE id = 7
go
COMMIT TRANSACTION

The output is sysclones and Paris.

Thus, we can see that READ COMMITTED applies the row with id = 7, since we now see the new value. And, it seems that because we applied REPEATABLE READ, this isolation level applies to id = 6, as we still see the old value. But this is not really the case. This is only an artefact of the locking applied. Because of the lock, the other process was stalled, and could not perform its update until the other process had committed.

But imagine that REPEATABLE READ had been implemented by reading from the version store, just like READ COMMITTED might be. In this case, the UPDATE process would not have been blocked. And in this case the second read of id = 6 would have returned Madrid, since the second read of this row is performed under the isolation level READ COMMITTED.