Do you think it’s a good idea to add UPDLOCK hint to the table which will be modified in an update query? Many people will say NO because it seems to be redundant. But the fact is that sometime it removes deadlocks in your application.
Deadlock
Locking and Blocking (9) – Live Locks and Deadlocks
Deadlock can happen at latch level, memory, MARS, parallel query, etc. In this post, I am only going to talk about deadlocks at lock level. Resource A and B, when Session 1 locks Resource A and tries to get a lock on Resource B which has a incompatible lock held by Session2, Session 1 will wait until Session 2 releases locks on Resource B. This is called live lock. A live lock will be eventually resolved when the blocking session releases the lock or requesting lock gets timeout. If Session 2 requests a incompatible lock on Resource A at this moment, deadlock happens, because Session 1 will be waiting for the released lock on Resource B and Session 2 will be waiting for the released lock on resource A. Both sessions are deadly locked and blocked. I am going to give you few deadlock scenarios and solutions below.
Deadlock in Pooled Connections
When we talk about deadlock, most of time we thought it’s caused by incorrect resource accessing. That’s correct. Then you dive into your application code and hopefully find a root cause in T-SQL. In some situation, the dead lock happens in a random manner. The place even you think that it’s impossible to get a deadlock gets deadlock. In this case, you need to check whether you are using pooled connection properly.