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('SET 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('SET 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
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] <
> [email protected]> 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 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) 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 [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.
