SQL escalating S lock to IX by itself and later Deadlocking

SQL escalating S lock to IX by itself and later Deadlocking

• MeetMe

  Yes, you could use nolock or readuncommitted hints. But it depends on your business needs and whether your application can tolerate some inconsistencies in the data. It is hard to tell without a repro why this particular escalation can be happening. It can also happen due to cursor type changes for example. You should try and identify the operation that is resulting in the lock conversion first. It could also be that you are updating and reading the same rows in a transaction. As I said we will need some sort of repro to pin down the exact problem.
  
  Hi *,
  
  I am seeing that SQL is escalating S (shows in query profiler as Intent S) to IX locks, why is this happening Is Sql trying to add some kind of perf tuning/gain!
  
  From my understanding of the theory what starts out as S lock where there are no updates should stay as S locks, or is that a rule that SQL follows if the S locks is applied 'too' many rows of the table and it takes to long it would escalate it to IX Even still confusing with IX what benefit/previledge do we gain as opposed to X lock, I thought intent X was used for hierachial purposes - its not the actual X lock
  
  IX locks appear more in Profile as the load is increased, an observation. My issue with SQL doing escalating is that the escalated IX locks are deadlocking with other legitmately updating sproc that require X locks.
  
  Another question, doing a nolock hint where I can afford dirty read should stop this particular deadlock since SQL cna't escalate it, is this a 'dumb' yet easy win for solving my deadlock issue
  
  Thanks for all your insights.
  
  Regards.
  
  Fola
  
  
  
  
  
  
  
  
  Link
  
  
• SQL escalating S lock to IX by itself and later Deadlocking