GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

innodb table updates locks all queries of other tables too

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • innodb table updates locks all queries of other tables too

    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

  • #2
    Saravanan,

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

    Comment


    • #3
      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

      Comment


      • #4
        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.

        Comment


        • #5
          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

          Comment

          Working...
          X