락과 데드락

3. 코드 예제

소스: sql-src/batch_07_lock_deadlock/01_lock_basics.sql, 02_job_queue.sql, 03_order_fk.sql.

이 절의 sql 은 H2 로 실행한 것입니다. 잠금이 실제로 부딪히는 장면은 세션이 하나뿐이라 만들지 못하고, 문법과 흐름만 실행으로 확인합니다.

예제 1: 수정한 행과 FOR UPDATE

자동 커밋을 끄고 김대표 행을 수정합니다. 이 시점부터 1번 행에 쓰기 잠금이 걸리고, 다른 세션이 이 행을 수정하려면 기다립니다.

SET AUTOCOMMIT OFF;
UPDATE account SET balance = balance - 100 WHERE id = 1;
SELECT
       id
     , balance
  FROM account
 ORDER BY id;

ID | BALANCE
---+--------
1  | 900
2  | 500
3  | 300
(3행)

같은 세션은 자기가 건 잠금에 막히지 않아서 바로 값을 읽고 다시 수정할 수 있습니다. 여기서 ROLLBACK 하면 잠금이 풀리고 값이 1000 으로 돌아갑니다.

수정하지 않고 잠그기만 하려면 SELECT ... FOR UPDATE 를 씁니다. 읽은 행을 이어서 수정할 때, 그 사이에 다른 세션이 바꾸지 못하게 하려는 용도입니다.

SELECT
       id
     , balance
  FROM account
 WHERE id = 2
   FOR UPDATE;

ID | BALANCE
---+--------
2  | 500
(1행)

남이 이 행을 잡고 있었다면 잠금이 풀릴 때까지 기다립니다. 기다리지 않으려면 뒤에 옵션을 붙입니다.

H2 에서 FOR UPDATE NOWAIT 와 FOR UPDATE WAIT 2 도 문법이 통과합니다. 잡은 세션이 없어서 오류 없이 행을 돌려줄 뿐이라, 대기와 즉시 오류는 이 스크립트로 보이지 않습니다.

예제 2: 작업 큐에서 처리할 3건 잡기

job_queue 에 대기 상태 10건이 있습니다. 작업자가 여럿이면 같은 작업을 둘이 가져가지 않아야 합니다. FOR UPDATE SKIP LOCKED 는 다른 작업자가 이미 잠근 행을 건너뛰고 다음 행을 잠급니다.

SET AUTOCOMMIT OFF;
SELECT
       id
  FROM job_queue
 WHERE status = 'WAIT'
 ORDER BY id
 FETCH FIRST 3 ROWS ONLY
   FOR UPDATE SKIP LOCKED;

ID
--
1
2
3
(3행)

작업자 w1 이 1, 2, 3번을 잡았습니다. 이 세 행은 COMMIT 할 때까지 w1 것이라, 그 사이에 w2 가 같은 문장을 실행하면 이 행들을 건너뛰고 4, 5, 6번을 가져갑니다. 이 겹침 없는 분배는 세션이 둘 있어야 보이므로 도식으로 봅니다.

시간 순서 도식(설명용). 두 작업자가 같은 문장을 실행합니다.

SKIP LOCKED 가 없었다면 w2 는 1번 행에서 멈춰 w1 의 COMMIT 을 기다립니다. 작업자를 늘려도 처리량이 늘지 않고 직렬로 줄을 서게 됩니다. 이제 잡은 행에 이름표를 붙이고 확정합니다.

UPDATE job_queue SET status = 'RUN', picked_by = 'w1' WHERE id IN (1, 2, 3);
COMMIT;

H2 에서는 w1 이 확정한 뒤 같은 문장을 다시 실행하는 것으로 w2 의 차례를 흉내 냅니다. 대기 상태 중 남은 앞의 3건이 나옵니다.

SELECT
       id
  FROM job_queue
 WHERE status = 'WAIT'
 ORDER BY id
 FETCH FIRST 3 ROWS ONLY
   FOR UPDATE SKIP LOCKED;

ID
--
4
5
6
(3행)

w2 도 이름표를 붙이고 COMMIT 합니다. 상태별 건수를 확인합니다.

SELECT
       status
     , picked_by
     , COUNT(*) AS cnt
  FROM job_queue
 GROUP BY status, picked_by
 ORDER BY status, picked_by;

STATUS | PICKED_BY | CNT
-------+-----------+----
RUN    | w1        | 3
RUN    | w2        | 3
WAIT   | NULL      | 4
(3행)

작업이 끝난 w1 의 3건을 DONE 으로 바꾸고 COMMIT 하면 표가 이렇게 됩니다.

STATUS | CNT
-------+----
DONE   | 3
RUN    | 3
WAIT   | 4
(3행)

예제 3: 같은 순서로 잠그고 갱신하기

2.3 의 데드락은 A 가 1 → 3, B 가 3 → 1 순서로 갱신했기 때문입니다. 이체 방향과 상관없이 잠글 행을 id 순으로 먼저 잡으면 순서가 고정됩니다. 박영업(3)에서 김대표(1)로 50 을 보내는 예입니다.

SET AUTOCOMMIT OFF;
SELECT
       id
     , balance
  FROM account
 WHERE id IN (3, 1)
 ORDER BY id
   FOR UPDATE;
UPDATE account SET balance = balance - 50 WHERE id = 3;
UPDATE account SET balance = balance + 50 WHERE id = 1;
COMMIT;

먼저 1번, 다음 3번 순으로 잠기고 그 뒤에 두 UPDATE 를 실행합니다. 반대 방향 이체도 같은 문장 모양이라 늘 1번을 먼저 잠급니다. 순서가 같으면 서로 상대의 행을 기다리는 순환이 만들어지지 않습니다.

시간 순서 도식(설명용). 순서를 고정하면 B 는 기다리기만 합니다.

B 는 기다릴 뿐 A 가 끝나면 이어 갑니다. 순환이 없으니 DB 가 트랜잭션을 끊을 일도 없습니다. 이 방식은 갱신할 행을 미리 알 수 있을 때 씁니다.