mysql中的元数据锁
来源:互联网 发布:java 时间格式 编辑:程序博客网 时间:2024/06/07 11:06
为了保证事务在执行的过程中,不会因为ddl语句导致事务失败,mysql中出现了元数据锁。下面简单测试下
session A:
test>begin
-> ;
Query OK, 0 rows affected (0.01 sec)
.test>insert into t2 values('mmm',77);
Query OK, 1 row affected (0.01 sec)
sessionB:
drop table t2;
会话B挂起来了,看下锁的信息,在innodb_locks及innodb_lock_wait中没有看到相关的信息,但是在show engine innodb status中能看到对应的元数据锁信息。
test>show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2016-04-19 23:21:49 7fc180261700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 19 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 36 srv_active, 0 srv_shutdown, 540015 srv_idle
srv_master_thread log flush and writes: 540051
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 93
OS WAIT ARRAY INFO: signal count 95
Mutex spin waits 36, rounds 454, OS waits 12
RW-shared spins 56, rounds 1710, OS waits 57
RW-excl spins 2, rounds 739, OS waits 24
Spin rounds per wait: 12.61 mutex, 30.54 RW-shared, 369.50 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 2489
Purge done for trx's n:o < 2489 undo n:o < 0 state: running but idle
History list length 33
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 58, OS thread handle 0x7fc180230700, query id 1498 192.168.0.101 user1 Waiting for table metadata lock
drop table t2
---TRANSACTION 2478, ACTIVE 225 sec
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 57, OS thread handle 0x7fc180261700, query id 1504 192.168.0.101 user1 init
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
203 OS file reads, 667 OS file writes, 498 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 2365241, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1801269
Log flushed up to 1801269
Pages flushed up to 1801269
Last checkpoint at 1801269
0 pending log writes, 0 pending chkp writes
179 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1098907648; in additional pool allocated 0
Dictionary memory allocated 128974
Buffer pool size 65528
Free buffers 65220
Database pages 307
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 178, created 129, written 463
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 307, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 8191
Free buffers 8152
Database pages 38
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 32, created 6, written 156
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 38, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 8191
Free buffers 8183
Database pages 8
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4, created 4, written 23
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 8191
Free buffers 8183
Database pages 8
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4, created 4, written 23
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 8191
Free buffers 8119
Database pages 72
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 68, created 4, written 35
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 72, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 8191
Free buffers 8108
Database pages 83
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 62, created 21, written 92
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 83, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 8191
Free buffers 8117
Database pages 74
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4, created 70, written 99
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 74, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 8191
Free buffers 8175
Database pages 16
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4, created 12, written 18
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 16, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 8191
Free buffers 8183
Database pages 8
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 8, written 17
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 29496, id 140466865665792, state: sleeping
Number of rows inserted 13, updated 9, deleted 0, read 128
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
- mysql中的元数据锁
- mysql 元数据锁
- 辛星浅谈mysql中的元数据
- MySQL--MDL,元数据锁的粒度
- 查看mysql元数据
- MySQL 元数据
- MySQL 元数据
- MySQL 元数据
- hive配置mysql元数据
- hive 元数据 mysql-1
- hive 元数据 mysql-2
- Flex中的元数据标签
- Flex 中的元数据标签
- Flex中的元数据标签
- Flex 中的元数据标签
- Flex 中的元数据标签
- Flex中的元数据标签
- C#中的元数据描述
- CentOS 7安装Chrome浏览器
- Jsoup 查询时根据标签class属性和id属性查找
- 内存池——第二章 自定义内存池 & 动态分段连续的内存池(1)
- 用Eslipse开发Android案例(1)——计算器<part2>
- C语言基本数据类型
- mysql中的元数据锁
- leetcode刷题,总结,记录,备忘 8
- exynos 4412 Framebuffer驱动详解
- [LeetCode] 343. Integer Break
- 记录开发中listview的动态显示
- 202. Happy Number
- httpclient 简单实例
- Android 多点touch触控事件传递
- React-Native系列Android——Native与Javascript通信原理(三)