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 6
MohitPanditAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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.
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/
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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?
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.
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
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MohitPanditAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.