MySQL InnoDB 락 / 데드락 해결 공유

InnoDB

MySQL InnoDB란

다른 스토리지 엔진과 다르게 InnoDB에서는 잠금, Lock을 제공해서 동시성을 보장해준다.

InnoDB Lock

자체적인 잠금을 가지고 있지 않은 MyISAM이나 Memory 스토리지 엔진과 달리, InnoDB 엔진은 자체적인 레코드 기반의 잠금 기능을 제공하여 동시성을 보장한다.

Lock(잠금) : 서로 다른 작업에서 같은 자원을 동시에 필요로 할 때 자원 경쟁이 일어나는데, 이때 동시성을 보장하기 위한 기능이다.

InnoDB Lock 종류

Shared Lock & Exclusive Lock

innoDB storage engine은 기본적으로 Row-level lock을 사용한다.

lock을 걸때는 query의 목적에 따라서 두가지 Type의 Lock을 사용한다.

• Read에 대한 Shared Lock (이하 S-lock)
• Write에 대한 Exclusive Lock (이하 X-lock)

S-lock

읽기 잠금. 어떤 트랜잭션에서 데이터를 읽고자 할 때 건다.
다른 shared lock은 허용이 되지만 exclusive lock은 불가하다. 즉, 리소스를 다른 사용자가 동시에 읽을 수 있게 하되 변경은 불가하다.

=> 어떤 자원에 shared lock이 동시에 여러개 적용될 수 있다.
=> 어떤 자원에 shared lock이 하나라도 걸려있으면 exclusive lock을 걸 수 없다.

X-lock

쓰기 잠금. 어떤 트랜잭션에서 데이터를 변경하고자 할 때건다. 해당 트랜잭션이 완료될 때까지 해당 테이블 혹은 레코드(row)를 다른 트랜잭션에서 읽거나 쓰지 못하게 하기 위해 Exclusive lock을 걸고 트랜잭션을 진행시킨다.

=> exclusive lock에 걸리면 shared lock을 걸 수 없다.
=> exclusive lock에 걸린 테이블, 레코드 등의 자원에 대해 다른 트랜잭션이 exclusive lock을 걸 수 없다.

Intention lock

Table-level lock
테이블 안의 “row에 대해서 나중에 어떤 row-level락을 걸 것”이라는 의도를 알려주기 위해 미리 table-level에 걸어두는 lock

아래는 Share lock, Exclusive lock, Intention lock이 각각 다른 트랜잭션에서 사용될때, 충돌(Conflict, 대기상태 빠짐), 또는 호환(Compatible, 대기상태에 빠지지 않음)이 되는지에 대해 정리된 표.

-	X	IX	S	IS
-	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

Record Lock

하나의 인덱스 레코드에만 lock을 거는 것을 의미.

UPDATE a_table SET name='김수정' WHERE id=1

(id는 pk)
id = 1 인 하나의 레코드에 X-Lock이 걸림.

실제 테이블의 레코드에 대해 락을 걸지 않고 인덱스 레코드에 락을 건다.
따로 생성한 인덱스가 없는 테이블은 InnoDB가 자체적으로 생성한 클러스터 인덱스를 이용해 락을 건다.
기본키나 유니크 키에 의한 변경 작업은 갭 락 없이 딱 인덱스 레코드에만 락을 건다.

Next key lock

인덱스 레코드도 잠그고 그 인덱스 레코드 앞, 뒤 갭도 잠근다.

SELECT c1 FROM t WHERE c1 BETWEEN 10 AND 20 FOR UPDATE;

c1=15 인 레코드를 insert 하는 트랜잭션이 있다면 막는다.
반드시 인덱스 레코드 사이에 있는 갭만 락을 거는 게 아니라 제일 앞 또는 뒤에 있는 인덱스 레코드의 갭도 락을 건다.
c1=10인 레코드 인덱스 바로 앞에 c1=8인 레코드 인덱스가 있는 상태라면, c1=9인 레코드를 insert 하려고 하면 막힌다. (그 갭에도 락이 걸려있기 때문에!)

