MySQL DEAD LOCK

1. 들어가며

멀티 스레드를 다룰 때 데드락을 디버깅하는 것은 쉽지 않습니다. 발생 조건을 항상 재현할 수 있는 것도 아니고, 코드가 복잡할 수록 원인 분석이 더 어려워집니다. 거기에 더해 MySQL의 데드락은, MySQL이 가진 락의 종류와 걸리는 조건이 단순하지 않기 때문에 더 골치 아픈 문제로 다가올 때가 많은 것 같습니다. 

https://rawshrimpsushi.tistory.com/80

MySQL InnoDB의 락

 

이 글에서 MySQL LOCK에 대해 간략히 다루고 있으니 참고해주세요!

 

오늘은 MySQL에서 발생하는 데드락에 대해 몇 가지 예제를 살펴보고 해결 방법에 대해서도 짧게 살펴보고자 합니다.

 

2. 데드락 예제

 

1) 간단한 예제

UPDATE wallet SET amount = amount - 100 WHERE user_id = 'A';	UPDATE wallet SET amount = amount - 100 WHERE user_id = 'B';
	UPDATE wallet SET amount = amount - 100 WHERE user_id = 'A';
UPDATE wallet SET amount = amount - 100 WHERE user_id = 'B';

 

• 위 상황에서 두 개의 스레드는 각각 트랜잭션을 시작하게 되는데, 본인 잔고를 감소시키고 다른 사용자에 대해 더하는 간단한 예제입니다.
• UPDATE는 기본적으로 X Lock을 걸기 때문에 데드락이 발생할 수 있습니다.

해결방법

• 가장 간단한 회피 방법: 전자 지갑에 있는 돈을 먼저 빼고 더하는 방식보다는 user_id 순서대로 더하기 빼기를 처리해주면 됩니다.
• Index 순서대로 잠금 실행 시, Lock Wait는 발생할 수 있지만 deadLock은 발생하지 않습니다.
• 두 트랜잭션이 serializable하게 처리되어서 늦어 보이지만 deadlock은 발생하지 않습니다.

2) INSERT의 중복 레코드에 대한 S-LOCK

Tx-1> BEGIN; DELETE FROM tab WHERE pk = 1;

Tx-2> BEGIN; INSERT INTO tab(pk) VALUES(2)

Tx-3> BEGIN; INSERT INTO tab(pk) VALUES(2)

Tx-4> COMMIT;

 

• DELETE가 아직 커밋되지 않은 상황에서
• 2번 트랜잭션과 3번 트랜잭션에서 insert 하려고 하면 어떻게 될까요?
• 2, 3 모두 INSERT 시 중복된 레코드에 대한 S-lock이 필요해 대기하게 됩니다.
• 그래서 tx-2와 tx-3 모두 S-lock을 가지고 기다리기 때문에 X-lock 획득 대기를 하게 됩니다.

왜 위와 같은 일이 벌어진 걸까요?

• 레코드가 삭제 되면 해당 레코드를 영구 삭제하지 않고 일정 시간까지는 삭제 표시만 하게 됩니다.
• 그래서 mySQL에서는 해당 기간동안은 삭제된 레코드로 인식합니다. 즉 유효성이 사라지지 않습니다.
• 중복된 프라이머리 키 값이 없기 위해 해당 레코드에 대해 반드시 S-lock을 잡아야 합니다.
• 하나의 문장에서 여러 INSERT가 실행될 때 항상 동일한 결과를 만들기 위해서입니다.
• Tx-2와 Tx-3은 삭제된 레코드에 대해 S-lock과 X-lock이 순서대로 필요하여 데드락이 발생해버린 것입니다.

 

3) 갭락

Session-1: BEGIN;	Session-2: BEGIN;
SELECT  *FROM tb_gaplock WHERE id=2 /* Not-Existed id */ FOR UPDATE;	SELECT  *FROM tb_gaplock WHERE id=2 /* Not-Existed id */ FOR UPDATE;
DELETE FROM tb_gaplock WHERE id=2;	DELETE FROM tb_gaplock WHERE id=2;
INSERT INTO tb_gaplock VALUES (2, 'Matt2');
	INSERT INTO tb_gaplock VALUES (2, 'Matt2');

(가정: tb_gaplock record에 id=2인 record는 존재하지 않는다.)

 

