August 20, 2014

MySQL 5.5 lock_wait_timeout: patience is a virtue, and a locked server

MySQL 5.5 lock_wait_timeout: patience is a virtue, and a locked server

MySQL 5.5 lock_wait_timeout: patience is a virtue, and a locked server

Like Ovais said in Implications of Metadata Locking Changes in MySQL 5.5, the hot topic these days is MySQL 5.6, but there was an important metadata locking change in MySQL 5.5.  As I began to dig into the Percona Toolkit bug he reported concerning this change apropos pt-online-schema-change, I discovered something about lock_wait_timeout that shocked me.  From the MySQL 5.5 docs for lock_wait_timeout:

This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000.

Translation: by default, MySQL 5.5 will “meta-block” for 1 year!  In my humble opinion, this is a bug, especially given the various subtle and sometimes quiet ways that metadata locking can lock the server as Ovais describes in his blog post.  The default for innodb_lock_wait_timeout, by comparison, is 50 seconds.  That’s reasonable, but 31536000 is not.  I would only set a timeout, or any kind of wait or interval value, to such a high value to play a practical joke on someone.

Second, and finally, if: “This variable specifies the timeout in seconds for attempts to acquire metadata locks,” then why isn’t it called metadata_lock_wait_timeout?

In conclusion: be careful when upgrading to MySQL 5.5 because the solstices may pass before your DLL statement completes.

Comments

  1. The timeout is so huge as MyISAM can’t do a rollback so it could end up in a unknown state. With InnoDB a rollback is okay as it will result in a known state.

  2. Daniël, interesting, but 1 hour wouldn’t have been sufficient to avoid this? Or 1 week? I doubt anyone has a legitimate transaction that lasts longer than a week, but if so I’d love to hear about it. :-)

  3. Kostja says:

    Hi Daniel,

    Your ALTER TABLE certainly doesn’t run for a year, but it can easily run for a week. In this case a 1-hour lock wait timeout can be a bug.

    lock_wait_timeout affects must affect all locks – in particular, it affects GRL and MyISAM locks. The fact that InnoDB doesn’t obey the setting of lock_wait_timeout is a bug in InnnoDB, which is a world in itself, not in lock_wait_timeout.

    It’s both a global and session variable. If your session doesn’t like the default, it can be changed. Or you can put the right default to your my.cnf – it will affect all your sessions. Setting a default value to any variable is always subjective, and the choice of 1 year was made to be compatible with 5.1, which had no such timeout. If any other choice was made, it would be criticized as well.

  4. Kostja says:

    And yes, the docs need to be updated to list explicitly which locks are affected. In my patch for multiple user level locks per session, lock wait timeout begins affecting user level locks just as well.

  5. Nitin Khullar says:

    Hi Daniel,

    I agree that value assigned to “lock_wait_timeout” is very high. But please note that this is just a default value and MySQL allows this value to be modified.

    Being a DBA, I think its a responsibility of any DBA who is configuring the server to ensure that he knows what he is configuring. Depending from case to case, DBA’s modify values for many system variables for various reasons and this value is just an addition to it. MySQL does not recommends to use this value (31536000) at all. This is just available by default. People can change it as necessary.

Speak Your Mind

*