MySQL KILL 명령: 예상치 못한 세션 재연결 처리

MySQL 데이터베이스를 관리하거나 테스트하는 과정에서 특정 세션을 강제 종료하기 위해 KILL 명령을 사용했음에도 불구하고, 해당 SQL 쿼리가 계속 실행되거나 종료된 세션이 자동으로 다시 연결되어 이전 작업을 이어가는 현상을 겪을 수 있습니다. 이는 KILL 명령의 동작 방식과 MySQL 클라이언트의 기본 설정 때문에 발생하는 오해를 불러일으킬 수 있습니다.

문제 상황 재현

본 테스트는 MySQL 8.0.27 버전을 기반으로 진행되었습니다.

1. 테스트용 테이블 생성

먼저, 테스트를 위한 간단한 테이블을 생성합니다.

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50) NOT NULL
);

INSERT INTO products (product_id, product_name) VALUES
    (101, 'Widget'),
    (102, 'Gadget'),
    (103, 'Doodad');

2. 세션 간 상호작용 시나리오

다음 시나리오는 세 개의 MySQL 클라이언트 세션에서 진행됩니다.

세션 1: 장기 실행 트랜잭션 시작
새로운 트랜잭션을 시작하고, 테이블에 대한 읽기 작업을 수행하여 메타데이터 잠금을 유발할 수 있는 상태를 만듭니다. 이 트랜잭션은 커밋하지 않고 유지합니다.
```
USE test_db; -- 데이터베이스 변경 (적절한 데이터베이스명 사용)
START TRANSACTION;
SELECT * FROM products;
-- 이 세션은 커밋하지 않고 유지합니다.
```
세션 2: DDL 작업 시도 (잠금 대기)
새로운 세션에서 테이블 이름을 변경하는 DDL 작업을 시도합니다. 세션 1이 테이블에 대한 메타데이터 잠금을 유지하고 있으므로, 이 DDL 쿼리는 잠금 대기 상태에 빠지게 됩니다.
```
USE test_db;
RENAME TABLE products TO archived_products; -- 이 명령은 세션 1의 잠금으로 인해 대기 상태에 빠집니다.
```

세션 3: 세션 2 강제 종료 시도

별도의 세션에서 현재 실행 중인 프로세스 목록을 확인하고, 잠금 대기 중인 세션 2를 KILL 명령으로 종료하려 합니다.

SHOW PROCESSLIST;

위 명령 실행 후 다음과 유사한 결과를 볼 수 있습니다. 여기서 DDL 작업을 수행하는 세션의 ID를 확인합니다 (예: 11번).

+-----+------+---------+---------+-----------------+---------+--------------------------------+---------------------------------------+
| Id  | User | Host    | db      | Command         | Time    | State                          | Info                                  |
+-----+------+---------+---------+-----------------+---------+--------------------------------+---------------------------------------+
| 10  | root | localhost | test_db | Sleep           | 300     |                                | NULL                                  |
| 11  | root | localhost | test_db | Query           | 2       | Waiting for table metadata lock| RENAME TABLE products TO archived_products|
| 12  | root | localhost | test_db | Query           | 0       | init                           | show processlist                      |
+-----+------+---------+---------+-----------------+---------+--------------------------------+---------------------------------------+

이제 세션 ID 11을 강제 종료합니다.

KILL 11;

3. 예상치 못한 동작 확인

세션 3에서 KILL 11; 명령을 실행한 후, 세션 2의 터미널에서는 다음과 같은 메시지를 보게 될 수 있습니다.

ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    13
Current database: test_db

이어서 세션 3에서 다시 SHOW PROCESSLIST;를 실행하면, 새로운 연결 ID(예: 13번)로 세션 2가 다시 연결되어 여전히 RENAME TABLE 명령을 실행하며 잠금 대기 상태에 있는 것을 확인할 수 있습니다.

+-----+------+---------+---------+-----------------+---------+--------------------------------+---------------------------------------+
| Id  | User | Host    | db      | Command         | Time    | State                          | Info                                  |
+-----+------+---------+---------+-----------------+---------+--------------------------------+---------------------------------------+
| 10  | root | localhost | test_db | Sleep           | 400     |                                | NULL                                  |
| 12  | root | localhost | test_db | Query           | 0       | init                           | show processlist                      |
| 13  | root | localhost | test_db | Query           | 50      | Waiting for table metadata lock| RENAME TABLE products TO archived_products|
+-----+------+---------+---------+-----------------+---------+--------------------------------+---------------------------------------+

마지막으로 세션 1에서 트랜잭션을 커밋하면, 세션 2 (이제 ID 13)의 RENAME TABLE 명령이 성공적으로 완료됩니다.

-- 세션 1
COMMIT;

-- 세션 2 (다시 연결된 세션)
Query OK, 0 rows affected (8 min 38.00 sec)

이러한 현상은 KILL 명령이 무용지물인 것처럼 느껴지게 합니다.

원인

