[MySQL] SELECT FOR UPDATE란?

 

1. SELECT FOR UPDATE

SELECT * FROM Student WHERE id = 1 FOR UPDATE;

SELECT FOR UPDATE를 실행하면 조회된 레코드에 대해 LOCK이 설정되어 해당 세션이 UPDATE 쿼리를 완료하고 COMMIT 또는 ROLLBACK 할 때까지 다른 세션에서 해당 레코드를 수정할 수 없다.

 

2. SELECT FOR UPDATE FlOW

2-1. 테스트 환경

도커를 사용하여 테스트 환경을 구성한다.

MysQL 이미지 설치 : docker pull mysql:latest
MySQL 컨테이너 실행 : docker run --name test-mysql-container -e MYSQL_ROOT_PASSWORD=1234 -d -p 3313:3306 mysql:latest
MySQL 접속 : docker exec -it test-mysql-container mysql -uroot -p

 

2-2. SELECT FOR UPDATE와 LOCK이 발생하는 과정

 

1. 트랜잭션 A가 트랜잭션을 시작한다.
2. 트랜잭션 B가 트랜잭션을 시작한다.
3. 트랜잭션 A는 name이 ‘hope’인 레코드를 SELECT FOR UPDATE로 조회한다. 이때, 해당 레코드에 LOCK이 걸리게 되어 다른 트랜잭션에서 쓰기 작업(UPDATE, DELETE)을 할 수 없도록 막는다.
   1. 단, 다른 트랜잭션에서는 이 레코드에 대해 읽기 작업(SELECT)은 가능하다. SELECT FOR UPDATE는 쓰기 작업에만 영향을 미치기 때문에, 읽기 작업에는 제한이 없다.
4. 트랜잭션 B는 LOCK이 걸린 레코드에 UPDATE를 시도하지만, 잠금이 해제되기 전까지 작업을 진행할 수 없으며, 대기 상태에 들어간다. 이 대기 시간은 MySQL의 innodb_lock_wait_timeout 설정에 의해 제한된다. 기본적으로는 50초이며, 이 시간을 넘기면 타임아웃 오류가 발생한다.
5. 트랜잭션 A는 해당 레코드에 LOCK을 소유하고 있으므로, 자유롭게 UPDATE 작업을 수행할 수 있다.
6. 트랜잭션 A가 작업을 완료한 후, 커밋(COMMIT)하거나 롤백(ROLLBACK)을 수행한다. 이로 인해 트랜잭션이 종료되면서 해당 레코드의 LOCK이 해제된다.
7. 트랜잭션 B는 더 이상 레코드에 LOCK이 걸려 있지 않기 때문에, 대기 상태에서 풀려나고 UPDATE 작업을 수행할 수 있게 된다.

 

3. LOCK을 얻지 못하면 무한정 대기할까?

MySQL에서 트랜잭션 A가 SELECT FOR UPDATE를 사용해 레코드르 잠그면, 트랜잭션 B는 해당 레코드에 대한 쓰기 작업을 하지 못하고, LOCK이 해제될 때까지 대기한다. 그렇다면 트랜잭션 A의 작업이 길어지면, 트랜잭션 B는 트랜잭션 A의 작업이 끝날 대까지 무한정 기다려야 할까?

 

MySQL에서는 트랜잭션 B가 LOCK이 해제될 때까지 기다리는 최대 시간(TIMEOUT)을 설정할 수 있다.

3-1. Lock Wait Timeout

innodb_lock_wait_timeout

• Lock Wait Timeout 조회

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

• Lock Wait Timeout 수정
  • TIMEOUT을 30초로 설정한다.

SET GLOBAL innodb_lock_wait_timeout = 30;

 

MySQL에서는 InnoDB 엔진에서 잠금 대기 시간을 설정하는 innodb_lock_wait_timeout이 있다. 이 값은 트랜잭션 B가 트랜잭션 A의 잠금을 기다릴 수 있는 최대 시간을 초 단위로 설정한다. 기본적으로 이 값은 50초로 설정되어 있다.

 

트랜잭션 B가 TIMEOUT 내에 LOCK을 얻지 못하면, MySQL은 오류를 반환하며 트랜잭션 B는 자동으로 롤백(ROLLBACK)된다.