[Mysql] InnoDB lock, Dead lock

Mysql 엔진 중 가장 범용적으로 사용되는 InnoDB 에서 활용되는 lock 에 대해 알아보자. lock(잠금)는 왜 필요한 이유는 데이터의 정합성 유지와 프로세스의 동시성을 처리하기 위해 반드시 필요하다.

 

예를 들어 여러 트랜잭션에서 특정 테이블에 insert를 시도 한다고 할 때 primary key 또는 unique key는 중복으로 등록되면 안 될 것이다.이를 위해 어찌되었건 처리는 한번에 하나씩 진행되어야 하고 이 과정에서 먼저 들어온 요청을 처리하는 동안 다음 요청의 접근은 제한하는 lock의 개념이 필요해 진다.

 

그 외에도 많이 사용되는 예로는 은행 입출금 프로세스가 있겠다. 이는 UPDATE 와 관련된 것인데 금액을 출금하는 프로세스를 UPDATE 로 구현했다고 가정해보자. 여러 트랜잭션에서 동시에 출금을 시도할 경우 역시 먼저 요청된 출금으로 통장의 잔액이 모두 소진되었으면 다음 요청에서는 출금을 실패해야 하기 때문에 결국 처리는 한번에 하나씩 진행되어야 하고 역시 lock의 개념이 필요하다.

 

Mysql 기반 시스템에서 병렬처리 로직을 개발하다보면 필요한 지식이기 때문에 아주 간단히 정리해보도록 한다. 논문이나 서적 수준의 지식은 타 블로그에 많으니 여기에서는 꼭 필요한 개념만 간단하게 살펴본다.

 

일단 lock 의 기본 동작은 특정 작업을 수행하기 전에 해당 자원에 대한 lock를 획득하고 작업이 끝난 뒤에 lock 을 해제하는 방식이다. 그리고 lock이 적용되어 있는 동안 타 트랜잭션에서 동일 자원에 대해 lock 을 적용하려고 하면 직전 lock 이 해제될 때까지 대기해야 한다.

 

우리가 일반적 사용하는 CRUD SQL 중 SELECT 를 제외한 구문은 자동으로 lock 이 적용되고 SELECT 구문은 기본적으로 lock 이 적용되지 않는데 아래와 같은 형태로 사용해서 lock 이 적용되도록 할 수 있다.  

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

 

참고로 이와 같이 SELECT 구문으로 lock를 적용하는 것은 해당 시점부터 트랜잭션이 종료될 때까지 읽어온 자원에 대해 lock을 적용하고자 할 때 활용될 수 있다. 즉, 자원을 읽을 때도 동기화가 필요할 때 활용할 수 있다.

 

이렇게 적용된 lock 은 rollback 또는 commit 과 같이 트랜잭션 이 종료되는 시점에 해제된다. 즉, lock 을 걸기 전에 트랜잭션 이 먼저 시작되어 lock을 걸고 작업을 수행하고 lock 를 해제하는 일련의 작업은 하나의 트랜잭션 단위로 진행된다.

 

이쯤에서 집고 넘어갈 것이 하나 있는데 위와 같이 lock이 적용될 경우 해당 자원에 대한 접근 자체가 모두 제한되는 것은 아니다. 기본적으로 lock 을 적용하는 구문 끼리는 접근이 제한되지만 그 중에서도 Shared lock 끼리는 접근이 가능하고 lock을 적용하지 않는 일반 SELECT 구문은 언제든 해당 자원에 접근이 가능하다.

 

일단 lock 의 종류를 살펴보면 아래와 같다.

Shared lock(약어: S)
SELECT ... LOCK IN SHARE MODE 에 의해 적용되는 lock 이다. 타 트랜잭션에서 해당 row에 대해 UPDATE, DELETE 로 접근이 불가능하고 동일한 형태의 SELECT ... LOCK IN SHARE MODE 와 일반 SELECT 구문으로는 접근이 가능하다.

Exclusive lock(약어: X)
UPDATE, DELETE, SELECT ... FOR UPDATE 에 의해 적용되는 lock 이다. 타 트랜잭션에서 해당 row에 대해 UPDATE, DELETE 는 물론이고 SELECT ... LOCK IN SHARE MODE, SELECT ... FOR UPDATE 와 같이 lock 을 적용하려는 SELECT 구문도 접근이 불가능하다. 오로지 일반 SELECT 로만 접근이 가능하다.

Intention shared lock(약어: IS)
SELECT ... LOCK IN SHARE MODE 의해 적용되는 Shared lock 과 함께 적용되는 lock으로 해당 row 외에 table 에도 lock을 적용한다.  이에 Intention shared lock 이 적용된 상태에서는 타 트랜잭션에서 DDL과 같이 table lock을 적용하는 구문은 접근이 제한된다.

Intention exclusive lock(약어: IE)
SELECT ... FOR UPDATE 에 의해 적용되는 Exclusive lock 과 함께 적용되는 lock으로 Intention shared lock 과 동일하게 해당 row 외에 table 에도 lock을 적용한다. 역시 타 트랜잭션에서 DDL 과 같이 table lock 을 적용하는 구문은 접근이 제한된다.

INSERT 구문에는 역시 unique key 가 존재할 경우 중복 등록을 제한하려면 lock이 적용되어야 할 것 같은데 어떤 lock 이 적용될까? 여기에는 Insert intention lock 이라는 특별한 lock이 적용된다. 

 

일단 intention lock 이기 때문에 table lock 이 함께 적용되고 row lock 은 유니크 키에 대해 Exclusive lock 이 적용된다. 즉, 서로 다른 트랙잭션에서 동일한 유니크키에 대해 insert를 수행할 경우 뒤에 수행된 트랜잭션은 접근이 제한된다.

 

이제 이쯤에서 Dead lock 에 대해 논의할 필요가 되었다. lock 이 적용되는 구문은 직전에 적용된 lock 의 해제를 대기하게 되는데 이런 특성으로 Dead lock 이 발생할 수 있다.

 

예를 들어 OLTP 시스템에서는 사용자가 많고 lock 이 적용되는 쿼리가 무거울 때 발생할 수 있다. 게시판의 특정 글을 수정한다고 해보자. UPDATE 문이 사용될 것이고 Exclusive lock 이 적용될 것이다. 그런데 동시에 많은 사용자가 해당 게시물을 수정했고 UPDATE 쿼리가 말도 안되게 느린 상황이라면 서로 다른 트랜잭션에서 서로의 lock 해제를 기다리다가 Dead lock 상태에 빠질 수 있다.

 

OLAP 시스템에서는 병럴처리를 사용할 경우 Dead lock 이 발생할 수 있다. 병렬처리를 하는 각각의 스레드에서 완전히 독립된 자원을 사용한다면 문제가 되지 않겠지만 중복된 자원을 활용할 경우 역시 서로의 lock 해제를 기다리다가 Dead lock 상태에 빠질 수 있다.

 

내가 경험한 것은 대량 insert 였다. 백만건 정도의 데이터를 insert 해야 하는 상황이었고 더 빠르게 진행하기 위해 10개의 스레드로 각각 1천건씩 병렬로 처리를 했는데 각 병렬 작업에서 중복된 유니크키가 사용되었고 Dead lock 이 발생되었다. 이 경우 병렬처리 전에 insert 할 자원에 대해 중복제거를 하면 되는데 이게 여의치 않다면 순차처리 밖에 답이 없다.

 

https://kukuta.tistory.com/215
https://www.letmecompile.com/mysql-innodb-lock-deadlock/
https://myinfrabox.tistory.com/64