List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 14 1999 5:16pm
Subject:Re: update goes into infinite loop
View as plain text  
Chris Bare wrote:
> 
> I observed this problem on 3.22.14b-gamma, so I just downloaded
> 3.22.23b and I still see the same problem.
> 
> The following sequence of commands appear to send mysql into an
> infinite loop. Is this update statement invalid in some way? It
> seems to work most of the time, but some times it goes into a loop.
> When I shut it down and restart, the value for layer is very large.
> Is there another way to do that that won't cause an infinite loop?
> 
> create table cut2 (
> cut int(11) unsigned NOT NULL,
> layer int(11) unsigned NOT NULL DEFAULT 1,
> production_id int(11) unsigned NOT NULL,
> index (production_id, cut, layer),
> index (cut)
> );
> 
> insert into cut2 (production_id, cut, layer) values (1, 1, 1);
> insert into cut2 (production_id, cut, layer) values (1, 2, 1);
> insert into cut2 (production_id, cut, layer) values (1, 2, 2);
> insert into cut2 (production_id, cut, layer) values (1, 2, 3);
> insert into cut2 (production_id, cut, layer) values (1, 2, 4);
> insert into cut2 (production_id, cut, layer) values (1, 2, 5);
> insert into cut2 (production_id, cut, layer) values (1, 3, 1);
> insert into cut2 (production_id, cut, layer) values (1, 4, 1);
> insert into cut2 (production_id, cut, layer) values (1, 4, 2);
> insert into cut2 (production_id, cut, layer) values (1, 4, 3);
> insert into cut2 (production_id, cut, layer) values (1, 5, 1);
> insert into cut2 (production_id, cut, layer) values (1, 6, 1);
> insert into cut2 (production_id, cut, layer) values (1, 7, 1);
> insert into cut2 (production_id, cut, layer) values (1, 8, 1);
> insert into cut2 (production_id, cut, layer) values (1, 9, 1);
> 
> update cut2 set layer = layer + 1 where production_id = 1 and cut = 4
> and layer >= 3;
> 
> --
> Chris Bare                                Metro Link Incorporated

Hi Chris

You update a column, which is in the index used to solve the where part of your update. 
This has the effect, that the updated row will be sorted to another place in the index.
So this updated row will be found a second time, be updated, new sorted into the index,
found again, .....

I would break the use of that index with:
UPDATE
	cut2
SET
	layer = layer + 1
WHERE
	production_id + 0 = 1   /* <-- now no index used! */
	AND cut = 4
	AND layer >= 3;

Tschau
Christian

Thread
update goes into infinite loopChris Bare14 Jun
  • Re: update goes into infinite loopChristian Mack14 Jun