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 쿼리를 확인합니다.
- 차단 트랜잭션의 프로세스 목록 ID를 식별합니다. sys.innodb_lock_waits 테이블에서 차단 트랜잭션의 processlist ID는 Blocking_pid 값입니다.
- Blocking_pid를 사용하여 MySQL performance_schema.threads 테이블을 쿼리하여 차단 트랜잭션의 THREAD_ID를 확인합니다. 예를 들어,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문을 볼 수 있습니다.
-
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID = 28 ORDER BY EVENT_ID;
InnoDB 트랜잭션과 MySQL 세션의 상관관계
내부 InnoDB locking 정보와 Session 정보를 연관시킬 수 있습니다. 예를 들어, 어떤 InnoDB 트랜잭션 ID에 대해 해당 세션이 잠금을 보유하여 다른 트랜잭션을 차단할 수 있는 세션인지 알고 싶을 수 있습니다.
아래 예제는 INFORMATION_SCHEMA.INNODB_TRX 테이블과 performance_schema의 data_locks 및 data_lock_waits 테이블 조회 데이터입니다.
- Transaction 77F (executing an INSERT) is waiting for transactions 77E, 77D, and 77B to commit.
- Transaction 77E (executing an INSERT) is waiting for transactions 77D and 77B to commit.
- Transaction 77D (executing an INSERT) is waiting for transaction 77B to commit.
- Transaction 77B (executing an INSERT) is waiting for transaction 77A to commit.
- Transaction 77A is running, currently executing SELECT.
- Transaction E56 (executing an INSERT) is waiting for transaction E55 to commit.
- Transaction E55 (executing an INSERT) is waiting for transaction 19C to commit.
- Transaction 19C is running, currently executing an INSERT.
Note
INFORMATION_SCHEMA PROCESSLIST 및 INNODB_TRX 테이블에 표시된 쿼리 간에 불일치가 있을 수 있습니다. 섹션 15.15.2.3, “InnoDB 트랜잭션 및 잠금 정보의 지속성과 일관성”( Section 15.15.2.3, “Persistence and Consistency of InnoDB Transaction and Locking Information”)을 참고합니다.
다음 표는 작업량이 많은 시스템에 대한 PROCESSLIST 테이블의 내용입니다.
|
ID
|
USER | HOST | DB | COMMAND | TIME | STATE | INFO |
| 384 | root | localhost | test | Query | 10 | update | INSERT INTO t2 VALUES … |
| 257 | root | localhost | test | Query | 3 | update | INSERT INTO t2 VALUES … |
| 130 | root | localhost | test | Query | 0 | update | INSERT INTO t2 VALUES … |
| 61 | root | localhost | test | Query | 1 | update | INSERT INTO t2 VALUES … |
| 8 | root | localhost | test | Query | 1 | update | INSERT INTO t2 VALUES … |
| 4 | root | localhost | test | Query | 0 | preparing | SELECT * FROM PROCESSLIST |
| 2 | root | localhost | test | Sleep | 566 | NULL |
다음 표는 작업량이 많은 시스템의 INNODB_TRX 테이블의 내용을 보여줍니다.
|
trx id
|
trx state | trx started | trx requested lock id | trx wait started | trx weight | trx mysql thread id | trx query |
| 77F | LOCK WAIT | 2008-01-15 13:10 | 77F | 2008-01-15 13:10 | 1 | 876 | INSERT INTO t09 (D, B, C) VALUES … |
| 77E | LOCK WAIT | 2008-01-15 13:10 | 77E | 2008-01-15 13:10 | 1 | 875 | INSERT INTO t09 (D, B, C) VALUES … |
| 77D | LOCK WAIT | 2008-01-15 13:10 | 77D | 2008-01-15 13:10 | 1 | 874 | INSERT INTO t09 (D, B, C) VALUES … |
| 77B | LOCK WAIT | 2008-01-15 13:10 | 77B:733:12:1 | 2008-01-15 13:10 | 4 | 873 | INSERT INTO t09 (D, B, C) VALUES … |
| 77A | RUNNING | 2008-01-15 13:10 | NULL | NULL | 4 | 872 | SELECT b, c FROM t09 WHERE … |
| E56 | LOCK WAIT | 2008-01-15 13:10 | E56:743:6:2 | 2008-01-15 13:10 | 5 | 384 | INSERT INTO t2 VALUES … |
| E55 | LOCK WAIT | 2008-01-15 13:10 | E55:743:38:2 | 2008-01-15 13:10 | 965 | 257 | INSERT INTO t2 VALUES … |
| 19C | RUNNING | 2008-01-15 13:09 | NULL | NULL | 2900 | 130 | INSERT INTO t2 VALUES … |
| E15 | RUNNING | 2008-01-15 13:08 | NULL | NULL | 5395 | 61 | INSERT INTO t2 VALUES … |
| 51D | RUNNING | 2008-01-15 13:08 | NULL | NULL | 9807 | 8 | INSERT INTO t2 VALUES … |
다음 표는 data_lock_waits 테이블의 내용을 보여줍니다.
|
requesting trx id
|
requested lock id | blocking trx id | blocking lock id |
| 77F | 77F:806 | 77E | 77E:806 |
| 77F | 77F:806 | 77D | 77D:806 |
| 77F | 77F:806 | 77B | 77B:806 |
| 77E | 77E:806 | 77D | 77D:806 |
| 77E | 77E:806 | 77B | 77B:806 |
| 77D | 77D:806 | 77B | 77B:806 |
| 77B | 77B:733:12:1 | 77A | 77A:733:12:1 |
| E56 | E56:743:6:2 | E55 | E55:743:6:2 |
| E55 | E55:743:38:2 | 19C | 19C:743:38:2 |
다음 표는 data_locks 테이블의 내용을 보여줍니다.
|
lock id
|
lock trx id | lock mode | lock type | lock schema | lock table | lock index | lock data |
| 77F:806 | 77F | AUTO_INC | TABLE | test | t09 | NULL | NULL |
| 77E:806 | 77E | AUTO_INC | TABLE | test | t09 | NULL | NULL |
| 77D:806 | 77D | AUTO_INC | TABLE | test | t09 | NULL | NULL |
| 77B:806 | 77B | AUTO_INC | TABLE | test | t09 | NULL | NULL |
| 77B:733:12:1 | 77B | X | RECORD | test | t09 | PRIMARY | supremum pseudo-record |
| 77A:733:12:1 | 77A | X | RECORD | test | t09 | PRIMARY | supremum pseudo-record |
| E56:743:6:2 | E56 | S | RECORD | test | t2 | PRIMARY | 0, 0 |
| E55:743:6:2 | E55 | X | RECORD | test | t2 | PRIMARY | 0, 0 |
| E55:743:38:2 | E55 | S | RECORD | test | t2 | PRIMARY | 1922, 1922 |
| 19C:743:38:2 | 19C | X | RECORD | test | t2 | PRIMARY | 1922, 1922 |
💎 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 을 종료하지 않았을 경우