innodb table updates locks all queries of other tables too

03-28-2008, 02:51 PM

Hi all,

i have created a procedure. It selects values from a myisam table and updates an innodb table one by one ( not as huge update it clearly use where clause). but whenever I run the procedure other statements(statements that selects,updates or inserts other tables too) waits for longtime until my procedure completes updates. any idea why it blocks. i tried start transaction and commint for every single update. but the result is same

DECLARE c10 CURSOR FOR SELECT TRIM(CONCAT(recipientname,'@',recipientdomain)) Email FROM bouncelog where bouncetype=bounce_type GROUP BY Email having count(*)>10;
DECLARE cnull CURSOR FOR SELECT TRIM(CONCAT(recipientname,'@',recipientdomain)) Email FROM bouncelog where bouncetype=bounce_type GROUP BY Email;
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '02000' BEGIN END;
select count(*) INTO total_bounces FROM bouncelog WHERE bouncetype=bounce_type;
IF (bounce_type=20) OR (bounce_type=21) OR (bounce_type=22) OR (bounce_type=23) OR (bounce_type=24) OR (bounce_type=40) OR (bounce_type=54) THEN
OPEN c10;
LOOP
FETCH c10 INTO email_var;
START TRANSACTION;
SELECT Email INTO email_var2 from ResUsers where Email=email_var FOR UPDATE;
CASE bounce_type
WHEN 20 THEN
UPDATE ResUsers SET Permission=220, Bounced=1,BadAddressDate=NOW() where Email=email_var AND BadAddressDate IS NULL;
SET bad_address = bad_address + ROW_COUNT();
WHEN 21 THEN
UPDATE ResUsers SET Permission=221, Bounced=1,BadAddressDate=NOW() where Email=email_var AND BadAddressDate IS NULL;
SET bad_address = bad_address + ROW_COUNT();
WHEN 22 THEN
UPDATE ResUsers SET Permission=222, Bounced=1,BadAddressDate=NOW() where Email=email_var AND BadAddressDate IS NULL;
SET bad_address = bad_address + ROW_COUNT();
WHEN 23 THEN
UPDATE ResUsers SET Permission=223, Bounced=1,BadAddressDate=NOW() where Email=email_var AND BadAddressDate IS NULL;
SET bad_address = bad_address + ROW_COUNT();
WHEN 24 THEN
UPDATE ResUsers SET Permission=224, Bounced=1,BadAddressDate=NOW() where Email=email_var AND BadAddressDate IS NULL;
SET bad_address = bad_address + ROW_COUNT();
WHEN 40 THEN
UPDATE ResUsers SET Permission=240, Bounced=1,BadAddressDate=NOW() where Email=email_var AND BadAddressDate IS NULL;
SET bad_address = bad_address + ROW_COUNT();
WHEN 54 THEN
UPDATE ResUsers SET Permission=254, Bounced=1,BadAddressDate=NOW() where Email=email_var AND BadAddressDate IS NULL;
SET bad_address = bad_address + ROW_COUNT();
END CASE;
COMMIT;
END LOOP;
CLOSE c10;k
END IF;