Kentoku Shiba (author of Spider storage engine) suggests metadata_lock_info plugin:
This plugin makes it possible knowing "who has metadata locks". In development stage, sometimes DBAs meet metadata locks when using alter table statement. This metadata locks are sometimes caused by GUI tools. In service stage, sometimes DBAs meet metadata locks when using alter table statement. This metadata locks are sometimes caused by long batch processing. In many cases, the DBAs need to judge immediately. So I made it for all DBAs.
Plugin is available at:
lp:~kentokushiba/maria/10.0.3-metadata_lock_info
SELECT GET_LOCK('abc',1000);
select * from METADATA_LOCK_INFO
you will get DB = 'abc', that's not a true information, the 'abc' is the key of the MDL lock, not the database (DB), maybe we should have a "KEY" and a "SUB_KEY" here? example... DB and TABLE, when a table lock, or GET_LOCK key name and '' for sub key
maybe we could change somethings...
instead of "ID", put "THREAD_ID", since it's not a lock id and it's not the query id, (if there's a global unique mdl id could be nice put it here)
instead of "DB" put "KEY"?
instead of "TABLE" put "SUB KEY"?
from mdl.h
@remark The key for a table is <mdl_namespace><database name><table name>
maybe we should call LOCK_TYPE as "MDL_NAMESPACE" ? but it don't tell what name is used for GET_LOCK for example...
from mdl.cc, why not use "m_namespace_to_wait_state_name" names, or maybe add this information to mdl.h ? the "lock_type" (namescape) don't have a name in mdl.h yet, maybe should be nice one there?
{0, "Waiting for stored function metadata lock", 0}
,
{0, "Waiting for stored procedure metadata lock", 0}
,
{0, "Waiting for trigger metadata lock", 0}
,
{0, "Waiting for event metadata lock", 0}
,
{0, "Waiting for commit lock", 0}
,
{0, "User lock", 0}
/* Be compatible with old status. */
};
well nice plugin, i'm using it now at production and it's very nice to know what is blocking my queries
thanks!
roberto spadim
added a comment - Some strange names in columns....
SELECT GET_LOCK('abc',1000);
select * from METADATA_LOCK_INFO
you will get DB = 'abc', that's not a true information, the 'abc' is the key of the MDL lock, not the database (DB), maybe we should have a "KEY" and a "SUB_KEY" here? example... DB and TABLE, when a table lock, or GET_LOCK key name and '' for sub key
maybe we could change somethings...
instead of "ID", put "THREAD_ID", since it's not a lock id and it's not the query id, (if there's a global unique mdl id could be nice put it here)
instead of "DB" put "KEY"?
instead of "TABLE" put "SUB KEY"?
from mdl.h
@remark The key for a table is <mdl_namespace> <database name> <table name>
maybe we should call LOCK_TYPE as "MDL_NAMESPACE" ? but it don't tell what name is used for GET_LOCK for example...
from mdl.cc, why not use "m_namespace_to_wait_state_name" names, or maybe add this information to mdl.h ? the "lock_type" (namescape) don't have a name in mdl.h yet, maybe should be nice one there?
PSI_stage_info MDL_key::m_namespace_to_wait_state_name [NAMESPACE_END] =
{
{0, "Waiting for global read lock", 0}
,
{0, "Waiting for schema metadata lock", 0}
,
{0, "Waiting for table metadata lock", 0}
,
{0, "Waiting for stored function metadata lock", 0}
,
{0, "Waiting for stored procedure metadata lock", 0}
,
{0, "Waiting for trigger metadata lock", 0}
,
{0, "Waiting for event metadata lock", 0}
,
{0, "Waiting for commit lock", 0}
,
{0, "User lock", 0}
/* Be compatible with old status. */
};
well nice plugin, i'm using it now at production and it's very nice to know what is blocking my queries
thanks!
other column to add is the "duration" variable
/** Duration of metadata lock. */
enum enum_mdl_duration
{
/**
Locks with statement duration are automatically released at the end
of statement or transaction.
*/
MDL_STATEMENT= 0,
/**
Locks with transaction duration are automatically released at the end
of transaction.
*/
MDL_TRANSACTION,
/**
Locks with explicit duration survive the end of statement and transaction.
They have to be released explicitly by calling MDL_context::release_lock().
*/
MDL_EXPLICIT,
/* This should be the last ! */
MDL_DURATION_END }
roberto spadim
added a comment - other column to add is the "duration" variable
/** Duration of metadata lock. */
enum enum_mdl_duration
{
/**
Locks with statement duration are automatically released at the end
of statement or transaction.
*/
MDL_STATEMENT= 0,
/**
Locks with transaction duration are automatically released at the end
of transaction.
*/
MDL_TRANSACTION,
/**
Locks with explicit duration survive the end of statement and transaction.
They have to be released explicitly by calling MDL_context::release_lock().
*/
MDL_EXPLICIT,
/* This should be the last ! */
MDL_DURATION_END }
Task
Minor
Some strange names in columns....
SELECT GET_LOCK('abc',1000);
select * from METADATA_LOCK_INFO
you will get DB = 'abc', that's not a true information, the 'abc' is the key of the MDL lock, not the database (DB), maybe we should have a "KEY" and a "SUB_KEY" here? example... DB and TABLE, when a table lock, or GET_LOCK key name and '' for sub key
maybe we could change somethings...
instead of "ID", put "THREAD_ID", since it's not a lock id and it's not the query id, (if there's a global unique mdl id could be nice put it here)
instead of "DB" put "KEY"?
instead of "TABLE" put "SUB KEY"?
from mdl.h
@remark The key for a table is <mdl_namespace><database name><table name>
maybe we should call LOCK_TYPE as "MDL_NAMESPACE" ? but it don't tell what name is used for GET_LOCK for example...
from mdl.cc, why not use "m_namespace_to_wait_state_name" names, or maybe add this information to mdl.h ? the "lock_type" (namescape) don't have a name in mdl.h yet, maybe should be nice one there?
PSI_stage_info MDL_key::m_namespace_to_wait_state_name[NAMESPACE_END]=
{0, "Waiting for global read lock", 0}{
,
{0, "Waiting for schema metadata lock", 0},
{0, "Waiting for table metadata lock", 0},
{0, "Waiting for stored function metadata lock", 0},
{0, "Waiting for stored procedure metadata lock", 0},
{0, "Waiting for trigger metadata lock", 0},
{0, "Waiting for event metadata lock", 0},
{0, "Waiting for commit lock", 0},
{0, "User lock", 0}/* Be compatible with old status. */
};
well
nice plugin, i'm using it now at production 
and it's very nice to know what is blocking my queries 
thanks!