Question

0

279

Views

UWSGI Flask SQLAlchemy Intermittent PostgreSQL errors with "WARNING: there is already a transaction in progress"

In my UWSGI Flask application I'm getting intermittent errors like the following :

DatabaseError: (psycopg2.DatabaseError) error with no message from the libpq
ResourceClosedError: This result object does not return rows. It has been closed automatically.
NoSuchColumnError: "Could not locate column in row for column 'my_table.my_column_name_that_exists'"
DatabaseError: (psycopg2.DatabaseError) insufficient data in "D" message...lost synchronization with server: got message type "2", length 740303471

In my postgresql log I see: WARNING: there is already a transaction in progress

Refreshing the web page in flask usually resolves the error.

Here are the steps I take to reproduce the error:

stop the application
sudo service postgresql restart
start the application
navigate to a web page in my flask app that does several simultaneous queries
- expected behavior: no database errors logged
- actual behavior: one or more of the errors listed above occur

I tried increasing the verbosity of postgresql logging and what appears to be inappropriate sharing of virtual transactions, e.g. following shows all log entries with virtual transaction 2/53 and corresponds to the above errors:

process 8548 session 5901589a.2164 vtransaction 2/53 LOG:  statement: BEGIN
process 8548 session 5901589a.2164 vtransaction 2/53 LOG:  statement: SELECT 1
process 8548 session 5901589a.2164 vtransaction 2/53 LOG:  statement: SELECT my_table.id AS my_table_id, ... 
    FROM my_table 
    WHERE my_table.id = 'my_id' 
     LIMIT 1
process 8548 session 5901589a.2164 vtransaction 2/53 LOG:  statement: BEGIN
process 8548 session 5901589a.2164 vtransaction 2/53 WARNING:  there is already a transaction in progress
process 8548 session 5901589a.2164 vtransaction 2/53 LOG:  statement: SELECT 1
process 8548 session 5901589a.2164 vtransaction 2/53 LOG:  statement: SELECT my_other_table.id AS my_other_table_id, ...
    FROM my_other_table
    WHERE 'my_other_id' = my_other_table.id
process 8548 session 5901589a.2164 vtransaction 2/53 LOG:  statement: SELECT my_table.id AS my_table_id, ... 
    FROM my_table 
    WHERE my_table.id = 'my_id' 
     LIMIT 1
process 8548 session 5901589a.2164 vtransaction 2/53 LOG:  statement: ROLLBACK

over 4 years ago · Santiago Trujillo

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-05-26T13:04:26.778Z

These errors are symptoms of database connections being shared incorrectly by multiple threads or processes.

By default, uwsgi forks the process after the application is created in the wsgi-file. If the application creation creates database connections that may be re-used, you will likely end up with forked processes having corrupt database state. To resolve this in uwsgi there are options:

do not create database connections until after the application is created, OR
call uwsgi with the --lazy-apps option, which changes uwsgi to fork before the application is created

There are negative performance consequences to lazy-apps mode (see preforking vs lazy-apps vs lazy), so avoiding database usage during app creation is generally the better option.

Thanks univerio for explaining this in the comments.

0

279

UWSGI Flask SQLAlchemy Intermittent PostgreSQL errors with "WARNING: there is already a transaction in progress"

1 answers

0

Find remote jobs

Andres GPT