如何使用mysql观测MDL锁
发布时间:2022-01-17 13:35:10 所属栏目:MySql教程 来源:互联网
导读:如何使用mysql观测MDL锁,相信很多没有经验的人对此束手无策,为此本文总结了问题出现的原因和解决方法,通过这篇文章希望你能解决这个问题。 在给一个小表加字段的时候,一直拿不到锁,报错: ERROR 1205 (HY000): Lock wait timeout exceeded; try restart
如何使用mysql观测MDL锁,相信很多没有经验的人对此束手无策,为此本文总结了问题出现的原因和解决方法,通过这篇文章希望你能解决这个问题。 在给一个小表加字段的时候,一直拿不到锁,报错: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 期间show processlist查看,ddl在等待MDL锁。MySQL 的 Metadata Lock 机制是为了保护并发访问数据对象的一致性,并且showps也看不到活动的事务。 如何查到MDL锁的源头呢?innodb_locks是看不到MDL锁的信息的。performance_schema下面有一张表叫metadata_locks mysql> desc metadata_locks; +-----------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------------+------+-----+---------+-------+ | OBJECT_TYPE | varchar(64) | NO | | NULL | | | OBJECT_SCHEMA | varchar(64) | YES | | NULL | | | OBJECT_NAME | varchar(64) | YES | | NULL | | | OBJECT_INSTANCE_BEGIN | bigint(20) unsigned | NO | | NULL | | | LOCK_TYPE | varchar(32) | NO | | NULL | | | LOCK_DURATION | varchar(32) | NO | | NULL | | | LOCK_STATUS | varchar(32) | NO | | NULL | | | SOURCE | varchar(64) | YES | | NULL | | | OWNER_THREAD_ID | bigint(20) unsigned | YES | | NULL | | | OWNER_EVENT_ID | bigint(20) unsigned | YES | | NULL | | +-----------------------+---------------------+------+-----+---------+-------+ 10 rows in set (0.00 sec) 默认需要开启metadata 的 instrument,才能拿到MDL锁的信息。 call sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl%') 或者: UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME ='global_instrumentation'; UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME ='wait/lock/metadata/sql/mdl'; 开启后重新执行DDL语句,然后观测 mysql> select * from performance_schema.metadata_locks where object_name='t'G *************************** 1. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: ming OBJECT_NAME: t OBJECT_INSTANCE_BEGIN: 87772208 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: OWNER_THREAD_ID: 29 OWNER_EVENT_ID: 990 *************************** 2. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: ming OBJECT_NAME: t OBJECT_INSTANCE_BEGIN: 140151695148016 LOCK_TYPE: SHARED_UPGRADABLE LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: OWNER_THREAD_ID: 31 OWNER_EVENT_ID: 178 *************************** 3. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: ming OBJECT_NAME: t OBJECT_INSTANCE_BEGIN: 140151695148992 LOCK_TYPE: EXCLUSIVE LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: OWNER_THREAD_ID: 31 OWNER_EVENT_ID: 237 3 rows in set (0.00 sec) 可以看到已经会话被授予(GRANTED)了SHARED_READ类型的MDL锁。DDL会话先被授予了SHARED_UPGRADABLE,然后需要被授予EXCLUSIVE的锁,但是拿不到,所以处于PENDING状态。所以只要找到GRANTED的会话是哪个,然后杀掉就可以了。 MDL_SHARED_READ, /* A shared metadata lock for cases when there is an intention to modify (and not just read) data in the table. A connection holding SW lock can read table metadata and modify or read table data (after acquiring appropriate table and row-level locks). To be used for tables to be modified by INSERT, UPDATE, DELETE statements, but not LOCK TABLE ... WRITE or DDL). Also taken by SELECT ... FOR UPDATE. */ MDL_SHARED_UPGRADABLE, /* A shared metadata lock for cases when we need to read data from table and block all concurrent modifications to it (for both data and metadata). Used by LOCK TABLES READ statement. 通过perforamnce_schema.threads查找: mysql> select * from performance_schema.threads where thread_id=29G *************************** 1. row *************************** THREAD_ID: 29 NAME: thread/sql/one_connection TYPE: FOREGROUND PROCESSLIST_ID: 2 PROCESSLIST_USER: root PROCESSLIST_HOST: localhost PROCESSLIST_DB: ming PROCESSLIST_COMMAND: Sleep PROCESSLIST_TIME: 107 PROCESSLIST_STATE: NULL PROCESSLIST_INFO: NULL PARENT_THREAD_ID: 1 ROLE: NULL INSTRUMENTED: YES HISTORY: YES CONNECTION_TYPE: Socket THREAD_OS_ID: 2822 1 row in set (0.00 sec) 取processlist_id,kill掉该会话即可。 mysql> kill 2; Query OK, 0 rows affected (0.00 sec) mysql> alter table t add c4 int; Query OK, 0 rows affected (3 min 58.00 sec) Records: 0 Duplicates: 0 Warnings: 0 查看pstack: 阻塞源头 Thread 9 (Thread 0x7f77c3043700 (LWP 2822)): #0 0x00007f77f1debf0d in poll () from /lib64/libc.so.6 #1 0x00000000012c48df in vio_io_wait (vio=<optimized out>, event=<optimized out>, timeout=28800000) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/vio/viosocket.c:786 #2 0x00000000012c49d3 in vio_socket_io_wait (vio=<optimized out>, event=<optimized out>) at /export/home/pb2/build/sb_0-32013917-1545390211.74/mysql-5.7.25/vio/viosocket.c:77 #3 0x00000000012c5008 in vio_read (vio=0x505ab10, buf=0x5372800 " |