이 예제는 id=2인 레코드가 있다면, 먼저 잠금을 걸어서 삭제하고 다시 INSERT하고자 하는 예제입니다. 이 예제에서 SELECT .. FOR UPDATE 문장은 없어져도 똑같이 Dead Lock이 발생하지만 일단 FOR UPDATE를 사용하면 데드락을 해결할 수 있다!는 생각을 깨고자 포함하고자 합니다.

 

위의 예제에서 3번까지의 SQL 명령은 실행 시점이나 순간에 관계없이, 2개 트랜잭션간 상호 간섭이나 대기없이 즉시 실행됩니다. FOR UPDATE인데 어떻게 가능한 걸까요? 실제 tb_gaplock 테이블에 id=2인 레코드가 존재하지 않으므로 SELECT .. FOR UPDATE 문장과 DELETE 문장은 Record Lock 없이 Gap Lock만 획득하게 되는데, Gap Lock은 항상 Shared 모드이므로 서로 충돌하지 않고 잠금 획득이 허용됩니다. 그래서 서로 같은 id=2 레코드에 대해서 SELECT .. FOR UPDATE 와 DELETE 문장이 잠금 대기없이 실행될 수 있는 것입니다.

그런데 4번과 5번의 INSERT 문장은 다른 형태의 잠금인 “INSERT Intention Gap Lock”을 필요로 합니다. 하지만 “INSERT Intention Gap Lock”은 Gap Lock과 호환되지 않기 때문에, 1번 트랜잭션의 INSERT는 2번 트랜잭션이 가진 Gap Lock을 기다리게 되고, 2번 트랜잭션의 INSERT는 1번 트랜잭션이 가진 Gap Lock을 기다리게 되어서 Dead Lock 상황이 발생하게 됩니다.

 

3. MySQL에서의 데드락 처리

그렇다면 MySQL은 데드락이 발생했을 때 어떻게 할까요?

1. MySQL에는 DeadLock detection thread가 모든 트랜잭션이 획득 또는 대기하고 있는 잠금 graph를 계속 감시하고 있습니다.
2. 데드락 감지 시 경합 중인 트랜잭션 중 롤백이 쉬운 트랜잭션을 Victim trx로 선정합니다. (Undo 레코드가 적은 트랜잭션)
3. Victim으로 선정된 트랜잭션은 강제 롤백 처리하고 남은 트랜잭션은 정상 처리 됩니다.

-> 여기서 주의해야될 점은, 언두 레코드가 적은 트랜잭션이기 때문에 주로 배치 작업과 서비스 쿼리 경합 시 배치 프로세스의 트랜잭션이 살아남게 된다는 것입니다. 

또한 deadlock 체크 작업으로 대기가 발생하기도 합니다.

• 구글에서는 해당 성능 저하를 막기 위해 감지 기능을 비활성화 한다고 합니다.
• 그럴 수 있었던 것은 pk 기반의 DML과 SELECT만 사용했기 때문에 데드락이 거의 발생할 일이 없었기 때문입니다.
• 그런데 만약에 해당 option을 비활성화해서 데드락이 발생하면? 잠금 대기 상태로 계속 남게 되고 lock timeout 시간 까지만 대기하게 됩니다.
• 같은 정책을 적용하고 싶다면 lock timeout 대기시간 기본값 50초는 너무 크므로 2~3초로 조정하는 것도 검토가 필요합니다.

 

MySQL deadlock 해석은 쉽지 않습니다. 로그에도 3개의 트랜잭션이 맞물렸지만 2개의 트랜잭션만 쓰여 있는 등, 로그만 믿어서는 안 되고, 동일 SQL 문장이더라도 항상 동일한 잠금을 사용하지도 않습니다. (데이터 상태, 동시 실행중인 잠금 경합 DML) 또한 gap lock과 같은 특수한 락도 존재합니다. 그리고 잠금의 대상은 모든 인덱스 키라는 어려움도 있습니다.

 

• REPEATABLE READ는 사용하는 잠금이 많아 파악하기 더 힘든 편입니다.
• 참고로 mySQL 서버에서 unique Index는 성능적인 장점은 없지만 잠금 횟수나 데드락 빈도만 높이는 경우가 많을 수 있다고 합니다..
• 심지어 데드락 해결을 위해 모델 변경이 필요할 때도 있습니다.
• 그래서 데드락이 발생하면 데드락을 해결할 지, retry를 짤 지 고민해봐야한다고 합니다.