MySQL metadata Lock blocking issue

. years 4 Month 1 Day Saturday

add up the main library of a business 2 field, the business party feeds back to the - minutes later from the library also have been unable to see this new field.

in the slave on the execution Show Slave Status\g as

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/8F/AE/wKioL1jpofvAZ5rLAACG7KeIfl8174.png "title=" 1.png "alt=" Wkiol1jpofvaz5rlaacg7keifl8174.png "/>

show Porcesslist; such as:

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/8F/AF/wKiom1jpolSTz80TAABFYSU_9zo871.png "style=" float : none; "Title=" 2_ see king. png "alt=" Wkiom1jpolstz80taabfysu_9zo871.png "/>

2 View , you can see a large delay from the Alter the operation has been waiting for metadata Lock , in a blocked state.

Workaround:

Use SELECT * from Information_schema.innodb_trx\g find the problem that the transaction did not commit :

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/8F/AE/wKioL1jpolWh0fTHAAB3qSKGdVE990.png "style=" float : none; "title=" 3.png "alt=" Wkiol1jpolwh0fthaab3qskgdve990.png "/>

kill2359; kill this thread.

after killing this thread, Show Slave Status\g The master-slave delay dropped immediately, Show Processlist There is no lock-in status. "show slave status\g even with a lock, which is a short time system lock"

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/8F/AF/wKiom1jpolXC40KGAAB32F4w_W0201.png "style=" float : none; "title=" 4.png "alt=" Wkiom1jpolxc40kgaab32f4w_w0201.png "/>

If we use Zabbix's percona monitoring, we can adjust the threshold of the relevant trigger, such as:

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M02/8F/AF/wKiom1jpoz3Q1Bc1AAA3UpxcbHQ418.png "title=" 1.png "alt=" Wkiom1jpoz3q1bc1aaa3upxcbhq418.png "/>

The default is 100 on the template. Usually only ALTER TABLE or SELECT. For update operations such as this will cause lock, so the normal business situation lock thread more than 50 need to pay attention to the situation.

This article is from the "Vegetable Chicken" blog, please be sure to keep this source http://lee90.blog.51cto.com/10414478/1914222

MySQL metadata Lock blocking issue