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
– Wessie, Commented Sep 27, 2012 at 1:20
I am testing on selects only and postgres is in auto-commit mode. — Sergey Melekhin
– Sergey Melekhin, Commented Sep 27, 2012 at 2:01
What errors are you getting? Do you get them on a single thread? — Chris Travers
– Chris Travers, Commented Sep 27, 2012 at 13:39
@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
– Sergey Melekhin, Commented Sep 27, 2012 at 21:22

nagylzs · Accepted Answer · 2012-09-27 19:32:50Z

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, 2012 at 19:32

nagylzs

4,2406 gold badges49 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sergey Melekhin Over a year ago

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 Over a year ago

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.

nagylzs Over a year ago

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.

Collectives™ on Stack Overflow

py-postgresql multithread problems

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related