What exactly do you mean by not using transactions? The Session always works within a transaction:
http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#managing-transactions I assume you are also using InnoDB tables. On Mon, Sep 2, 2013 at 1:19 PM, herzaso <herz...@gmail.com> wrote: > I do have it set as REPEATABLE READ. > However, I don't use transactions in sqlalchemy > > > On Monday, September 2, 2013 3:08:58 PM UTC+3, Simon King wrote: >> >> Do you know what transaction isolation level you are running at? The >> default apparently is "REPEATABLE READ": >> >> >> http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html#isolevel_repeatable-read >> >> The important sentence in that link is: >> >> All consistent reads within the same transaction read the snapshot >> established by the first read >> >> When you query the database for the first time, to see if the entity >> already exists, you are setting that initial snapshot. If you run the >> same query again (such as in your exception handler), you will get the >> same results, whether or not another connection has inserted a >> matching row in the meantime. >> >> Simon >> >> On Mon, Sep 2, 2013 at 12:54 PM, herzaso <her...@gmail.com> wrote: >> > I'm not sure what to make of the results: >> > On the first connection, I ran BEGIN and INSERT and both were >> > successful, >> > but when I tried the INSERT statement on the second connection, I got >> > "ERROR >> > 1205 (HY000): Lock wait timeout exceeded; try restarting transaction". >> > Running the same query on the first connection produced the required >> > result >> > which is "ERROR 1062 (23000): Duplicate entry" >> > After the ROLLBACK on the first connection, the INSERT statement worked >> > well >> > on the second connection >> > >> > Regarding your second remark, the answer is yes, the error was due to >> > the >> > unique constraint on those columns >> > >> > BTW: I'm working on MySQL >> > >> > On Monday, September 2, 2013 1:31:12 PM UTC+3, Simon King wrote: >> >> >> >> I don't really know the answer, but I'd be interested in the results >> >> of this experiment: >> >> >> >> Forget about SQLAlchemy for the moment, and start 2 plain SQL >> >> connections to your database. In the first, type something like the >> >> following: >> >> >> >> BEGIN; >> >> INSERT foo(bar, baz, qux) VALUES(1, 1, 1); >> >> >> >> Now in the second connection do the same. I assume it'll fail because >> >> of the duplicate values. >> >> >> >> Now in the first connection issue a "ROLLBACK". You should now be in a >> >> state where no matching row exists in the database, even though you >> >> received an error about constraint violations. >> >> >> >> The results you see may be different, depending on your transaction >> >> isolation level. (It may be that you don't get the constraint >> >> violation at all until you try to commit the second connection). >> >> >> >> Another thing you could look at: are you sure that the error you are >> >> getting is due to the unique constraint on bar/baz/qux, and not some >> >> other constraint in the database? >> >> >> >> Simon >> >> >> >> On Mon, Sep 2, 2013 at 8:45 AM, herzaso <her...@gmail.com> wrote: >> >> > I'm afraid it didn't solve my problem. >> >> > >> >> > Here is my updated method: >> >> > @classmethod >> >> > def get(cls, bar=None, baz=None, qux=None, **kwargs): >> >> > query = session.query(cls).\ >> >> > filter(cls.bar == bar).\ >> >> > filter(cls.baz == baz).\ >> >> > filter(cls.qux == qux) >> >> > >> >> > item = query.first() >> >> > updated = False >> >> > >> >> > if not item: >> >> > try: >> >> > with session.begin_nested(): # run inside a >> >> > SAVEPOINT >> >> > updated = True >> >> > item = cls(bar=bar, baz=baz, qux=qux, **kwargs) >> >> > session.add(item) >> >> > session.flush() >> >> > except sa.exc.IntegrityError: >> >> > item = query.first() >> >> > if not item: >> >> > raise Exception("invalidIntegrityError") >> >> > except: >> >> > raise >> >> > >> >> > if not updated: >> >> > for k, v in kwargs.iteritems(): >> >> > if getattr(item, k) != v: >> >> > setattr(item, k, v) >> >> > >> >> > return item >> >> > >> >> > With this code, i'm getting invalidIntegrityError. How is it >> >> > possible? >> >> > (it's also worth pointing out that this solution requires SA 0.8.2 >> >> > (otherwise, there is a problem with session.begin_nested) >> >> > >> >> > >> >> > On Tuesday, August 27, 2013 6:40:03 PM UTC+3, Michael Bayer wrote: >> >> >> >> >> >> I'm not a fan of catching integrity errors, i prefer to try to make >> >> >> sure >> >> >> they aren't going to happen, or if they are, they aren't a normal >> >> >> occurrence >> >> >> and the system is such that the particular operation can just fail >> >> >> (of >> >> >> course it depends on what it is). A problem with catching the >> >> >> integrity >> >> >> error due to concurrent, conflicting operations is that depending on >> >> >> backend >> >> >> and isolation level, you can't be totally sure when the error is >> >> >> going >> >> >> to >> >> >> get raised (e.g. serializable isolation vs. non). Also on a backend >> >> >> like >> >> >> Postgresql, the database can't recover the transaction after an >> >> >> integrity >> >> >> error unless you used a savepoint. >> >> >> >> >> >> But here you're doing the "concurrent transactions need row identity >> >> >> X", >> >> >> so maybe it is appropriate here. Here is a rough idea of a >> >> >> transactional >> >> >> pattern for that, noting this isn't tested: >> >> >> >> >> >> try: >> >> >> my_object = Session.query(MyClass).filter(....).one() >> >> >> except NoResultFound: >> >> >> try: >> >> >> with Session.begin_nested(): # run inside a SAVEPOINT >> >> >> my_object = MyClass(...) >> >> >> Session.add(my_object) >> >> >> Session.flush() >> >> >> except IntegrityError: >> >> >> my_object = Session.query(MyClass).filter(....).one() >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> On Aug 27, 2013, at 11:13 AM, herzaso <her...@gmail.com> wrote: >> >> >> >> >> >> Suppose we are looking at a race condition, do you also think this >> >> >> should >> >> >> be handled by catching the IntegrityError? >> >> >> If so, what should I do? only flush and do the operation again? >> >> >> >> >> >> On Tuesday, August 27, 2013 5:42:23 PM UTC+3, Michael Bayer wrote: >> >> >>> >> >> >>> the word "occasional" is very meaningful. It usually suggests race >> >> >>> conditions. Then with the word "tornado", the baysean filters >> >> >>> are >> >> >>> strongly leaning towards "race condition" at that point :). >> >> >>> >> >> >>> if an error is occurring only under volume then you have to revisit >> >> >>> where >> >> >>> race conditions can occur. >> >> >>> >> >> >>> On Aug 27, 2013, at 10:32 AM, herzaso <her...@gmail.com> wrote: >> >> >>> >> >> >>> I'm running a Tornado server without redundancy (only one process, >> >> >>> requests can arrive at the same time but will be handled one at a >> >> >>> time) >> >> >>> I do agree that for large volumes, catching the IntegrityError >> >> >>> would >> >> >>> be >> >> >>> better, but currently I am handling a single request at a time and >> >> >>> I >> >> >>> want to >> >> >>> fix this problem before I move on ... >> >> >>> >> >> >>> >> >> >>> On Tuesday, August 27, 2013 5:24:07 PM UTC+3, Simon King wrote: >> >> >>>> >> >> >>>> On Tue, Aug 27, 2013 at 2:31 PM, herzaso <her...@gmail.com> wrote: >> >> >>>> > On Tuesday, August 27, 2013 3:55:50 PM UTC+3, Simon King wrote: >> >> >>>> >> >> >> >>>> >> On Tue, Aug 27, 2013 at 1:40 PM, herzaso <her...@gmail.com> >> >> >>>> >> wrote: >> >> >>>> >> > I have a model with an ID column set as the primary key, >> >> >>>> >> > though >> >> >>>> >> > i'd >> >> >>>> >> > like >> >> >>>> >> > to >> >> >>>> >> > be able to identify records by 3 other columns. >> >> >>>> >> > For this situation, I've added a classmethod that will fetch >> >> >>>> >> > the >> >> >>>> >> > record >> >> >>>> >> > if >> >> >>>> >> > found or a new record if not. >> >> >>>> >> > The problem i'm having is that every once in a while, I get >> >> >>>> >> > IntegrityError >> >> >>>> >> > trying to flush a change >> >> >>>> >> > >> >> >>>> >> > class Foo(Base): >> >> >>>> >> > __table_args__ = (sa.UniqueConstraint('bar', 'baz', >> >> >>>> >> > 'qux'),) >> >> >>>> >> > >> >> >>>> >> > id = sa.Column(Identifier, sa.Sequence('%s_id_seq' % >> >> >>>> >> > __tablename__), >> >> >>>> >> > nullable=False, primary_key=True) >> >> >>>> >> > bar = sa.Column(sa.BigInteger) >> >> >>>> >> > baz = sa.Column(sa.BigInteger) >> >> >>>> >> > qux = sa.Column(sa.BigInteger) >> >> >>>> >> > a1 = sa.Column(sa.BigInteger) >> >> >>>> >> > a2 = sa.Column(sa.BigInteger) >> >> >>>> >> > >> >> >>>> >> > @classmethod >> >> >>>> >> > def get(cls, bar=None, baz=None, qux=None, **kwargs): >> >> >>>> >> > item = session.query(cls).\ >> >> >>>> >> > filter(cls.bar== bar).\ >> >> >>>> >> > filter(cls.baz == baz).\ >> >> >>>> >> > filter(cls.qux == qux).\ >> >> >>>> >> > first() >> >> >>>> >> > >> >> >>>> >> > if item: >> >> >>>> >> > for k, v in kwargs.iteritems(): >> >> >>>> >> > if getattr(item, k) != v: >> >> >>>> >> > setattr(item, k, v) >> >> >>>> >> > else: >> >> >>>> >> > item = cls(bar=bar, baz=baz, qux=qux, **kwargs) >> >> >>>> >> > >> >> >>>> >> > return item >> >> >>>> >> > >> >> >>>> >> > This is the code I use to add/update records: >> >> >>>> >> > >> >> >>>> >> > foo = Foo.get(**item) >> >> >>>> >> > session.merge(foo) >> >> >>>> >> > >> >> >>>> >> > I'm struggling with this problem for some time now, and would >> >> >>>> >> > appreciate >> >> >>>> >> > any >> >> >>>> >> > help ... >> >> >>>> >> > >> >> >>>> >> >> >> >>>> >> I'm not sure of the exact problem, but there are a couple of >> >> >>>> >> things >> >> >>>> >> that you could investigate. >> >> >>>> >> >> >> >>>> >> Firstly, session.merge returns a copy of the object, rather >> >> >>>> >> than >> >> >>>> >> adding the object that you supplied into the session. See >> >> >>>> >> http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#merging >> >> >>>> >> for >> >> >>>> >> details. >> >> >>>> >> >> >> >>>> >> Secondly, your "get" method sometimes returns objects that are >> >> >>>> >> already >> >> >>>> >> part of the session (if they were in the database), and >> >> >>>> >> sometimes >> >> >>>> >> objects that are not in the session. It would probably be more >> >> >>>> >> consistent to always return objects that are part of the >> >> >>>> >> session, >> >> >>>> >> by >> >> >>>> >> putting "session.add(item)" in your "else" clause. This would >> >> >>>> >> get >> >> >>>> >> rid >> >> >>>> >> of the need for session.merge(). (If you want to be able to use >> >> >>>> >> the >> >> >>>> >> "get" with non-global sessions, pass the session as a >> >> >>>> >> parameter.) >> >> >>>> >> >> >> >>>> >> Finally, if your session isn't auto-flushing, it would be >> >> >>>> >> possible >> >> >>>> >> for >> >> >>>> >> you to call "get" twice with the same parameters and get 2 >> >> >>>> >> different >> >> >>>> >> objects back. >> >> >>>> >> >> >> >>>> >> You may want to look at the UniqueObject recipe in the wiki: >> >> >>>> >> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject >> >> >>>> >> >> >> >>>> > Hi Simon, >> >> >>>> > Thanks for the fast reply. >> >> >>>> > >> >> >>>> > I tried adding session.add(item) and session.flush() in the else >> >> >>>> > clause in >> >> >>>> > the past but that didn't solve my problem. >> >> >>>> > I didn't however remove the merge, do you think that might be >> >> >>>> > the >> >> >>>> > problem? >> >> >>>> > >> >> >>>> > Regarding the flush, this code is part of an API server where a >> >> >>>> > scoped_session is committed after each change. I haven't changed >> >> >>>> > the >> >> >>>> > autoflush parameter, and as I understand the default value is >> >> >>>> > True >> >> >>>> > making a >> >> >>>> > flush before each commit or query. >> >> >>>> > >> >> >>>> > As for the UniqueObject recipe, thanks! Amazing that I never >> >> >>>> > found >> >> >>>> > it >> >> >>>> > searching for a cure. As I see it basically does the same ... >> >> >>>> > >> >> >>>> > I never managed to reproduce this bug on my development >> >> >>>> > environment. >> >> >>>> > It only >> >> >>>> > happens in my production environment. >> >> >>>> > Do you suppose adding a session.add and removing the merge will >> >> >>>> > solve >> >> >>>> > this >> >> >>>> > issue? >> >> >>>> > >> >> >>>> > Thanks, >> >> >>>> > Ofir >> >> >>>> >> >> >>>> It's difficult to say without knowing more about your system. For >> >> >>>> example, does your production system get multiple concurrent API >> >> >>>> requests, or are they serialised? If 2 requests can come in at >> >> >>>> approximately the same time and are handled by 2 different threads >> >> >>>> (or >> >> >>>> processes), then it is easy to imagine that the first handler will >> >> >>>> check the database, find that an entry doesn't exist, and create >> >> >>>> it. >> >> >>>> But before it flushes the change to the database (or even after it >> >> >>>> flushes, but before it commits, depending on your transaction >> >> >>>> isolation), the second handler will check for the same object, >> >> >>>> find >> >> >>>> it >> >> >>>> missing, and so create it. >> >> >>>> >> >> >>>> To track down problems like this, you could ensure that your >> >> >>>> development environment has the same thread/process behaviour as >> >> >>>> the >> >> >>>> production environment, then try submitting multiple concurrent >> >> >>>> requests to it. If you add "time.sleep" statements somewhere >> >> >>>> between >> >> >>>> the creation of the object and the commit of the transaction you >> >> >>>> will >> >> >>>> probably find it easier to trigger. >> >> >>>> >> >> >>>> To actually fix the problem, you could choose to only handle a >> >> >>>> single >> >> >>>> request at a time (fine if you don't expect a high volume of >> >> >>>> requests). If that's not acceptable, you could catch the >> >> >>>> IntegrityError and then re-process the request. >> >> >>>> >> >> >>>> Hope that helps, >> >> >>>> >> >> >>>> Simon >> >> >>> > > -- > 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 http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. -- 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 http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
