Post reply

SQL escalting S to IX or X, Why when there is no update!

  • October 13, 2005 at 7:28 pm

    #110624

    Hi *,

    Does SQL change S locks  to NOLOCKS hints on its own because it seems like S Locks are being escalted to IX or X locks.  A friends claims this to be the case and is trying to convince me that SQL has a mind of its own!  Only if there is an update to the table will SQL escalted the S Lock, right?

    My friend is further suggesting that these X locks can potentially cause deadlocks because the sproc is becoming an LRQ long runing query so he is proposing a NOLOCK hint if dirty reads are allowed!

     

    What is the cost of S locks vs X locks as it relates to Perf?  If dirty reads are allowed and there is a query that uses X locks and S locks is there more performance cost attributed to the X?  I would not necessarily think so, the logic for set locks type should be pretty simple.

    Moreover, what is the gain in changing to IX, I would understand X if it is tyring to protect data?

     

    Any input on this matter is appreciated...I am getting more confused.

     

    Thanks.

  • October 14, 2005 at 1:58 am

    #597281

    "Does SQL change S locks to NOLOCKS hints on its own because it seems like S Locks are being escalted to IX or X locks."

    SQL Server doesn't change S locks to NOLOCK hints at any time. The reason being that one thing is a lock, and the other is a hint.

    S locks can't be escalated to IX locks. If SQL Server finds you trying to place an IX lock on an object already lock by yourself with an S lock, it transforms the lock to a SIX lock.

    "Only if there is an update to the table will SQL escalted the S Lock, right?"

    You are correct that no amount of reading a table ever will cause a U/X lock to be taken on a resource.

    "My friend is further suggesting that these X locks can potentially cause deadlocks because the sproc is becoming an LRQ long runing query so he is proposing a NOLOCK hint if dirty reads are allowed!"

    Exclusive locks in and of themselve do not cause dead locks, even in long running queries. If you are careful to hold locks as shortly as possible, and make sure to always take locks in the same order, you should be free from those pesky dead-locks.

    "What is the cost of S locks vs X locks as it relates to Perf? If dirty reads are allowed and there is a query that uses X locks and S locks is there more performance cost attributed to the X? I would not necessarily think so, the logic for set locks type should be pretty simple."

    Well, for the connection holding the lock there is no difference. It's equally expensive to get a shared lock as an exclusive one. But you might be locking other connections from the data when you hold X-locks.

    "Moreover, what is the gain in changing to IX, I would understand X if it is tyring to protect data?"

    Generally speaking, intent-locks are good for multi-user performance, and bad for single-user performance. But my advice would be that you let SQL Server worry about the size of the locks.

    Hope this helps,

    Andrés Taylor

    .

  • October 14, 2005 at 12:40 pm

    #597443

    Thanks Andres for your inputs.  I realized that I did not ask the right questions:-

    Why does SQL escalate an S lock to IX lock when there is no updates?

    Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply