# innodb table updates locks all queries of other tables too

**URL:** <https://forums.percona.com/t/innodb-table-updates-locks-all-queries-of-other-tables-too/691>\
**Category:** Other MySQL® Questions\
**Created:** [March 28, 2008, 1:21pm UTC](https://forums.percona.com/t/innodb-table-updates-locks-all-queries-of-other-tables-too/691 "2008-03-28T13:21:49Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![suzuki\_babu](https://avatars.discourse-cdn.com/v4/letter/s/3da27b/32.png) [@suzuki\_babu](https://forums.percona.com/u/suzuki_babu)\
**Post date:** [March 28, 2008, 1:21pm UTC](https://forums.percona.com/t/innodb-table-updates-locks-all-queries-of-other-tables-too/691/1 "2008-03-28T13:21:49Z")

</div>

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,‘&#64;’,recipientdomain)) Email FROM bouncelog where bouncetype=bounce\_type GROUP BY Email having count(_)\>10;  
DECLARE cnull CURSOR FOR SELECT TRIM(CONCAT(recipientname,‘&#64;’,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

---

<div class="post-metadata">

**Author:** ![debug](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/debug/32/772_2.png) [@debug](https://forums.percona.com/u/debug)\
**Post date:** [March 30, 2008, 8:19pm UTC](https://forums.percona.com/t/innodb-table-updates-locks-all-queries-of-other-tables-too/691/2 "2008-03-30T20:19:18Z")

</div>

Saravanan,

Can you please clarify, queries to which of tables are being blocked during execution of this procedure?

---

<div class="post-metadata">

**Author:** ![suzuki\_babu](https://avatars.discourse-cdn.com/v4/letter/s/3da27b/32.png) [@suzuki\_babu](https://forums.percona.com/u/suzuki_babu)\
**Post date:** [March 31, 2008, 4:54am UTC](https://forums.percona.com/t/innodb-table-updates-locks-all-queries-of-other-tables-too/691/3 "2008-03-31T04:54:26Z")

</div>

Hi debug,

My database has 100’s of table. The tables not related to the two tables I am using in my procedure also locks and waits for long time.

Saravanan

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [March 31, 2008, 6:47pm UTC](https://forums.percona.com/t/innodb-table-updates-locks-all-queries-of-other-tables-too/691/4 "2008-03-31T18:47:59Z")

</div>

How long does it take to run that stored procedure?  
And what load on the server do you get when running it?

My guess is that your stored procedure is consuming a lot of cpu and that is why you experience a slowdown in the DB access for other queries also.

Using cursors, especially ones where you are selecting recursively inside them can consume a lot of CPU.

And although I’m not entirely sure what changes you are performing , I’m pretty certain based on what I have understood of this query that it is possible to perform in one SQL statement.

So I would recommend that you try to change it into one SQL statement instead of running it as cursors. Should speed up things a lot.

---

<div class="post-metadata">

**Author:** ![suzuki\_babu](https://avatars.discourse-cdn.com/v4/letter/s/3da27b/32.png) [@suzuki\_babu](https://forums.percona.com/u/suzuki_babu)\
**Post date:** [March 31, 2008, 7:08pm UTC](https://forums.percona.com/t/innodb-table-updates-locks-all-queries-of-other-tables-too/691/5 "2008-03-31T19:08:19Z")

</div>

Hi,

Not only the procedure stops innodb queries. It seems whenever I run some huge queries or others like queries in procedures locks all queries. Its weird. I tried

innodb\_table\_locks=0 but it didnt work.

any idea.

Saravanan