How exclusive lock works?

Sudip Bhatt 2,281 Reputation points
2020-09-10T18:56:36.54+00:00

How exclusive lock works?

see the code

create table zc
(id int not null,
col1 char(4000),
col2 char(4000)
constraint pk_zc primary key clustered(id)
)

insert into zc
select 1,'aaa','bbb' union all
select 2,'ccc','ddd' union all
select 3,'eee','fff' union all
select 4,'ggg','hhh' union all
select 5,'iii','jjj'

-----connection A (execute the steps one by one A.1--->B.1(wait)--->A.2)
-----step A.1
set transaction isolation level repeatable read
begin tran
select * from zc (xlock) where id=2

------connection B
-----step B.1 (it will wait and not finish)
select * from zc (xlock) where id=2

-----connection A
-----step A.2
commit tran

see the code specially connection B there no isolation mention. so for connection B what will be isolation ? but for connection A the isolation mention which is set transaction isolation level repeatable read

please guide me. thanks

Developer technologies | Transact-SQL
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2020-09-10T21:36:50.283+00:00

    Since you specify xlock, this implies REPEATABLE READ.

    Basically, when you specify a locking hint, this always trumps your setting for the isolation level. (Although, that is a bit of simplification. If your isolation level is SERIALIZABLE, the XLOCK hint will not downgrade you to REPEATABLE READ. (Or at least I think so. I did not actually test.)

    2 people found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,621 Reputation points
    2020-09-11T07:54:58.06+00:00

    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.

    1 person found this answer helpful.

  2. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2020-09-11T21:44:31.52+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.