Blocking Select/Insert/Update/Delete from different session

Question

see this post https://stackoverflow.com/a/4627011

BEGIN TRANSACTION

SELECT @ID=RowID FROM MyTable WITH (ROWLOCK, XLOCK, HOLDLOCK) WHERE RowID=6822

when above sql will run then from another session can not read that data?

if so which hint will block another session to read that same data ? is it XLOCK hint ?

do i need to set any isolation specifically for this or does it work with Read Committed too?

please guide me. thanks

EDIT

Thanks Madam,

1. WITH (ROWLOCK, XLOCK, HOLDLOCK) does this lock hint row lock or table lock ?

2)if i use only XLOCK & HOLDLOCK then lock will place on row or table ?

3) without XLOCK if i use ROWLOCK & HOLDLOCK then lock will not be placed on row ?

4) XLOCK prevent read rows of data and ROWLOCK prevent updating or deleting row of data ?

5) Bit confused about XLOCK & ROWLOCK. what is difference between XLOCK & ROWLOCK ?
i assume XLOCK prevent other session to read data and as well as update & delete data.....am i right ?

ROWLOCK allow read data from other session but prevent user to update & delete data from other session.....am i right ?

if i am right then XLOCK & HOLDLOCK does the whole job....then why should i use ROWLOCK hint.....please guide me.

please guide me further. thanks

Answer 1

Hi @Sudip Bhatt ，

As mentioned in Hints, please refer details about these hints below:

• ROWLOCK

Specifies that row locks are taken when page or table locks are ordinarily taken.

• XLOCK

Specifies that exclusive locks are to be taken and held until the transaction completes. If specified with ROWLOCK, PAGLOCK, or TABLOCK, the exclusive locks apply to the appropriate level of granularity.

So combination of using XLOCK and ROWLOCK will block another session to read this same row.

• HOLDLOCK

Is equivalent to SERIALIZABLE which is the highest level of isolation transactions, it requires serialized execution of transactions, transactions can only be executed one after another, not concurrent execution

So using HOLDLOCK means the isolation level is set to the Serializable.

The combination of using xlock, rowlock, holdlock do not conflict and it will lead to an exclusive rowlock that is held until the end of the transaction.

Best regards
Melissa

---

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.

Answer 2

The XLOCK hint asks for an exclusive lock, so that should block another process from reading the row.

do i need to set any isolation specifically for this or does it work with Read Committed too?

Yes, and in the example, the isolation level is set to the Serializable. (HOLDLOCK is a legacy synonym for SERIALIZABLE.)

However, you would rarely do things like this. This is nothing you should play with.

Answer 3

Somehow I feel that I have answered these questions from you more than once before.

1) WITH (ROWLOCK, XLOCK, HOLDLOCK) does this lock hint row lock or table lock ?

So what do you think that ROWLOCK means?

2)if i use only XLOCK & HOLDLOCK then lock will place on row or table ?

Where SQL Server finds it most suitable.

3) without XLOCK if i use ROWLOCK & HOLDLOCK then lock will not be placed on row ?

Again, where SQL Server it most suitable.

4) XLOCK prevent read rows of data and ROWLOCK prevent updating or deleting row of data ?

ROWLOCK as such prevents nothing. It only tells SQL Server on which level to take the locks it needs to take. Or as I have told you a few times: it is a granularity hint.

5) Bit confused about XLOCK & ROWLOCK. what is difference between XLOCK & ROWLOCK ?
i assume XLOCK prevent other session to read data and as well as update & delete data.....am i right ?

The difference is like the difference between a knife and a spoon. You use the for different purposes. XLOCK tells SQL Server what lock to take (Exclusive). ROWLOCK tells on what level to take the lock.

And all this I have explained to your more than once.

Yes, I understand that locking is not simple material, but it frustrating to answer the same question over and over again. A forum is suitable for quick question in an area where the poster already have some knowledge. But it is not good for something where you need to learn things from start to end. Here is a good book where you can learn about locking so that you can understand these hints:
https://www.amazon.com/Expert-Server-Transactions-Locking-Practitioners/dp/1484239563/ref=sr_1\_2?dchild=1&keywords=dmitri+korotkevitch&qid=1610143849&sr=8-2

Full disclosure: Dmitri is a good friend of mine.