트랜잭션 프로세서

마지막 업데이트: 2022년 5월 19일 | 0개 댓글
  • 네이버 블로그 공유하기
  • 네이버 밴드에 공유하기
  • 페이스북 공유하기
  • 트위터 공유하기
  • 카카오스토리 공유하기
위의 명령을 실행 할 경우 위에서 언급한 반복읽기 수준일 경우에는 세션2의 데이터가 정상적으로
INSERT가 되며 다시 세션1의 SELECT문 실행시는 결과가 다르게 나온다.
하지만 직렬화(SERIALIZABLE)의 수준에서는 아래의 그림처럼 세션2가 대기 상태에 빠지게 된다.
세션1(spid 62)는 직렬화를 위한 공유키 잠금(RangeS-U)이 설정 되었으며 세션2(spid 61)은

트랜잭션 프로세서

트랜잭션은 데이터베이스의 상태를 변화시키는 논리적인 작업 단위를 의미한다. 데이터베이스의 상태를 변화시키는 작업이란 데이터베이스에 접근하여 데이터를 읽고, 쓰고, 수정하고, 삭제하는 등의 작업을 의미한다. 이런 작업들, 트랜잭션은 성공과 실패가 분명하고, 상호 독립적이다.

트랜잭션을 조작함으로 인해 사용자는 데이터베이스의 완전성 (Integrity)를 유지한다. 단일 트랜잭션은 데이터베이스에 읽거나 쓰는 등의 작업을 수행하는 여러 개의 쿼리를 요구한다. 이때 트랜잭션을 수행하면서 쿼리의 일부만 수행되는 일이 없어야 한다. 모든 작업이 완벽하게 처리되거나 또는 처리되지 못하는 경우 원상태로 복구되어야 한다.

2. 트랜잭션의 특징 (ACID)

데이터베이스의 트랜잭션이 안전하게 수행된다는 것을 보장하기 위해서는 다음의 성질들을 만족해야 한다.

원자성, Atomicity

트랜잭션은 DB 작업의 최소 단위가 되어야한다.

트랜잭션은 논리 작업 단위로써 정상적으로 트랜잭션 전체가 수행되거나 문제가 있는 경우 아예 수행되지 않아야 한다.
트랜잭션을 수행하던 중 문제가 생기면 이전에 수행한 작업이 모두 원복되어서 작업의 일부분만 수행되는 일이 없어야 한다.

일관성, Consistency

트랜잭션이 완료된 다음의 상태에서도 트랜잭션이 일어나기 전의 상황과 동일하게 데이터의 일관성을 보장해야 한다.

트랜잭션에서 사용하는 데이터가 작업 도중 다른 작업에 의해 변경되더라도 해당 트랜잭션의 수행에는 영향을 미치지 않아야한다.
트랜잭션이 진행되는 동안에 데이터베이스가 변경되더라도 트랜잭션은 업데이트 된 데이터가 아닌 작업이 수행될 때의 데이터베이스를 가지고 변경을 진행함으로 데이터의 일관성을 보장해야한다.
만약 데이터의 일관성에 문제가 있으면 다시 원복되어야 한다.

고립성, Isolation

각각의 트랜잭션은 서로간에 간섭없이 독립적으로 수행되어야 한다.
트랜잭션이 완료될 때까지 다른 트랜잭션에서는 해당 변경의 결과를 참조할 수 없다.

지속성, Durability

트랜잭션이 정상적으로 완료된 후에는 영구적으로 데이터베이스에 작업 결과가 저장되어야 한다.

3. Commit, Rollback

트랜잭션을 수행 중에 작업이 실패하는 경우가 발생할 수 있다. 이러한 상황을 방지하기 위해서 데이터베이스에서는 commit과 rollback 연산을 수행하여 트랜잭션 결과를 저장 또는 원복한다.

Commit

트랜잭션의 모든 작업이 수행된 후에 작업의 결과, 데이터베이스의 상태 변화를 저장하기 작업 완료를 알려준다. 이 연산을 수행하면 수행했던 트랜잭션이 로그에 저장되고, 후에 Rollback 연산을 수행하는 경우 트랜잭션 단위로 원복을 할 수 있도록 해준다.

Rollback

만약 트랜잭션이 정상적으로 모든 작업을 수행하지 못한 경우에 트랜잭션 수행 전으로 데이터베이스를 원복한다. 트랜잭션의 처리 단위대로 원복할 수 있다.

4. 트랜잭션의 상태

1) Active

트랜잭션의 활동 상태 . 트랜잭션이 실행 중이며 동작중인 상태를 말한다 .트랜잭션 프로세서

2) Failed

트랜잭션 실패 상태 . 트랜잭션이 더이상 정상적으로 진행할 수 없는 상태를 말한다 .

3) Partially Commited

트랜잭션의 commit 명령이 도착한 상태 . 트랜잭션의 commit 이전 sql 문이 수행되고 commit 만 남은 상태를 말한다 .

4) Aborted

