[MySQL] Table Lock (feat. Deadlock 💀)

틀린 내용을 발견하시면 말씀 부탁드립니다! 🙇

---

 

살려줘...

특정 테이블이 업데이트될 때 동작하는 트리거에서 수정할 부분이 있어 수정 후 저장을 하려고 했는데 계속 Table Lock 걸리는 이슈가 생겼다. 이슈는 (팀장님이) 해결했는데,-뭔지는 모르겠지만 트랜잭션 1개가 계속 동작하고 있었고 해당 트랜잭션을 kill하니 트리거 수정 내용이 정상 반영됨- 정확히 어떤 상황에서 Lock이 발생하고 이를 해결하기 위해 뭘 확인해야 하는지 궁금한 관계로 이번 기회에 정리하기로 했다.

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

# 문제있는 트랜잭션을 찾아 TRX_ID를 kill

cf) INFORMATION_SCHEMA.INNODB_TRX: 트랜잭션이 락 때문에 대기 상태인지 뿐만 아니라 트랜잭션이 시작된 시간, 트랜잭션이 실행된 쿼리 등 현재 동작하는 모든 트랜잭션에 대한 정보를 볼 수 있다.


궁금한 건 아래와 같고 순서대로 정리해 볼 예정이다.

1. Table Lock은 무엇일까?
2. 트리거 적용이 Deadlock과 관련 있을까?
3. Deadlock을 피하기 위해서는 어떻게 해야 할까?

1. Table Lock은 무엇일까?

Lock은 트랜잭션 처리의 순차성을 보장해준다. DB는 다수의 클라이언트가 동일한 데이터에 동시에 접근하는 것을 제어해야 한다 (동시성 제어). 동시성 제어가 제대로 이루어지지 않는다면 데이터가 잘못 업데이트되거나 변경될 수 있다. 예를 들어내가 A 테이블을 수정하고 있는데 다른 사람이 동시에 A 테이블의 데이터를 수정한다면 데이터의 일관성과 무결성이 깨질 것이다. 동시성 제어는 트랜잭션을 통해 관리되며 트랜잭션은 Lock을 통해 데이터에 대한 동시 접근을 제어하여 두 가지 중요한 데이터베이스 목표를 달성한다. (일관성 & 무결성) 한 줄로 풀어서 말하면, 1개의 트랜잭션에서 특정 테이블의 데이터를 읽고/쓰고/수정하고/삭제할 때, 외부에서 해당 테이블의 데이터를 수정할 수 없게끔 Table Lock을 걸고 트랜잭션이 끝나면(commit or rollback) Lock은 해제된다.

[동시성 제어와 동시성 보장]
두 개는 서로 대립되는 것이지만 적정 수준에서 타협해야 애플리케이션 품질을 높일 수 있다. 동시성 제어를 엄격하게 유지한다면 ACID 원칙을 보장할 수 있으나 속도가 느려질 것이고, 반대로 동시성 보장을 위해 데이터 동시 수정을 허용하면 ACID 원칙이 깨져버릴 것이다. 타협점은 어디까지이며 이를 어떻게 컨트롤할 수 있을까?

 

2. 트리거 적용이 Deadlock과 관련 있을까?

Deadlock은 2개 이상의 트랜잭션 (또는 애플리케이션)이 동시에 동일한 리소스에 접근하는 상황에서 무한 대기 상태 (교착 상태)에 빠지는 것을 의미한다. 예를 들어 트랜잭션이 시작되면 Lock 상태가 되는데, 2개의 트랜잭션이 동일한 리소스에 접근하는 상황에서는 서로의 Lock 때문에 리소스에 접근하지 못하고 계속 대기하게 되는 것이다. 트리거 적용이 Deadlock과 관련이 있을까? 에 대한 답은 '그렇다'이다. 정확히 이야기하면 트리거 자체의 문제라기보다 Deadlock을 유발하는 요소가 있는 트리거를 작성했을 때의 문제이다.

참고) Lock 상태인 테이블에 트리거가 존재한다면, 해당 트리거에 명시된 테이블들은 모두 Lock상태가 된다. Lock 걸린 테이블의 트리거가 다른 테이블을 Lock 상태로 만들 수 있다는 것이다. 아래 쿼리는 MySQL 공식 문서에서 가져온 예시이다.

