Database - PL/SQL 트랜잭션

트랜잭션의 시작과 끝

• 트랜잭션은 암시적 혹은 명시적으로 끝날 수 있다.
• 데이터 정의어를 만나거나, 데이터 제어어를 만나거나, COMMIT/ROLLBACK 없이 SQL을 정상적으로 종료했을 때는 수행 중이던 트랜잭션이 암시적으로 COMMIT된다.
• COMMIT, ROLLBACK, SAVEPOINT문을 사용하여 트랜잭션의 논리를 명시적으로 제어할 수 있다.
• SQL *PLUS에서는 묵시적으로 한 트랜잭션은 데이터 정의어나 데이터 제어어 이전까지 입력한 여러 개의 데이터 조작어로 이루어진다.
• SET 명령을 사용하여 각 데이터 조작어를 한 트랜잭션으로 처리할 수 있다.
  

COMMIT

• 현재의 트랜잭션에서 수행한 1개 이상의 데이터 조작어의 결과를 데이터베이스에 모두 반영하고 완료

ROLLBACK

• 현재의 트랜잭션에서 수행한 1개 이상의 데이터조작어의 결과를 데이터베이스에서 모두 되돌리고 철회

SAVEPOINT

• 현재의 트랜잭션 내에 저장점을 표시하여 트랜잭션을 더 작은 부분으로 나눈다.

ROLLBACK TO SAVEPOINT

• 현재의 트랜잭션에서 지정된 저장점 이후의 갱신된 내용만 되돌린다.
  트랜잭션의 속성
• 만일 트랜잭션이 읽기만 한다면 읽기전용임을 명시하여 동시성의 정도를 높일 수 있다.
• 읽기 전용이라고 명시했으면 갱신 작업을 수행할 수 없다.
  

트랜잭션의 속성

• 만일 트랜잭션이 읽기만 한다면 읽기전용임을 명시하여 동시성의 정도를 높일 수 있다.
• 읽기 전용이라고 명시했으면 갱신 작업을 수행할 수 없다.
• SET TRANSACTION READ WRITE라고 명시하면 SELECT, DELETE, UPDATE문을 모두 수행할 수 있다.
  

고립 수준

• 한 트랜잭션이 다른 트랜잭션과 고립되어야 하는 정도를 나타낸다.
• 고립 수준이 낮으면 동시성은 높아지지만 데이터의 정확성은 떨어진다.
• 고립 수준이 높으면 데이터는 높아지지만 동시성이 저하된다.
• 응용에 따라 고립 수준을 선택하여 성능을 높일 수 있으며, 명시한 고립 수준에 따라 로킹 동작이 달라진다.
• 명시한 고립 수준에 따라 읽을 수 있는 데이터에만 차이가 있으며 다른 트랜잭션의 고립 수준에 영향을 주지 않음

READ UNCOMMITTED

• 가장 낮은 고립 수준
• 트랜잭션 내의 질의들이 공유 로크를 걸지 않고 데이터를 읽는다/
• 따라서 오손 데이터를 읽을 수 있다.
• 갱신하려는 데이터에 대해서는 독점 로크를 걸고, 트랜잭션이 끝날 때까지 보유한다.

SET TRANSACTION READ WRITE
ISOLATION LEVEL READ UNCOMMITTED;

READ COMMITTED

• 트랜잭션 내의 질의들이 읽으려는 데이터에 대해서 공유 로크를 걸고, 읽기가 끝나자마자 로크를 해제한다.
• 따라서 동일한 데이터를 다시 읽기 위해 공유 로크를 다시 걸고 데이터를 읽으면, 이전에 읽은 값과 다른 값을 읽는 경우가 생길 수 있다.
• 갱신하려는 데이터에 대해서는 독점 로크를 걸고, 트랜잭션이 끝날 때까지 보유한다.
• 이 고립 수준은 PL/SQL의 디폴트

SET TRANSACTION READ WRITE
ISOLATION LEVEL READ COMMITTED;

REPEATABLE READ

• 질의에서 검색되는 데이터에 대해 공유 로크를 걸고, 트랜잭션이 끝날 때까지 보유한다.
• 한 트랜잭션 내에서 동일한 질의를 두 번 이상 수행할 때, 이전에 읽은 값이 항상 동일하게 유지된다.
• 갱신하려는 데이터에 대해서는 독점 로크를 걸고, 트랜잭션이 끝날 때까지 보유한다.

SET TRANSACTION READ WRITE
ISOLATION LEVEL REPEATABLE READ;

SERIALIZABLE

• 가장 높은 고립 수준
• 질의에서 검색되는 투플들 뿐만 아니라 인덱스에 대해서도 공유 로크를 걸고 트랜잭션이 끝날 때까지 보유한다.
• 한 트랜잭션 내에서 동일한 질의를 두 번 이상 수행할 때 매번 같은 값을 포함한 결과를 검색하게 된다.
• 갱신하려는 데이터에 대해서는 독점 로크를 걸고 트랜잭션이 끝날 때까지 보유한다.
• SERIALIZABLE은 SQL2의 디폴트 고립 수준

SET TRANSACTION READ WRITE
ISOLATION LEVEL SERIALIZABLE;

REPEATABLE READ vs. SERIALIZABLE

SELECT col1 FROM A WHERE col1 BETWEEN 1 AND 10;

• REPEATABLE READ
  • 위의 검색문의 예시에서 col1이 1과 5를 가지고 있다면 사용자가 col1이 1과 5를 가지는 row에 대하여는 UPDATE가 불가능하다.
  • 하지만 col1이 1과 5를 제외한 나머지 범위에 해당하는 ROW에 INSERT하는 것이 가능하다.
  • 팬텀 문제를 야기한다.
• SERIALIZABLE
  • 영역에 해당되는 데이터에 대한 수정 및 입력이 불가능하다.
  • SQL의 결과가 항상 동일하다.