<p dir="auto">Hi,</p>
<p dir="auto">Thank you so much for creating SQLAlchemy!</p>
<h3 dir="auto">Summary</h3>
<p dir="auto">We are currently seeing the following error:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(mysql.connector.errors.IntegrityError) 1062 (23000): Duplicate entry '1-62' for key 'user_id_item_id'
[SQL: INSERT INTO item_user_associations (user_id, item_id, extra_data) VALUES (%(user_id)s, %(item_id)s, %(extra_data)s)]
[parameters: {'user_id': 1, 'item_id': 62, 'extra_data': 'hello world!'}]
(Background on this error at: http://sqlalche.me/e/gkpj)"><pre class="notranslate"><code class="notranslate">sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(mysql.connector.errors.IntegrityError) 1062 (23000): Duplicate entry '1-62' for key 'user_id_item_id'
[SQL: INSERT INTO item_user_associations (user_id, item_id, extra_data) VALUES (%(user_id)s, %(item_id)s, %(extra_data)s)]
[parameters: {'user_id': 1, 'item_id': 62, 'extra_data': 'hello world!'}]
(Background on this error at: http://sqlalche.me/e/gkpj)
</code></pre></div>
<p dir="auto">We believe the error occurs when users send two POST requests to our API containing similar data in very close succession. However, the issue is reproducible without networking.</p>
<p dir="auto">Here is an example PyCharm project to reproduce the issue:<br>
<a href="https://github.com/sqlalchemy/sqlalchemy/files/4032490/AssociationErrorExample-test.zip">AssociationErrorExample-test.zip</a></p>
<h3 dir="auto">Steps to Repro</h3>
<ol dir="auto">
<li>Get a local copy of MySQL running:
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content=" docker run --name my_db \
      -e MYSQL_DATABASE=my_db \
      -e MYSQL_USER=user \
      -e MYSQL_PASSWORD=password \
      -p 3306:3306 -d mysql/mysql-server:5.7"><pre class="notranslate"><code class="notranslate"> docker run --name my_db \
      -e MYSQL_DATABASE=my_db \
      -e MYSQL_USER=user \
      -e MYSQL_PASSWORD=password \
      -p 3306:3306 -d mysql/mysql-server:5.7
</code></pre></div>
</li>
<li>Run the "Compound" run configuration from PyCharm:
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="Run &gt; Run 'Compound'"><pre class="notranslate"><code class="notranslate">Run &gt; Run 'Compound'
</code></pre></div>
This executes the "example" run configuration 8 times in parallel.</li>
<li>As an alternative you can execute example.py in multiple terminal windows.</li>
</ol>
<p dir="auto">Expected: No error.</p>
<p dir="auto">Actual: You see an <code class="notranslate">sqlalchemy.exc.IntegrityError</code> error in at least one of the outputs.</p>
<h3 dir="auto">Workaround</h3>
<p dir="auto">We've been doing something like this to overcome this issue:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="@contextmanager
def session_scope():
    &quot;&quot;&quot;
    This code is taken from the final example here:
    https://docs.sqlalchemy.org/en/13/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it
    &quot;&quot;&quot;
    session = Session()
    try:
        yield session
        session.commit()
    except exc.InternalError:
        session.rollback()
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()"><pre class="notranslate"><code class="notranslate">@contextmanager
def session_scope():
    """
    This code is taken from the final example here:
    https://docs.sqlalchemy.org/en/13/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it
    """
    session = Session()
    try:
        yield session
        session.commit()
    except exc.InternalError:
        session.rollback()
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()
</code></pre></div>
<h3 dir="auto">Questions</h3>
<p dir="auto">Is there a built in SQLAlchemy mechanism or recommended best practice to overcome this issue?</p>
<p dir="auto">Please let me know. Thanks!</p>
<p dir="auto">-Nick</p>
<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">We have some code with a one-to-many relationship that we want to normally populate in an eager manner.  The relationship definition had the attribute lazy="subquery" to achieve this.</p>
<p dir="auto">If a query is then written that attempts to explicitly load the one-to-many collection and its backref eagerly, then you can trigger the warning: SAWarning: Multiple rows returned with uselist=False for eagerly-loaded attribute '...'.  Clearly there is redundant complexity here, the query should not need to duplicate the eager loading behaviour specified by the lazy="subquery" attribute.  But I don't think that it should cause a problem either: more complex queries that want to join against additional tables need to introduce this complexity.</p>
<p dir="auto">We found the behaviour differs between SQLAlchemy 0.6 and 0.7 (0.7 generates the warning in a greater variety of situations).  This has caused problems trying to run some of our code under SQLAlchemy 0.7, while maintaining compatibility with SQLAlchemy 0.6.  We have found a solution, but 3/4s of the combinations of query and lazy attribute we tried trigger the warning on one version of SQLAlchemy or the other.  This is probably easiest explained by the attached code, but I'll try and explain it here too.</p>
<p dir="auto">With the relationship specifying lazy="subquery" we found that SQLAlchemy 0.6 could correctly run queries that used the 'eagerload' option in the query, while 0.7 would generate the warning.  Rewriting the query to use an outerjoin makes the code work without a warning in SQLAlchemy 0.7 but generates the warning in SQLAlchemy 0.6.  Switching the relationship from lazy="subquery" to lazy="joined" allowed the outerjoin style query to work in both versions of SQLAlchey, while the eagerload form of the query generates a warning in SQLAlchemy 0.7.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/2331/lazy-attribute-issue.py">lazy-attribute-issue.py</a></p>