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