Blocked thread

[Larry Martell]

On Thu, Jul 17, 2014 at 2:04 PM, Chris Angelico <rosuav at gmail.com> wrote:
> On Fri, Jul 18, 2014 at 4:00 AM, Larry Martell <larry.martell at gmail.com> wrote:
>> I did ask my DBA - he said "Blocking is a normal part of database
>> operations. It's only a problem when it's a deadlock, in which case
>> the server will detect that, kill one of the processes and log it."
>>
>> My situation is not a deadlock - there's just multiple scripts hitting
>> the same tables at the same time. When one gets done the next one
>> proceeds. I just want to find out what are the other processes causing
>> this.
>
> Then you definitely want server-side info. The DB server's the one who
> knows who's waiting on whom. See if your DBA can help you with the
> specifics; this will be bread-and-butter monitoring work for him.
> Automating it might require some tweaking, but if he's at all
> competent at his job, he'll know how to figure out (if manually)
> what's blocking an important query.

I know what SQL to run to see who is blocking whom. The issue is that
the scripts that are hitting this table run every 5 minutes 24/7 from
5 different locations (Chicago, New Jersey, London, Tokyo, and
Sydney).  99% of the time it's fine and the query takes under 1
second. But every once it a while it takes 2 minutes. It will be hard
to detect that manually. What I was trying to do in my threaded code
was detect when it was taking more than 5 seconds, and then start
monitoring it.

I think I just need a second script that is always running and when it
finds blocking, log it to a file. I was trying to avoid that and deal
with it from within.