[Oralce] 락 쿼리 (Lock Query), 실행 흐름

락 쿼리(Lock Query) 란?

데이터베이스에서 특정 데이터를 수정하거나 삭제하기 전에 미리 Lock을 걸어, 다른 트랜잭션이 동시에 해당 데이터를 변경하지 못하도록 제어하는 쿼리.

 

왜 락 쿼리가 필요한가?

1. 동시 업데이트 충돌

A트랜잭션, B트랜잭션이 동시에 같은 데이터를 w 하려고 하고, B가 마지막에 끝난 트랜잭션이라고 가정해보자. 그러면 마지막에 B 트랜잭션이 수정한 값만 DB에 저장된다.

 

2. r-w 충돌

A트랜잭션이 수정 중인 데이터를 B 트랜잭션이 동시에 읽게되면 시점에 따라 수정 전의 값을 조회하게됨.

 

예를들어, 입금 처리하는 트랜잭션이라고 가정을 해보자. A는 +50원, B는 +30원 을 처리하는 트랜잭션이고 사용자의 잔고에는 100원이 있다고 하자. A와 B가 동시에 트랜잭션 처리를 하려고 들어오게되면, 잔고는 180원이 아닌 130(B가 제일 마지막 처리)으로 잘못 반영된다 Race Condition 경쟁상태 에 빠지게 됨

=> 이를 막기 위해 락쿼리를 적용해서 트랜잭션 간 충돌을 방지하고, 일관된 데이터 처리를 해야한다. 따라서 입금 전 잔액을 조회하면서 해당 행에 락을 걸고, 다른 트랜잭션이 접근하지 못하도록 해야한다.

 

락 쿼리 종류

1. `SELECT ... FOR UPDATE` 

특정 행을 수정하기 전, 해당 행에 대한 락을 걸고 다른 트랜잭션의 접근을 차단 

 

-- ① 해당 계좌 조회하면서 락 걸기
SELECT balance
FROM accounts
WHERE account_no = '123-456-7890'
FOR UPDATE;

-- ② 입금 처리 (예: 5만원 입금)
UPDATE accounts
SET balance = balance + 50000
WHERE account_no = '123-456-7890';

-- ③ 트랜잭션 종료 → 락 해제
COMMIT;

 

=> 입금 처리 중 다른 트랜잭션이 동일 계좌에 접근하지 못함

FOR UPDATE : 트랜잭션 종료되어서 락 해제시까지 무한 대기 

FOR UPDATE NOWAIT : 바로 에러 뱉어서 즉시 빠져나옴

+) 테이블 전체가 아닌 행 단위 락이다. 특정 행만 락이 걸리는 것.

 

2. `LOCK TABLE ... IN [MODE]`

테이블 전체에 락을 거는 명령 (예: SHARE MODE, EXCLUSIVE MODE 등)

 

 

예시

세션 A가 락을 걸었을떄 B가 그냥 조회, 락 조회, 업데이트 세가지 행동을 했을때 어떤 흐름인지 궁금해졌다

 

세션 A

SELECT * 
FROM accounts 
WHERE account_no = '123-456-7890'
FOR UPDATE NO WAIT;

오라클은 이 시점에 account_no = '123-456-7890' 행에 배타적 락을 걸어두므로, 세션 A만 이 행을 수정할 수 있음.

따라서 A가 해당 행에 락을 걸어두고 update 치면 바로 갱신 가능.

 

 

세션 B

-- 단순 조회 
SELECT *
FROM accounts
WHERE account_no = '123-456-7890';

세션 B가 동시에 동일 계좌 조회를 시도하면 -> 그냥 읽기 가능

-- 락 조회
SELECT * 
FROM accounts 
WHERE account_no = '123-456-7890'
FOR UPDATE NOWAIT;

만약에 락쿼리로 조회했다면

-> ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired 

A가 해당 행을 락 중이므로, B 는 NOWAIT로 접근 불가

 

=> 업데이트 치기 전 조회하는 쿼리에는 락을 걸어서 조회에서 no wait으로 튕기게하고,

일반 조회하는 쿼리는 락과 상관없이 조회가 가능하도록 설계 해야겠다

SELECT * 
FROM accounts 
WHERE account_no = '555-666-7777'
FOR UPDATE NOWAIT;

세션 B가 같은 테이블 다른 행에 대해 락 조회할때는 당연히 정상 실행됨

 

그러면 내가 궁금한건, A가 락을 잡은 상태에서 B가 일반 조회 후에 update를 시도한다면?

UPDATE accounts 
SET amt=amt+1 
WHERE accounts_no='123-456-7890';

-> 락 대기 상태에 빠진다.

오라클은 update 시점에 락을 얻으려고 하는데, 이미 세션A가 락을 잡고있으니 B는 대기하게 되는 것.. wait 모드이다.

이 상황에서 세션 B가 무한대기 상태에 빠질 수 있겠다

 

update 전 조회 쿼리에는 lock 잡을지 말지 잘 판단해서 데이터 정합성을 지키자