Re: Multi-threaded Remote Calls vs. Python imterpreter Lock

On 15/03/2008, Jozsef Szalay <jszalay-7JS02rSrduhWk0Htik3J/[email protected]> wrote:
> Hi,
>
> I've got an interesting problem and I'm wondering if there is a solution for
> it
>
> First I create a connection and get two cursors:
>
> con = psycopg2.connect("dbname=test user=test password=test)
> cursor = con.cursor(cursor_factory = psycopg2.extras.DictCursor)
> cursor2 = con.cursor(cursor_factory = psycopg2.extras.DictCursor)
>
> Then I start three threads, two of which execute a (very slow) SQL statement
> using the separate cursors. I also start a third thread that is doing
> additions and printouts in a tight loop (no sleeping).

Note that all cursors for a connection are sharing the database
connection, and execute queries in series, so I'd expect one thread to
block waiting for the query from the first thread to complete.  The
third thread should not get blocked by either of the first two.  Is
that what you expect to see?


> t1 = CursorThread(cursor)
> t2 = CursorThread(cursor2)
> t3 = RuniingCrazyThread()
>
> t1.start()
> t2.start()
> t3.start()
>
> .
> .
> .
>
> t1.join()
> t2.join()
> t3.join()
>
> When I run this against the local Postgresql engine I see that the third
> thread is not blocked. It is running happily while the cursor threads are
> waiting for Postgres to return the data.
>
> Now if I change the connect statement to target a remote host
>
> con = psycopg2.connect("dbname=test user=test password=test hosr=10.10.0.2)
>
> but everything else remains the same, the third thread is blocked until the
> cursor threads (or perhaps at least one of them) are done.
>
> Everything works fine if I change the setup by starting only one cursor
> thread. Unfortunately I need two threads both using the same transaction.
>
> Everything works also, if I create two connections and use one cursor within
> each. Again, I need to process data in the same transaction from multiple
> threads.
>
> So my questions are:
>
> 1)       Why does psycopg work differently when targeting a local and a
> remote Postgres?

It shouldn't.  It probably indicates that the GIL is being held where
it shouldn't be.


> 2)       More importantly, what can I do to force psycopg to release the
> global python lock when targeting the remote host in this situation?

The first thing I'll ask you to do is check whether the problem
presents itself when using the latest Subversion version (what will be
2.0.7).

If it is still a problem, a self contained test case would help here.
If you need to artificially delay a query, the pg_sleep() postgres
function can be used to delay the query.

Another thing that'd be helpful would be to know what the program is
doing when it hangs: if you can attach gdb to the process and get
backtraces for all threads, that would be helpful.

James.