*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('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]<javascript:>
> > wrote:
>
>> On Thu, Aug 9, 2012 at 5:58 PM, Cal Leeming [Simplicity Media Ltd] <
>> [email protected] <javascript:>> 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]<javascript:>
>> .
>> To unsubscribe from this group, send email to
>> [email protected] <javascript:>.
>> 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 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.
