Pessimistic lock (FOR UPDATE)

Resolved duplicate ID generation across multi-instance manufacturing execution services by implementing Oracle row-level locks (FOR UPDATE), preventing data anomalies and manufacturing downtime.

• Date-based LOT ID: one table, (target_date, current_seq), seq resets daily
• Multi-instance: two servers read the same seq in the same ms -> duplicate ID -> PK error / line stops
• synchronized is per-JVM — useless across servers; lock at the DB
• SELECT ... FOR UPDATE on TODAY's row only (row scope, not the table) -> DB serializes
• Scope vs DURATION: FOR UPDATE holds the lock across the app round-trip (read -> +1 -> update)
• Better: single UPDATE seq=seq+1 -> DB does the +1, lock taken later, held only to commit

-- holds the lock across the app round-trip (read -> +1 in app -> write)
SELECT current_seq FROM lot_seq
  WHERE target_date = :today FOR UPDATE;   -- lock taken NOW
-- app: n = current_seq + 1
UPDATE lot_seq SET current_seq = :n WHERE target_date = :today;
COMMIT;                                    -- lock released

-- better: DB does the +1; lock taken at UPDATE, no app round-trip held
UPDATE lot_seq SET current_seq = current_seq + 1
  WHERE target_date = :today
  RETURNING current_seq INTO :n;

• ·Date-based LOT IDs from one counter row per day.
• ·Two servers could read the same seq and both increment — duplicate ID, the line stops.
• ·synchronized is per-JVM, so I locked at the DB: SELECT FOR UPDATE on today's row only.
• ·The subtlety is duration, not just scope — FOR UPDATE holds the lock across the app round-trip.
• ·A single UPDATE seq=seq+1 lets the DB do the increment, so the lock is held only to commit.

• Pessimistic: waiters block until commit — a hot row serializes throughput.
• FOR UPDATE holds the lock across the app round-trip — longer than necessary.
• Custom LOT format rules out a native sequence (which would be non-blocking).

• Single-statement atomic UPDATE (seq=seq+1 RETURNING) to shrink lock duration.
• Hi/Lo: allocate a block of N under one lock, hand out from memory -> 1/N lock frequency.
• At a few/sec a DB lock is right; at thousands/sec the row is a hot bottleneck -> batch / sharded counter.
• If the format allowed it, a native DB sequence — atomic and non-blocking.

Understand it step by step (한국어로 이해 → 영어로 말하기)

1. 1

   번호표 기계를 생각하면 된다. 오늘 날짜로 1, 2, 3... 번호를 순서대로 준다. DB에 '오늘 날짜 + 지금까지 준 번호' 한 줄이 있고, 하루 지나면 다시 1부터 시작한다.

   -- one counter row per day; seq resets daily
   CREATE TABLE lot_seq (
     target_date DATE PRIMARY KEY,
     current_seq NUMBER
   );

   🗣 영어로 말해

   “We hand out date-based LOT IDs from one counter row per day.”

   checking microphone…

2. 2

   번호를 줄 때 순서가 이렇다. 지금 번호를 읽고, 거기에 1을 더하고, 그 값을 다시 쓴다. 한 대일 땐 문제없다.

   -- read, +1 in the app, write back
   SELECT current_seq FROM lot_seq WHERE target_date = :today;
   -- app: n = current_seq + 1
   UPDATE lot_seq SET current_seq = :n WHERE target_date = :today;

   🗣 영어로 말해

   “To issue an ID we read the current number, add one, and write it back.”

   checking microphone…

3. 3

   그런데 서버가 두 대다. 둘이 거의 같은 순간에 같은 번호(예: 5)를 읽는다. 둘 다 1을 더해서 6을 쓴다. 결국 두 작업에 똑같은 번호가 나가고, 중복 ID라 라인이 멈춘다.

   ⚖️ Trade-off: 그냥 두면 둘이 같은 번호를 보고 둘 다 쓴다 → 한쪽 증가분이 사라지고 번호가 겹친다.

   🔧 도구:read-modify-write race

   🗣 영어로 말해

   “With two servers, both read the same number and both write the next one, so they hand out a duplicate ID.”

   checking microphone…

