qiyuef (10/14/2009)
Can you explain why there will be a deadlocks when SQL Server doesn't perform a lock escalation even when the batch size is high?Each process will lock different sets of key(rows) , I don't understand how deadlock could happen.
Thanks
Hi
I would request you to try out the example given in the article. It can be directly executed.
If you see the keys locked during the process, you'd find that there is some key which is locked in common by both the processes. Ideally that should not happen and it doesn't happen when the batch size is as small as 5 in the example. But for a relatively bigger batch size there is a conflict.
Thanks
Satish More