오라클 쿼리중 

조회하려는 row(s)단위로 lock설정이가능한데,

"FOR UPDATE 구문"을 사용한다



select * from employees

where empno = 1234

for update nowait;


해당 empno=1234에 lock을 획득하지 못하면 ORA-00054로 에러발생시키고 종료한다.

현재 해당 rows가 lock상태라고 이해하면 되겠다.

(ORA-00054:자원이 사용중이고,NOWAIT가 지정되어 있음)


select * from employees

where empno = 1234

for update wait 10  --(10 seconds동안 lock잡기위해 대기);


10초이후도 lock을 얻지 못하면 ORA-00054 에러 발생시키고 종료된다.


아래 출처의 내용을 보게되면

ORA-00054에러 발생시 계속해서 try하는 로직이 있다.

반드시 수행되어야 하는 내용이 있으면 이렇게 반복적인 작업을 진행하더라고 진행되도록 처리한다.

(예, 프로세스 처리를 위해 중요한 key, 값이 되는 것의 채번/값수정 할때 사용할 수 있겠다.)



예)로직수행시 기lock이 있을경우 sleep후 재시도하여 처리

샘플)출처: http://www.java2s.com/Tutorial/Oracle/0500__Cursor/CursorFORUPDATENOWAIT.htm

'DataBase > Oracle' 카테고리의 다른 글

select for update wait/nowait  (0) 2018.07.22
Oracle Error Code description  (0) 2018.04.20

Oracle DB를 사용하다보면 항상 찾아보게되는 에러코드


Exception name Oracle Database
error number
SQLCODE Description
ACCESS_INTO_NULL ORA-06530 -6530 Program attempted to assign values to the attributes of an uninitialized object.
CASE_NOT_FOUND ORA-06592 -6592 None of the choices in the WHEN clauses of a CASE statement were selected and there is no ELSE clause.
COLLECTION_IS_NULL ORA-06531 -6531 Program attempted to apply collection methods other than EXISTS to an uninitialized nested table or varray, or program attempted to assign values to the elements of an uninitialized nested table or varray.
CURSOR_ALREADY_OPENED ORA-06511 -6511 Program attempted to open an already opened cursor.
DUP_VAL_ON_INDEX ORA-00001 -1 Program attempted to insert duplicate values in a column that is constrained by a unique index.
INVALID_CURSOR ORA-01001 -1001 There is an illegal cursor operation.
INVALID_NUMBER ORA-01722 -1722 Conversion of character string to number failed.
NO_DATA_FOUND ORA-01403 100 Single row SELECT returned no rows or your program referenced a deleted element in a nested table or an uninitialized element in an associative array (index-by table).
PROGRAM_ERROR ORA-06501 -6501 PL/SQL has an internal problem.
ROWTYPE_MISMATCH ORA-06504 -6504 Host cursor variable and PL/SQL cursor variable involved in an assignment statement have incompatible return types.
STORAGE_ERROR ORA-06500 -6500 PL/SQL ran out of memory or memory was corrupted.
SUBSCRIPT_BEYOND_COUNT ORA-06533 -6533 A program referenced a nested table or varray using an index number larger than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 -6532 A program referenced a nested table or varray element using an index number that is outside the legal range (for example, -1).
SYS_INVALID_ROWID ORA-01410 -1410 The conversion of a character string into a universal rowid failed because the character string does not represent a ROWID value.
TOO_MANY_ROWS ORA-01422 -1422 Single row SELECT returned multiple rows.
VALUE_ERROR ORA-06502 -6502 An arithmetic, conversion, truncation, or size constraint error occurred.
ZERO_DIVIDE ORA-01476 -1476 A program attempted to divide a number by zero.

reference url : https://docs.oracle.com/cd/E11882_01/timesten.112/e21639/exceptions.htm#TTPLS192


'DataBase > Oracle' 카테고리의 다른 글

select for update wait/nowait  (0) 2018.07.22
Oracle Error Code description  (0) 2018.04.20

+ Recent posts