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个小时之久