id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
9479	Infinite loop in QuerySet delete() using MySQL 5.0.44 with InnoDB and default repeatable read transaction isolation	jjpersch	nobody	"Given: MySQL 5.0.44, InnoDB engine, default repeatable read transaction isolation.

If two database transactions attempt to perform a query set delete() at the same time that reference the same object, the the second delete will loop forever issuing SELECT and DELETE statements.

See django/db/models/query.py, line 385:
{{{
#!python
        # Delete objects in chunks to prevent the list of related objects from
        # becoming too long.
        while 1:
            # Collect all the objects to be deleted in this chunk, and all the
            # objects that are related to the objects that are to be deleted.
            seen_objs = CollectedObjects()
            for object in del_query[:CHUNK_SIZE]:
                object._collect_sub_objects(seen_objs)

            if not seen_objs:
                break
            delete_objects(seen_objs)
}}}

The following mysql console logs demonstrates why this will loop forever in this case:

=== Schema ===
{{{
% mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 66944
Server version: 5.0.44-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table foo ( id int(11) not null auto_increment, primary key (id) ) engine=innodb;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into foo values ();
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values ();
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
}}}

=== Connection A ===
{{{
% mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 67571
Server version: 5.0.44-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from foo;
+----+
| id |
+----+
|  1 | 
|  2 | 
+----+
2 rows in set (0.00 sec)
}}}

=== Connection B ===
{{{
% mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 67642
Server version: 5.0.44-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from foo;
+----+
| id |
+----+
|  1 | 
|  2 | 
+----+
2 rows in set (0.00 sec)
}}}

=== Connection A ===
{{{
mysql> delete from foo where id = 1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from foo;
+----+
| id |
+----+
|  2 | 
+----+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
}}}

=== Connection B ===
{{{
mysql> delete from foo where id = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from foo;
+----+
| id |
+----+
|  1 | 
|  2 | 
+----+
2 rows in set (0.00 sec)

mysql> delete from foo where id = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from foo;
+----+
| id |
+----+
|  1 | 
|  2 | 
+----+
2 rows in set (0.00 sec)
}}}

Until the second transaction commits, it will continue to select the row that was deleted by the first transaction (and attempted to be deleted by the second transaction).
"		closed	Database layer (models, ORM)	1.0		fixed		Andrew Ball	Accepted	0	0	0	0	0	0