Обсуждение: Is it allowed to reuse a connection on which another thread waits for notifications?

Hello,

Recently I started to use LISTEN and NOTIFY with psycopg2 and I'm
experiencing rare hangs of the application. I suspect my notification
handling logic may be incorrect, in particular, I started to wonder
whether it is OK to share a connection between a thread that listens
for a notification and a thread that sends a notification.

My notification thread executes:

cursor = connection.cursor() # this connection has ISOLATION_LEVEL_AUTOCOMMIT
cursor.execute("NOTIFY " + channel + ", %s", [message])
cursor.close()

My listening thread executes:

cursor = connection.cursor() # This is the same connection that is
used by the NOTIFY thread.
cursor.execute('LISTEN %s;' % (channel))
while True:
      if select.select([connection],[],[]) == ([],[],[]):
          continue
      connection.poll()
      while connection.notifies:
            notify = connection.notifies.pop()
            self.handle_notify(notify.channel, notify.payload)

Is this approach correct, or should I use separate connection to send
notifications? I know that in general connections are thread safe, but
it is still true if one of the threads calls selects() with the
connection or can this cause a deadlock?

Best regards,
Jan

On Wed, Mar 13, 2013 at 11:32 AM, Jan Wrobel <wrr@mixedbit.org> wrote:
> Hello,
>
> Recently I started to use LISTEN and NOTIFY with psycopg2 and I'm
> experiencing rare hangs of the application. I suspect my notification
> handling logic may be incorrect, in particular, I started to wonder
> whether it is OK to share a connection between a thread that listens
> for a notification and a thread that sends a notification.

It shouldn't be a problem, as long as you use separate cursors, as you
appear doing.

Are you positive you don't get the same locks using two different connections?


> My notification thread executes:
>
> cursor = connection.cursor() # this connection has ISOLATION_LEVEL_AUTOCOMMIT
> cursor.execute("NOTIFY " + channel + ", %s", [message])
> cursor.close()
>
> My listening thread executes:
>
> cursor = connection.cursor() # This is the same connection that is
> used by the NOTIFY thread.
> cursor.execute('LISTEN %s;' % (channel))
> while True:
>       if select.select([connection],[],[]) == ([],[],[]):
>           continue
>       connection.poll()
>       while connection.notifies:
>             notify = connection.notifies.pop()
>             self.handle_notify(notify.channel, notify.payload)
>
> Is this approach correct, or should I use separate connection to send
> notifications? I know that in general connections are thread safe, but
> it is still true if one of the threads calls selects() with the
> connection or can this cause a deadlock?

It's a scenario I've never explicitly tested, but there is no problem
I foresee out of it. You may be triggering a bug: if you manage to put
together a contained test case it would be great. A gdb stack trace of
the locked process would be helpful too.

Notifications may also be processed during the execute() in the other
thread: in this case they would be correctly queued into
connection.notify, but there would be nothing to wake the fd in the
select(). Uhm... why don't you try using a timeout in select()?

What psycopg version are you using? We have fixed a few multithread
issues in the 2.4.x releases.


-- Daniele

On Wed, Mar 13, 2013 at 12:56 PM, Daniele Varrazzo
<daniele.varrazzo@gmail.com> wrote:
> On Wed, Mar 13, 2013 at 11:32 AM, Jan Wrobel <wrr@mixedbit.org> wrote:
>> Hello,
>>
>> Recently I started to use LISTEN and NOTIFY with psycopg2 and I'm
>> experiencing rare hangs of the application. I suspect my notification
>> handling logic may be incorrect, in particular, I started to wonder
>> whether it is OK to share a connection between a thread that listens
>> for a notification and a thread that sends a notification.
>
> It shouldn't be a problem, as long as you use separate cursors, as you
> appear doing.
>
> Are you positive you don't get the same locks using two different connections?

I'll test this with two different connections and see if the problem
is still there.

>> My notification thread executes:
>>
>>[...]
>> Is this approach correct, or should I use separate connection to send
>> notifications? I know that in general connections are thread safe, but
>> it is still true if one of the threads calls selects() with the
>> connection or can this cause a deadlock?
>
> It's a scenario I've never explicitly tested, but there is no problem
> I foresee out of it. You may be triggering a bug: if you manage to put
> together a contained test case it would be great. A gdb stack trace of
> the locked process would be helpful too.

At the moment I don't have any useful stack trace. The problem occurs
about once a day on a web server that automatically restarts hanged
processes after 30s. I'll try to isolate the bug and collect a stack
trace.

> Notifications may also be processed during the execute() in the other
> thread: in this case they would be correctly queued into
> connection.notify, but there would be nothing to wake the fd in the
> select(). Uhm... why don't you try using a timeout in select()?

Is a reverse scenario possible? A notification thread waits for the
execute() result, but it is never woken up because listening thread is
woken up instead?

> What psycopg version are you using? We have fixed a few multithread
> issues in the 2.4.x releases.

I use 2.4.6

Thank you, I'll let you know when I have more info,
Jan