트랜잭션의 취소 상태 . 트랜잭션이 취소되고 rollback 연산을 수행하여 트랜잭션 실행 이전 데이터로 돌아간 상태를 말한다 .

5) Commited

※ Partially Commited 와 Commited 의 차이점

Commit 요청이 들어오면 상태는 partially commited 상태가 된다 . 이후 commit 을 문제없이 수행할 수 있으면 commited 상태로 전이되고 만약 오류가 발생하는 경우 failed 상태가 된다 . 즉 partially commited 는 commit 요청이 들어왔을 때를 말하며 , commited 는 commit 을 정상적으로 완료한 상태를 말한다 .

5. Deadlock

복수의 트랜잭션을 사용하다 보면 교착상태가 일어날 수 있다 . 교착상태란 두 개 이상의 트랜잭션이 특정한 자원의 lock 을 획득한 채 다른 트랜잭션이 소유하고 있는 잠금을 요구하면 아무리 기다려도 상황이 바뀌지 않는 상태가 되는데 , 이를 교착상태라고 한다 .

lock 이란 트랜잭션의 처리 순차성을 보장하기 위한 방법이다. lock은 여러 커넥션에서 동시에 동일한 자원을 요청하는 경우 순서대로 하나의 커넥션만 해당 자원에 접근할 수 있도록 해준다. lock 은 해당 트랜잭션이 완료될 떄까지 유지된다.

이러한 데드락을 방지하기 위해서는 트랜잭션을 자주 커밋하거나 쿼리를 작성할 때 트랜잭션들이 테이블에 접근하는 순서를 동일하게 구성하는 등의 방법으로 데드락 빈도를 낮출 수 있다. 테이블의 순서를 동일하게 구성하게 되면 테이블 단위의 lock 을 획득해 작업을 수행하기 때문에 동시성은 떨어지지만 순차적으로 접근하여 교착상태를 피할 수 있다.

트랜잭션 프로세서

트랜잭션(Transaction) 이란 간단하게 말해서 논리적인 작업의 최소 단위라 정의 할 수 있다.
우리가 SQL Server에서 실행되는 일련의 명령 실행 과정이 트랜잭션으로 이뤄지며 이것은
명시적 또는 암시적 으로 시작(Begin Transaction)과 끝(Commit or Rollback)의 처리 범위를
지정할 수 있으며 이를 지정하지 않을경우 개별적인 트랜잭션이 자동으로 시작되고 끝이 난다.

이러한 트랜잭션은 원자성(Atomicity),일관성(Consistency),고립성(Isolation),
보존성(Durability) 등의 4가지 속성을 가지는데 이를 트랜잭션의 ACID속성 이라 하며
이제부터 할려는 주제는 이중 고립성(Isolation)에 대한 내용이다.

고립성이란 하나의 트랜잭션이 발생되었을 경우 다른 트랜잭션에 대하여 얼마만큼 방해를 받지
않고 독립성을 가지느냐에 대한 트랜잭션 특성이다. 이것은 한 프로세스가 작업하는 리소스에
대해 다른 프로세스가 작업하지 못하도록 차단하는데 그 방법이 잠금(Lock) 이다.
사용자는 트랜잭션 작업에 대한 일관성을 향상 시키기 위해 잠금(Lock) 의 방법을 정의할수 있는데
이것을 격리수준(Isolation Level) 이라 한다.

SQL Server 2000에서 지원하는 격리수준은 아래와 같다.
1. 커밋되지 않은 읽기(Read Uncommittes)
2. 커밋된 잃기(Read Committed)
3. 반복 읽기(Repeatable Read)
4. 직렬화(Serializabale)

SQL Server 2005에서는 이에 2가지를 더 지원한다.
5. 스냅샷(Snapshot)
6. 커밋된 읽기(Read Committed Snapshot)
이제 각각의 대해 알아보자.

1. 커밋되지 않은 읽기(Read Uncommittes)
이것은 말 그대로 "커밋되지 않은 데이터도 읽을 수 있다." 라는 것이다.
일반적으로 업데이트시 커밋되지 않은 데이터는 배타점 잠금(X-Lock)이 걸려있기 다른 프로세스에서
읽기 조차 되지 않는다. 하지만 이것은 커밋되지 않는 데이터를 다른 프로세스에서 SELECT시
어떠한 잠금도 걸지 않는다. 다만 업데이트 되지 않은 데이터를 읽을 수 있다는 것이 무결성을 깨트릴
위험성이 있다. 그러나 Rollback되는 데이터를 SELECT하는 경우가 흔치 않고 이 격리수준에서는
동시성의 트랜잭션 프로세서 향샹되는 효과가 있기 때문에 자주 사용되는 격리수준이다.
이제 실습을 해보자.
먼저 실습용 테이블과 데이터를 만들고

이제 하나의 데이터를 명시적으로 트랜잭션을 지정하여 업데이트 해보자.

잠금(Lock)이 걸리는지도 확인해보고

해당 테이블 Object ID로 배타적 잠금이 걸려있는걸 볼 수 있다.

그럼 다른 세션으로 Read Uncommittes을 이용하여 데이터를 불러보자 방법은 크게 2가지가 있다.

