i am developing a website where huge anount of users are loging and do the transactions. every transactions are under sql transactions. what is going on is that everyday 10 to 15 times deadlock uccures and cpu usage will go to 100% and website will go down. then we have to restart the iis. what will be the solution of this. can read_committed_snapshot isolation level will help
Deadlocks are primarily an artifact of poor performance. You're taking out locks on the system for too long. Or you're taking out too many locks because of scans. In general, the best solution to deadlocks is to tune your queries and configure your indexes in order to ensure maximum performance. You also need to worry about the order in which objects within the system are accessed by different queries. Those are your best tools for fixing deadlocks. And yes, using a snapshot isolation level can help because it will separate the locks needed for writes from the reads, but it's not a magic bullet. You'll still need to do the first thing which is tune your system.