none
Locking when xlock is specified in query RRS feed

  • Question

  • I am a newbie in SQL server and I am finding difficult understanding the below locking scenario:

    Done the below scenarios in SQL server 2016

    Scenerio 1
    ++++++++++++

    TEst case
    =============
    A very simple table :

    Table A with column a1 

    a1
    --
    1
    2
    3
    4
    5

    session 1
    ==========
    BeGIN TRAN
    select * from a (xlock) where a between 1 and 4
    <==== not commmiting this one

    output : I get 4 rows (as expected)

    session 2
    =============
    select * from a  where a=3

    I get the output 1 row. 


    Scenerio 2
    ++++++++++++
    Same table,but now I am add a primary key (index) on a1.

    session 1
    ==========
    BeGIN TRAN
    select * from a (xlock) where a between 1 and 4
    <==== not commmiting this one

    output : I get 4 rows 

    session 2
    =============
    select * from a  where a=3

    output : Query hangs 


    I would like to understand why lock is not applicable in scenario 1 and it comes into play in scenario 2 when I am having a primary key(index). 

    • Edited by vikas_r_s Tuesday, June 25, 2019 4:45 AM
    Tuesday, June 25, 2019 4:37 AM

Answers

  • I was not able to reproduce the behaviour. That is, when I ran the query in session 2, that query was blocked also when the table was a heap.

    Then again, if the database is in READ_COMMITTED_SNAPSHOT, the SELECT will not block, regardless of the clustered index or not.

    I suggest that you go back and redo your test. Be careful to rollback the open transaction before you do the next test.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 25, 2019 9:46 PM
    Moderator

All replies

  • Good day,

    The short answer is that in order to use XLOCK in ROWLOCK level (which is what happen in your case) the server need to have INDEX. When you add the INDEX what is locked is the INDEX in this case. You can change the lock level to TABLOCK for example and than it will create the lock even if you do not have index

    BeGIN TRAN
    select * 
    --from T with(ROWLOCK ,xlock) -- this is your case probably since you did not explicitly tell the server what level of lock to use
    from T with(xlock ,TABLOCK) 
    where ID between 1 and 4

    Note! Using locking hints is usually a red flag when I come to a new client. Most people use hints without a full understanding (isolation, locking, deadlocks) in a desperate attempt to eliminate a problem. In most case I do not recommend to explicitly use locking hints.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, June 25, 2019 5:13 PM
    Moderator
  • I was not able to reproduce the behaviour. That is, when I ran the query in session 2, that query was blocked also when the table was a heap.

    Then again, if the database is in READ_COMMITTED_SNAPSHOT, the SELECT will not block, regardless of the clustered index or not.

    I suggest that you go back and redo your test. Be careful to rollback the open transaction before you do the next test.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 25, 2019 9:46 PM
    Moderator
  • Hi Ronen,

    Thank you for your quick response. 

    This was the 1st time, I have ever put up any question for SQL server and I am very happy with the super quick reply from you. 

    I have just started on SQL server and a very long way to go. But I am sure, if I get supportive people like you, it will be a joyful and lot of learning. 

    I will work on your recommendation.

    Regards

    Vikas


    Wednesday, June 26, 2019 2:24 AM
  • Hi Erland,

    Thank you for getting back. 

    Even I am surprised, when I tried doing the same test again. 2nd session didn't get lock at all (with/without index) Issue was  not getting reproduced.

    I didn't specifically set anything specifically (SET ....) 

    I am newbie in SQL server, just learning and working on simple examples. 

    I created the table

    create table a(a1 int primary key)

    insert into a values(1)

    insert into a values(2)

    insert into a values(3)

    insert into a values(4)

    insert into a values(5)

    and when I tried reproducing the issue again. I was unsuccessful.

    Regards

    Vikas



    Wednesday, June 26, 2019 2:36 AM
  • Hi Ronen,

    Thank you for your quick response. 

    This was the 1st time, I have ever put up any question for SQL server and I am very happy with the super quick reply from you. 

    I have just started on SQL server and a very long way to go. But I am sure, if I get supportive people like you, it will be a joyful and lot of learning. 

    I will work on your recommendation.

    Regards

    Vikas

    Hi Vikas,

    Just to clarify, I did not recommend you anything specifically, since I am not familiar with your system or your knowledge and ability. I said that "In most case I do not recommend to explicitly use locking hints" (typo "case" should be "cases") 😃.

    Thanks for the nice words👍


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, June 26, 2019 6:02 AM
    Moderator
  • Hi Ronen,

    I am wondering, why i am not able to reproduce the locking again.

    I have not used any SET... 

    Created the table like :

    -----------------------------------------------------

    create table a(a1 int primary key)

    insert into a values(1)

    insert into a values(2)

    insert into a values(3)

    insert into a values(4)

    insert into a values(5)

    -----------------------------------------------------

    Now when I am trying to simulate the scenario which I had described earlier . I am not getting any locking at all .

    Any idea ?

    Regards

    Vikas

    Wednesday, June 26, 2019 2:31 PM