• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

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
0
MohitPandit
Asked:
MohitPandit
  • 3
  • 3
  • 2
2 Solutions
 
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
 
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
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
 
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
 
MohitPanditAuthor Commented:
Thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now