You must Sign In to post a response.
  • Category: SQL Server

    Why we use with(xlock,rowlock,readpast) in SQL

    Hi All,

    Kindly clarify why we use with(xlock,rowlock,readpast) in SQL Select statement.
    Help me out with an example.
  • #768187

    XLOCK: allows to lock and held until some transaction completes.
    rowlock: Sets locks on a row level.It will lock the rows in an update or insert or delete. For example if rowlock with update, it tells the query engine to lock the rows of our table , so we will perform update.
    It is advisable to use this on single or some rows, because it reduces the performance if it is at table level
    update Table1 with (ROWLOCK)
    set name = "sk"
    where ID = 101
    Readpast: will not consider any locked rows. For Example In one transaction we are updating one table record , at that time that record will be locked, if we fetch the data of the table at that time with readpast hint, it will not show that record, means readpast is ignoring the locked record
    Ex; Assume table1 has 10 records
    Begin Transaction
    Update table1 set name="sk" where id ="101"

    Here Table1 101 id is locked until it commit/rollback the transaction, at the same time if i run the below query
    select count(*) from table1 with (readpast)
    It will give count as 9. means here locked record is not showing with readpast.

    Hope this will give you an idea

    Never lose hope..You never know what tomorrow will bring

  • Sign In to post your comments