CODEWITHSUNDEEP
postgresqlflasksqlalchemyuwsgi
JDiMatteo
JDiMatteo

Reputation: 13170

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 :

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:

  1. stop the application
  2. sudo service postgresql restart
  3. start the application
  4. 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

Upvotes: 2

Views: 1860

Answers (1)

JDiMatteo
JDiMatteo

Reputation: 13170

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:

  1. do not create database connections until after the application is created, OR
  2. 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.

Upvotes: 3

Related Questions