On Tue, Mar 5, 2013 at 6:38 AM, Shai Berger <s...@platonix.com> wrote: > I'm not sure what you mean by "unsafe". > > Version 1: code in the question > > rows = MyModel.objects.all() > for row in rows: > try: > MyModel.objects.get(photo_id=row.photo_id) > except: > row.delete() > > 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.
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. I don't have MySQL handy to test, 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. > 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. -- 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 django-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@googlegroups.com. Visit this group at http://groups.google.com/group/django-developers?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
