Concurrrency issue with psycopg2.ThreadConnectionPool, uWSGI and Flask

Question

I'm using Flask, psycopg2 and uWSGI. I'm using psycopg2.ThreadConnectionPool for DB connection pooling and only cursor.callproc is used for quering DB.

The problem: sometimes, during concurrent requests, procedure call results are mixed up, the code is querying procedure_1 but is getting results for procedure_2 (and vice versa for another concurrent client). Even if in uwsgi.ini there is threads=1, and only processes are used for concurrency. maxconn for psycopg2.ThreadConnectionPool is set to 1. The problem disappears if processes is set to 1 in uwsgi.ini

What can cause the problem?

Here is the simplified code that reproduces the problem:

Flask app:

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import contextlib

import flask
import psycopg2
import psycopg2.pool


app = flask.Flask(__name__)


class ThreadConnectionPool(psycopg2.pool.ThreadedConnectionPool):

    @contextlib.contextmanager
    def item(self):
    close = True
    connection = self.getconn()
    connection.autocommit = True
    try:
        yield connection
        close = False
    finally:
        self.putconn(connection, close=close or connection.closed)


pool = ThreadConnectionPool(maxconn=1, minconn=1, **kwargs)  # kwargs are omitted here 

# sp_1 always returns 1, sp_2 always returns 2
EXPECTED = {'sp_1': 1, 'sp_2': 2}


@app.route('/main/', methods=['GET'])
def main_handler():
    procname = flask.request.args['procname']

    with pool.item() as connection:
        cursor = connection.cursor()
        cursor.callproc(procname)

        rows = cursor.fetchall()

        if rows[0][0] == EXPECTED[procname]:
            return 'OK\n'
        else:
            # procedure returned something that it shouldn't return
            return 'ERROR\n'


if __name__ == '__main__':
    app.run()

uwsgi.ini:

[uwsgi]
callable=app
chdir=/path/to/my/project/
module=mymodule
master=True
processes=4 ; if 1, there is no problem
socket=127.0.0.1:9000
threads=1

Procedures:

-- sp_1
BEGIN
    Return 1;
END;


-- sp_2
BEGIN
    Return 2;
END;

Reproducing (it happens not on every request, but rather often):

curl 'http://mymodule.local/main/?procname=sp_1' & curl 'http://mymodule.local/main/?procname=sp_2'
[1] 10636
ERROR
ERROR
[1]+  Done                    curl 'http://mymodule.local/main/?procname=sp_1'

Thanks in advance!

Answer 1

You are initializing the threadpool in the master, after it calls fork() (to generate workers) all will be messed up (unless you manage it). Ensure to initialize the pool one time per worker or use lazy-apps = true in uWSGI to load the app one time per worker.