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
here is my.cnf
user=mysql
port=3306
basedir=/usr/local/mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
Default to using old password format for compatibility with mysql 3.x
clients (those using the mysqlclient10 compatibility package).
old_passwords=1
tmpdir=/tmp
log-bin
log-error=/var/log/mysqld.log
log-slow-queries
server-id=1
max_connections=500
max_user_connections=300
table_cache=600
query_cache_size=128M
query_cache_limit=1M
join_buffer_size=2M
read_buffer_size=2M
innodb_buffer_pool_size=2G
innodb_additional_mem_pool_size=40M
innodb_log_file_size=256M
innodb_log_buffer_size=8M
max_allowed_packet=10M
following is the procedure
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;
Saravanan