Hi,
On Monday 21 March 2016 13:01:27
hcha...@medici.tv wrote:
>
> I agree with Karen that documenting that some functions don't work is not
> helping. Especially since this is Django's fault, not MySQL's. I mean,
> sure, MySQL has some very weird isolation modes, I definitely agree with
> Shai on that, but an ORM's job is to know all that, and to use the database
> in such ways that it is safe. I reckon those safe ways exist,
I disagree. The ORM cannot keep you safe against MySql's REPEATABLE READ.
> and that the
> problem in Django is not the MySQL default isolation level, but the
> DELETE-SELECT-INSERT way of updating M2Ms.
The reason for that, and the major point you seem to be missing, is that
Django 1.8's DELETE-SELECT-INSERT dance for updating M2M's is not reserved to
Django's internals; you can be quite certain that there are a *lot* of similar
examples in user code. And that user code, making assumptions that hold
everywhere except with MRR, are not buggy. It is MySql's documented behavior
that is insensible.
> Incidentally, to prove my point,
> this has been changed in Django 1.9 and data-loss doesn't happen anymore,
> in that same default isolation level.
>
That indeed seems to support your point, but it is far from proving it.
It is trivial to show that MRR simply isn't repeatable read:
create table yoyo (id int, age int, rank int);
insert yoyo values(1,2,3);
Now, using MySql's so-called REPEATABLE READ,
SESSION A SESSION B
==================================
SET autocommit=0; SET autocommit=0;
SELECT * FROM yoyo; SELECT age FROM yoyo;
(1,2,3) (2)
UPDATE yoyo set age=5;
(ok, 1 row affected)
UPDATE yoyo set rank=7;
(blocks)
COMMIT;
(ok)
(resumes)
(ok, 1 row affected)
SELECT age from yoyo;
(5)
Session B sees, in its reads, changes made by session A after B's transaction
started. This is called, in the SQL standard, "a non-repeatable read". This is
*exactly* the situation that REPEATABLE READ is required to prevent. Calling a
transaction isolation level that allows this "REPEATABLE READ" is a lie. At
the very least, MySql needs to change the name of this isolation level to
something less misleading.
> It seems that setting a READ COMMITTED default level would help in that
> particular schedule of operations that is described in the ticket, but I
> disagree that it will work always and not cause problems elsewhere. For
> example, given a different schedule of the same operations needed to update
> a M2M, (DELETE-SELECT-INSERT), under READ COMMITTED, data-loss could still
> happen (see attachment).
>
I can reproduce this. But frankly, I cannot understand it. Can you explain
what happens here? Point to some documentation? The way I understand it, the
DELETE from session B either attempts to delete the pre-existing record (in
which case, when it resumes after the lock, it should report "0 records
affected" -- that record has been already deleted) or the one inserted by
session A (in which case, the second SELECT in session B should retrieve no
records). In either case, the SELECT in session B should not be finding any
record which has already been deleted, because all deletions have either been
committed (and we are in READ COMMITTED) or have been done in its own
transaction. What is going on here?
The point of the above question, besides academic interest, is that this
behavior is also suspect of being a bug. And if it is, Django should try to
work around it and help users work around it, but not base its strategic
decisions on its existence.
>
> In my opinion, the fix should not be to set a different default isolation
> level, as that could trigger other problems that may be very hard to find.
> I was lucky to find this one. I think that Django should find sequences of
> operations that are proven to be safe under specific isolation levels.
Again: The burden of "finding operation sequences" on Django is relatively
small -- most ORM operations are single queries. Most of the burden created by
MySql's REPEATABLE READ falls on users, who should not be expected to deal
with such blatant violations of common standards and terminology.
Shai.