실행결과 트랜잭션이 종료 되지 않아도 데이터를 읽을 수 있다. 위와 같은 상태를 좀 더 깊게
설명을 하자면 일반적으로 SELECT시에도 공유잠금 이라는걸 요청을 하게 되는데 세션1에서의
배타적 잠금이 있는 상태에서 세션2에서의 공유잠금이 일어 날 수가 없다.
(베타적 잠금은 어떠한 잠금과도 호환 되지 않는다.)
그래서 해당 세션의 격리 수준을 커밋 되지 않은 읽기 수준으로 설정을 하면서 데이터를 트랜잭션 프로세서 읽는
작업이 실행되면 해당 세션은 공유잠금을 요청 하지 않는다는 것이다.
그렇지만 위에서 언급했듯이 커밋되지 않은 데이터를 읽기 때문에 일관성이 떨어진다.
다만 동시성이 좋기 때문에 OLTP환경에서 가장 흔하게 쓰이는 격리 수준이라 할 수 있다.

2. 커밋된 읽기(Read Committed)
커밋된 읽기는 SQL Server의 기본 격리 수준이다. 앞서 언급했듯이 데이터를 읽기 위해서는
기본적으로 공유잠금을 요청 하기 때문에 UPDATE를 실행중인 데이터를 SELECT시 배타적잠금이
걸려있는 데이터에 공유 잠금을 요청하기 때문에 UPDATE가 종료될 때까지 대기상태에 있으며
트랜잭션이 종료 됨과 동시에 실행이 된다.
간단하게 테스트를 해보자.

이제 세션2에서 SELECT문을 실행해보자

잠금상태를 확인해 보자

보시는 바와 같이 세션1(spid 62)는 해당 테이블에 배타점 잠금(Mode : X)을 걸어논 상태이며
해당 데이터에 SELECT를 시도하는 세션2(spid 61)은 공유잠금(Mode : S)을 요청한후
대기 상태(Status : Wait)에 있는 걸 확인 할 수 있다.

세션1에서 COMMIT또는 ROLLBACK을 실행하면 잠금정보는 해제되며 세션2에서는
해당 데이터를 읽어 올 수 있는걸 확인해볼수 있다.
위와 같은 격리 수준은 커밋되지 않은 데이터를 읽음으로써 일관성을 떨어뜨리는
Dirty Read를 하지 않았지만 동시성이 떨어진다는것을 알 수 있다. 하지만 이 격리수준 역시도
반복 읽기(REPEATABLE READ) 가 불가능해 일관성에 대한 문제가 있다.

3. 반복 읽기(Repeatable Read)
이것은 반복 가능한 읽기 수준이다. 위에서 언급했던 격리 수준과는 조금 다른 개념이며
SELECT시 명시적인 트랜잭션을 시작하여 공유잠금이 설정되면 해당 데이터에 대해 다른 세션에서는
UPDATE나 DELETE시 일어나는 배타적 잠금이 대기 상태에 놓이며 트랜잭션이 종료시 까지
해당 데이터를 반복적으로 SELECT시 동일한 데이터를 보장한다.

일반적인 커밋된 읽기(READ COMMITED)수준일 경우 위의 명령을 순서대로 실행 했을 경우

세션1에서 공유잠금이 요청되고 세션2에서는 배타적 잠금이 요청되며 세션1의 두번째 명령문은

실행이 안되고 대기를 하게 된다. 하지만 격리 수준을 반복 가능한 읽기수준으로 설정을 하면

세션1의 첫번째 명령문이 실행된 후 세션2의 명령은 세션1의 커밋 또는 롤백 명령으로 트랜잭션이

종료될때 까지 대기를 하게 된다. 그리고 세션2의 두번째 명령문을 실행하면 트랜잭션이 종료 되기 전
다시 해당 데이터에 명령을 요청하면 데이터를 아무런 제약없이 읽을 수 있다.
또한 대기중인 세션2의 트랜잭션도 정상적으로 실행이 완료 된다.

요컨대, 한 세션의 트랜잭션이 진행 중일 때는 외부에서 데이터의 변경을 차단하고 동일한
세션에서는 동일한 데이터를 읽을수 있는 구조가 필요할때 활용 가능한 격리 수준이다.
하지만 외부로부터의 UPDATE에 대한 트랜잭션으로 부터 잠금이 획득되며 데이터의 INSERT시
까지 차단되지는 않는다. 즉 SELECT한 결과가 항상 동일 하다는 것은 아니다 라는걸 주의해야한다.

직렬화는 간단히 말해 명령이 순서대로 처리 되는 것이다. 이 수준은 가장 높은 수준의 격리 수준으로

트랜잭션이 실행시 키범위 잠금(Range S-Lock)이 걸리고 트랜잭션이 종료 되기 전까지는 모든

외부로 부터의 트랜잭션으로 부터 격리된다. 또한 이것은 이미 존재하는 물리적인 데이터 뿐만이

아니라 가상의 논리적 데이터 까지도 잠금을 걸어 격리되기 때문에 동일한 SELECT결과가 보장되어