Write Lock: 다른 세션이 해당 테이블 접근 불가능 → 내가 A 테이블의 내용을 INSERT/UPDATE/DELETE 할 때, Write 작업이 끝나기 전까지 다른 사람이 읽기/쓰기 못하게 막는 것.

 

Read Lock: 다른 세션이 해당 테이블의 데이터읽기만 가능 → 내가 A 테이블의 내용을 SELECT 할 때, Read 작업이 끝나기 전까지 다른 사람이 쓰기 작업할 수 없게 막는 것.

# t1 테이블에 write lock, t2 테이블에 read lock 
LOCK TABLES t1 WRITE, t2 READ;

# t1 테이블의 insert 트리거 생성
CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW
BEGIN
  UPDATE t4 SET count = count+1
      WHERE id = NEW.id AND EXISTS (SELECT a FROM t3);
  INSERT INTO t2 VALUES(1, 2);
END;

• t1 : LOCK TABLE t1 WRITE 쿼리로 인해 Write Lock 상태
• t2: LOCK TABLE t2 READ 쿼리로 Read Lock 걸었지만, t1의 t1_a_ins 트리거 내에서 t2 테이블에 INSERT 하는 쿼리가 명시되었기 때문에 Write Lock 상태
• t3: t1의 t1_a_ins 트리거 내에서 t3 테이블을 SELECT 하는 쿼리가 명시되었기 때문에 Read Lock 상태
• t4: t1의 t1_a_ins 트리거 내에서 t4 테이블을 UPDATE 하는 쿼리가 명시되었기 때문에 Write Lock 상태

그럼 만약 트리거 안에서 동일한 테이블에 UPDATE, SELECT 쿼리를 적용한다면 어떻게 될까? (내 생각) 먼저 해당 테이블에 Write Lock이 걸리게 되고, 그다음 SELECT 하는 구문이 나왔으니 해당 테이블에 접근하지 못해 대기 상태로 머물 것이다. 그리고 계속 대기 상태이다 보니 트리거는 작업이 끝나지 않을 것이므로 이 경우에도 데드락 상태가 되지 않을까 싶다. → 테스트 결과: 트리거 안에서 동일한 테이블에 순서대로 UPDATE, SELECT 쿼리를 적용했을 때 데드락 안 걸리고 정상으로 작업이 수행됐다. 

 

3. Deadlock을 피하기 위해서는 어떻게 해야 할까?

• 인덱스를 잘 구성하여 더 적은 인덱스 레코드를 스캔하고 더 적은 행에 Lock이 걸리도록 하자. MySQL InnoDB엔진은 자동으로 Row Level Locking이 사용되지만 테이블에 인덱스 적용이 안되어 있다면 Full Scan을 하게 됨으로써 모든 행에 Lock이 걸리게 된다. (EXPLAIN 활용)
• SHOW ENGINE INNODB STATUS를 통해 가장 최근의 Deadlock 원인을 살펴보고 애플리케이션을 튜닝하여 Deadlock을 예방하자.
• Deadlock이 빈번하게 발생한다면 innodb_print_all_deadlocks 옵션을 활성화하여 디버깅에 필요한 정보들을 수집하자. 해당 옵션을 활성화하면 각각의 Deadlock 정보가 MySQL error log에 기록된다. (디버깅을 마치면 해당 옵션은 비활성화하자)
• 트랜잭션은 작은 단위로 구성함으로써 충돌 가능성을 줄이자.
• 일련의 수정 내용은 즉시 커밋/롤백하자. 특히 Interactive session에서 커밋/롤백되지 않은 트랜잭션 상태를 오랫동안 유지하지 말자.
• 트랜잭션에서 여러 개의 테이블을 수정하거나 동일 테이블의 여러 개 행들을 수정하는 경우, 함수/프로시저 사용을 통해 항상 동일한 순서로 실행하자. 
  
  

 

---

참고 문서

• https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html
• https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks-handling.html
• https://suhwan.dev/2019/06/09/transaction-isolation-level-and-lock/
• http://labs.brandi.co.kr/2019/06/19/hansj.html
• https://mysqldba.tistory.com/54
• https://blog.appkr.dev/cheatsheet/understanding-mysql-db-lock/
• https://blog.yevgnenll.me/posts/what-is-acid-about-transaction
• https://serverfault.com/questions/375136/what-is-the-difference-between-wait-timeout-and-interactive-timeout