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 <[email protected]> wrote: > *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 query until all concurrent transactions will commit > requested rows. They are conventional conflict resolution mechanisms for > databases. > > > > On Sunday, August 12, 2012 9:41:15 PM UTC+6, Cal Leeming [Simplicity Media > Ltd] 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 time we tried read committed isolation levels, it caused various PHP >> applications to break for an unknown reason - as it was in a production >> environment we had to instantly revert to save downtime, and after it was >> reverted the problem went away. Sadly no time time was put into finding the >> exact cause of why this broke. >> >> This also broke PHP applications to which we did not have any source code >> access, and caused some deadlocking problems - again, due to lack of source >> code we were unable to determine the root cause of the problem. >> >> In general, it seems that READ COMMITTED may break apps that execute >> database queries in a certain way. >> >> >> *B) Use "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" before every >> transaction (or apply to the session somehow).* >> >> It is not clear how this would integrate nicely into the ORM. Is there a >> cleaner way of ensuring a transaction isolation level is set to read >> commited, other than having to call the following before every transaction? >> >> >>> connection.cursor().execute('S**ET TRANSACTION ISOLATION LEVEL READ >> COMMITTED') >> >> You could apply the above to the session but, as above, I'm not sure how >> you'd ensure every db session had this query executed, other than doing it >> manually (which again, seems ugly). >> >> >>> connection.cursor().execute('S**ET SESSION TRANSACTION ISOLATION >> LEVEL READ COMMITTED') >> >> Should there perhaps be an additional ticket that raises the need to have >> a decorator that does this for us, or a settings.py attribute of some sort? >> >> >> *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 previously the app wouldn't even last 60 seconds >> without throwing an IntegrityError exception. >> >> So although this approach is not fool proof (as you detailed in your >> earlier threads), it has been close enough to prevent the problem from >> happening in our use case. >> >> >> *D) Use database level auto commit* >> >> Karen - could you clarify further on this, as I might have misunderstood. >> >> Looking at the docs, MySQL states that autocommit for transactions are >> enabled by default >> >> http://dev.mysql.com/doc/**refman/5.0/en/commit.html<http://dev.mysql.com/doc/refman/5.0/en/commit.html> >> >> I couldn't find any other mentioning of 'autocommit' in the MySQL docs >> - so I'm not sure this would have any impact? >> >> >> On Sat, Aug 11, 2012 at 11:29 PM, Karen Tracey <[email protected]> wrote: >> >>> On Thu, Aug 9, 2012 at 5:58 PM, Cal Leeming [Simplicity Media Ltd] < >>> cal.l...@**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<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': { >>>> 'autocommit': True, >>>> } >>>> >>>> Is that correct? >>>> >>> >>> No...that syntax is pulled out of a PostgreSQL doc note and I don't >>> think it would work with MySQL, though I am not entirely sure of that. >>> >>> Also I am not sure I would recommend a global DB level setting for this >>> -- you're dispensing with any transactions at that point, which may well be >>> inappropriate for an app that is having trouble with get_or_create. It's >>> very hard for Django to give explicit instructions for what is best to do >>> "in general" since it all depends on the needs of the application with >>> respect to transactions. I would say in general I'd recommend "read >>> committed" isolation level vs. database-level autocommit, but the ticket >>> noted that read committed "can break legacy apps" (why, I'm not sure, and >>> it doesn't explain), so for the sake of completeness I mentioned that >>> database level autocommit would also fix the race condition that exists in >>> get_or_create. >>> >>> I don't believe the doc can give a blanket "do this and your code will >>> work" statement here. It can say Django's own code in get_or_create >>> requires either that the transaction isolation level be set to "read >>> committed" or DB level autocommit be used. Whether that is best done for >>> the project globally via an init_command or only for certain requests via >>> explicit cursor commands (see http://groups.google.com/** >>> group/django-users/msg/**55fa3724d2754013<http://groups.google.com/group/django-users/msg/55fa3724d2754013>) >>> depends on the project itself and what else it is doing besides calling >>> get_or_create. >>> >>> Karen >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "Django developers" group. >>> To post to this group, send email to django-d...@**googlegroups.com. >>> To unsubscribe from this group, send email to django-develop...@** >>> googlegroups.com. >>> >>> For more options, visit this group at http://groups.google.com/** >>> group/django-developers?hl=en<http://groups.google.com/group/django-developers?hl=en> >>> . >>> >> >> -- > You received this message because you are subscribed to the Google Groups > "Django developers" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/django-developers/-/eyBFQG9jtmIJ. > > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/django-developers?hl=en. > -- You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.
