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

查看更多关于MYSQL METALOCK的详细内容...