ORACLE에서 SELECT 시 LOCK 처리

select ~ for update문

다수의 oracle 사용자가 동시에 한 테이블을 내용을 변경할 경우 발생되는 문제점을 해결하기 위해 특정 행을 lock할 필요가 발생된다. 특정 행에 lock이 설정되면 commit문이 실행되어 lock이 해제될때 까지 다른 사용자들에게 접근을 막을 수 있다. lock을 설정한 사용자는 transaction이 완료되면 반드시 commit문을 수행하여 다른 사용자의 접근을 허용하여야 한다.

lock 설정 : select ~ for update nowait;

해제 : commit

• 동시 사용으로 발생되는 문제의 예

은행에서 이용되는 데이타베이스 시스템은 각지점의 단말기와 현금 인출기를 통해 동시에 사용된다. 한 은행의 P 라는 사람의 구좌에 100만원이 들어있다고 가정했을 때 구좌에서 50만원을 출금하는 과정은 다음과 같다.

1. 현재의 잔액을 읽어 옴. read X .
2.  
3. 잔액에서 50원을 뺌. X = X - 50만원
4. 뺀 결과를 저장. write X

P의 한 구좌에 대해 동시에 두 곳에서 50만원이 출금되는 경우는 다음과 같다.

1. A에서 현재의 잔액을 읽음. read 100만원
2. B에서 현재의 잔액을 읽음. read 100만원
3. A에서 잔액에서 50원을 출금. 50원 = 100만원 - 50만원
4. B에서 잔액에서 50원을 출금. 50원 = 100만원 - 50만원
5. A에서 결과가 저장됨. write 50만원
6. B에서 결과가 저장됨. write 50만원

위의 과정에서 잔액이 0이어야 할 구좌에는 50만원원이 남아있어 잘못된 결과가 데이타베이스에 저장된다. 대부분의 경우 데이타베이스는 둘이상의 장소에서 동시에 사용되고 있으므로 위와 같은 문제를 해결하기 위한 방법이 제공되어야 한다.

SQL> select name from std where no='9712008' for update nowait;

NAME

--------------------

Kim Dongsu

다른 사용자가 동일한 행에 대해 lock을 시도할 경우 에러발생

SQL> select name from std where no='9712008' for update nowait;

ERROR:

ORA-00054: resource busy and acquire with NOWAIT specified

no rows selected

다른 사용자가 lock이 설정된 행에 대해 변경작업을 수행하려고 할 때

SQL> update std set no='9712001' where name='Kim Dongsu';

à 응답이 없음

à CTRL+C입력

^Cupdate std set no='9712001' where name='Kim Dongsu'

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

SQL> update std set no='9712001' where name='Kim Dongsu';

à lock을 설정한 사용자가 commit문으로 lock을 해제할때까지 대기중

1 row updated. à commit문 실행

SQL> select name from std where no='9712008' for update nowait;

NAME

--------------------

Kim Dongsu

SQL> commit;

Commit complete.