Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I've setup my databases so that anytime a procedure is blocked for more than 45 seconds the database notifies the DBA email. Is it bad practice to setup a way to auto kill the process that is doing the blocking? I'm assuming yes; however, waiting until an off hours DBA can get to a computer and fix the change seams problematic as well. Is there a better way to handle the blocking processes?

share|improve this question
You want to auto-kill every block that lasts 45 seconds? That does not seem wise to me at all. There are a lot of cases where you want blocking, and you don't necessarily just want to kill the source. You want to record it maybe, and troubleshoot it so that you can reduce the duration of the blocking (or maybe the blocking itself, depending on the situation), but I don't think you would ever want to blindly kill all blocks > 45 seconds. Sounds like giving even an ambulance a ticket for going through a red light. – Aaron Bertrand Mar 20 at 2:31
@AaronBertrand - Yeah felt unwise to me too. Thats why I asked. I'm trying to figure a solution that allows me to react quickly before the website starts timeing out on customers. This was the solution suggested by my boss. I put the brakes on till I could get some more research done. – Lumpy Mar 20 at 12:27

1 Answer

up vote 0 down vote accepted

To piggy back off @AaronBertrand, killing a process could cause transaction inconsistency and that would mean you are throwing out ACID compliance. Why not first record all queries that take over 45 seconds and then change your code design based off that?

There's a lot you can do to resolve locking/blocking issues from changing the isolation levels such as READ UNCOMMITED or using snapshot isolation (snapshot isolation is preferred as there's lots of caveats with READ UNCOMMITED, but it won't help in the case of writers blocking writers).

check out:

MCM Snapshot Isolation Intro Video

Good Start On Troubleshooting Locking And Blocking

share|improve this answer
Killing a session will not cause transactional inconsistency. If the session had a transaction open then MSSQLServer will roll it back. If the session was participating in a distributed transaction then MSSQLServer will vote "no" on the outcome and the DTC will roll it back. – Greenstone Walker Mar 20 at 6:36
1  
@GreenstoneWalker so you've never seen a batch aborted and the transaction not rolled back? (This can certainly happen in SQL Server 2012.) I've seen orphaned transactions - the system is good but, due to the complexity of error handling and transactions, it is not exactly perfect. Add in XABORT, savepoints and TRY/CATCH and I don't think you can say with any authority that every open transaction that is abandoned will be rolled back. – Aaron Bertrand Mar 20 at 12:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.