인덱스와 Lock

InnoDB의 잠금은 실제 테이블 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리한다.

예시)

SELECT COUNT(*) FROM employees WHERE first_name='Harry';

-> 253
헤당 employees 테이블에 first_name 컬럼에 인덱스가 걸려있다.
해당 테이블에서 first_name이 Harry인 사원은 전체 253명이 있다.

SELECT COUNT(\*) FROM employees WHERE first\_name='Harry' AND last\_name='Kane';

-> 1
first_name이 Harry이고 last_name이 Kane인 사원은 1명만 있다.

UPDATE employees SET hire_date=NOW() WHERE first_name='Harry' AND last_name='Kane';

UPDATE 쿼리의 조건에서 인덱스를 이용할 수 있는 조건은 first_name='Harry' 이며,
last_name 컬럼이 인덱스에 없기 때문에 first_name='Harry' 인 레코드 253건에 모두 잠금이 걸린다.

데드락

A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither ever release the locks it holds.

데드락은 서로 다른 트랜잭션이 각자 가지는 잠금(lock)으로 인해 처리가 안되는 상황.
각 트랜잭션이 각자 가지고 있는 잠금을 해제하지 않으면서, 다른 트랜잭션이 가지고 있는 잠금이 해제되기를 기다린다.

예시)

클라이언트 A

> START TRANSACTION;  
> SELECT \* FROM t WHERE i = 1 LOCK IN SHARE MODE; (1)

클라이언트 B

> START TRANSACTION;  
> DELETE FROM t WHERE i = 1; (2)

클라이언트 A

> DELETE FROM t WHERE i = 1; (3) ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction  
> try restarting transaction

쿼리를 보면 (1)에서 S-lock이 일어남.

이 후, 클라이언트 B에서 (2) 작업을 하기 위해서는 X- lock 이 필요하기에 (1)의 잠금이 끝나길 기다림

클라이언트 A는 (2)가 실행 된 후 같은 쿼리 (3)을 실행해야 되는데, 이 떄 (3)도 마찬가지로 독점 잠금이 필요.
하지만, 잠금 요청 대기열에는 이미 (2)의 요청이 들어와 있기에 (3)을 위해선 (2)가 끝나길 기다림. (대기열 순서에 따라 (2) 후로 밀림)

(1) <- (2)
(2) <- (3)
(3) <- (1)

(1)의 잠금이 풀리기 위해선 트랜잭션이 끝나야 하는데, (3)의 잠금 대기때문에 트랜잭션을 끝낼 수가 없음.

deadlock 상황을 해결하는 방안은 둘 중 하나의 트랜잭션을 롤백하는 방법 밖에는 없다.
그래서 데이터베이스 엔진은 deadlock으로 판단하게 되면 트랜잭션을 롤백시켜 문제를 해결.

innodb에서는 deadlock 현상이 발생하면 하나의 트랜잭션을 롤백시켜 deadlock을 해결한 후 과정을 로그로 남김.

실제 데드락이 일어난 쿼리

Transaction isolation level(격리 수준) 에 따라 Lock을 거는 상황들이 달라지게 되는데,
아래 예시는 MySQL 기본 설정인 Reapeatalbe Read.

> INSERT INTO T SELECT ... FROM S WHERE ...

T 테이블에 insert 되는 각 Row에 대하여 exculsive Lock을 걸고,
SELECT Table S의 row들에 대해 shared next-key locks 을 건다.

> UPDATE ... WHERE ...  
> sets an exclusive next-key lock on every record the search encounters

(참고: https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html)

데드락 정보 확인

show engine innodb status 명령어를 통해 마지막으로 발생한 데드락 정보를 확인할 수 있다.

참고

https://jeong-pro.tistory.com/94
https://taes-k.github.io/2020/05/17/mysql-transaction-lock/
https://jeong-pro.tistory.com/241
https://moood.dev/db/mysql-innodb-deadlock/
https://steady-coding.tistory.com/553
https://greeng00se.tistory.com/38