Re: Waiting for Table metadata lock for DWH

[Marco Compagnoni]

Thank you very much for you precious help.
I just launch your query during an alter table.

Unfortunately I cannot still understand who is locking the table considering that except my query, nobody else are using it.

Here the results.

OBJECT_TYPE|OBJECT_SCHE|OBJECT_NAME        |LOCK_TYPE          |LOCK_STATUS|THREAD_ID|PROCE..|PROCESSLIST_INFO                                                                                        
-----------|-----------|-------------------|-------------------|-----------|---------|-------|-------------------
TABLE      |sys        |innodb_lock_waits  |SHARED_READ        |GRANTED    |55507    |53901  |                                                                                                        
SCHEMA     |sys        |                   |INTENTION_EXCLUSIV |GRANTED    |55507    |53901  |                                                                                                        
TABLE      |performance|data_lock_waits    |SHARED_READ        |GRANTED    |55507    |53901  |                                                                                                        
SCHEMA     |performance|                   |INTENTION_EXCLUSIV |GRANTED    |55507    |53901  |                                                                                                        
TABLE      |performance|data_locks         |SHARED_READ        |GRANTED    |55507    |53901  |                                                                                                        
FUNCTION   |sys        |quote_identifier   |SHARED             |GRANTED    |55507    |53901  |                                                                                                        
FUNCTION   |sys        |format_statement   |SHARED             |GRANTED    |55507    |53901  |                                                                                                        
FUNCTION   |sys        |sys_get_config     |SHARED             |GRANTED    |55507    |53901  |                                                                                                        
TABLE      |sys        |sys_config         |SHARED_READ        |GRANTED    |55507    |53901  |                                                                                                        
TABLE      |performance|threads            |SHARED_READ        |GRANTED    |55507    |53901  |                                                                                                        
TABLE      |performance|metadata_locks     |SHARED_READ        |GRANTED    |55507    |53901  |                                                                                                        
GLOBAL     |           |                   |INTENTION_EXCLUSIV |GRANTED    |55682    |54076  |ALTER TABLE `municipalities`...
SCHEMA     |jlab       |                   |INTENTION_EXCLUSIV |GRANTED    |55682    |54076  |ALTER TABLE `municipalities`...
TABLE      |jlab       |municipalities     |SHARED_UPGRADABLE  |GRANTED    |55682    |54076  |ALTER TABLE `municipalities`...
BACKUP LOC |           |                   |INTENTION_EXCLUSIV |GRANTED    |55682    |54076  |ALTER TABLE `municipalities`...
TABLESPACE |           |jlab/municipalitie |INTENTION_EXCLUSIV |GRANTED    |55682    |54076  |ALTER TABLE `municipalities`...
TABLE      |jlab       |#sql-6e85_d33c     |EXCLUSIVE          |GRANTED    |55682    |54076  |ALTER TABLE `municipalities`...
TABLE      |jlab       |municipalities     |EXCLUSIVE          |PENDING    |55682    |54076  |ALTER TABLE `municipalities`...
TABLE      |bilb       |reports            |SHARED_READ        |GRANTED    |55244    |53718  |                                                                                                        
TABLE      |bilb_dwh_jl|dir_logs           |SHARED_READ        |GRANTED    |55244    |53718  |                                                                                                        
TABLE      |bilb       |parameters         |SHARED_READ        |GRANTED    |55244    |53718  |                                                                                                        
TABLE      |jlab       |invoice_head       |SHARED_READ        |GRANTED    |55251    |53725  |                                                                                                        
TABLE      |jlab       |servers            |SHARED_READ        |GRANTED    |55251    |53725  |                                                                                                        
TABLE      |jlab       |dir_customers      |SHARED_READ        |GRANTED    |55251    |53725  |                                                                                                        
TABLE      |jlab       |municipalities     |SHARED_READ        |GRANTED    |55251    |53725  |

Edited 1 time(s). Last edit at 09/05/2019 09:58AM by Peter Brawley.