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. says


    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.

  3. 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.

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

    Server ver: 5.1.48-community-log, Binlog ver: 4
    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?

  5. says

    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

  6. Amar says

    Hi Ovais Tariq,

    Could please help us in understanding better on queries could be written in different order to the binary log breaking locking semantics and contrary to serialization before MySQL 5.5.3


Leave a Reply

Your email address will not be published. Required fields are marked *