InnoDB 테이블에 대해서는 레코드를 SELECT할 때 레코드에 아무런 잠금도 걸지 않는데, 이를 잠금 없는 읽기(Non Locking Consistent Read) 라고 한다.

하지만 SELECT 쿼리를 이용해 읽은 레코드의 컬럼값을 애플리케이션에서 가공해서 다시 업데이트하고자 할 때는 SELECT가 실행된 후 다른 트랜잭션이 그 컬럼의 값을 변경하지 못하게 해야 한다.

이럴 때는 레코드를 읽으면서 강제로 잠금을 걸어 둘 필요가 있는데, 이때 사용하는 옵션이 FOR SHARE, FOR UPDATE 절이다.

• FOR SHARE:
  • SELECT 쿼리로 읽은 레코드에 대해 읽기 잠금(공유 잠금, Shared lock)을 설정하고 다른 세션에서 해당 레코드를 변경하지 못하게 한다.
  • 다른 세션에서 잠금이 걸린 레코드를 읽는 것은 가능.
• FOR UPDATE:
  • SELECT 쿼리가 읽은 레코드에 대해 쓰기 잠금(배타 잠금, Exclusive lock)을 설정하고, 다른 트랜잭션에서는 그 레코드를 변경하는 것 뿐만 아니라 읽기도 수행할 수 없다.

1
2

SELECT * FROM employees WHERE emp_no = 10001 FOR SHARE;
SELECT * FROM employees WHERE emp_no = 10001 FOR UPDATE;

MySQL 8.0 이전 버전에서는 SELECT로 읽은 레코드에 대한 읽기 잠금을 위해 LOCK IN SHARE MODE 절을 사용했지만, 버전이 업그레이드되며 FOR SHARE로 변경되었다.
기존 문법을 호환하기는 하지만, 8.0 버전 부터 제공하는 SELECT 쿼리 잠금을 위해 여러 새로운 기능을 위해 FOR SHARE, FOR UPDATE 문법을 사용하는 것이 좋다.

언급한 잠금 옵션은 모두 자동 커밋이 비활성화된 상태 또는 BEGIN 명령이나 START TRANSACTION 명령으로 트랜잭션이 시작된 상태에서만 잠금이 유지된다.

주의 사항

InnoDB 스토리지 엔진을 사용하는 테이블에서 FOR UPDATE, FOR SHARE 절을 가지지 않는 SELECT 쿼리는 잠금 없는 읽기가 지원되기 때문에 특정 레코드가 SELECT ... FOR UPDATE 쿼리에 의해 잠겨진 상태라 하더라도 아무런 대기 없이 실행된다.

잠금 대기하지 않는 경우

잠금 대기하는 경우

잠금 테이블 선택

1
2
3
4
5
6

SELECT * 
FROM employees e
    INNER JOIN dept_emp de ON de.emp_no=e.emp_no
    INNER JOIN departments d ON d.dept_no=de.dept_no
FOR UPDATE
;

위 쿼리는 employees, dept_emp, departments 테이블을 조인해서 읽으며 FROM UPDATE절을 사용했으므로 InnoDB 스토리지 엔진은 3개 테이블에서 읽은 레코드에 대해 모두 쓰기 잠금을 걸게 된다.

그런데 실제 쓰기 잠금은 employees 테이블만 필요하다면 잠금을 테이블을 선택할 수 있도록 MySQL 8.0 부터 개선이 되었다.

1
2
3
4
5
6

SELECT * 
FROM employees e
    INNER JOIN dept_emp de ON de.emp_no=e.emp_no
    INNER JOIN departments d ON d.dept_no=de.dept_no
FOR UPDATE OF e
;

NOWAIT & SKIP LOCKED

지금까지 MySQL 잠금은 누군가 레코드를 잠그고 있다면 다른 트랜잭션은 그 잠금이 해제될 때까지 기다려야 했고, 때로는 일정 시간이 지나면서 잠금 획득 실패 에러 메시지를 받을 수도 있었다.

이러한 처리는 서비스 상황에 따라 적절하지 않응 처리일 수 있으며, 이러하 부분을 개선하기 위해 MySQL 8.0 부터는 NOWAIT, SKIP LOCKED 옵션을 사용할 수 있게 기능이 추가되었다.

