[MySQL] 동시성 문제의 해결을 위한 for update

2020/05/09 - [프로그래밍/Database] - [MySQL] Lock wait timeout exceeded

[MySQL] Lock wait timeout exceeded

Lock wait timeout exceeded 문제를 직면하고 이를 해결하는 과정에서 새롭게 학습하고, 기존의 지식을 재정리하게 된 또 하나의 주제로 For Update를 다뤄보고자 한다.

 

Web Service의 특징과 필수 조건

For Update에 대한 글 이전에 InnoDB와 MyISAM에 대한 글(링크)을 우선적으로 다룬 이유가 있다.

 

Web Service의 기본적인 전제는 "동시성(Concurrency)"을 다룰 수 있어야 한다는 점이다.

특정 시점에 복수의 User(사용자)의 요청이 발생하기 때문에 데이터의 일관성에 대한 처리가 필수적이다.

 

가장 많이 사용하는 Storage Engine에서는 이러한 동시성 문제를 해결하기 위해 어떤 Locking 방법을 사용하는 지, 선제적으로 언급하는 것이 논리적이기에 사전에 다룬 것이었다.

 

복습 겸 다시 살펴보면,

MyISAM을 사용할 경우 테이블 단위의 잠금(Table Level Locking)이 이뤄진다.

하지만 동시성의 상황에서 생각해본다면 Table 전체에 대해서 Lock을 하기 때문에 동시성 문제를 해결하기에는 적합하지 않다.

동시간이 유입된 사용자의 요청이 동일 Table의 다른 Row를 타겟으로 할 경우, 전체 Table이 잠겨있기 때문에 2번째 요청에 대해서는 처리가 불가능하기 때문이다.

 

그렇다면 InnoDB에서는 어떨까?

InnoDB는 기본적으로 행 단위의 잠금(Row Level Locking)이 이뤄진다.

즉, 한 테이블 내에서 하나의 Row에 대한 CRUD 작업이 이뤄질 때 해당 Row로의 접근만 불가능할 뿐 다른 Row에 접근하는 것은 허용된다.

이러한 특성으로 인해 다중 사용자 환경이 고려된 서비스에서는 일반적으로 InnoDB를 Storage Engine으로 많이 사용하고 있다.

 

MySQL의 FOR UPDATE문

Transaction을 시작한 뒤, FOR UPDATE 문을 사용하면 조회 된 row에 대해서는 Transaction이 종료(Commit, Rollback)될 때 까지 CRUD가 모두 차단된다.

해당 Row에 대한 access가 발생할 경우, 해당 request에는 Lock Wait이라는 상황으로 응답하며 Transaction이 종료될 때까지 기다리도록 하는 것이다.

 

mysql> START TRANSACTION;
mysql> SELECT * FROM table WHERE idx = 1 FOR UPDATE;
mysql> COMMIT;

위와 같은 query가 있다면, 3번째 줄의 Commit 이전까지는 idx=1에 해당하는 row에는 다른 요청이 허용되지 않는다.

 

위에 첨부한 링크 속의 Lock Wait Timeout Exceeded 에러가 발생했을 당시, 가장 먼저 시도해본 방법이 Select query에서 For Update 구문을 삭제한 채 쿼리를 재구성하는 것이었다.

Lock이 발생한 Row에 접근하기 위해 무한히 대기하는 상황 즉, Deadlock이 발생하는 상황일 수도 있기 때문이다.