Re: #18557: get_or_create() causes a race condition with MySQL

2012-09-05 Thread Anssi Kääriäinen
On 5 syys, 10:09, lucky wrote: >                Session A                            Session B           > CREATE TABLE t (k INT, v INT, PRIMARY KEY (k)); > >            SET autocommit=0;                    SET autocommit=0; > time > |          SELECT * FROM t; > |          empty set > |        

Re: #18557: get_or_create() causes a race condition with MySQL

2012-09-05 Thread lucky
среда, 5 сентября 2012 г., 5:32:44 UTC+6 пользователь Anssi Kääriäinen написал: > > > > On 4 syys, 17:53, lucky wrote: > > *E) Use locking with SELECT query* > > According tohttp:// > dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html > > > > If you want to see the “freshest” stat

Re: #18557: get_or_create() causes a race condition with MySQL

2012-09-04 Thread Anssi Kääriäinen
On 4 syys, 17:53, lucky wrote: > *E) Use locking with SELECT query* > According tohttp://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html > >  If you want to see the “freshest” state of the database, use either the > READ COMMITTED isolation level or a locking read: >  SELECT * FROM t

Re: #18557: get_or_create() causes a race condition with MySQL

2012-09-04 Thread Cal Leeming [Simplicity Media Ltd]
Curious - I didn't know about that. At some point I will have to build a unit test for all these approaches, and see which comes out best - prob won't have time to sort this out for a few weeks though. Cal On Tue, Sep 4, 2012 at 3:53 PM, lucky wrote: > *E) Use locking with SELECT query* > Acco

Re: #18557: get_or_create() causes a race condition with MySQL

2012-09-04 Thread lucky
*E) Use locking with SELECT query* According to http://dev.mysql.com/doc/refman/5.5/en/innodb-consistent-read.html If you want to see the “freshest” state of the database, use either the READ COMMITTED isolation level or a locking read: SELECT * FROM t LOCK IN SHARE MODE; It locks the SELECT

Re: #18557: get_or_create() causes a race condition with MySQL

2012-08-13 Thread Anssi Kääriäinen
On 12 elo, 18:41, "Cal Leeming [Simplicity Media Ltd]" wrote: > *C) Execute a commit before each get_or_create() call* > > It is worth noting that committing the transaction prior to calling > get_or_create() has given our apps a 100% success rate in preventing this > race condition, where as prev

Re: #18557: get_or_create() causes a race condition with MySQL

2012-08-12 Thread Cal Leeming [Simplicity Media Ltd]
On Sun, Aug 12, 2012 at 4:41 PM, Cal Leeming [Simplicity Media Ltd] < cal.leem...@simplicitymedialtd.co.uk> wrote: > Based on all the responses given so far, here are the options available. > > Further feedback would be much appreciated. > > *A) Use READ COMMITTED as a global/my.cnf:* > > Last tim

Re: #18557: get_or_create() causes a race condition with MySQL

2012-08-12 Thread Cal Leeming [Simplicity Media Ltd]
Based on all the responses given so far, here are the options available. Further feedback would be much appreciated. *A) Use READ COMMITTED as a global/my.cnf:* Last time we tried read committed isolation levels, it caused various PHP applications to break for an unknown reason - as it was in a

Re: #18557: get_or_create() causes a race condition with MySQL

2012-08-11 Thread Karen Tracey
On Thu, Aug 9, 2012 at 5:58 PM, Cal Leeming [Simplicity Media Ltd] < cal.leem...@simplicitymedialtd.co.uk> wrote: > Sorry, please ignore that last message, I see now that you > were referring to this: > > https://docs.djangoproject.com/en/dev/ref/databases/#autocommit-mode > > So essentially, the

Re: #18557: get_or_create() causes a race condition with MySQL

2012-08-09 Thread Cal Leeming [Simplicity Media Ltd]
Sorry, please ignore that last message, I see now that you were referring to this: https://docs.djangoproject.com/en/dev/ref/databases/#autocommit-mode So essentially, the official documentation would state that to resolve this problem, you would use the following for your db settings: 'OPTIONS'

Re: #18557: get_or_create() causes a race condition with MySQL

2012-08-09 Thread Cal Leeming [Simplicity Media Ltd]
Thanks for the detailed explanation on this. Am I correct in assuming you are referring to the following decorator as being the fix for the problem? @transaction.autocommit def viewfunc(request): pass On Thu, Aug 9, 2012 at 3:55 AM, Karen Tracey wrote: > On Wed, Aug 8, 2012 at 9:25 AM, Ca

