metadata lock
metadata lock 은 dql, dml, ddl 들의 조합에서 발생하는 테이블의 구조변경 시도에 따른 lock 이다.
session 1) metadata lock 발생
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.02 sec)
mysql> create table t1(i1 int primary key,i2 int );
insert into t1 values( 1,1 );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values( 1,1 );
commit;
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1 ;
+----+------+
| i1 | i2 |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)session 2) metadata lock waiting
mysql> alter table t1 add column i3 int;session 3) metadata lock 확인
-- Waiting for table metadata lock 은 누군가에 의해 발생한 metadata lock 으로 인해
-- waiting 이 발생했다는 것이다.
mysql> show processlist;
+----+----------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+----------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------------+-----------+---------------+
| 7 | proxysql | 10.1.5.31:57248 | NULL | Sleep | 7 | | NULL | 0 | 0 |
| 8 | repl | 10.1.5.24:48320 | NULL | Binlog Dump GTID | 675 | Master has sent all binlog to slave; waiting for more updates | NULL | 0 | 0 |
| 9 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |
| 10 | bos | localhost | test | Query | 125 | Waiting for table metadata lock | alter table t1 add column i3 int | 0 | 0 |
| 12 | bos | localhost | test | Sleep | 131 | | NULL | 1 | 1 |
+----+----------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------------+-----------+---------------+
5 rows in set (0.00 sec)
-- processlist_id 12번이 조회를 하면서 SHARED_READ lock 을 유지하고 있기 때문에
-- ( auto commmit 이었다면 data fetch 후 lock 이 해제된다. )
-- 10번 의 alter 구문이 waiting 상태가 된다.
SELECT
OBJECT_TYPE,
OBJECT_SCHEMA,
OBJECT_NAME,
LOCK_TYPE,
LOCK_STATUS,
THREAD_ID,
PROCESSLIST_ID,
PROCESSLIST_INFO
FROM
performance_schema.metadata_locks INNER JOIN performance_schema.threads
ON
THREAD_ID = OWNER_THREAD_ID
WHERE
PROCESSLIST_ID <> CONNECTION_ID() and
OBJECT_SCHEMA = 'test'
;
+-------------+---------------+-------------+---------------------+-------------+-----------+----------------+----------------------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_INFO |
+-------------+---------------+-------------+---------------------+-------------+-----------+----------------+----------------------------------+
| TABLE | test | t1 | SHARED_READ | GRANTED | 64 | 12 | select * from t1 |
| SCHEMA | test | NULL | INTENTION_EXCLUSIVE | GRANTED | 62 | 10 | alter table t1 add column i3 int |
| TABLE | test | t1 | SHARED_UPGRADABLE | GRANTED | 62 | 10 | alter table t1 add column i3 int |
| TABLE | test | #sql-313d_a | EXCLUSIVE | GRANTED | 62 | 10 | alter table t1 add column i3 int |
| TABLE | test | t1 | EXCLUSIVE | PENDING | 62 | 10 | alter table t1 add column i3 int |
+-------------+---------------+-------------+---------------------+-------------+-----------+----------------+----------------------------------+session 3 ) metadata lock 해결
-- 1) 12번에서 commmit; 하기
-- 2) 10번에서 alter 를 취소하기
-- 3) 12번 connection 을 강제로 kill하기
-- session 3
mysql> kill 12;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+----+----------+-----------------+------+------------------+------+---------------------------------------------------------------+------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+----------+-----------------+------+------------------+------+---------------------------------------------------------------+------------------+-----------+---------------+
| 7 | proxysql | 10.1.5.31:57248 | NULL | Sleep | 9 | | NULL | 0 | 0 |
| 8 | repl | 10.1.5.24:48320 | NULL | Binlog Dump GTID | 1257 | Master has sent all binlog to slave; waiting for more updates | NULL | 0 | 0 |
| 9 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |
| 10 | bos | localhost | test | Sleep | 707 | | NULL | 0 | 0 |
+----+----------+-----------------+------+------------------+------+---------------------------------------------------------------+------------------+-----------+---------------+
4 rows in set (0.00 sec)
-- session 2
mysql> alter table t1 add column i3 int;
Query OK, 0 rows affected (9 min 9.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- session 1
mysql> commit;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 37
Current database: test
Query OK, 0 rows affected (0.00 sec)
mysql>
data lock
data lock 은 테이블 전체 혹은 일부 row 에 대해 lock 을 하는 경우이다. 보통은 dml 에 의한 transaction 이 commit 되지 않은 상태에서 발생한다.
session 1) data lock 발생
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table t1;
create table t1(i1 int primary key,i2 int );
Query OK, 0 rows affected (0.02 sec)
mysql> create table t1(i1 int primary key,i2 int );
insert into t1 values( 1,1 );
commit;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values( 1,1 );
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set i2 = 2 where i1 =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>session 2) data lock waiting
mysql> update t1 set i2 = 3 where i1 =1;| mysql> update t1 set i2 = 3 where i1 =1; |
session 3) data lock 확인
-- 87 이 update 를 위해 wait 하고 있다.
mysql> show processlist;
+----+----------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+----------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------------+-----------+---------------+
| 7 | proxysql | 10.1.5.31:57248 | NULL | Sleep | 7 | | NULL | 0 | 0 |
| 8 | repl | 10.1.5.24:48320 | NULL | Binlog Dump GTID | 4235 | Master has sent all binlog to slave; waiting for more updates | NULL | 0 | 0 |
| 9 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |
| 37 | bos | localhost | test | Sleep | 39 | | NULL | 0 | 1 |
| 68 | root | localhost | NULL | Sleep | 63 | | NULL | 0 | 0 |
| 87 | bos | localhost | test | Query | 35 | updating | update t1 set i2 = 3 where i1 =1 | 0 | 0 |
+----+----------+-----------------+------+------------------+------+---------------------------------------------------------------+----------------------------------+-----------+---------------+
6 rows in set (0.00 sec)
-- 37번이 최초 lock 을 잡고 있고
-- 87번이 대기하고 있다.
select
a.ROLE,
a.PROCESSLIST_ID,
a.PROCESSLIST_USER,
a.PROCESSLIST_INFO,
c.LOCK_TYPE,
c.LOCK_MODE
from
(select
a.THREAD_ID,
case
when a.THREAD_ID = b.BLOCKING_THREAD_ID
then 'GRANTER'
when a.THREAD_ID = b.REQUESTING_THREAD_ID
then 'REQUESTER'
else 'UNKNOW'
end as ROLE,
a.PROCESSLIST_ID,
a.PROCESSLIST_USER,
a.PROCESSLIST_INFO
from
performance_schema.threads a,
performance_schema.data_lock_waits b
where
a.THREAD_ID = b.BLOCKING_THREAD_ID or a.THREAD_ID = b.REQUESTING_THREAD_ID
) a,
performance_schema.data_locks c
where
a.THREAD_ID = c.THREAD_ID
;
+-----------+----------------+------------------+----------------------------------+-----------+---------------+
| ROLE | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_INFO | LOCK_TYPE | LOCK_MODE |
+-----------+----------------+------------------+----------------------------------+-----------+---------------+
| GRANTER | 37 | bos | update t1 set i2 = 2 where i1 =1 | TABLE | IX |
| GRANTER | 37 | bos | update t1 set i2 = 2 where i1 =1 | RECORD | X,REC_NOT_GAP |
| REQUESTER | 87 | bos | update t1 set i2 = 3 where i1 =1 | TABLE | IX |
| REQUESTER | 87 | bos | update t1 set i2 = 3 where i1 =1 | RECORD | X,REC_NOT_GAP |
+-----------+----------------+------------------+----------------------------------+-----------+---------------+
4 rows in set (0.00 sec)session 3) data lock 해결
-- 1) 10번에서 commmit; 하기
-- 2) 12번에서 update 를 취소하기
-- 3) 10번 connection 을 강제로 kill하기
-- session 2)
mysql> kill 37;
Query OK, 0 rows affected (0.00 sec)
-- session 1)
mysql> commit;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 102
Current database: test
Query OK, 0 rows affected (0.00 sec)
-- session 2)
mysql> update t1 set i2 = 3 where i1 =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
reference
'Database > MySQL' 카테고리의 다른 글
| semi replication (0) | 2021.10.01 |
|---|---|
| mysql admin script (0) | 2021.10.01 |
| install mysql(percona) by tarball (0) | 2021.10.01 |
| MySQL partition 관리 (0) | 2021.10.01 |
| InnoDB on-disk structures (0) | 2021.10.01 |