1. 개요
이 사용방법(예제)에서는 MySQL의 "Lock wait timeout exceeded" 오류에 대해 설명합니다. 이 오류의 원인과 MySQL 잠금에 관한 약간의 뉘앙스에 대해 논의할 것입니다.
단순화를 위해 가장 많이 사용되는 MySQL의 InnoDB 엔진에 중점을 둘 것입니다. 그러나 여기에 사용된 것과 동일한 테스트를 사용하여 다른 엔진의 동작을 확인할 수 있습니다.
2. MySQL에서 잠금
잠금 은 리소스에 대한 액세스를 제어 하는 특수 개체입니다. MySQL의 경우 이러한 리소스는 테이블, 행 또는 내부 데이터 구조일 수 있습니다.
익숙해져야 할 또 다른 개념은 잠금 모드입니다. 잠금 모드 "S"(공유)를 사용하면 트랜잭션이 행을 읽을 수 있습니다 . 여러 트랜잭션이 동시에 특정 행의 잠금을 획득할 수 있습니다.
"X"(독점) 잠금을 사용하면 단일 트랜잭션이 이를 획득할 수 있습니다. 트랜잭션은 행을 업데이트하거나 삭제할 수 있는 반면 다른 트랜잭션은 잠금이 해제되어 획득할 수 있을 때까지 기다려야 합니다 .
MySQL에는 의도 잠금도 있습니다. 이들은 테이블과 관련이 있으며 트랜잭션이 테이블의 행에서 획득하려는 잠금 종류를 나타냅니다.
잠금은 동시성이 높은 환경에서 일관성과 안정성을 보장하는 데 중요합니다. 그러나 성능을 최적화할 때 약간의 절충이 필요하며 이러한 경우 올바른 격리 수준을 선택하는 것이 중요합니다.
3. 격리 수준
MySQL InnoDB는 네 가지 트랜잭션 격리 수준 을 제공합니다 . 성능, 일관성, 신뢰성 및 재현성 간에 다양한 수준의 균형을 제공합니다. 그것들은 각각 가장 덜 엄격한 것부터 가장 엄격한 것까지입니다:
- READ UNCOMMITTED : 즉, 모든 트랜잭션은 커밋되지 않은 경우에도 다른 사람이 변경한 모든 변경 사항을 읽을 수 있습니다.
- READ COMMITTED: 커밋된 변경 사항만 다른 트랜잭션에서 볼 수 있습니다.
- REPEATABLE READ: 첫 번째 쿼리는 스냅샷을 정의하고 해당 행의 기준선이 됩니다. 읽기 직후에 다른 트랜잭션이 행을 변경하더라도 첫 번째 쿼리 이후 변경 사항이 없으면 항상 기준선이 반환됩니다.
- SERIALIZABLE: 자동 커밋이 비활성화된 경우 업데이트 또는 삭제 중에 행을 잠그고 커밋 후에만 읽기가 허용된다는 점을 제외하면 이전 것과 동일하게 동작합니다.
이제 서로 다른 격리 수준이 작동하는 방식을 이해했으므로 몇 가지 테스트를 실행하여 잠금 시나리오를 조사해 보겠습니다. 먼저 짧게 유지하기 위해 기본 격리 수준인 REPEATABLE READ에서 모든 테스트를 실행합니다. 그러나 나중에 다른 모든 수준에 대한 테스트를 실행할 수 있습니다.
4. 모니터링
여기에서 보게 될 도구는 프로덕션 용도로 반드시 적용되는 것은 아닙니다. 대신 내부에서 일어나는 일을 이해할 수 있게 해줍니다.
명령은 MySQL이 트랜잭션을 처리하는 방법과 어떤 잠금이 어떤 트랜잭션과 관련되는지 또는 그러한 트랜잭션에서 더 많은 데이터를 얻는 방법을 설명합니다. 따라서 이러한 도구는 테스트 중에 도움이 되지만 프로덕션 환경에는 적용되지 않거나 적어도 오류가 이미 발생한 경우에는 적용되지 않을 수 있습니다.
4.1. InnoDB 상태
SHOW ENGINE INNODB STATUS 명령 은 내부 구조, 개체 및 메트릭에 대한 많은 정보를 보여줍니다 . 사용 가능한 활성 연결 수에 따라 출력이 잘릴 수 있습니다. 그러나 사용 사례에 대한 트랜잭션 섹션만 보면 됩니다.
트랜잭션 섹션에서 다음과 같은 항목을 찾을 수 있습니다.
- 활성 거래 수
- 각 트랜잭션의 상태
- 각 트랜잭션과 관련된 테이블 수
- 트랜잭션에서 획득한 잠금 수
- 아마도 트랜잭션을 보유하고 있을 수 있는 실행된 명령문
- 잠금 대기에 대한 정보
거기에는 더 많은 볼거리가 있지만 지금은 이것으로 충분할 것입니다.
4.2. 프로세스 List
SHOW PROCESSLIST 명령 은 현재 열려 있는 세션이 있는 테이블을 표시하며 테이블에는 다음 정보가 표시됩니다.
- 세션 ID
- 사용자 이름
- 연결된 호스트
- 데이터 베이스
- 명령/현재 활성 명령문 유형
- 시간을 실행
- 연결 상태
- 세션 설명
이 명령을 사용하면 다양한 활성 세션, 해당 상태 및 활동에 대한 개요를 얻을 수 있습니다.
4.3. 문 선택
MySQL은 몇 가지 테이블을 통해 몇 가지 유용한 정보를 제공하며 주어진 시나리오에 적용되는 잠금 전략의 종류를 이해하는 데 사용할 수 있습니다. 그들은 또한 현재 트랜잭션의 ID와 같은 것을 보유합니다.
이 기사에서는 information_schema.innodb_trx 및 performance_schema.data_locks 테이블을 사용합니다 .
5. 테스트 설정
테스트를 실행하기 위해 MySQL의 도커 이미지를 사용하여 데이터베이스를 만들고 테스트 스키마를 채워 일부 트랜잭션 시나리오를 실행할 수 있습니다.
# Create MySQL container
docker run --network host --name example_db -e MYSQL_ROOT_PASSWORD=root -d mysql데이터베이스 서버가 있으면 연결하고 스크립트를 실행하여 스키마를 생성할 수 있습니다.
# Logging in MySQL
docker exec -it example_db mysql -uroot -p그런 다음 암호를 입력한 후 데이터베이스를 만들고 일부 데이터를 삽입합니다.
CREATE DATABASE example_db;
USE example_db;
CREATE TABLE zipcode (
code varchar(100) not null,
city varchar(100) not null,
country varchar(3) not null,
PRIMARY KEY (code)
);
INSERT INTO zipcode(code, city, country)
VALUES ('08025', 'Barcelona', 'ESP'),
('10583', 'New York', 'USA'),
('11075-430', 'Santos', 'BRA'),
('SW6', 'London', 'GBR');
6. 테스트 시나리오
기억해야 할 가장 중요한 점은 트랜잭션이 다른 트랜잭션에서 획득한 잠금을 기다리고 있을 때 "잠금 대기 시간 초과 초과" 오류가 발생한다는 것입니다.
트랜잭션이 대기하는 시간 은 전역 또는 세션 수준에서 정의된 innodb_lock_wait_timeout 속성의 값에 따라 다릅니다 .
이 오류에 직면할 가능성은 복잡성과 초당 트랜잭션 수에 따라 다릅니다. 그러나 몇 가지 일반적인 시나리오를 재현하려고 합니다.
언급할 가치가 있는 또 다른 요점은 간단한 재시도 전략으로 이 오류로 인해 발생한 문제를 해결할 수 있다는 것입니다.
테스트 중에 도움이 되도록 열려 있는 모든 세션에 대해 다음 명령을 실행합니다.
USE example_db;
-- Set our timeout to 10 seconds
SET @@SESSION.innodb_lock_wait_timeout = 10;
이는 잠금 대기 제한 시간을 10초로 정의하여 오류를 보기까지 너무 오래 기다리지 않도록 합니다.
6.1. 행 잠금
여러 상황에서 행 잠금을 획득하므로 샘플을 재현해 봅시다.
먼저 앞에서 본 로그인 MySQL 스크립트를 사용하여 두 개의 서로 다른 세션에서 서버에 연결합니다. 그런 다음 두 세션에서 아래 명령문을 실행해 보겠습니다.
SET autocommit=0;
UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';10초 후 두 번째 세션이 실패합니다.
mysql> UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
자동 커밋 비활성화로 인해 첫 번째 세션이 트랜잭션을 시작하기 때문에 오류가 발생합니다. 다음으로 UPDATE 문이 트랜잭션 내에서 실행되면 해당 행의 배타적 잠금이 획득됩니다. 그러나 커밋이 실행되지 않아 트랜잭션이 열린 상태로 유지되고 다른 트랜잭션이 계속 대기하게 됩니다. 커밋이 발생하지 않기 때문에 잠금 대기 시간 초과가 한계에 도달합니다. 이는 DELETE 문에도 적용됩니다.
6.2. 데이터 잠금 테이블에서 행 잠금 확인
이제 두 세션에서 롤백하고 첫 번째 세션에서 이전과 같이 스크립트를 실행하되 이번에는 두 번째 세션에서 다음 명령문을 실행하겠습니다.
SET autocommit=0;
UPDATE zipcode SET code = 'Test' WHERE code = '08025';관찰할 수 있듯이 두 문은 더 이상 동일한 행의 잠금이 필요하지 않기 때문에 성공적으로 실행됩니다.
이를 확인하기 위해 랜덤의 세션 또는 새 세션에서 다음 명령문을 실행합니다.
SELECT * FROM performance_schema.data_locks;위의 명령문은 4개의 행을 반환합니다. 그 중 2개는 트랜잭션이 테이블의 행을 잠그도록 지정하는 테이블 의도 잠금이고 다른 2개는 레코드 잠금 입니다. LOCK_TYPE , LOCK_MODE 및 LOCK_DATA 열을 보면 방금 설명한 잠금을 확인할 수 있습니다.
세션과 쿼리 모두에서 롤백을 다시 실행하면 결과는 빈 데이터 세트입니다.
6.3. 행 잠금 및 인덱스
이번에는 WHERE 절에서 다른 열을 사용하겠습니다. 첫 번째 세션에서는 다음을 실행합니다.
SET autocommit=0;
UPDATE zipcode SET city = 'SW6 1AA' WHERE country = 'USA';두 번째 명령문에서는 다음 명령문을 실행해 보겠습니다.
SET autocommit=0;
UPDATE zipcode SET city = '11025-030' WHERE country = 'BRA';예상치 못한 일이 일어났습니다. 문이 두 개의 다른 행을 대상으로 하지만 잠금 시간 초과 오류가 발생했습니다. 좋습니다. performance_schema.data_locks 테이블에서 SELECT 문을 실행한 직후에 이 동일한 테스트를 반복하면 실제로 첫 번째 세션이 모든 행을 잠그고 두 번째 세션이 대기 중인 것을 볼 수 있습니다.
문제는 WHERE 절 에 사용된 열에 인덱스가 없기 때문에 MySQL이 업데이트 후보를 찾기 위해 쿼리를 실행하는 방법과 관련이 있습니다. MySQL은 WHERE 조건 과 일치하는 행을 찾기 위해 모든 행을 스캔해야 하며 이로 인해 이러한 행도 잠깁니다.
우리의 진술이 최적인지 확인하는 것이 중요합니다 .
6.4. 여러 테이블에서 행 잠금 및 업데이트/삭제
잠금 시간 초과 오류에 대한 다른 일반적인 경우는 여러 테이블과 관련된 DELETE 및 UPDATE 문입니다. 잠긴 행의 수는 명령문 실행 계획에 따라 다르지만 관련된 모든 테이블에 잠긴 행이 있을 수 있음을 염두에 두어야 합니다.
예를 들어 다른 모든 트랜잭션을 롤백하고 다음 명령문을 실행해 보겠습니다.
CREATE TABLE zipcode_backup SELECT * FROM zipcode;
SET autocommit=0;
DELETE FROM zipcode_backup WHERE code IN (SELECT code FROM zipcode);
여기에서 우리는 테이블을 생성하고 zipcode 테이블 에서 읽고 zipcode_backup 테이블에 단일 문으로 쓰는 트랜잭션을 시작했습니다.
다음 단계는 두 번째 세션에서 다음 명령문을 실행하는 것입니다.
SET autocommit=0;
UPDATE zipcode SET code = 'SW6 1AA' WHERE code = 'SW6';다시 한 번, 트랜잭션 2가 첫 번째 트랜잭션이 테이블의 행 잠금을 획득했기 때문에 시간 초과되었습니다. 무슨 일이 일어났는지 보여주기 위해 data_lock 테이블에서 SELECT 문을 실행해 봅시다 . 그런 다음 두 세션을 모두 롤백하겠습니다.
6.5. 임시 테이블을 채울 때 행 잠금
이 예에서는 새 스크립트의 첫 번째 세션에서 실행되는 DDL과 DML을 혼합해 보겠습니다.
CREATE TEMPORARY TABLE temp_zipcode SELECT * FROM zipcode;
그런 다음 두 번째 세션에서 이전에 사용한 문장을 반복하면 잠금 오류를 다시 한 번 볼 수 있습니다.
6.6. 공유 및 배타적 잠금
각 테스트가 끝날 때 두 세션 트랜잭션을 모두 롤백하는 것을 잊지 마십시오.
우리는 이미 공유 잠금과 배타 잠금에 대해 논의했습니다. 그러나 LOCK IN SHARE MODE 및 FOR UPDATE 옵션 을 사용하여 명시적으로 정의하는 방법을 보지 못했습니다 . 먼저 공유 모드를 사용하겠습니다.
SET autocommit=0;
SELECT * FROM zipcode WHERE code = 'SW6' LOCK IN SHARE MODE;이제 이전과 동일한 업데이트를 실행하고 결과는 다시 시간 초과입니다. 그 외에도 여기에서 읽기가 허용된다는 점을 기억해야 합니다.
SHARE MODE 와 달리 FOR UPDATE 는 첫 번째 세션에서 명령문을 실행할 때 다음과 같이 읽기 잠금을 허용하지 않습니다.
SET autocommit=0;
SELECT * FROM zipcode WHERE code = 'SW6' FOR UPDATE;그런 다음 첫 번째 세션에서 이전에 사용된 SHARE MODE 옵션을 사용 하여 동일한 SELECT 문을 실행 하지만 이제 두 번째 세션에서 시간 초과 오류를 한 번 더 관찰합니다. 요약하면 SHARE MODE 잠금은 둘 이상의 세션에 대해 획득할 수 있으며 쓰기를 잠급니다. 배타적 잠금 또는 FOR UPDATE 옵션은 읽기를 허용하지만 읽기 또는 쓰기를 잠그지는 않습니다.
6.7. 테이블 잠금
테이블 잠금 에는 시간 초과가 없으며 InnoDB에는 권장되지 않습니다.
LOCK TABLE zipcode WRITE;
이것을 실행하면 다른 세션을 열고 선택 또는 업데이트를 시도하고 잠길지 확인할 수 있지만 이번에는 시간 초과가 발생하지 않습니다. 조금 더 나아가 세 번째 세션을 열고 다음을 실행할 수 있습니다.
SHOW PROCESSLIST;상태와 함께 활성 세션을 표시하고 첫 번째 세션은 잠자고 있고 두 번째 세션은 테이블의 메타데이터 잠금을 기다리고 있는 것을 볼 수 있습니다. 이 경우 솔루션은 다음 명령을 실행하는 것입니다.
UNLOCK TABLES;일부 메타데이터 잠금을 획득하기 위해 대기 중인 세션을 찾을 수 있는 다른 시나리오는 ALTER TABLE s와 같이 DDL을 실행하는 동안입니다.
6.8. 갭 락
간격 잠금 은 인덱싱된 레코드의 특정 간격이 잠겨 있고 다른 세션이 이 간격 내에서 일부 작업을 수행하려고 할 때 발생합니다. 이 경우 인서트도 영향을 받을 수 있습니다.
첫 번째 세션에서 실행된 다음 문을 살펴보겠습니다.
CREATE TABLE address_type ( id bigint(20) not null, name varchar(255) not null, PRIMARY KEY (id) );
SET autocommit=0;
INSERT INTO address_type(id, name) VALUES (1, 'Street'), (2, 'Avenue'), (5, 'Square');
COMMIT;
SET autocommit=0;
SELECT * FROM address_type WHERE id BETWEEN 1 and 5 LOCK IN SHARE MODE;두 번째 세션에서는 다음 문을 실행합니다.
SET autocommit=0;
INSERT INTO address_type(id, name) VALUES (3, 'Road'), (4, 'Park');데이터 잠금을 실행한 후 세 번째 세션에서 명령문을 선택하여 새 LOCK MODE 값인 GAP 를 확인할 수 있습니다 . 이는 UPDATE 및 DELETE 문에도 적용할 수 있습니다.
6.9. 교착 상태
기본적으로 MySQL은 교착 상태를 식별하려고 시도하고 트랜잭션 간의 의존성 그래프를 해결하는 경우 다른 작업이 통과할 수 있도록 작업 중 하나를 자동으로 종료합니다. 그렇지 않으면 앞에서 본 것처럼 잠금 시간 초과 오류가 발생합니다.
간단한 교착 상태 시나리오를 시뮬레이션해 봅시다. 첫 번째 세션에서는 다음을 실행합니다.
SET autocommit=0;
SELECT * FROM address_type WHERE id = 1 FOR UPDATE;
SELECT tx.trx_id FROM information_schema.innodb_trx tx WHERE tx.trx_mysql_thread_id = connection_id();
마지막 SELECT 문은 현재 트랜잭션 ID를 제공합니다. 나중에 로그를 확인하는 데 필요합니다. 그런 다음 두 번째 세션에서 다음을 실행해 보겠습니다.
SET autocommit=0;
SELECT * FROM address_type WHERE id = 2 FOR UPDATE;
SELECT tx.trx_id FROM information_schema.innodb_trx tx WHERE tx.trx_mysql_thread_id = connection_id();
SELECT * FROM address_type WHERE id = 1 FOR UPDATE;시퀀스에서 세션 1로 돌아가서 다음을 실행합니다.
SELECT * FROM address_type WHERE id = 2 FOR UPDATE;즉시 오류가 발생합니다.
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
마지막으로 세 번째 세션으로 이동하여 다음을 실행합니다.
SHOW ENGINE INNODB STATUS;명령의 출력은 다음과 유사해야 합니다.
------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 4036, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 9, OS thread handle 139794615064320, query id 252...
SELECT * FROM address_type WHERE id = 1 FOR UPDATE
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4036 lock_mode X locks rec but not gap
Record lock
...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4036 lock_mode X locks rec but not gap waiting
Record lock
...
*** (2) TRANSACTION:
TRANSACTION 4035, ACTIVE 59 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), ... , 2 row lock(s)
MySQL thread id 11, .. query id 253 ...
SELECT * FROM address_type WHERE id = 2 FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4035 lock_mode X locks rec but not gap
Record lock
...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS ... index PRIMARY of table `example_db`.`address_type` trx id 4035 lock_mode X locks rec but not gap waiting
Record lock
...
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 4037
...
LIST OF TRANSACTIONS FOR EACH SESSION:
...
---TRANSACTION 4036, ACTIVE 18 sec
3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 9, ... , query id 252 ...
이전에 얻은 트랜잭션 ID를 사용하면 오류가 발생한 순간의 연결 상태, 행 잠금 수, 마지막으로 실행된 명령, 잠금 유지에 대한 설명, 트랜잭션이 기다리고 있는 잠금에 대한 설명입니다. 그 후 교착 상태에 관련된 다른 트랜잭션에 대해 동일한 작업을 반복합니다. 또한 결국 롤백된 트랜잭션에 대한 정보를 찾습니다.
7. 결론
이 기사에서는 MySQL의 잠금, 작동 방식 및 "Lock wait timeout 초과" 오류가 발생하는 시기를 살펴보았습니다.
이 오류를 재현하고 트랜잭션을 처리할 때 데이터베이스 서버의 내부 뉘앙스를 확인할 수 있는 테스트 시나리오를 정의했습니다.
