- Anssi
--
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.
--
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.
Cal Leeming
Technical Support | Simplicity Media Ltd
US 310-362-7070 | UK 02476 100401 | Direct 02476 100402
Available 24 hours a day, 7 days a week.
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).
--
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.
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?
On Aug 8, 2012 10:25 AM, "Cal Leeming [Simplicity Media Ltd]" <cal.l...@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 prevent the issue.
No, that's not sufficient. The crux of the problem is that the two get attempts within get_or_create are within the same transaction, and so to satisfy the requirements of REPEATABLE READ they *must* return the same results. So it's not enough just to start with a clean transaction; in order to prevent this you would need to commit or rollback between the two gets, *within* the get_or_create call. MySQL actually does handle this much correctly AFAICT. The weirdness is just that it defaults to RR instead of RC.
Resorting to database-level autocommit would be overkill (and might create some other issues), but it does solve this particular problem by adding the necessary commit in the one place it's needed.
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.
Sorry, please ignore that last message, I see now that you were referring to this:
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?
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?
To view this discussion on the web visit https://groups.google.com/d/msg/django-developers/-/eyBFQG9jtmIJ.
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 | INSERT INTO t VALUES (1, 2); | v SELECT * FROM t; empty set
SELECT * FROM t LOCK IN SHARE MODE; (it blocks here) COMMIT; --------------------- | 1 | 2 | --------------------- 1 row in set
SELECT * FROM t;
empty set
UPDATE t SET v=3 WHERE k=1; 1 row affected
SELECT * FROM t;
---------------------
| 1 | 3 |
---------------------
1 row in set
- Anssi