MySQL
MySQL Lock 발생
필유아사
2023. 9. 22. 00:21
MySQL DB에서 어떤 경우에 LOCK이 발생하고 그 LOCK을 어떻게 찾아가는지에 대해
여러 시나리오에 따라 테스트를 해본다.
- 테스트 테이블 생성 : 테스트를 위한 테이블과 데이터를 생성한다.
-- create table
CREATE TABLE select_lock
(col1 varchar(50),
col2 varchar(50));
INSERT INTO select_lock VALUES('1st', 'first');
INSERT INTO select_lock VALUES('2nd', 'second');
commit;
💎 autocommit = OFF 일 때, select lock 발생
auto_commit = 0 일 때, select 일 경우에도 shared_read lock이 발생하여 DDL 수행 시 대기상태로 빠진다.
- session A) SELECT 수행
SELECT * FROM select_lock;- lock 정보 조회 : 다른 session에서 session C) 의 SQL 수행으로 LOCK 정보를 확인해 보면, LOCK_TYPE이 SHARED_READ로 보여지며 이 테이블의 row에 대한 LOCK을 잡는다. 같은 session에서 조회할 경우 LOCK 정보가 보이지 않는다. 이와같이 autocommit 이 off 인 session에서 select 만 할 경우에도 LOCK이 발생하며 다른 session에서 DML은 가능하지만 DDL 수행 시 waiting LOCK이 발생한다.
+-------------+---------------+-------------+-------------+-------------+----------------+------------------+------------------+------------------+-----------+
| object_type | object_schema | object_name | lock_type | lock_status | processlist_id | processlist_user | processlist_host | processlist_info | thread_id |
+-------------+---------------+-------------+-------------+-------------+----------------+------------------+------------------+------------------+-----------+
| TABLE | deldb | select_lock | SHARED_READ | GRANTED | 16 | root | localhost | NULL | 57 |
+-------------+---------------+-------------+-------------+-------------+----------------+------------------+------------------+------------------+-----------+
- session B) DDL 수행
아래의 명령어와 같이 DDL을 수행하면 수행되지 않고 대기 상태로 된다. 즉 다른 session의 LOCK이 해소되기를 기다린다.
ALTER TABLE select_lock CHANGE col1 colA varchar(16);
-- 대기상태
- session C) lock 조회
위의 두 SQL을 실행한 상태에서 다른 session에서 아래의 SQL을 수행한다.
SELECT l.object_type, l.object_schema, l.object_name, l.lock_type, l.lock_status,
t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_time, t.processlist_info,
t.processlist_state, t.thread_id
FROM performance_schema.metadata_locks l
inner join performance_schema.threads t on t.thread_id = l.owner_thread_id
WHERE processlist_id <> connection_id();아래의 결과와 같이 최초 autocommit이 OFF 인 session에서 LOCK을 발생시키고, 다른 session에서 수행한 DDL이 대기상태인 것을 알 수 있다. thread_id 또는 processlist_id 별로 LOCK_STATUS를 보면 동일한 id 중에 GRANTED 만 있는 processlist 또는 thread가 있는 것을 확인할 수 있다. 아래에서는 thread_id가 49 또는 processlist_id가 8 인 것을 볼 수 있다. 다른 process는 PENDING 이 있는 것을 볼 수 있다.
+-------------+---------------+-------------------+---------------------+-------------+-----------+----------------+------------------------------------------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_INFO |
+-------------+---------------+-------------------+---------------------+-------------+-----------+----------------+------------------------------------------------------+
| TABLE | deldb | select_lock | SHARED_READ | GRANTED | 49 | 8 | NULL |
| GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE | GRANTED | 51 | 10 | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| BACKUP LOCK | NULL | NULL | INTENTION_EXCLUSIVE | GRANTED | 51 | 10 | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| SCHEMA | deldb | NULL | INTENTION_EXCLUSIVE | GRANTED | 51 | 10 | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| TABLE | deldb | select_lock | SHARED_UPGRADABLE | GRANTED | 51 | 10 | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| TABLESPACE | NULL | deldb/select_lock | INTENTION_EXCLUSIVE | GRANTED | 51 | 10 | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| TABLE | deldb | #sql-1698_a | EXCLUSIVE | GRANTED | 51 | 10 | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| TABLE | deldb | select_lock | EXCLUSIVE | PENDING | 51 | 10 | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
+-------------+---------------+-------------------+---------------------+-------------+-----------+----------------+------------------------------------------------------+
processlist를 조회해 보면 STATE이 "Waiting for table metadata lock" 인 것을 볼 수 있으며 이는 다른 프로세스의 LOCK이 해소되기를 기다리는 상태인 것이다. 또는 threads 에서 processlist_state 에서도 확인할 수 있다.
mysql> select * from information_schema.processlist;
+----+-----------------+-----------------+-------+---------+------+---------------------------------+------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+-----------------+-----------------+-------+---------+------+---------------------------------+------------------------------------------------------+
| 8 | root | localhost:63207 | deldb | Sleep | 272 | | NULL |
| 10 | root | localhost:63240 | deldb | Query | 242 | Waiting for table metadata lock | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| 12 | root | localhost:63450 | deldb | Query | 0 | executing | select * from information_schema.processlist |
| 5 | event_scheduler | localhost | NULL | Daemon | 1943 | Waiting on empty queue | NULL |
+----+-----------------+-----------------+-------+---------+------+---------------------------------+------------------------------------------------------+
lock를 유발한 session이 아닌 다른 session에서 metalock이 발생한 상태에서 테이블에 대한 어떤 작업을 수행할 경우 작업이 수행되지 않고 PENDING 상태로 대기한다.
즉, 다른 session에서 select를 수행할 경우에도 DDL이 수행되지 않고 PENDING 상태로 대기한다.
단, lock을 유발한 session에서는 DML, DDL 작업이 가능하다.
- LOCK 해소
1) 첫번째 session에서 commit 또는 rollback을 수행한다.
2) 첫번째 session을 찾아 kill 시킨다.
3) DDL session을 kill 시킨다.
💎 explain 을 수행할 때
session A) autocommit = OFF일 때, EXPLAIN 수행 시
EXPLAIN FORMAT=tree
select * from select_lock;
session B)
SELECT l.object_type, l.object_schema, l.object_name, l.lock_type, l.lock_status,
t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_time, t.processlist_info,
t.processlist_state, t.thread_id
FROM performance_schema.metadata_locks l
inner join performance_schema.threads t on t.thread_id = l.owner_thread_id
WHERE processlist_id <> connection_id();
수행결과 : 아래와 같이 SHARED_READ LOCK이 잡히는 것을 볼 수 있다. 즉, EXPLAIN을 수행하더라도 테이블에 SHARED_READ LOCK이 잡히는 것을 알 수 있다.
+-------------+---------------+-------------+-------------+-------------+-----------+----------------+------------------+------------------+------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_HOST | PROCESSLIST_USER | PROCESSLIST_INFO |
+-------------+---------------+-------------+-------------+-------------+-----------+----------------+------------------+------------------+------------------+
| TABLE | deldb | select_lock | SHARED_READ | GRANTED | 122 | 75 | localhost | root | NULL |
+-------------+---------------+-------------+-------------+-------------+-----------+----------------+------------------+------------------+------------------+
💎 autocommit = OFF 일 때, DML lock 발생
💎 유휴 세션 Blocking 쿼리 식별
Blocking 트랜잭션을 식별할 때 쿼리 세션이 유휴 상태가 되면 processlist를 조회해 보면 Blocking 쿼리가 NULL로 보여집니다. 이 경우 다음 단계를 사용하여 Blocking 쿼리를 확인합니다.
- Blocking 트랜잭션의 프로세스 ID를 식별합니다. sys.innodb_lock_waits 테이블에서 Blocking_pid 컬럼을 조회해보면 확인할 수 있습니다.
- 위에서 확인한 Blocking_pid 값으로 performance_schema.threads 테이블을 쿼리하여 Blocking 트랜잭션의 THREAD_ID를 확인합니다. 예를 들어, 1에서 확인한 blocking_pid가 6일 경우 다음과 같은 쿼리를 실행합니다.
-
SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6; - 위의 쿼리에서 확인한 THREAD_ID를 사용하여 performance_schema.events_statements_current 테이블을 쿼리하여 스레드가 실행한 마지막 쿼리를 확인합니다. 예를 들어 THREAD_ID가 28인 경우 다음과 같이 쿼리를 실행합니다.
-
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = 28; - 스레드에 의해 실행된 마지막 쿼리가 LOCK을 확인하는 데 충분하지 않은 경우 performance_schema.events_statements_history 테이블을 쿼리하여 스레드에 의해 실행된 마지막 10개의 SQL문을 확인항여 Lock을 발생시킨 SQL을 확인합니다.
-
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID = 28 ORDER BY EVENT_ID;
💎 Lock 유발 SQL 찾기
1. DML
- session#1 DML 수행
- session#2 session#1과 동일한 데이터에 대한 DML 수행
- session#1의 SQL 찾기
SELECT l.OBJECT_TYPE, l.OBJECT_SCHEMA, l.OBJECT_NAME, l.LOCK_TYPE, l.LOCK_STATUS,
t.THREAD_ID, PROCESSLIST_ID, PROCESSLIST_HOST, PROCESSLIST_USER, PROCESSLIST_INFO,
s.sql_text, s.statement_id
FROM performance_schema.metadata_locks l
INNER JOIN performance_schema.threads t ON t.THREAD_ID = l.OWNER_THREAD_ID
INNER JOIN performance_schema.events_statements_current s ON t.THREAD_ID = s.THREAD_ID
WHERE PROCESSLIST_ID <> CONNECTION_ID();
2. DDL
1. row lock
- insert, update, delete : transaction 을 종료하지 않았을 경우