일관성이 뛰어 나지만 동시성이 현저히 떨어지므로 성능은 최악이다.

위의 명령을 실행 할 경우 위에서 언급한 반복읽기 수준일 경우에는 세션2의 데이터가 정상적으로
INSERT가 되며 다시 세션1의 SELECT문 실행시는 결과가 다르게 나온다.
하지만 직렬화(SERIALIZABLE)의 수준에서는 아래의 그림처럼 세션2가 대기 상태에 빠지게 된다.
세션1(spid 62)는 직렬화를 위한 공유키 잠금(RangeS-U)이 설정 되었으며 세션2(spid 61)은

그로인해 대기상태에 있는것을 확인 할수 있다. 위에 언급한 반복읽기(Repeatable Read)수준에서

말했던 외부로부터의 INSERT나 DELETE시에 발생하는 가상 데이터의 읽기(Panthom Read) 를

차단 할 수 있다. 이처럼 데이터의 일관성이 보장되는 만큼 모든 트랜잭션에 대해서 차례차례

처리가 완료되어야 한다는 부담때문에 높은 격리수준에 비해 성능이 현저히 떨어질수 있다는

부담이 있다.

5. 스냅샷 격리수준(Snapshot Isolation)
오라클에는 이미 로우 버저닝이라는 기반의 트랜잭션 처리가 가능하다. 이것은 특정 데이터가

업데이트될 경우 Undo라는 공간에 로그를 남겨 커밋되기 전까지 이전 버젼의 데이터를 사용자에게

반환하여 읽기 일관성과 사용자 간의 동시성을 향상 시킨 방법이다. 오라클 10g버젼은 이러한

트랜잭션 처리 방식으로 TPC-C라는 OLTP벤치마크 테스트에서 32bit프로세서로 1분에 160만개의

트랜잭션을 처리하여 타 DBMS와 비해 성능이 우수 하다는 것을 검증 하였다.


이에 SQL Server 2005부터는 이와 유사항 기능인 스냅샷 격리수준을 가능케 하였는데 이것은

데이터의 변경이 일어날시 임시 DB인 TempDB에 저장하여 행 단위 버젼으로 관리함으로써 일관성과

동시성을 높이는 격리수준이다.

이것은 수많은 프로세스가 동시에 읽기/쓰기 작업으로 발생되는 공유잠금 확대(Lock Escalation)에

지금까지의 격리수준 설정과는 다르게 이것은 데이터베이스의 옵션을 변경을 트랜잭션 프로세서 해줘야 한다.

그리고 아래 쿼리를 실행해 보자.

위의 쿼리를 실행하면 세션1의 결과는 Weight값이 50으로 보이며 세션2의 결과는 Weight값이

변경되지 않은 기존값으로 나올것이다. 다시 세션1의 트랜잭션을 커밋시켜본후 세션2을 실행해보자

그럼에도 데이터는 변경되지 않은 상태로 나올 것이다. 이것은 앞서 얘기했던 스냅샷 격리 수준에서는

변경된 데이터에 대해서 행 버젼 관리가 이뤄지므로 변경되기전의 버젼 데이터를 읽어오기 때문이다.

6. 커밋된 읽기 스냅샷(Read Committed Snapshot)

트랜잭션 프로세서

1. 트랜잭션( transaction )

트랜잭션이란 질의(query)를 하나의 묶음 처리해서 만약 중간에 실행이 중단됐을 경우,

처음부터 다시 실행하는 Rollback을 수행하고, 오류없이 실행을 마치면 commit을 하는 실행 단위를 의미합니다.

즉, 한 번 질의가 실행되면 질의가 모두 수행되거나 모두 수행되지 않는 작업수행의 논리적 단위입니다.

예를 들어, 친구에게 인터넷 뱅킹으로 10,000원을 송금하는 상황을 가정해보겠습니다.

제가 친구에게 송금을 한다면, 저의 계좌에서 10,000원을 차감하고 친구의 계좌에 10,000원을 증가시켜야 하는데,

알 수 없는 오류로 인해 저의 계좌에서는 10,000원이 줄었지만 친구 계좌에는 10,000원이 증가되지 않는다면 어떻게 될까요?

저의 10,000원은 그냥 공중으로 증발해버리는 문제가 발생합니다.

이러한 경우가 생기지 않도록 중간에 오류가 발생하면 다시 처음부터 송금을 하도록 하는 것이 rollback입니다.

오류 없이 정상적으로 송금이 됐다면 정상적으로 실행이 끝났으므로 commit을 합니다.

즉, 송금 과정을 하나의 트랜잭션이라 볼 수 있습니다.

참고로 트랜잭션을 작업수행의 논리적 단위라고 했는데요, 때문에 DBMS의 성능은 초당 트랜잭션의 실행 수( TPS : Transaction per second )로 측정합니다.

트랜잭션은 DB 서버에 여러 개의 클라이언트가 동시에 액세스 하거나 응용프로그램이 갱신을 처리하는 과정에서 중단될 수 있는 경우 등 데이터 부정합을 방지하고자 할 때 사용합니다.

