I ran the analyze table command on production mysql db without knowing it would prevent me from selecting the contents of the table. This caused production site to go down :( How long can it take for the lock to release? Also, would recreating the db from a backup solve the problem / get rid of the locks?
我在生产mysql db上运行了analyze table命令,却不知道它会阻止我选择表的内容。这导致生产站点出现故障:(锁定释放需要多长时间?另外,从备份重新创建数据库可以解决问题/摆脱锁定?
Please let me know.
请告诉我。
Thanks.
2 个解决方案
#1
1
ANALYZE TABLE quite clearly says 'During the analysis, the table is locked with a read lock for InnoDB and MyISAM'.
ANALYZE TABLE非常清楚地说'在分析过程中,表被锁定了InnoDB和MyISAM的读锁'。
You can KILL {connection number} in SQL to stop the command.
您可以在SQL中KILL {连接号}来停止命令。
Note: you probably should update to a more recent version of MySQL-5.6.
注意:您可能应该更新到更新版本的MySQL-5.6。
#2
1
ANALYZE TABLE waits to acquire a metadata lock. While it's waiting, any SQL query against the table waits for ANALYZE TABLE.
ANALYZE TABLE等待获取元数据锁。在等待时,对表的任何SQL查询都会等待ANALYZE TABLE。
ANALYZE TABLE is normally pretty quick, i.e. 1-3 seconds. But that quick operation doesn't start until it can acquire the metadata lock.
ANALYZE TABLE通常非常快,即1-3秒。但是,快速操作直到它可以获取元数据锁定才开始。
It can't acquire the metadata lock while you have long-running transactions going against the table. So if you want this to run faster, finish your transactions.
当您对表进行长时间运行的事务时,它无法获取元数据锁。因此,如果您希望此运行更快,请完成您的交易。
See my answer to MySQL failing to ALTER TABLE which is being actively written to for more information.
请参阅我对MySQL的回答,而不是ALTER TABLE正在积极编写以获取更多信息。
#1
1
ANALYZE TABLE quite clearly says 'During the analysis, the table is locked with a read lock for InnoDB and MyISAM'.
ANALYZE TABLE非常清楚地说'在分析过程中,表被锁定了InnoDB和MyISAM的读锁'。
You can KILL {connection number} in SQL to stop the command.
您可以在SQL中KILL {连接号}来停止命令。
Note: you probably should update to a more recent version of MySQL-5.6.
注意:您可能应该更新到更新版本的MySQL-5.6。
#2
1
ANALYZE TABLE waits to acquire a metadata lock. While it's waiting, any SQL query against the table waits for ANALYZE TABLE.
ANALYZE TABLE等待获取元数据锁。在等待时,对表的任何SQL查询都会等待ANALYZE TABLE。
ANALYZE TABLE is normally pretty quick, i.e. 1-3 seconds. But that quick operation doesn't start until it can acquire the metadata lock.
ANALYZE TABLE通常非常快,即1-3秒。但是,快速操作直到它可以获取元数据锁定才开始。
It can't acquire the metadata lock while you have long-running transactions going against the table. So if you want this to run faster, finish your transactions.
当您对表进行长时间运行的事务时,它无法获取元数据锁。因此,如果您希望此运行更快,请完成您的交易。
See my answer to MySQL failing to ALTER TABLE which is being actively written to for more information.
请参阅我对MySQL的回答,而不是ALTER TABLE正在积极编写以获取更多信息。