MySQL Lock & DeadLock
✅ MySQL Lock?
DB의 특정 자원에 대한 동시 접근을 제어하고, 데이터의 일관성을 보장하는 방식이다. 경합 상태를 방지하기 위해서 Mysql에서는 다양한 헝태의 Lock을 제공한다.
🔎 경합 상태(Race Condition)?
여러 트랜잭션이 자원에 동시 접근하여 사용하려고 경합을 하는 현상을 의미한다.
⭐ Lock의 종류
1. Shard Lock
레코드 레벨에 적용되는 Lock으로 Read Lock으로써 활용된다.
특정 레코드에 해당 Lock이 걸려있다면 Read작업과 Write/Update작업이 동시에 발생하여 Read작업이 우선적으로 이루어지고 있는 동안에는 Write/Update작업은 해당 레코드에 접근할 수 없고 대기한다. 이와는 다르게 여러 개의 Read작업일 경우에는 Read작업이 진행되는 중이라도 대기하지 않고 언제든 접근/참조가 가능하다.
즉, SELECT 작업에 대해서 항상 동일한 결과를 보장해주는 것이 Shard Lock의 목적이 될 것이다.
2. Exclusive Lock
레코드 레벨에 적용되는 Lock으로 Write/Update Lock으로써 활용된다.
동일한 SELECT 작업은 허용하는 Shard Lock과는 다르게 Exclusive Lock은 Lock의 종류와 관계없이 오직 한 작업(트랜잭션)만을 허용한다.
3. Intention Lock (=IS Lock, IX Lock)
테이블 레벨에 적용되는 Lock으로, Shard Lock/Exclusive Lock과 관련된 작업을 처리하고 있을 경우, 해당 작업을 수행하는 테이블에 걸리는 Lock이다. 해당 Lock이 걸려 있는 동안, ALTER, DROP과 같은 테이블 구조의 변경이 요구되는 SQL의 실행이 제한된다.
행 수준의 Lock을 설정하기 이전에, 미리 테이블에 Lock을 걸어둠으로써 다른 트랜잭션과의 충돌을 사전에 방지하는데 도움을 준다.
🔎 예시를 통한 Lock 사용 흐름
- Read 작업에 대한 트랜잭션 발생(= A작업)
- 대상 테이블에 IS Lock 설정
- 대상 행에 Shard Lock 설정
- Write 작업에 대한 트랜잭션 발생(= B작업)
- B작업의 대상이 되는 테이블의 Intention Lock 유무 체크
- 현재 A작업 중이기에 대기
- A작업 종료 후, 대상 테이블에 IX Lock 설정
- 대상 행에 Exclusive Lock 설정
⭐ DeadLock(교착 상태)
여러 트랜잭션이 서로 자원을 얻지 못하여 다음 작업을 진행하지 못하는 상태. 즉, 무한히 다음 자원을 기다리는 상태를 뜻한다. 각각의 작업의 제한 시간을 거는 Timeout이나 DeadLock이 발생한 직후부터 일정 시간이 지난 후 다시 재시도를 하는 Retry와 같은 방법으로 대비할 수 있지만, 근본적으로 DeadLock이 발생하지 않게끔 하는 것이 가장 중요하다.
🛠️ DeadLock 방지법
-
복잡한 쿼리를 최대한 지양
통상적으로 하나의 쿼리만으로 다양한 리소스를 얻고자 하는 경우가 많다. 이러한 쿼리는 최소한의 DB Conneciton만으로 리소스를 받아오기에 효율이 좋다고 보여질 수 있겠으나, 여러 리소스를 동시에 받아오기 위한 복잡한 쿼리 작성으로 인해, 각각의 테이블, 레코드별 Lock이 꼬일 가능성이 높아진다. DeadLock 발생 상황까지 고려하여 하나의 쿼리로 모든 것을 해결하려 하지말고, 간단한 쿼리를 여러번 호출하는 방식 또한 생각해 보아야 할 것이다. -
각 트랜잭션의 Lock의 순서를 지킬 것
한 트랜잭션 내의 Write/Update 작업을 할 때 거는 Lock의 순서를 일관되게 지킨다. 즉, 단순히 각각의 테이블/레코드 별 Lock을 거는 것뿐만 아니라, 순서까지도 통일시켜야 DeadLock을 최대한 방지할 수 있다.트랜잭션X : A테이블 수정 → B테이블 수정
트랜잭션Y : B테이블 수정 → A테이블 수정
위와 같은 두 트랜잭션이 동시에 수행된다고 가정했을 때, A테이블 수정이 끝난 X는 B테이블을 수정 중인 Y가 끝나기를 대기한다. 하지만, Y는 B테이블의 수정이 끝났다고 할지라도 다음 작업인 A테이블 수정을 하기 위해서 A테이블을 점유하고 있는 X를 기다려야하기에 서로 대기하는 DeadLock상황이 발생하게 된다. -
ON DUPLICATE KEY UPDATE
ON DUPLICATE KEY UPDATE는 Insert문을 실행할 때, 중복된 키 값이 있을 경우, 해당 레코드를 Update하는 방식으로 동작한다. 해당 키워드를 통해 동일한 키를 가진 다수의 트랜잭션이 있더라도 DeadLock이 발생하지 않게 된다.
// 기본 키인 ID가 1인 레코드가 존재한다면, 해당 레코드의 name을 갱신
INSERT INTO users (id, name, email) VALUES (1, 'haru', 'test@gmail.com')
ON DUPLICATE KEY UPDATE name = VALUES(name);