Deadlock opinion in sql server

Hi,

Can anyone give your opinion for Deadlock in SQL Server?

And if deadlock occurs then can we kill block processes as per below article

http://www.kodyaz.com/articles/identify-kill-blocking-sql-server-processes.aspx

Best Regards,
Mohit Sharma
LVL 5
MohitPanditAsked:
Who is Participating?
 
Olaf DoschkeSoftware DeveloperCommented:
In answer to your last question: A client closing the browser just disconnects from the web server, but that does not trigger sql server to stop processing the request from the web server. Even the web server does not detect right away that the clients browser did close. It's response just will not arrive anywhere. Client (browser), web server and sql server are strongly decoupled systems, from which the stability of eac profits, but closing the browser does not free web server and sql server resources in itself.

Bye, Olaf.
0
 
Olaf DoschkeSoftware DeveloperCommented:
I never had problems with deadlocks. While I only did one sql server application it is a rather large one with a large data and user base: ~50GB ~500 concurrent users using it all day. It's a custom software for research & development of cosmetics.

Just googling deadlock I find that sql server does automatically detect deadlocks and kills the process, for which the rollback of it's transaction poses the least overhead. So I wonder why you would like to do that manually. There is one way to influence how SQL Server does kill deadlocks via SET DEADLOCK_PRIORITY.

I also find a list of tips (translated from german - http://www.kodyaz.com/articles/identify-kill-blocking-sql-server-processes.aspx):

1. make sure your database is normalized
2. make suree the application aways accesses server objects in the same order.
3. Do not allow user input during a transaction, so collect all needed information beforehand.
4. avoid cursor
5. keep the transaction as short as possible. A way of achieving this is to minimize the round trips between application and sql server via stored procedures or to keep the transaction in a single batch. Another way to reduce the duration of a transaction is to make sure, you don't need to read the same data multiple times. I you need data more than once, try reading them into variables for caching and reading the variables instead.
6. minimize lock times. Try to develop the application, to lock as late as possible and to unlock as soon as possible. If applicable, lower the usage of ROWLOCK or PAGLOCK.
7. Take into account the NOLOCK hint, if the data, which is locked, isn't modified often.
8. Take into account the lowest isolation level to get to your goal to finish the transaction successfully.
9. Take into account the usage of "bound connections".

Before I did the forementioned SQL Server based database application I did a few others using other databases and can say I live by these ints or rules anyway, as far as they are not sql server specific, but valid in a more general way.

And as you asked for oppinion, I agree to the essence of all this: To not solve the deadlock problem directly, but investigae what leads to the deadlocks. All thiese hints say is, that deadlocks are just a consequence of working against the rules.

I can also image you would have deadlocks because your databases system is dimensioned much too low, but if scaling up does not minimise the problem you'll need to look into the reasons for the deadlocks in more detail.

Bye, Olaf.
0
 
Olaf DoschkeSoftware DeveloperCommented:
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.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
dwkorCommented:
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/
0
 
MohitPanditAuthor Commented:
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?
0
 
dwkorCommented:
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.
0
 
MohitPanditAuthor Commented:
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
0
 
MohitPanditAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.