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.
You must Sign In to post a response.
Category: SQL Server
Kindly clarify why we use with(xlock,rowlock,readpast) in SQL Select statement.
Help me out with an example.
#768187
Hi,
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
Ex:
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
Regards,
SonyShiva
Never lose hope..You never know what tomorrow will bring
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
Ex:
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
Regards,
SonyShiva
Never lose hope..You never know what tomorrow will bring
Return to Return to Discussion Forum