[MDEV-4748] metadata_lock_info plugin Created: 2013-07-02  Updated: 2013-12-16  Resolved: 2013-12-13

Status: Closed
Project: MariaDB Server
Component/s: None
Fix Version/s: 10.0.7

Type: Task Priority: Minor
Reporter: Sergey Vojtovich Assignee: Sergey Vojtovich
Resolution: Fixed Votes: 0
Labels: None

Attachments: Text File metadata_lock_info.cc    
Issue Links:
Duplicate
duplicates MDEV-4619 PLUGINS - Add LOCKS information at I_... Closed
Relates
relates to MDEV-4704 PROCESSLIST - Query Rank execution order Open

 Description   

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

 Comments   
Comment by roberto spadim [ 2013-08-26 ]

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!

Comment by roberto spadim [ 2013-08-26 ]

for query id:

table->field[0]>store((longlong) tmp_thd>thread_id, TRUE);

table->field[ QUERY_ID COLUMN NUMBER ]>store((longlong) tmp_thd>query_id, TRUE);

Comment by roberto spadim [ 2013-08-26 ]

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 }
Comment by roberto spadim [ 2013-08-27 ]

Added file with changes that i talked in comments

Comment by Sergey Vojtovich [ 2013-12-13 ]

Pushed to 10.0.7,
revision-id: kentokushiba@gmail.com-20131210153104-e8h5m61072s2boqd
revision-id: svoj@mariadb.org-20131213084245-f8w1v2lirtmqbm0e

Generated at Thu Feb 08 06:58:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.