Re: #18557: get_or_create() causes a race condition with MySQL

2012-08-08 Thread Ian Kelly
On Aug 8, 2012 10:25 AM, "Cal Leeming [Simplicity Media Ltd]" < cal.leem...@simplicitymedialtd.co.uk> wrote: > > I'm not entirely sure that suggesting every query needs to be committed is the right way forward either, given that you only need to commit once before get_or_create() is called to preve

Re: #18557: get_or_create() causes a race condition with MySQL

2012-08-08 Thread Karen Tracey
On Wed, Aug 8, 2012 at 9:25 AM, Cal Leeming [Simplicity Media Ltd] < cal.leem...@simplicitymedialtd.co.uk> wrote: > I'm not entirely sure that suggesting every query needs to be committed is > the right way forward either, given that you only need to commit once > before get_or_create() is called

Re: #18557: get_or_create() causes a race condition with MySQL

2012-08-08 Thread Cal Leeming [Simplicity Media Ltd]
I'm not entirely sure that suggesting every query needs to be committed is the right way forward either, given that you only need to commit once before get_or_create() is called to prevent the issue. Could you expand on why you feel every query would need to be committed? Cal On Wed, Aug 8, 2012

Re: #18557: get_or_create() causes a race condition with MySQL

2012-08-08 Thread Karen Tracey
On Wed, Aug 8, 2012 at 8:13 AM, Cal Leeming [Simplicity Media Ltd] < cal.leem...@simplicitymedialtd.co.uk> wrote: > Does anyone else have any input at this stage?? > > It seems to me that the most appropriate way forward is a documentation > update which explains that get_or_create() is not atomic

Re: #18557: get_or_create() causes a race condition with MySQL

2012-08-08 Thread Cal Leeming [Simplicity Media Ltd]
Does anyone else have any input at this stage?? It seems to me that the most appropriate way forward is a documentation update which explains that get_or_create() is not atomically safe across all databases, and may need a commit() before hand, or a read isolation change (with warnings about both)

Re: #18557: get_or_create() causes a race condition with MySQL

2012-07-16 Thread Anssi Kääriäinen
On 16 heinä, 23:43, Ian Kelly wrote: > On Mon, Jul 16, 2012 at 2:18 PM, Cal Leeming [Simplicity Media Ltd] > > wrote: > > Okay - anyone else want to throw their thoughts at this? > > > Also - messing with the isolation levels on MySQL is really not a great > > idea.. it can cause all sorts of une

Re: #18557: get_or_create() causes a race condition with MySQL

2012-07-16 Thread Anssi Kääriäinen
On 16 heinä, 23:18, "Cal Leeming [Simplicity Media Ltd]" wrote: > Okay - anyone else want to throw their thoughts at this? > > Also - messing with the isolation levels on MySQL is really not a great > idea.. it can cause all sorts of unexpected behavior. The idea is that one could use different i

Re: #18557: get_or_create() causes a race condition with MySQL

2012-07-16 Thread Ian Kelly
On Mon, Jul 16, 2012 at 2:18 PM, Cal Leeming [Simplicity Media Ltd] wrote: > Okay - anyone else want to throw their thoughts at this? > > Also - messing with the isolation levels on MySQL is really not a great > idea.. it can cause all sorts of unexpected behavior. Just a thought -- I don't have

Re: #18557: get_or_create() causes a race condition with MySQL

2012-07-16 Thread Cal Leeming [Simplicity Media Ltd]
Okay - anyone else want to throw their thoughts at this? Also - messing with the isolation levels on MySQL is really not a great idea.. it can cause all sorts of unexpected behavior. Cal On Mon, Jul 16, 2012 at 7:28 PM, Anssi Kääriäinen wrote: > On 16 heinä, 14:00, "Cal Leeming [Simplicity Medi

Re: #18557: get_or_create() causes a race condition with MySQL

2012-07-16 Thread Anssi Kääriäinen
On 16 heinä, 14:00, "Cal Leeming [Simplicity Media Ltd]" wrote: > Hi guys, > > Okay so, this has been marked as wontfix in its current approach. > > The problem exists purely because of the way MySQL transactions and indexes > work - if you create a row that matches a unique index, it won't show u

[Django] #18557: get_or_create() causes a race condition with MySQL

2012-07-16 Thread Cal Leeming [Simplicity Media Ltd]
om: Django Date: Thu, Jul 12, 2012 at 10:25 PM Subject: Re: [Django] #18557: get_or_create() causes a race condition with MySQL To: Cc: django-upda...@googlegroups.com #18557: get_or_create() causes a race condition with MySQL -+-