DB2 - WITH UR, For Read Only With UR

제목: WITH UR, For Read Only With UR


  

출처 : http://blog.naver.com/virtual2002/120007499292

 

Online : 실시간으로 데이타가 들어오는 상황

Batch : 실시간으로 들어온 데이타를 특정시간대(새벽)에 가공하는 상황

 

FOR FETCH ONLY (Online, Batch 공통)

DB2는 기본적으로 아무 옵션없이 조회를 하면 먼저 테이블락을 걸고 조회후 테이블락을

푼다. 하지만 위의 옵션을 붙히면 테이블락을 걸지 않고 조회한다.

DECLARE CURSOR 의 SELECT 문장 마지막에는 항상 'FOR FETCH ONLY' 를 붙이도록

합니다.

FOR FETCH ONLY는 DB2에게 CURSOR가 READ-ONLY라는 것을 알려주어서

LOCK Avoidance(Lock 회피)와 Concurrency를 사용하게 함으로서 성능 향상에 도움이

됩니다.

※ FOR FETCH ONLY와 FOR READ ONLY는 거의 비슷하다.

 

OPTIMIZE FOR 1 ROW (Online Program 만 해당함)

DECLARE CURSOR의 SELECT 문에 ORDER BY 가 있을 경우에는 반드시

'OPTIMIZE FOR 1 ROW'를 기술하도록 합니다.

배치 프로그램에서는 절대로 사용하지 않도록 합니다.

 

WITH UR

WITH UR을 사용할 경우 Commit이 안된 Dirty Data가 읽혀질 수가 있으므로

금액 Update를 위한 SELECT 문이나 중요한 정보를 Update하기 위한 SELECT

문에는 WITH UR을 절대 사용하시면 안됩니다.그러면 무엇을 쓰는지 출처에가서 물어보자!

해당 프로그램이 조회성 프로그램일 경우에는 모든 SELECT문에 "WITH UR" Option을

기술하도록 합니다.

 

UR(언커미트 읽기)

1) 조회작업 동안 읽어들인 모든 행을 다른 응용프로그램 프로세스가 변경 가능하도록

합니다.

 

2) 해당 응용프로그램 프로세스에서 변경사항을 커미트하지 않은 경우에도 다른 응용프로그

램 프로세스가 커밋하기 전의 모든 행을 읽어들이도록 합니다.

 

3) 예를들어 삽입 트랜잭션 완료전인 원장을 조회할때 WITH UR를 사용하면 삽입하고 있는

테이블을 삽입된 시점까지 데이터를 조회 할수가 있습니다.

하지만 WITH UR을 빼고 조회하면 트랜잭션이 완료될때까지 대기한다.

 

※ IBM에서는 조회시에 가능한 WITH UR 이나 FOR READ ONLY WITH UR를

쓰는것을권장합니다.

 

'WITH UR'은 isolation(격리성) level의 지정방법중 하나입니다.

SELECT 문에서 WITH UR은 다른 유저(Connection)에서 변경중인 데이타를 읽을 수

있습니다.

오라클에서만 개발을 한 개발자들은 이를 모르고 문제를 발생시키는 application개발을

하는 경우가 있습니다.

예를들어 크기가 크고 변경이 매우 빈번한 중요 테이블에 full scan select를 한 경우 이 SQL

이 끝날 때까지 테이블에 lock이 발생하면서 대기하는 Application들이 많이 발생하게 됩니다.

이를 방지하기 위해 UR isolation을 사용합니다. 말그대로 commit이 발생하지 않아도 당시의

데이터를 읽어 오겠다는 겁니다.

 

※ 오라클은 조회시 해당 row에 대한 변경을 할 수가 있지만 DB2는 조회시 해당 row에

대한 변경을 할수 없습니다.

※ 오라클은 해당 row에 대한 변경을 할때 조회를 할수 있지만 DB2는 row에 대한 변경을

할때 조회를 할수 없습니다.

※ 'FOR READ ONLY WITH UR'은 SELECT 쿼리에만 사용하며 INSERT, UPDATE,

DELETE 쿼리에는 사용하지 않습니다.

※ 오라클은 RBS라는 곳에 작업 대상 데이터를 따로 보관하기 때문에 데이터의 변경과는

무관하게 조회작업을 할 수 있는 것으로 알고 있습니다.

 

조회 쿼리의 맨 끝에 WITH UR을 붙히면 조회 테이블이 현재 트랜잭션이 진행중일때 대기하지 않고 실시간 변경중인 데이타를 가져올수 있게 되지만 신뢰성은 떨어진다.

 

조회 쿼리의 맨끝에 WITH UR을 안붙히면 조회 테이블이 현재 트랜잭션이 진행중일때 대기하고 있다가 해당 트랜잭션이 커밋 처리되면 그때 데이타를 가져온다.

 

조회 쿼리의 맨끝에 WITH UR을 안붙히면 조회 테이블이 현재 트랜잭션이 진행중일때 대기하고 있다가 해당 트랜재션을 종료하지 않고 떨어져나가면 조회쿼리는 타임아웃에 의해 정지되고 "현재의 트랜잭션이 교착상태 또는 시간종료에 인해 롤백되었습니다." 메시지를 출력한다.

 

이때 해당 테이블은 트랜잭션 주체가 커밋 처리를 해주지 않았기 때문에 풀어주기 전까지는 계쏙 락상태를 유지한다.

 

테이블에 트랜잭션중일때 커밋을 안때리고 죽어버리면 해당 테이블은 락이 걸려버린다.

테이블 락은 테이블설계가 잘되어 있다면 발생하지 않는다.
락이 자주 생기는건 테이블 설계에 문제가 있는것이다.

WITH UR을 안 붙인것은 해당 원장의 트랜잭션이 진행일땐 자체 테이블락이 걸려 대기상태로 있다가 커밋처리를 하게된후에 해당원장을

조회하게 된다.

 

락이 걸린 테이블은 다른 세션에서 조회쿼리에 FOR READ ONLY with ur을 붙여도 조회는 가능하다.

락이 걸린 테이블은 insert, update, delete, import 가 불가능하다

※ 락 걸린 테이블은 "현재의 트랜잭션이 교착 상태 또는 시간종료로 인해 롤백되었습니다." 에러 메시지 출력

 

락 테이블은 트랜잭션을 사용하는 dbms에서만 걸린다.

 

처리성격에 따라서 WITH UR의 사용을 달리 할 필요가 있다. 왜냐하면 아래현상이 발생

할수도 있기 때문이다.

더티 읽기 :

1. 작업1에서 행을 삽입,수정합니다.
2. 작업2는 작업1에서 COMMIT을 수행하기 전에 행을 읽습니다.
3. 그런후 작업1이 ROLLBACK을 수행하면, 작업2는 존재하지 않는 행을 읽은 것이 됩니다.

반복 불가능한 읽기 :

1. 작업1에서 행을 읽습니다.

2. 작업2가 행을 수정하고 COMMIT을 수행합니다.

3. 그런후 작업1이 행을 다시 읽으면, 수정된 값을 받을 수도 있습니다.

팬텀 :

1. 작업1이 검색조건을 만족시키는 n행 집합을 읽습니다.

2. 그런후 작업2는 검색조건을 만족시키는 하나 이상의 행을 INSERT합니다.

3. 작업11이 같은 검색조건으로 첫번째 읽기를 반복하면, 원래의 행과 삽입된 행을 얻습니다.