• NOWAIT

  • 해당 레코드가 다른 트랜잭션에 의해서 잠겨진 상태라면 에러를 반환하면서 즉시 종료된다.
  • SELECT 쿼리가 해당 레코드에 대해 즉시 잠금을 획득했다면 옵션이 NOWAIT 없을때와 동일하게 실행된다.

• SKIP LOCKED

  • SELECT 하려는 레코드가 다른 트랜잭션에 의해 이미 잠겨진 상태라면 에러를 반환하지 않고, 잠금이 걸리지 않은 레코드만 가져온다.

SKIP LOCKED 구문을 사용하는 SELECT 구문은 잠금이 걸린 레코드를 무시하고 결과가 반환되므로 확정적이지 않은(NOT-DETERMINISTIC) 쿼리가 된다.

NOWAIT, SKIP LOCKED를 이용한 큐

NOWAIT, SKIP LOCKED 기능을 활용하여 큐와 같은 기능을 구현할 때 유용하게 쓰일 수 있다.

쿠폰 발급 기능 예시

• 하나의 쿠폰은 한 사용자만 사용 가능하다.
• 쿠폰의 개수는 1000개 제한이며, 선착순으로 요청한 사용자에게 발급한다.

1
2
3
4
5
6
7
8

CREATE TABLE coupon (
    coupon_id BIGINT NOT NULL,
    owned_user_id BIGINT NULL DEFAULT 0,
    coupon VARCHAR(15) NOT NULL,
    ...
    PRIMARY KEY (coupon_id),
    INDEX ix_owneduserid (owned_user_id)
);

1. 응용 프로그램 코드에서 주인이 없는(owned_user_id=0) 쿠폰을 검색해서 하나를 가져온다.
2. owned_user_id를 요청한 사용자의 id로 업데이트 한다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

BEGIN;

SELECT * 
FROM coupon
WHERE owned_user_id=0 
ORDER BY coupon_id ASC
LIMIT 1
FOR UPDATE
;

/* 프로그램 연산 수행 */

UPDATE coupon
SET owned_user_id=?
WHERE coupon_id=?
;

COMMIT;

• 위 처리에서 1000명의 사용자가 쿠폰을 요청하면 애플리케이션 서버는 그 요청만큼 프로세스를 생성해서 위의 트랜잭션을 동시에 실행.
• 각 트랜잭션에서 실행하는 SELECT ... FOR UPDATE 쿼리는 coupon 테이블에서 하나의 레코드로 집중해서 잠금 획득 시도.
  • 처음으로 잠금을 획득하는 트랜잭션 외 999개의 트랜잭션은 첫번째 트랜잭션의 COMMIT 까지 대기
• 요청 순서대로 잠금을 획득 및 대기
  • 트랜잭션의 처리 속도에 따라 일정 시점 이후 트랜잭션은 timeout 시간 내에 잠금을 획득하지 못해 에러처리됨

SELECT 쿼리의 ORDER BY coupon_id으로 인해 모든 트랜잭션이 하나의 레코드로 집중된다고 생각할 수 있지만, DBMS 서버는 쿼리의 실행이 항상 실행 계획을 기반으로 수행되기 때문에 ORDER BY와 무관하게 모든 트랜잭션은 항상 순서대로 레코드를 읽는다.

MySQL 8.0 이전 버전에서는 이러한 문제를 해결하기 위해 레디스 같은 캐시 솔루션을 별도로 구축해서 쿠폰 발급 기능을 구현했다.

하지만 MySQL 8.0 버전에서 제공하는 UPDATE SKIP LOCKED 절을 이용하면 트랜잭션이 수행되는 데 걸리는 시간과 관계없이 다른 트랜잭션에 의해서 이미 잠금된 레코드를 스킵하는 시간만 지나면 각자의 트랜잭션을 수행할 수 있다.

• NOWAIT, SKIP LOCKED 절은 SELECT ... FOR UPDATE 구문에서만 사용할 수 있다.
• UPDATE, DELETE 에서는 사용할 수 없다.
  • NOWAIT, SKIP LOCKED 절은 쿼리 자체를 비확정적으로 만드릭 때문에 실행될 때마다 데이터베이스의 상태를 다른 결과로 만든다.
  • 이로인해 정상적으로 실행되어도 어떤 레코드가 업데이트되거나 삭제됐는지 알 수 없게 되므로 사용자를 혼란에 빠트릴 수 있고, 서버 복제에서 큰 문제가 발생할 수 있다.