April 20, 2014

About Ovais Tariq

Ovais Tariq works as a Support Engineer for Percona. Before joining Percona, he worked for different types of companies ranging from a startup to a big media organization. He has worked in different roles, including that of a software engineer, development manager, DBA and consultant.

Comments

  1. marc castrovinci says:

    The above scenario is when you would see the “Waiting for table metadata lock” status, right?

    I’ve encountered this issue about every time I have to do a production DDL change. After a few times fighting it, I now have a script that will kill all sleeping threads over 1 second for the database i’m working on. If not the DDL hangs on a sleeping thread, and the reads after the DDL also hang.

  2. Bug #67873 is fixed in MariaDB 5.5.29, but CREATE doesn’t wait for DROP TABLE. https://mariadb.atlassian.net/browse/MDEV-3941

  3. Does pt-osc need to get updated because of these changes?
    https://code.launchpad.net/~percona-toolkit-dev/percona-toolkit/pt-osc-2.1.1

  4. Marc,

    Yes when queries are waiting for metadata locks, the thread state is “Waiting for table metadata lock” in the processlist. Probably we can have a new feature in Percona Server to tackle the issue that you face during DDLs.

  5. Mark,

    Indeed the current version of pt-osc needs to be updated because it does not currently tackle the condition where it has to wait on metadata locks, and hence I have reported the bug: https://bugs.launchpad.net/percona-toolkit/+bug/1113301

  6. Patryk,

    Thanks, for letting me know I will test out MariaDB 5.5.29

  7. zuoxingyu says:

    Ovais ,i’m very interesting in why you could get the “Note how ALTER is logged before the UPDATE, because ALTER did not block waiting for the transaction to commit.” example case but i can’t.

  8. zuoxingyu says:

    For my test,server version : 5.1.48-community-log ,table engine=innodb
    My steps :
    session1 session2

    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)

    mysql> update t1 set id=30 where id=3;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0
    alter table t1 add column title2 varchar(30) default ‘ook’; (hang)

    mysql> commit;
    Query OK, 0 rows affected (0.01 sec)
    mysql> alter table t1 add column title2 varchar(30) default ‘ook’;
    Query OK, 3 rows affected (24.20 sec)
    Records: 3 Duplicates: 0 Warnings: 0

    Events in binlog

    Info
    ————————————————————————
    Server ver: 5.1.48-community-log, Binlog ver: 4
    BEGIN
    use bruce; update t1 set id=30 where id=3
    COMMIT /* xid=18 */
    use bruce; alter table t1 add column title3 varchar(30) default ‘oook’

    As you see,alter table is logged after update,that’s correct.
    How can i get the same result as you ? Is there something wrong in my steps?

  9. Hi zuoxingyu,

    If you follow my test case you will see that I have started a transaction and issued a SELECT query and not a query that would hold any row lock. The point was to show the implications of a transaction not holding metadata locks on tables that have been queried in the transaction, which causes problems by breaking isolation.

    However, in your case what you are doing is that by executing an update query on the table, the transaction holds row locks on row with the id=3, which is what is preventing the ALTER from going through. So you are facing a different scenario in that you have row locks blocking the ALTER, while the post deals with metadata locks. If you can enable the InnoDB plugin on your 5.1 installation instead of using the built-in InnoDB, then you can further clarify to yourself that the ALTER in your test case is waiting on InnoDB row locks by setting innodb_lock_wait_timeout to 1 and then issuing the ALTER:

    – session 1
    start transaction;
    select * from t1 where id=3 for update;

    – session 2
    set innodb_lock_wait_timeout=1;
    alter table t1 engine=innodb;

    And the alter will timeout after approximately a second with the following error:
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Speak Your Mind

*