이 문제의 핵심 원인은 MySQL 클라이언트의 --reconnect 옵션이 기본적으로 활성화되어 있기 때문입니다. 이 옵션은 MySQL 서버와의 연결이 끊어졌을 때 자동으로 다시 연결을 시도하고, 이전에 실행 중이던 쿼리를 다시 실행하려 합니다. 따라서 KILL 명령으로 세션이 종료되면 클라이언트는 즉시 재연결을 시도하고, 원래 실행하려던 DDL 쿼리를 다시 서버로 보냅니다.

--reconnect         Reconnect if the connection is lost. Disable with
                    --disable-reconnect. This option is enabled by default.
                    (Defaults to on; use --skip-reconnect to disable.)

문제 해결 방안

위와 같은 자동 재연결로 인한 문제를 방지하기 위해 다음 두 가지 방법을 사용할 수 있습니다.

1. KILL QUERY 명령 사용

KILL QUERY 명령은 현재 세션이 실행 중인 쿼리만 종료하고, 연결 자체는 유지합니다. 이는 세션을 완전히 끊는 것이 아니라, 특정 쿼리의 실행만 중단시키고자 할 때 유용합니다.

세션 3: KILL QUERY 명령 실행

SHOW PROCESSLIST;

+-----+------+---------+---------+-----------------+---------+--------------------------------+---------------------------------------+
| Id  | User | Host    | db      | Command         | Time    | State                          | Info                                  |
+-----+------+---------+---------+-----------------+---------+--------------------------------+---------------------------------------+
| 10  | root | localhost | test_db | Sleep           | 4401560 |                                | NULL                                  |
| 11  | root | localhost | test_db | Query           | 3       | Waiting for table metadata lock| RENAME TABLE products TO archived_products|
| 12  | root | localhost | test_db | Query           | 0       | init                           | show processlist                      |
+-----+------+---------+---------+-----------------+---------+--------------------------------+---------------------------------------+

KILL QUERY 11;

세션 2: 결과 확인

RENAME TABLE products TO archived_products; -- 원래 실행하던 명령

세션 2에서는 다음과 같은 오류 메시지를 받게 되며, 쿼리 실행이 중단됩니다.

ERROR 1317 (70100): Query execution was interrupted

이 경우 세션 2의 연결은 유지되지만, RENAME TABLE 쿼리는 더 이상 실행되지 않습니다.

2. MySQL 클라이언트 로그인 시 --skip-reconnect 옵션 사용

MySQL 클라이언트(mysql 명령)로 접속할 때 --skip-reconnect 옵션을 추가하면, 연결이 끊어졌을 때 자동으로 다시 연결을 시도하지 않습니다. 이는 세션을 완전히 종료하고자 할 때 가장 확실한 방법입니다.

세션 2: --skip-reconnect 옵션으로 로그인

mysql -u root -p -h 127.0.0.1 -P 3306 --skip-reconnect

이후 세션 2에서 동일하게 RENAME TABLE products TO archived_products; 명령을 실행하고 잠금 대기 상태로 만듭니다.

세션 3: KILL 명령 실행

SHOW PROCESSLIST;

+-----+------+---------+---------+-----------------+---------+--------------------------------+---------------------------------------+
| Id  | User | Host    | db      | Command         | Time    | State                          | Info                                  |
+-----+------+---------+---------+-----------------+---------+--------------------------------+---------------------------------------+
| 10  | root | localhost | test_db | Sleep           | 4402073 |                                | NULL                                  |
| 11  | root | localhost | test_db | Query           | 4       | Waiting for table metadata lock| RENAME TABLE products TO archived_products|
| 12  | root | localhost | test_db | Query           | 0       | init                           | show processlist                      |
+-----+------+---------+---------+-----------------+---------+--------------------------------+---------------------------------------+

KILL 11;

세션 2: 결과 확인

세션 2에서는 다음과 같은 오류 메시지를 받게 되며, 연결이 완전히 종료됩니다.

ERROR 2013 (HY000): Lost connection to MySQL server during query

이후 세션 2는 자동으로 재연결을 시도하지 않으므로, KILL 명령의 의도대로 세션이 완전히 종료되었음을 확인할 수 있습니다.

정리

MySQL 클라이언트의 --reconnect 옵션은 기본적으로 활성화되어 있어, 서버 연결이 끊겼을 때 자동으로 재연결을 시도합니다. 이는 KILL 명령이 의도대로 작동하지 않는 것처럼 보이게 하는 주된 원인입니다.
KILL CONNECTION (또는 단순히 KILL) 명령은 특정 세션의 모든 쿼리 실행을 중단시킨 후 해당 연결을 종료합니다. 하지만 클라이언트의 --reconnect 옵션이 활성화되어 있다면 세션이 자동으로 다시 연결될 수 있습니다.
KILL QUERY 명령은 특정 세션이 현재 실행 중인 쿼리만 중단시키고, 연결 자체는 유지합니다. 쿼리 실행만 중단하고 싶을 때 유용합니다.
클라이언트의 자동 재연결을 방지하려면 MySQL 클라이언트를 실행할 때 --skip-reconnect 옵션을 사용하여 로그인해야 합니다.

태그: MySQL KILL KILL QUERY reconnect skip-reconnect

7월 4일 02:37에 게시됨

괴물 클럽