Hi, I'm maintaining a multi-threaded application which uses sqlalchemy to access mysql db. Some time ago we decided to upgrade sqlalchemy version from 1.1.18 to 1.2.14 and since then we started observing strange problems, the application started throwing IntegrityError in code like that:
translation = item.translation # translation is a relation if translation is None: session.add(Translation(item=item)) # this throws error Strange thing was that these errors happened rather rarely, there were a few hundred application processes running and only a few of them were throwing such errors. Restarting those processed made the errors vanish. I started debugging faulty processes and noticed that sql query generated looked like this: SELECT <truncated list of columns> FROM ebay_translations WHERE NULL = ebay_translations.item_id Further debugging led me to LazyLoader._emit_lazyload and BackedQuery thingy where I believe problem lays: if a thread switch occurs inside _memoized_attr__simple_lazy_clause it could happen that BindParameter keys and cached relation query will not match each other. Since that discovery I've been running the application with patched version of sqlalchemy for two months now, IntegrityErrors are gone. Can you please take a look at this patch? Or maybe this issue is known and there exists workaround for it although I searched the mailing list archive and found nothing. best regards Krzysztof Sulejczak -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py index d7597d3b2..d265f570b 100644 --- a/lib/sqlalchemy/orm/strategies.py +++ b/lib/sqlalchemy/orm/strategies.py @@ -8,6 +8,8 @@ """sqlalchemy.orm.interfaces.LoaderStrategy implementations, and related MapperOptions.""" +import threading + from .. import exc as sa_exc, inspect from .. import util, log, event from ..sql import util as sql_util, visitors @@ -446,7 +448,7 @@ class LazyLoader(AbstractRelationshipLoader, util.MemoizedSlots): '_lazywhere', '_rev_lazywhere', 'use_get', '_bind_to_col', '_equated_columns', '_rev_bind_to_col', '_rev_equated_columns', '_simple_lazy_clause', '_raise_always', '_raise_on_sql', - '_bakery') + '_bakery', '_lock') def __init__(self, parent, strategy_key): super(LazyLoader, self).__init__(parent, strategy_key) @@ -483,6 +485,8 @@ class LazyLoader(AbstractRelationshipLoader, util.MemoizedSlots): self.logger.info("%s will use query.get() to " "optimize instance loads", self) + self._lock = threading.Lock() + def init_class_attribute(self, mapper): self.is_class_level = True @@ -531,7 +535,8 @@ class LazyLoader(AbstractRelationshipLoader, util.MemoizedSlots): return criterion, params def _generate_lazy_clause(self, state, passive): - criterion, param_keys = self._simple_lazy_clause + with self._lock: + criterion, param_keys = self._simple_lazy_clause if state is None: return sql_util.adapt_criterion_to_null(