Hi Ian, Aymeric and all, I stand corrected.
On Tuesday 05 March 2013, Ian Kelly wrote: > On Tue, Mar 5, 2013 at 6:38 AM, Shai Berger <[email protected]> wrote: > > > > When Django checks for a second record on get() it reads the second > > record, so a repeatable-read transaction would acquire an exclusive read > > lock on it. This makes it impossible for another transaction to delete > > the second row before the first finishes. > This statement was based in part on my previous understanding of transaction isolation levels, and in part on the Wikipedia article about them[1], which explicitly names these locks (apparently, following the SQL Stansard). > SELECT statements without "FOR UPDATE" do not generally acquire > exclusive locks to my knowledge, even under serializable or repeatable > read isolation levels. That would be a major issue for parallel > requests if they did. Also, I don't think there's any distinction > between exclusive read or exclusive write locks; row-level locks are > either just exclusive or shared. > > I just verified that, at least in PostgreSQL, the logic above works in > the serializable isolation level but can result in data loss in the > repeatable read isolation level. A deeper look in the wikipedia article finds a SIGMOD article[2] which analyzes isolation levels and, indeed, shows that such anomalies are allowed by the Postgres "Repeatable Read" level (which they name "snapshot isolation"). They also note in passing, that by SQL-92's definitions, this also passes for serializable. I note, also, that the stricter (more correct) "serializable" definition is new even in Postgres -- only since 9.1[3]; before that, Postgresql's "serializable" was really just "snapshot isolation". > I don't have MySQL handy to test, I do, and -- as might be expected in view of the above -- it allows the bad updates in repeatable-read (the default) as well as serializable isolation levels. So -- I'm giving up on this. As Aymeric noted, on Postgres and Oracle there's no harm done, and MySql, as exemplified here, undermines correctness anyways; if you are writing your code naively, the change may break your correct code in some spot, but it is likely already broken in a dozen others. One clarification (though I still got it wrong) -- > > Version 2: code in the first answer. > > > > for row in MyModel.objects.all(): > > if MyModel.objects.filter(photo_id=row.photo_id).count() > 1: > > row.delete() > > > > To the best of my understanding, a repeatable-read transaction gets a > > read lock on all records participating in a count, so again, nobody can > > delete the other records before the transaction finishes. > > But with any kind of autocommit on, the transaction ends after the > row.delete(), unlocking the remaining rows and allowing them to then > be deleted. according to [3], in PG/Serializable the count selection would place a "predicate lock" on all counted records, preventing wrongful deletes even with (current django) autocommit on. But not in repeatable-read, and I don't have a PG available to verify. I'm leaving Ian's test description in the tail of this message for reference. Sorry for the disruption, Shai. [1] http://en.wikipedia.org/wiki/Isolation_(database_systems) [2] "A Critique of ANSI SQL Isolation Levels", http://www.cs.umb.edu/~poneil/iso.pdf [3] http://www.postgresql.org/docs/9.1/static/transaction-iso.html -- > and Oracle and sqlite3 support serializable and read committed but not > repeatable read. I started two transactions in separate psql shells. > The starting data was: > > id | photo_id > ----+---------- > 1 | 1 > 2 | 1 > > The first transaction ran: > > BEGIN; > SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; > SELECT * FROM TEST WHERE photo_id = 1; > DELETE FROM TEST WHERE id = 1; > COMMIT; > > The second transaction ran the same thing but deleted id 2 instead. > The individual statements were interleaved (so both queries returned > two rows in the SELECT before either query deleted anything). The end > result was that both rows were deleted. Trying the same thing in the > serializable isolation level results in this error when attempting to > commit the second transaction: > > ERROR: could not serialize access due to read/write dependencies > among transactions > DETAIL: Reason code: Canceled on identification as a pivot, during > commit attempt. > -- You received this message because you are subscribed to the Google Groups "Django developers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/django-developers?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
