Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.mixmin.net!rt.uk.eu.org!newsfeed.xs4all.nl!newsfeed2.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
MIME-Version: 1.0
In-Reply-To: <CAPTjJmpi8d28ahbba0LNQU8rY4r0EvwUmDS2zRMVsUn0tubtuQ@mail.gmail.com>
References: <CACwCsY7BWeV14+=OHbgYyfwV8qGkTv_nRC4e4+Ahxv3+RP+tng@mail.gmail.com> <CAPTjJmpi8d28ahbba0LNQU8rY4r0EvwUmDS2zRMVsUn0tubtuQ@mail.gmail.com>
Date: Thu, 17 Jul 2014 13:55:21 -0400
Subject: Re: Blocked thread
From: Larry Martell <larry.martell@gmail.com>
To: "python-list@python.org" <python-list@python.org>
Content-Type: text/plain; charset=UTF-8
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.11937.1405619727.18130.python-list@python.org>
Lines: 40
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:74655

On Thu, Jul 17, 2014 at 1:32 PM, Chris Angelico <rosuav@gmail.com> wrote:
> On Fri, Jul 18, 2014 at 2:26 AM, Larry Martell <larry.martell@gmail.com> wrote:
>> I have a python cx_Oracle script that does a delete from a table.
>> Usually this takes well under 1 second. But sometimes it takes 1 to 2
>> minutes. I wanted to monitor that delete and if it's taking too long I
>> want to see what is blocking it. I run the delete sql in a thread...
>
> I don't know Oracle specifically, but if it's anything like
> PostgreSQL, you'll probably do better with a completely separate
> connection to the server, which might need to be a separate process.
> In PostgreSQL, I can query currently-active transactions thus:
>
> rosuav=> select state,query from pg_stat_activity;
>         state        |                   query
> ---------------------+-------------------------------------------
>  idle in transaction | select * from pg_stat_activity;
>  active              | select state,query from pg_stat_activity;
>  active              | drop table test;
> (3 rows)
>
> (Better than that: Add "where pid=..." to that, using the backend PID
> provided by the thread you're monitoring, by "SELECT
> pg_backend_pid()". But that's even more PostgreSQL-specific.)
>
> With info like that, you can see what's happening, and whether it's
> stalled out or in a query or whatever. You should also be able to get
> some timestamps (Postgres can do that, I would be highly surprised if
> Oracle can't), such as when the transaction started, so you can see
> how long it's been stalled.
>
> Thing is, this requires a quite separate connection, which means
> you're monitoring the far end rather than the local thread. I suspect
> this will give you better results; Oracle's bound to have facilities
> for doing this, whereas your local thread may or may not be usefully
> monitorable.

I can have as many connections to the db server as I want, that's not
the issue. The issue is that my main thread seems to be blocked in the
join(), so I guess I'm going to need a separate script. I was trying
to avoid that.