CODEWITHSUNDEEP
pythonpostgresqlsubprocessmultiprocessing
Dschoni
Dschoni

Reputation: 3862

Concurrency on PostgreSQL Database with python subprocesses

I use python multiprocessing processes to establish multiple connections to a postgreSQL database via psycopg.

Every process establishes a connection, creates a cursor, fetches an object from a mp.Queue and does some work on the database. If everything works fine, the changes are commited and the connection is closed.

If one of the processes however creates an error (e.g. an ADD COLUMN request fails, because the COLUMN is already present), all the processes seem to stop working.

import psycopg2
import multiprocessing as mp
import Queue

def connect():
    C = psycopg2.connect(host = "myhost", user = "myuser", password = "supersafe", port = 62013, database = "db")
    cur = C.cursor()
    return C,cur

def commit_and_close(C,cur):
    C.commit()
    cur.close()
    C.close()

def commit(C):
    C.commit()

def sub(queue):
    C,cur = connect()
    while not queue.empty():
        work_element = queue.get(timeout=1)
        #do something with the work element, that might produce an SQL error
    commit_and_close(C,cur)       
    return 0

if __name__ == '__main__':
    job_queue = mp.Queue()
    #Fill Job_queue
    print 'Run'
    for i in range(20):
        p=mp.Process(target=sub, args=(job_queue))
        p.start()

I can see, that processes are still alive (because the job_queue is still full), but no Network traffic / SQL actions are happening. Is it possible, that an SQL error blocks communication from other subprocesses? How can I prevent that happening?

Upvotes: 1

Views: 1304

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22893

As chance would have it, I was doing something similar today.

It shouldn't be that the state of one connection can affect a different one, so I don't think we should start there.

There is clearly a race condition in your queue handling. You check if the queue is empty and then try to get a statement to execute. With multiple readers one of the others could empty the queue leaving the others all blocking on their queue.get. If the queue is empty when they all lock up then I would suspect this.

You also never join your processes back when they complete. I'm not sure what effect that would have in the larger picture, but it's probably good practice to clean up.

The other thing that might be happening is that your error-ing process is not rolling back properly. That might leave other transactions waiting to see if it completes or rolls back. They can wait for quite a long time by default but you can configure it.

To see what is happening, fire up psql and check out two useful system views pg_stat_activity and pg_locks. That should show where the cause lies.

Upvotes: 1

Related Questions