*본 포스팅은 개인의 학습 내역을 정리한 것입니다.
1. 트랜잭션(Transaction)
- 트랜잭션은 ACID라는 4 종류의 속성을 가지고 있다.
(1) Atomic : 트랜잭션은 더이상 분리할 수 없는 작업이다.
(2) Consistency :완련된 트랜잭션의 모든 데이터는 일관적이어야 한다.
(3) Isolation : 동시 트랜젝션에 의한 수정은 다른 트랜젝션에 의한 수정과 격리되어야 한다.
(4) Durability : 트랜젝선이 완료되면 그 영향이 DB에 영구적으로 반영된다.
- 트랜젝션 제어
(1) 명시적 트랜잭션 : BEGIN TRANSACTION을 이용.
(2) 자동 커밋 트랜잭션 : SQL Server의 경우 각 T-SQL문은 완료시 커밋됨
(3) 암시적 트랜잭션 : API함수나 T-SQL의 SET IMPLICIT_TRANSACTIONS ON문을 이용
- 트랜잭션 완료시 : COMMIT으로 적용하거나 ROLLBACK으로 되돌림
(1) 트랜젝션 제어문
1) BEGIN TRANSACTION
|
BEGIN TRAN [SACTION] [<transaction_name>|<@tran_name_variable> [ WITH MARK ['<description>']] |
transaction_name은 트랜잭션에 할당된 이름. 명시하지 않아도 됨. 길이는 최대 32자
이 구문을 만나면 @@TRANCOUNT를 1 증가.
2) COMMIT TRANSACTION
|
COMMIT [TRAN [SACTION] [{transaction_name | @tran_name_variable}] |
- 사용자 정의 트랜잭션 또는 암시적 트랜잭션의 끝을 표기. 최근 트랜잭션 문에서 부터 변경사항을 반영하고, @@TRANCOUNT를 1 감소시킨다. 0이 아니면 트랜잭션은 진행중.
3) COMMIT WORK
|
COMMIT [WORK] |
트랜잭션의 끝을 의미. 사용자 정의 트랜잭션을 허용하는것을 제외하고는 COMMIT TRANSACTION과 동일
4) ROLLBACK TRANSACTION
|
ROLLBACK [ TRAN [SACTION] [<트랜잭션명>|<@트랜잭션이름변수>]|<저장점명>|<@저장점이름변수>]] |
명시적 또는 암시적인 트랜잭션을 트랜잭션의 처음이나 저장점으로 롤백
5) ROLLBACK WORK
|
ROLLBACK [WORK] |
트랜잭션의 처음으로 되돌린다.
6) SAVE TRANSACTION
|
SAVE TRAN[SACTION] {<저장점이름>|<@저장점이름변수>} |
트랜잭션 내의 저장점을 설정하여, 롤백시 해당 지점까지 롤백 가능
2. SQL Server의 트랜잭션
SQL Server의 트랜잭션은 런타임 오류나 컴파일 오류시의 데이터 일관성을 보장하지는 않는다. (오라클과의 차이)
- 트랜잭션 예
|
BEGIN TRAN 구문1(오류) 구문2(오류) 구문3(성공) COMMIT TRAN |
위 코드에서 구문1, 2, 3 개별적으로는 런타임 오류에 대한 트랜잭션이 유지된다. 그러나 구문 1,2,3 전체에 대해서는 수행을 하고, COMMIT을 만나서 구문3이 수행된 결과가 반영된다.
(1) XACT_ABORT
|
SET XACT_ABORT {ON | OFF} |
오류 발생시에 전체를 롤백할 지 여부를 설정. 위 BEGIN TRAN 전에 이 구문을 실행하여 두면, 구문3의 내용은 적용되지 않고, 오류 발생시에 바로 롤백을 수행한다.
3. 효율적인 트랜잭션 코딩
- 트랜잭션의 경우 가능한 짧게 유지되어야 한다. 길어질 경우 자원 문제, 잠금에 따른 경쟁을 줄여야 한다.
- 효율적인 트랜잭션을 위해 고려해야 할 사항
1) 트랜잭션중 사용자의 입력을 요구하지 말것
2) 데이터를 검색하는 중에는 트랜잭션을 시작하지 말 것 : 트랜잭션은 데이터 수정시 사용
3) 가능한 짧게 유지할 것
4) 낮은 트랜잭션 격리 수준을 효율적으로 사용할 것 : 공유 잠금으로 충분한 곳에 단독잠금을 하거나 하지는 말자
5) 낙관적 동시성 옵션과 같이 낮은 커서 동시성 옵션을 효율적으로 사용할 것
- 낙관적 동시성 옵션은 동시 업데이트가 희박하다고 간주하고, 수정 내용을 전송하기 전에 다른 곳에서 변동사항이 있었는지 확인하는 방법
6) 트랜잭션에서는 가능한 적은 양의 데이터에 엑세스 할 것
(1) 트랜잭션에서 허용되지 않는 T-SQL문
- ALTER DATABASE, CREATE DATABASE, DROP FULLTEXT INDEX, ALTER FULLTEXT CATALOG, CREATE FULLTEXT CATALOG, RECONFIGURE, ALTER FULLINDEX TEXT, CREATE FULLTEXT INDEX, RESTORE, BACKUP, DROP DATABASE, DROP FULLTEXT CATALOG, 전체텍스트 시스템 저장프로시저, sp_dboption또는, 트랜잭션 내에서, master DB를 수정하는 시스템 프로시저
출처 : http://technet.microsoft.com/ko-kr/library/jj856598(v=sql.110).aspx
(2) 중첩 트랜잭션의 사용
- 트랜잭션 역시 32단계 까지의 중첩을 지원한다. 모듈별로 트랜잭션 단위 업무를 만들고 이를 재사용 할 떄 중첩될 수 있다. 중첩되는 트랜잭션의 경우 모든 중첩된 트랜잭션이 완료되어야 세션 전체의 트랜잭션이 완료된다.
(3) 현재 Lock상태 확인하기 : sp_lock 프로시저를 사용하여 확인 가능하다.
(4) 암시적 트랜잭션 (implicit transactions)
- 지속적으로 트랜잭션이 걸려있는 상태로, 어느 시점에서도 롤백이 가능하다. 아래 구문으로 설정가능
|
SET IMPLICIT_TRANSACTIONS {ON | OFF} |
SQL Server에서 해당 설정은 기본이 OFF임. 해당 설정이 ON이고, 아래 구문 중 하나가 수행되면 트랜잭션이 시작된다.
|
ALTER TABLE |
DELETE |
FETCH |
INSERT |
REVOKE |
SELECT |
|
TRUNCATE TABLE |
CREATE |
DROP |
GRANT |
OPEN |
UPDATE |
(5) 분산 트랜잭션
- 단일 서버가 아닌 둘 이상의 서버에서 동작하는 트랜잭션.
- SQL Server의 경우 MS DTC서비스가 가동되고 있어야 분산 트랜잭션이 가능하다.
- 2단계 커밋 (2-Phase Commit). 안전한 트랜잭션 종료와 데이터 일관성을 위해 사용
|
단계 |
수행동작 |
|
준비단계 |
1. 응용 프로그램에서 트랜잭션이 종료될 때, 트랜잭션 관리자에게 종료를 요청한다. 트랜잭션 관리자는 모든 자원관리자에게 트랜잭션 종료 요청을 전송 2. 트랜잭션의 종료 요구를 받은 관리자는 자신이 가진 잠금과 충돌이 없고, 요청된 트랜잭션이 종료되어도 문제가 없다면 종료 허가 응답을 보냄 3. 요청을 보낸 관리자는 모든 응답이 올때까지 대기 |
|
완료단계 |
1. 모든 응답이 받아지면 트랜잭션 종료 명령을 전송 2. 명령을 받은 관리자는 트랜잭션을 종료한다. 3. 모든 관리자에게 성공 응답을 받으면 성공 응답을, 아닐경우 실패 응답을 응용프로그램에 전달 |
4. 잠금
- 트랜잭션을 수행할 때, 데이터를 잠궈 안전하게 보호한다.
(1) 동시성 문제
잠금을 사용하지 않으면 다음과 같은 문제들이 발생할 수 있다.
1) 손실업데이트 : 둘 이상의 트랜잭션이 같은 행에 대해 업데이트 할 때 발생.
2) 커밋되지 않은 종속성 : 다른 트랜잭션이 업데이트 중인 자료를 읽을 때 발생.
3) 일관성 없는 분석 : 트랜잭션이 하나의 행을 업데이트 중일 때, 다른 트랜잭션이 해당 행을 읽을 때 마다 값이 다름
4) 팬텀읽기(가상읽기) : 한 트랜잭션이 읽고있는 행에 대해 삽입 또는 삭제를 수행할 때 발생.
(2) 잠금 단위
잠금 단위는 SQL Server가 결정함. 기본적으로 행 단위이나, 여러 행을 잠글 경우 페이지 단위로, 여러 페이지를 잠글 경우 테이블 단위로 잠근다. SQL Server가 동적으로 결정하게 하는것이 유리
|
리소스 |
설명 |
|
RID |
행 식별자. 행 잠금시 사용. 클러스터된 인덱스가 없을 경우 RID를 사용. 아닐경우 키. |
|
키 |
인덱스에 있는 행 잠금. 트랜잭션에서 키 범위를 보호하기 위해 사용 |
|
페이지 |
8KB 데이터 페이지 또는 인덱스 페이지를 잠글 때 사용 |
|
익스텐트 |
인접한 8개의 데이터 페이지 또는 인덱스 페이지의 그룹 |
|
테이블 |
모든 데이터와 인덱스가 포함된 전체 테이블을 잠글 때 사용 |
|
DB |
DB잠금. DB복구시에 사용 |
(3) 잠금모드
동시 트랜잭션이 리소스에 접근하는 방법을 결정하는 모드
|
잠금 모드 |
설명 |
|
공유(S) |
SELECT처럼 데이터를 변경하거나 업데이트 하지 않는 작업에 사용 |
|
업데이트(U) |
업데이트 할 수 있는 리소스에 사용. 교착상태를 방지할 수 있다 |
|
단독(X) |
INSERT/UPDATE/DELETE와 같은 데이터 수정 작업에 사용한다. |
|
내재(I) |
잠금 계층 구조를 만드는데 사용. 내재된공유(IS), 내재된단독잠금(IX),공유및내재된단독잠금(SIX)가 있다. 하나의 행이 단독 잠금일 때, 해당 페이지 및 테이블은 IX로 포함되어 하위 개체에 어떤 잠금이 설정되었는지 확인할 수 있다. |
|
스키마(Sch) |
테이블의 스키마에 관련된 작업으로 컬럼의 추가나 테이블 삭제가 있다. Sch-M(스키마 수정)와 Sch-S(스키마 안정성) 두 종류가 있다. |
|
대량업데이트(BU) |
데이터를 테이블로 대량 복사하는 경우와 TABLOCK옵션이 지정된 경우 |
(4) 잠금 호환성
- 이미 잠긴 리소스에 대해 다른 잠금이 처리할 수 있는 것에 대한 가능 여부
|
기존 허용 모드 |
요청한 모드 | |||||
|
IS |
S |
U |
IX |
SIX |
X | |
|
내재된공유 잠금(IS) |
O |
O |
O |
O |
O |
X |
|
공유 잠금(S) |
O |
O |
O |
X |
X |
X |
|
업데이트 잠금(U) |
O |
O |
X |
X |
X |
X |
|
내재된 단독 잠금(IX) |
O |
X |
X |
O |
X |
X |
|
공유 및 내재된 단독 잠금(SIX) |
O |
X |
X |
X |
X |
X |
|
단독 잠금(X) |
X |
X |
X |
X |
X |
X |
(5) 인덱스와 잠금
인덱스가 없는 경우 효율성 문제로 테이블 잠금과 같이 처리된다. 이럴 경우 동시성이 떨어진다. 그에 따라 각 행에 대해 별도의 인덱스나 대리키를 만들어 사용하는 것이 좋다.
5. 트랜잭션 격리 수준
트랜잭션이 수행하는 일에 따라 격리 수준이 달라야 한다. 격리 수준이 높으면 동시성은 떨어지나 일관성이 높아지고, 격리수준이 낮으면 그 반대가 된다.
|
SET TRANSACTION ISOLATION LEVEL { <LEVEL값> } |
위 구문에서 격리 수준(LEVEL값)및 발생할 수 있는 현상은 아래와 같다
|
격리수준 |
커밋되지 않은 읽기 |
반복하지않는 읽기 |
팬텀 |
|
READ UNCOMMITTED |
O |
O |
O |
|
READ COMITTED |
X |
O |
O |
|
REPEATABLE READ |
X |
X |
O |
|
SERIALIZABLE |
X |
X |
X |
(1) READ UNCOMMITTED : 가장낮음. 공유 잠금이 만들어지지 않고 단독잠금이 무시됨
(2) READ COMMITTED : 데이터를 읽을 때는 공유잠금이 유지됨. SQL Server의 기본 값
(3) REPEATABLE READ : 읽은 데이터에 대해 잠금을 배치하여 다른 사용자가 업데이트 못하도록 함
(4) SERIALIZABLE : 트랜잭션이 완료될 때 까지, 다른 사용자가 행을 업데이트 하거나 행 삽입이 불가능
6. 테이블 수준 잠금 힌트
- SELECT, UPDATE, INSERT, DELETE에서는 테이블 명 다음에 잠금 힌트를 주어 테이블 잠금 수준을 지정할 수 있다.
자세한 내용은 옆의 링크 참조 : http://technet.microsoft.com/ko-kr/library/ms172398(v=sql.110).aspx
- 테이블 수준 잠금 힌트는 아래와 같이 사용한다.
|
SELECT <가져올컬럼> FROM <테이블명> WITH(잠금힌트) |
7. 교착 상태 및 세션 레벨 옵션
(1) 교착상태 : 두 트랜잭션이 서로 잠그고 있는 자원을 차지하기 위해 대기하는 상황. 이를 줄이기 위해 다음과 같이 작성하는 편이 좋다.
1) 트랜잭션 내에서 같은 순서로 개체에 엑세스 한다. 작업의 순서가 같다면, 해당 트랜잭션들은 동시에 진행될 수 없어 교착상태를 줄일 수 있다.
2) 트랜잭션 중 사용자 상호작용을 피함.
3) 트랜잭션을 하나의 일괄처리로 짧게 유지
4) 낮은 격리수준을 사용한다.
5) 바운드 연결을 사용한다.
(2) 잠금과 관련된 세션 레벨 옵션
- 트랜잭션이 LOCK_TIMEOUT설정보다 오래 기다린 경우 명령문은 취소되고 오류 1222번이 리턴된다. 응용프로그램에서는 1222 오류 메시지를 잡을 수 있는 오류 처리기가 필요할 것이다. 타임아웃 설정은 아래와 같이 한다.
|
SET LOCK_TIMEOUT <시간> |
시간 단위는 밀리초 이다.
- 트랜잭션에서 교착상태 발생시 우선순위를 설정할 수 있다.
|
SET DEADLOCK_PRIORITY { LOW | NORMAL | <@교착상태값>} |
LOW일수록 교착상태에서 밀려날 가능성이 큼
-출처 : [SQL Server advanced programming]
댓글