Data Modeling Evangelist Kaien Kim's Blog
[Oracle] For Update의 사용 본문
안녕하세요. 반갑습니다.
하나의 테이블에서 사용하지 않는 row들중 키(pk)값이 min값을 가져와서 update를 차례로 하는 프로그램을 작성하다가.
한가지 문제가 조금 생겼습니다. c/s 환경상 여러 pc에서 동시에 처리가 되니깐... 동일한 min값을 두 pc에서 사용하지
못하도록 해당 row에 락(lock)을 걸려고 select 문에 for update를 사용하였습니다.
근데 제가 이것을 잘 이해하지 못해서 락이 제대로 걸리지 않습니다. 아래 저의 적용예를 보시고 많은 조언 부탁드립니다.
예)
1. 정상적으로 lock 됨.
select serial_no /* 사용하지 않는 row중 가장 작은 row */
from stock_temp
where use_flag = 'N'
for update ;
==> 이경우에는 해당 row가 commit 문이 수행되기전까지 lock이 걸려있습니다.
2. lock이 제대로 되지 않는 경우(♥)
select serial_no
from stock_temp
where use_flag = 'N'
and substr(serial_no,1,6) = :as_serial_base
and rownum = 1
for update ;
==> 부득이하게 substr()을 사용하여야 하는데 이경우 lock기능이 제대로
실행되지 않는 것 같습니다. 유사한 serial_no를 검색해서 가장 작은 값을 조회할려고
substr 또는 like를 사용하여야 하거든요...ㅠ.ㅠ
※for update기능/원리에 대해서 가르침을 부탁드립니다.
즐거운 하루되세요.
__________________________________________________________________________________________________________________
for update 경합은 잘 못 사용할 경우.. 큰 영향을 끼칩니다.
예를 들어 3명이 동시 작업을 진행해 나간다고 할때..
한명이
select col1, col2
from AT
where col1 = '20' and rownum = 1
for update
라는 명령어를 치는 순간 해당 로우에 락이 걸리게 됩니다.
첫번째 사람이 트랜잭션을 완료하기 전에 두번째 사람이 같은 명령어인
select col1, col2
from AT
where col1 = '20' and rownum = 1
for update
를 시도하면 앞에 사람의 락 때문에 실패하고 재시도를 합니다..(앞에사람이 트랜잭션이 완료될 때까지..)
물론 세번째 사람도 계속 실패하며.. 앞의 트랜잭션이 완료될때까지 재시도하며 기다리게 됩니다.
즉 원래 의도는 첫번째 사람이 하나를 처리하고 있을때 ..
두번째 사람은 그 하나를 제외하고 두번째걸 가져올거라고 생각을 하였겠지만...
하나씩 밖에 처리되지 않고 나머지 사람은 모두 대기 상태이며.. 계속 재시도를 하면서 트래픽만 가중됩니다.
특히나 이런 예는 컴퓨터 수리센터등의 여러명이 동시에 접수를 받을 때 많이 사용하는데요.
위와 같이 하면.. 한명씩 밖에 처리가 되지 않습니다.
이와 대한 명확한 해결책은 대용량 수업이나 책을 보면 나오는데 간단히 설명드리자면..
sequence를 사용하야 약간의 사고를 바꾸면 바로 해결할수 있습니다.
먼저.. 10명이 동시 작업을 한다면..
create sequence squ
start with 1
increment by 1
maxvalue 10
cycle
cache 9
즉 위와 같이 1-10사이로 cycle 도는 sequence를 만들고...
sql을 다음과 같이 수정합니다.
select squ.nextval into :A from dual ;
select rnum, col1, col2
from ( select rownum as rnum, col1, col2
from AT
where col1 = '20')
where rnum = :A and rownum = 1
보시면 곧 아시겠지만 부연설명을 조금 달자면...처음 select할때 자신이 선택할 놈을 물고 들어가는 겁니다.
즉 4번째라면.. 4라는 squ Number를 가지고 있으므로 4번째 놈이 선택되겠죠.
10명단위로 cycle이 돌기 때문에 10명 이하의 작업시 락이나 경합 발생을 염려하지 않아도 됩니다.
만약 나중에 사람이 늘었다면.. squ sequence의 maxvalue를 수정해 주시면 됩니다.
이렇게 하면.. for update의 경합발생을 우려하지 않고 동시 작업이 가능합니다.
만약 가장 최근 값 순서대로 하고 싶다면.. 힌트등을 select 문에 넣어주어 해당 순서대로 셀렉트 하게 하면 되겠죠.