Issue 1:
Hi,

Thank you so much for creating SQLAlchemy!

Summary

We are currently seeing the following error:

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)

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.

Here is an example PyCharm project to reproduce the issue:

AssociationErrorExample-test.zip

Steps to Repro

Get a local copy of MySQL running:

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

Run the "Compound" run configuration from PyCharm:

Run > Run 'Compound'

This executes the "example" run configuration 8 times in parallel.

As an alternative you can execute example.py in multiple terminal windows.

Expected: No error.

Actual: You see an sqlalchemy.exc.IntegrityError error in at least one of the outputs.

Workaround

We've been doing something like this to overcome this issue:

@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()

Questions

Is there a built in SQLAlchemy mechanism or recommended best practice to overcome this issue?

Please let me know. Thanks!

-Nick

Issue 2:
Migrated issue, originally created by Anonymous

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.

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.

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.

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.

Attachments: lazy-attribute-issue.py