py-postgresql multithread problems

Question

I discovered that under heavy load my pyramid web app throws py-postgresql exceptions like postgresql.exceptions.ProtocolError. Some search revealed, that py-postgresql is not thread-safe and one connection can not be used by several threads concurrently.

I tried to make some sort of pooling mechanism, but I still get ProtocolErrors :(

What am I doing wrong?

First I create number of connection objects:

    for x in range(num_db_connections):
        self.pool.append(Connection(conn_string,x))

Each object in pool contains db_lock = threading.Lock() and a connection to database self.conn = postgresql.open( conn_string )

Then I try to acquire lock on a connection and do some work with it. This code can be executed by many threads concurrently, but i think no two threads can run work on one connection concurrently because of lock.

    time_start = time.time()
    while time.time() - time_start < self.max_db_lock_wait_time:
        for conn in self.pool:
            acquired = conn.db_lock.acquire(False)
            if acquired:
                try:
                        lst = conn.work()
                finally:
                    conn.db_lock.release()
                return lst
        time.sleep(0.05)
    raise Exception('Could not get connection lock in time')

Maybe there is flaw in my code, or I misunderstood the nature of "thread unsafety" of py-postgresql? Please, help me!

Are you properly committing changes before releasing the lock? — Wessie, Sep 27 '12 at 01:20
I am testing on selects only and postgres is in auto-commit mode. — Sergey Melekhin, Sep 27 '12 at 02:01
What errors are you getting? Do you get them on a single thread? — Chris Travers, Sep 27 '12 at 13:39
Have you tried to use [Psycopg](http://www.initd.org/psycopg/) instead? — javex, Sep 27 '12 at 13:45
@ChrisTravers i am getting py-postgresql ProtocolError exceptions. I dont get them when single threaded, and errors pop up on random threads when multithreaded. javex I tried, but discovered, that it prepares queries with string literals emmbedded in sql, instead of just binding parameters. It is just plain wrong. — Sergey Melekhin, Sep 27 '12 at 21:22

score 2 · Accepted Answer · answered Sep 27 '12 at 19:32

2

Are you sure that you are not using cursor objects outside your lock?

Just a suggestion: instead of using time.sleep() and "trying" to lock, use a Queue to pop/push connection objects from/into the pool. That is already thread safe, and it has a timeout parameter. Much more effective. Especially if you have many threads and just a few connections. (Those tiny sleep instructions add up, when you need to run 100 000 queries. All of them will increase your response times.)

answered Sep 27 '12 at 19:32

nagylzs

3,954
6
39
70

I was thinking about queue, but I intended to put queries, not connection objects into queue. I tried using queue as you described, but errors are same. – Sergey Melekhin Sep 27 '12 at 22:48
1

Thanks, you pointed me in right direction! I did not use cursors outside my lock, but it looks like I had implemented thread-unsafe connection object - it had cached prepared statements pool. I still don't know, why it's not thread safe, because one connection object should be used by one thread, but when I got rid of prep stmt cache - errors disappeared. – Sergey Melekhin Sep 27 '12 at 22:56
Glad that I could help. The DB API specification does not specify the validity of "prepared statement objects". But you can't be wrong if you assume that they work like cursors. So is you have thread safety level 2, then you can only use those prepared statements from the same thread where you have created them. – nagylzs Sep 28 '12 at 07:22

py-postgresql multithread problems

1 Answers1