부정합이 발생하지 않으려면 프로세스를 병렬로 처리하지 않도록 하여 한 번에 하나의 프로세스만 처리하도록 하면 되는데, 이는 효율이 너무 떨어집니다.

즉, 병렬로 처리할 수 밖에 없는 현실적인 상황으로 인해 부정합을 방지하고자 트랜잭션을 사용하는 것입니다.

트랜잭션에서 중요한 것은 스케줄 관리입니다.

스케줄을 잘못 짜게 되면, 데드락에 빠지게 되는데 이와 관련된 주제가 뒤에서 살펴볼 2PL입니다.

트랜잭션에는 아래와 같이 4가지의 특성이 있습니다.

4가지 특성의 앞 글자만 따서 ACID 특성이라 하며, 면접질문 및 정보처리기사 등의 시험에서 자주 등장하는 개념입니다.

  • 원자성 ( Atomicity )
    • 트랜잭션의 작업이 부분적으로 실행되거나 중단되지 않는 것을 보장하는 것을 말합니다.
    • 즉, All or Noting의 개념으로서 작업 단위를 일부분만 실행하지 않는다는 것을 의미합니다.

    일관성 ( Consistency )

    트랜잭션이 성공적으로 완료되면 일관적인 DB상태를 유지하는 것을 말합니다.

    여기서 말하는 일관성이란, 위의 송금 예제에서 금액의 데이터 타입이 정수형(integer)인데, 갑자기 문자열(string)이 되지 않는 것을 말합니다.

    즉, 송금 전후 모두 금액의 데이터 타입은 정수형이여야 한다는 것이 일관성입니다.

    격리성 ( Isolation )

    트랜잭션 수행시 다른 트랜잭션의 작업이 끼어들지 못하도록 보장하는 것을 말합니다.

    트랜잭션 프로세서 즉, 트랜잭션끼리는 서로를 간섭할 수 없습니다.

    지속성 ( Durability )

    성공적으로 수행된 트랜잭션은 영원히 반영이 되는 것을 말합니다.

    commit을 하면 현재 상태는 영원히 보장됩니다.

    이제부터 트랜잭션이 각 특성들을 어떻게 보장하고 있는지 알아보도록 하겠습니다.

    트랜잭션에서 원자성은 수행하고 있는 트랜잭션에 의해 변경된 내역을 유지하면서, 이전에 commit된 상태를 임시 영역에 따로 저장함으로써 보장합니다.

    즉, 현재 수행하고 있는 트랜잭션에서 오류가 발생하면 현재 내역을 날려버리고 임시 영역에 저장했던 상태로 rollback 합니다.

    이전 데이터들이 임시로 저장되는 영역을 롤백 세그먼트(rollback segment)라고 하며,

    현재 수행하고 있는 트랜잭션에 의해 새롭게 변경되는 내역을 테이터베이스 테이블이라고 합니다.

    다시 말하면, 트랜잭션의 원자성은 롤백 세그먼트에 의해 보장된다고 할 수 있습니다.

    그런데 오류가 발생하면 트랜잭션 프로세서 rollback을 하는데, 트랜잭션의 길이가 길어지면 어떻게 될까요?

    확실하게 오류가 발생하지 않는 부분도 다시 처음부터 작업을 수행해야 합니다.

    따라서 확실한 부분에 대해서는 rollback이 되지 않도록 중간 저장 지점인 save point를 지정할 수 있습니다.

    save point를 지정하면 rollback 할 때 save point 이전은 확실하다 간주하고 그 이후부터 진행하게 됩니다.

    트랜잭션에서 일관성은 트랜잭션 수행 전, 후에 데이터 모델의 모든 제약 조건(기본키, 외래키, 도메인, 도메인 제약조건 등)을 만족하는 것을 통해 보장합니다.

    예를 들어, Movie와 Video 테이블이 있을 때 Video 테이블에 Movie 테이블의 primary key인 movie_id 가 외래키로 존재한다고 가정하겠습니다.

    만약 movie_id 의 제약조건이 Movie 테이블에서 변경되면, Video 테이블에서도 movie_id 가 변경되어야 합니다.

    한 쪽의 테이블에만 데이터 변경사항이 이루어지면 안되는 것이죠.

    그렇다면 어떻게 트랜잭션은 일관성을 보장할까요?

    어떤 이벤트조건이 발생했을 때, 트리거( Trigger )를 통해 보장합니다.

    트리거는 "방아쇠"인데, 데이터베이스 시스템이 자동적으로 수행할 동작을 명시하는데 사용됩니다.

    어떤 행위의 시작을 알리는 것이죠.

    위의 트랜잭션 프로세서 코드는 트리거가 호출되면, 수행할 질의들을 트리거로 생성해서 작성한 코드입니다.

    create 는 트리거를 생성하는 코드이고, after 는 트리거가 실행되기 위한 event를 나타냅니다.

    트랜잭션이 고립성을 보장하는 방법에 대해 이해하기 위해서는 병행 트랜잭션에 대해 먼저 알아야 합니다.

    1) 병행 처리 ( concurrent processing )

    CPU가 여러 프로세스를 처리하는 것처럼, 트랜잭션에 정해진 시간을 할당해서 작업을 하다가 부여된 시간이 끝나면 다른 트랜잭션을 실행하는 이런 방식으로 트랜잭션들을 조금씩 처리하는 것을 말합니다.

    그런데 이렇게 되면 많은 트랜잭션들이 조금씩 처리되는 과정에서 공통된 데이터를 조작하게 되는데, 이 경우 데이터가 혼란스러워 질 수 있습니다.

    예를 들어, A 트랜잭션에서 x라는 데이터를 100으로 설정한 후 시간이 만료되어 B 트랜잭션으로 넘어갔다고 가정해보겠습니다.

    B 트랜잭션에서는 x 데이터에 -50 연산을 해서 저장을 했을때, 시간이 만료되어 다시 A 트랜잭션이 실행될 경우 x 데이터의 값은 50이 됩니다.

    이렇게 트랜잭션이 조금씩 수행될 때, 공통된 데이터가 다른 트랜잭션에 의해 방해되면 안됩니다.

    이와 같이 트랜잭션의 간섭이 일어날 경우 갱신분실, 오손판독, 반복불가능, 팬텀문제 등 여러 문제점들이 발생합니다.

    병행처리 과정에서 트랜잭션의 고립성이 왜 보장되어야 하는지를 알게되었습니다.

    그러면 고립성을 어떻게 보장할 수 있을까요?

    OS의 세마포어(semaphore)와 비슷한 개념으로 lock & excute unlock을 통해 고립성을 보장할 수 있습니다.

    즉, 데이터를 읽거나 쓸 때는 문을 잠궈서 다른 트랜잭션이 접근하지 못하도록 고립성을 보장하고, 수행을 마치면 unlock을 통해 데이터를 다른 트랜잭션이 접근할 수 있도록 허용하는 방식입니다.

    트랜잭션에서는 데이터를 읽을 때, 여러 트랜잭션이 읽을 수는 있도록 허용하는 shared_lock을 합니다.

    즉, 트랜잭션 프로세서 shared_lock은 데이터 쓰기를 허용하지 않고 오직 읽기만 허용합니다.

    또한 데이터를 쓸 때는 다른 트랜잭션이 읽을 수도 쓸 수도 없도록 하는 exclusive_lock을 사용합니다.

    그리고 읽기, 쓰기 작업이 끝나면 unlock을 통해 다른 트랜잭션이 lock을 할 수 있도록 데이터에 대한 잠금(lock)을 풀어줍니다.

    그런데 lock과 unlock을 잘못 사용하면 데드락(deadlock)상태에 빠질 수 있습니다.

    모든 트랜잭션이 아무것도 수행할 수 없는 상태가 되는 것이죠.

    3) 2PL 프로토콜 ( 2 Phase Locking protocol )

    당연히 데드락이 걸리면 안되므로, 어떤 규칙에 의해서 고립성을 보장해야 한다는 2PL 프로토콜이 연구되었습니다.

    • 보수적 locking ( conservative locking )
      • 트랜잭션이 시작되면 모든 lock을 얻는 방식으로서, 데드락이 발생하지 않지만 병행성이 좋지 못함
      • 트랜잭션이 commit을 만날 때까지 lock을 갖고 있다가 commit을 만날때 unlock을 하는 방식으로 데드락이 발생하지만 병행성이 좋음
      • 일반적으로 병행성이 좋은 strict 방식을 사용합니다.

      6. MySQL InnoDB 엔진의 트랜잭션과 Locking

      지금까지 트랜잭션의 ACID 특징에 대해 알아보았습니다.

      RDB에서는 일반적으로 잠금 스케줄러를 사용하는데, 충분히 좋은 성능을 낼 수 있다고 합니다.

      지금까지 쿼리에서 transaction을 사용하면 자동으로 locking 처리가 되는 줄 알았는데, 제품에 따라 또는 설정에 따라 다른것 같습니다.

      트랜잭션 프로세서

      트랜잭션은 데이터베이스의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위 또는 한꺼번에 모두 수행되어야 할 일련의 연산들을 의미한다.

      트랜잭션은 데이터베이스 시스템에서 병행 제어 및 회복 작업 시 처리되는 작업의 논리적 단위로 사용

      트랜잭션은 사용자가 시스템에 대한 서비스 요구 시 시스템이 응답하기 위한 상태 변환 과정의 작업 단위로 사용

      트랜잭션 특성

      다음은 데이터의 무결성을 보장하기 위하여 DBMS의 트랜젝션이 가져야 할 특성이다.

      Atomicity(원자성)

      트랜잭션의 연산은 데이터베이스에 모두 반영되도록 완료(Commit)되든지 아니면 전혀 반영되지 않도록 복구(Rollback)되어야 함

      트랜잭션 내의 모든 명령은 반드시 완벽히 수행되어야 하며, 모두가 완벽히 수행되지 않고 어느 하나라도 오류가 발생하면 트랜잭션 전부가 취소되어야 함

      Consistency(일관성)

      트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 변환

      시스템이 가지고 있는 고정 요소는 트랜잭션 프로세서 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 함

      Isolation(독립성, 격리성, 순차성)

      둘 이상의 트랜잭션이 동시에 병행 실행되는 경우 어느 하나의 트랜잭션 실행 중에 다른 트랜잭션의 연산이 끼어들 수 없음

      수행중인 트랜잭션은 완전히 완료될 때까지 다른 트랜잭션에서 수행 결과를 참조할 수 없음

      Durability(영속성, 지속성)

      성공적으로 완료된 트랜잭션의 결과는 시스템이 고장나더라도 영구적으로 반영되어야 함

      CRUD 분석

      CRUD는 '생성(Create), 읽기(Read), 갱신(Update), 삭제(Delete)'의 앞 글자만 모아 만든 용어이며, CRUD 분석은 데이터베이스 테이블에 변화를 주는 트랜잭션의 CRUD 연산에 대해 CRUD 매트릭스를 작성하여 분석하는 것이다.

      CRUD 분석으로 테이블에 발생되는 트랜잭션의 주기별 발생 횟수를 파악하고 연관된 테이블들을 분석하면 테이블에 저장되는 데이터의 양을 유추할 수 있음

      CRUD 분석을 통해 많은 트랜잭션이 몰리는 테이블을 파악할 수 있으므로 디스크 구성 시 유용한 자료로 활용할 수 있음

      CRUD 분석을 통해 외부 프로세스 트랜잭션의 부하가 집중되는 데이터베이스 채널을 파악하고 분산시킴으로써 연결 지연이나 타임아웃 오류를 방지할 수 있음

      트랜잭션 프로세서 CRUD 매트릭스

      CRUD 매트릭스는 2차원 형태의 표로서, 행에는 프로세스를, 열에는 테이블을, 행과 열이 만나는 위치에는 프로세스가 테이블에 발생시키는 변화를 표시하는 업무 프로세스와 데이터 간 상관 분석표이다.

      CRUD 매트릭스를 통해 프로세스의 트랜잭션이 테이블에 수행하는 작업을 검증

      CRUD 매트릭스의 각 셀에는 Create, Read, Update, Delete의 앞 글자가 들어가며, 복수의 변화를 줄 때는 기본적으로 C > D > U > R 의 우선순위를 적용하여 한가지만 적지만, 활용 목적에 따라 모두 기록할 수 있음

      CRUD 매트릭스가 완성되었다면 C, R, U, D 중 어느 것도 적히지 않은 행이나 열, C나 R이 없는 행을 확인하여 불필요하거나 누락된 테이블 또는 프로세스를 찾음

      트랜잭션 분석

      트랜잭션 분석의 목적은 CRUD 매트릭스를 기반으로 테이블에 발생하는 트랜잭션 양을 분석하여 테이블에 저장되는 데이터의 양을 유추하고 이를 근거로 DB 용량을 산정하고 DB 구조를 최적화하는 것이다.

      트랜잭션 분석은 업무 개발 담당자가 수행

      트랜잭션 분석을 통해 프로세스가 과도하게 접근하는 테이블을 확인하여 여러 디스크에 배치함으로써 디스크 입출력 분산을 통한 성능 향상을 가져올 수 있음

      트랜잭션 분석서

      트랜잭션 분석서는 단위 프로세스와 CRUD 매트릭스를 이용하여 작성하며, 구성 요소에는 단위 프로세스, CRUD 연산, 테이블명, 컬럼명, 등이 있다.

      단위 프로세스 : 업무를 발생시키는 가장 작은 단위의 프로세스

      CRUD 연산 : 프로세스의 트랜잭션이나 데이터베이스 테이블에 영향을 주는 C, R, U, D의 4가지 연산

      테이블명, 컬럼명 : 프로세스가 접근하는 데이터베이스의 테이블명을 기록. 필요한 경우 테이블의 컬럼명을 적음. 컬럼명을 적을 때는 마침표로 연결하여 '테이블.컬럼명'과 같이 적음

      [sql-server] 프로세스가 교착 상태의 희생양이되는 원인

      완료하는 데 5 ~ 10 분 정도 걸리는 Select 프로세스가 있습니다.
      현재 MS SQL 데이터베이스 엔진에 대한 힌트로 NOLOCK을 사용하지 않습니다.
      동시에 동일한 데이터베이스 및 동일한 테이블에 업데이트 및 삽입을 수행하는 다른 프로세스가 있습니다.
      첫 트랜트랜잭션 프로세서 잭션 프로세서 번째 프로세스가 시작되었으며 최근에 메시지와 함께 조기 종료됩니다.

      SQLEXCEPTION : 트랜잭션이 다른 프로세스와 함께 잠금 리소스에서 교착 상태가되었고 교착 상태 희생자로 선택되었습니다.

      이 첫 번째 프로세스는 동일한 조건으로 다른 사이트에서 실행되지만 데이터베이스가 더 작아서 문제의 select 문이 훨씬 더 짧은 시간 (약 30 초 정도)이 걸립니다. 이러한 다른 사이트에서는 이러한 다른 사이트에서 교착 상태 메시지가 표시되지 않습니다. 또한 처음에 문제가 발생한 사이트에서이 메시지를받지 못했지만 데이터베이스가 커짐에 따라 어느 정도 임계 값을 넘었을 것입니다. 내 질문은 다음과 같습니다.

      1. 트랜잭션을 실행하는 데 걸리는 시간으로 인해 관련 프로세스가 교착 상태로 표시 될 가능성이 높아질 수 있습니다.
      2. NOLOCK 힌트로 선택을 실행하면 문제가 제거됩니까?
      3. select 문에서 WHERE 절의 일부로 확인 된 datetime 필드로 인해 조회 시간이 느려진 것 같습니다. 이 필드를 기반으로 색인을 만들 수 있습니까? 권장됩니까?

      답변

      Q1 : 트랜잭션이 실행되는 데 걸리는 시간으로 인해 관련 프로세스가 교착 상태로 표시 될 가능성이 높아질 수 있습니다.

      아니요. SELECT는 데이터를 읽기만 했으므로 희생자이므로 트랜잭션 과 관련된 비용이 더 낮 으므로 희생자로 선택됩니다.

      기본적으로 데이터베이스 엔진은 롤백하는 데 가장 비용적게 드는 트랜잭션을 실행하는 세션을 교착 상태로 선택합니다 . 또는 사용자가 SET DEADLOCK_PRIORITY 명령문을 사용하여 교착 상태 상황에서 세션의 우선 순위를 지정할 수 있습니다 . DEADLOCK_PRIORITY는 LOW, NORMAL 또는 HIGH로 설정하거나 또는 범위 (-10 ~ 10)의 정수 값으로 설정할 수 있습니다.

      Q2. NOLOCK 힌트로 선택을 실행하면 문제가 제거됩니까?

      아니요. 몇 가지 이유가 있습니다.

      • 먼저 근본 원인을 조사하여 교착 상태를 적절히 제거해야합니다.
      • 더티 읽기는 일관성없는 읽기 입니다.
      • 더티 읽기를 지정하는 적절한 방법은 트랜잭션 격리 수준 을 사용하는 것입니다.
      • 훨씬 더 나은 솔루션이 있습니다 : read commit snapshot .

      Q3. select 문에서 WHERE 절의 일부로 확인 된 datetime 필드로 인해 조회 시간이 느려진 것 같습니다. 이 필드를 기반으로 색인을 만들 수 있습니까? 권장됩니까?트랜잭션 프로세서

      아마. 교착 상태의 원인은 거의 색인이 제대로되지 않은 데이터베이스 일 가능성이 매우 높습니다. 10 분 쿼리는 이러한 좁은 조건에서 허용되며, 귀하의 경우 100 % 확신 할 수 없습니다 .

      99 %의 신뢰를 바탕으로 업데이트와 충돌하는 대형 테이블 스캔으로 인해 교착 상태가 발생한다고 선언합니다. 교착 상태 그래프 를 캡처 하여 원인을 분석하십시오. 데이터베이스의 스키마를 최적화해야 할 가능성이 높습니다. 수정하기 전에 색인 및 하위 문서 디자인 주제를 읽으십시오 .

      답변

      이 특정 교착 상태 문제가 실제로 발생하는 방법과 실제로 해결 된 방법은 다음과 같습니다. 이것은 매일 130K 트랜잭션이 발생하는 상당히 활동적인 데이터베이스입니다. 이 데이터베이스의 테이블에있는 인덱스는 원래 클러스터링되었습니다. 클라이언트는 인덱스를 클러스터링하지 않도록 요청했습니다. 그렇게하자마자 교착 상태가 시작되었습니다. 인덱스를 클러스터 된 상태로 다시 설정하면 교착 상태가 중지되었습니다.

      답변

      여기에있는 답변은 시도해 볼 가치가 있지만 코드도 검토해야합니다. 특히 Polyfun의 답변을 읽으십시오.
      SQL Server 2005 및 C # 응용 프로그램에서 교착 상태를 제거하는 방법은 무엇입니까?

      동시성 문제와 쿼리에서 “with (updlock)”를 사용하면 실제로 코드가 수행하는 작업에 따라 교착 상태 상황을 수정할 수있는 방법에 대해 설명합니다. 코드가이 패턴을 따르는 경우 더티 읽기 등에 의존하기 전에 이것이 더 나은 수정일 가능성이 높습니다.

      답변

      @ Remus Rusanu ‘s가 이미 탁월한 대답이지만 SQL Server의 교착 상태 원인 및 추적 전략 에 대한 더 나은 통찰력을 기대하는 경우 Brad McGehee 의 SQL Server 2005 프로파일 러를 사용하여 교착 상태를 추적하는 방법을 읽어 보는 것이 좋습니다.


0 개 댓글

답장을 남겨주세요