mysql中metalock,MYSQL METALOCK

MYSQL METALOCK

祖仙教小凡仙 海鲨数据库架构师

MYSQL 没有ORACLE 阻塞视图 找到谁阻塞了谁比较麻烦

--## 开启性能抓获功能

UPDATE performance_schema.setup_consumers SET ENABLED='YES';

UPDATE performance_schema.setup_instruments SET ENABLED='YES',TIMED='YES'

要开启各种事件收集器

##会话1

mysql> SET AUTOCOMMIT=OFF;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM MONITER_DDL;

会话1 把自动提交给关闭了,然后执行一个查询,在MYSQL里查询是个事务,感觉好扯蛋.

在ORACLE DML语句才是事务,查询根本不是个事务!

##会话2

mysql> CREATE INDEX IX_IP ON MONITER_DDL(IPADDRES);

会话2 对该表建个索引,结果被阻塞了!

##3 查看进程信息

show processlist

这里看到ID 为10的 进程 创建索引在等待元数据锁

那谁阻塞了它? 谁持有MDL锁?

##4 元数据锁

select OWNER_THREAD_ID,OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION,LOCK_STATUS

from performance_schema.metadata_locks

where OWNER_THREAD_ID !=

sys.ps_thread_id(connection_id());

这里看到有两个线程ID 持有元数据锁 分别是 39 和35线程.

通过OBJECT_NAME,可以看出39,35持有同一张表的锁.

5查看事务ID

select TRX_ID,TRX_STATE,TRX_STARTED,TRX_mysql_thread_id from information_schema.innodb_trx;

这里的TRX_MYSQL_THREAD_ID 应该是进程ID

会话1 进程ID

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

| 14 |

+-----------------+

1 row in set (0.00 sec)

会话1 线程ID

mysql> select sys.ps_thread_id(14);

+----------------------+

| sys.ps_thread_id(14) |

+----------------------+

| 39 |

+----------------------+

1 row in set (0.00 sec)

##查看线程信息

SELECT

THREAD_ID,

NAME,

PROCESSLIST_ID,

CONNECTION_TYPE,

THREAD_OS_ID,

PROCESSLIST_USER,

PROCESSLIST_HOST,

PROCESSLIST_DB,

PROCESSLIST_COMMAND,

PROCESSLIST_TIME,

PROCESSLIST_STATE,

PROCESSLIST_INFO

FROM performance_schema.threads

WHERE type = 'FOREGROUND'

and THREAD_ID in (39,35)

##查询39线程执行了什么语句

select s.OBJECT_TYPE,s.OBJECT_SCHEMA,s.OBJECT_NAME,s.LOCK_TYPE,s.LOCK_DURATION,s.LOCK_STATUS,

sh.THREAD_ID,sh.EVENT_NAME,sh.TIMER_START,sh.LOCK_TIME,sh.SQL_TEXT

from performance_schema.metadata_locks s

leftjoin performance_schema.events_statements_history sh

on s.OWNER_THREAD_ID=sh.THREAD_ID

where owner_thread_id != sys.ps_thread_id(CONNECTION_ID())

AND owner_thread_id=39

orderby TIMER_START DESC;

从列表发现 39线程 有设置 自动提交为假!

会话1 执行COMMIT

mysql> select sys.ps_thread_id(14);

+----------------------+

| sys.ps_thread_id(14) |

+----------------------+

| 39 |

+----------------------+

1 row in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

会话2 自动完成创建索引

mysql> CREATE INDEX IX_IP ON MONITER_DDL(IPADDRES);

Query OK, 0 rows affected (1 hour 2 min 41.23 sec)

Records: 0 Duplicates: 0 Warnings: 0

创建索引等了1个小时之久