4. 4

   자바 자체 lock(synchronized)은 왜 안 되나? 그건 한 서버(JVM) 안에서만 통한다. 서버가 별개라 서로의 lock을 못 본다. 그래서 둘 다 본인은 막은 줄 알고 그냥 들어간다.

   ⚖️ Trade-off: synchronized는 서버 한 대 안에서만 막아준다. 옆 서버는 그 lock을 아예 못 본다.

   🔧 도구:synchronized (per-JVM only — useless here)

   // per-JVM only: the other server never sees this monitor
   synchronized (lock) {
       int n = readSeq() + 1;
       writeSeq(n);
   }

   🗣 영어로 말해

   “A Java lock only works inside one server, so a second server can't see it.”

   checking microphone…

5. 5

   그래서 둘 다 보는 곳, 즉 DB에서 잠갔다. 그것도 테이블 전체가 아니라 '오늘 날짜 그 한 줄'만 잠근다. 한 서버가 그 줄을 잡으면, 다른 서버는 그 줄을 만질 때 끝날 때까지 기다린다. 순서대로 처리되니 번호가 안 겹친다.

   ⚖️ Trade-off: 잠그면 한 명이 끝낼 때까지 다른 명은 무조건 기다려야 한다(pessimistic). 그 줄이 바쁘면 줄 서서 처리돼 느려질 수 있다.

   ✅ Fix: LOT 번호는 초당 몇 개 수준이라, 잠깐 줄 서는 비용은 무시할 만하다. 그래서 DB 줄 lock으로 충분하다.

   🔧 도구:SELECT ... FOR UPDATE (pessimistic row lock)

   -- lock only TODAY's row, not the whole table
   SELECT current_seq FROM lot_seq
     WHERE target_date = :today FOR UPDATE;

   🗣 영어로 말해

   “So I locked it at the DB, on today's row only, with SELECT FOR UPDATE.”

   checking microphone…

6. 6

   여기서 진짜 미묘한 게 '얼마나 오래 잠그느냐'다. 범위(어느 줄)만 줄여도, FOR UPDATE는 읽고 → 앱에서 +1 하고 → 다시 쓸 때까지 그 사이 네트워크 왕복 동안 내내 lock을 쥐고 있다. 즉 필요한 시간보다 길게 잡고 있다.

   ⚖️ Trade-off: FOR UPDATE는 앱이 +1 해서 돌려줄 때까지 lock을 놓지 않는다. 서버-DB 왔다 갔다 하는 동안 다른 서버는 계속 기다린다.

   🔧 도구:lock duration vs lock scope

   -- FOR UPDATE holds the lock across the round-trip
   SELECT current_seq FROM lot_seq
     WHERE target_date = :today FOR UPDATE;  -- lock taken NOW
   -- app: n = current_seq + 1
   UPDATE lot_seq SET current_seq = :n WHERE target_date = :today;
   COMMIT;                                  -- lock finally released

   🗣 영어로 말해

   “The subtle part is duration, not just scope — FOR UPDATE holds the lock across the whole round-trip.”

   checking microphone…

7. 7

   더 나은 방법: +1을 앱이 하지 말고 DB가 하게 한다. 'seq를 seq+1로 올려줘'라는 한 문장만 보낸다. 그러면 읽고-더하고-쓰는 왕복이 사라지고, lock은 그 문장 실행하고 커밋할 때만 잠깐 잡혔다 풀린다.

   ⚖️ Trade-off: 한 문장 UPDATE라도 잠그긴 잠근다 — 다만 쥐는 시간이 확 짧아진다.

   ✅ Fix: 더 폭주하면(초당 수천 개) 그 한 줄이 병목이 된다. 그땐 번호를 100개씩 미리 한 번에 받아와 메모리에서 나눠 주는 식(Hi/Lo)으로 lock 횟수를 1/100로 줄인다.

   🔧 도구:UPDATE seq = seq+1 RETURNINGHi/Lo block allocation

   -- DB does the +1; lock taken at UPDATE, held only to commit
   UPDATE lot_seq SET current_seq = current_seq + 1
     WHERE target_date = :today
     RETURNING current_seq INTO :n;

   🗣 영어로 말해

   “A single UPDATE seq = seq + 1 lets the DB do the increment, so the lock is held only to commit.”

   checking microphone…

7단계 영어를 다 말하면 → 이 메커니즘 전체를 영어로 설명할 수 있게 된다.