Deadlock opinion in sql server

Sorry, I repeated your link, instead of linking to the german pageI found: http://www.insidesql.org/blogs/frankkalis/2005/09/20/deadlocks. It doesn't help much, if you don't speak german, but I just want to point to the origin and get my mistake straight.

Bye, Olaf.

In most part of the cases deadlocks occur because of non-optimized queries. I'd suggest to dive a little bit deeper in how sql server locking manager works - it will help you to understand why deadlocks happen and how to troubleshoot and detect them. Check this out: http://aboutsqlserver.com/2011/09/28/locking-in-microsoft-sql-server-table-of-content/

thank you for this. Can you please clarify on below scenario?


1. We have reports section in web application.
2. The 15 users run same report for that day approx. same time for last one month search criteria.
3. That report is having per day around 15000 records average.
4. But out of 15 users, 7 users didn't see report. They just close the browser
5.. After that in the morning, we found deadlock state as we are doing on retrieval i.e. SELECT only.

Question: the browser is closed but will report query still going on back end?
Suggestion: Any suggestion on aforesaid scenario?

A lot of things depend on the implementation/architecture of the system as well as transaction isolation levels. I'd say it's very rare when you have deadlocks based on selects - data modification is usually involved. We need to see deadlock graph to understand what happens.

One of the temporary "workarounds" I can suggest in your case is to switch database to read committed snapshot mode. It should be more or less seamless for the application (but introduce a little overhead for tempdb/row size). In this mode writers do not block readers so your select reports should not be blocked/deadlocked. Check http://aboutsqlserver.com/2011/08/25/locking-in-microsoft-sql-server-part-8-optimistic-transaction-isolation-levels/ for more details.

Hi dwkor,

Thanks. I'll check that. But FYI, it's kind of production database around 2 TB.

Also, can you please give your feedback on my aforesaid question as well?

Best Regards

Thanks