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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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).