트랜잭션
- 의미
- 특징
- 필요성
- Operation
- 동시성 제어
격리 수준과 일관성
- 의미
- Read Phenomena
- 격리 수준
잠금 관리
- 레코드 잠금 관리
- 스키마 잠금 관리
- 키 잠금 관리
- 호환성
- cubrid lockdb $DB-NAME
- cubrid tranlist & cubrid killtran -i $TRAN-ID
- Deadlock
- From 2 Phase Locking to MVCC
궁금한 점
- U_LOCK 1
- U_LOCK 2
- SIX_LOCK
- 키 잠금의 대상
- Lock Escalation 1
- Lock Escalation 2
- Num blocked-holders
- Slow Query
- MVCC
- Vacuum
- Vacuum Cleanup
- Cursor
- REPEATABLE READ & PHANTOM READ에서의 INSERT 연산 시 LOCK 획득 차이
트랜잭션이란 업무적으로 의미가 있는 최소 작업 단위이자, 데이터베이스 운용에서 장애 발생 시 데이터를 복구하는 단위로 이용된다. 데이터베이스의 연산들은 SQL로 구성되기 때문에 트랜잭션은 작업 수행에 필요한 SQL들의 모임이라고 볼 수 있다.
트랜잭션은 의미 상 하나의 단위로 취급되므로, 트랜잭션을 이루는 SQL을 모두 수행할 수 있는 것이 아니라면 하나도 처리되지 않도록 만들어 항상 데이터베이스의 일관성을 유지할 수 있어야 한다. 즉, 트랜잭션은 All or Nothing의 성격을 갖는다.
트랜잭션은 데이터의 쓰기 일관성을 보장한다. 여러 트랜잭션이 동시에 몰리더라도 그 결과는 기대값과 일치하도록 보장된다. 트랜잭션은 DBMS의 가장 중요한 기능 중 하나라고 볼 수 있다.
트랜잭션을 이용할 때는 평소처럼 SQL을 수행했다고 해서 데이터베이스에 즉각적으로 반영이 되지 않는다. 트랜잭션을 이루는 SQL의 반영 시점은 SQL의 수행 시점이 아니라 트랜잭션 단위가 완료되는 시점을 기준으로 한다. 트랜잭션에는 이와 관련된 Commit과 Rollback이라는 2개의 연산이 있다.
두 작업 모두 사용자가 직접 명시하면서 처리될 수 있다. Commit은 autocommit을 켜두면 시스템에 의해 자동적으로 발생할 수 있으며, Rollback 역시 트랜잭션의 일부 연산에서 오류가 발생했을 때 시스템에 의해 자동으로 발생할 수 있다.
그리고 동시성 제어라는 말은 트랜잭션들의 동시성을 보장하기 위해 연산들을 적절히 관리하는 것을 의미하는데, 트랜잭션들 간 공유되지 않는 데이터를 이용하는 연산들은 동시에 진행하고, 그렇지 않은 연산들은 LOCK과 같은 자원을 먼저 획득한 트랜잭션이 진행할 수 있도록 이뤄진다. 공유되고 있는 데이터들의 연산들을 격리 수준에 따른 방법으로 순차적인 진행을 하지 않도록 만든다면 더 높은 동시성을 제공할 수 있게 된다. (트랜잭션은 데이터의 쓰기 일관성은 보장되어야 하므로, 이 부분은 읽기 연산들에게 해당된다. 격리 수준 자체도 읽기 연산에만 해당된다는 점을 유의한다.)
읽기 연산에 대해 이와 같은 조정이 가능한 이유는 데이터의 직접적인 조작에 영향을 끼치진 연산이 쓰기 작업이기 때문이다. 즉, 동시성의 높낮이는 데이터의 읽기 일관성을 어느 수준으로 두는지에 따라 제어될 수 있다.
읽기 작업의 동시성을 높인다는 말은 곧 한 트랜잭션의 읽기 혹은 쓰기 연산 도중에 다른 트랜잭션의 읽기 연산이 개입할 수 있다는 말을 의미하므로, 일관성을 일부 희생하게 된다. 즉, 데이터의 읽기 일관성과 트랜잭션의 동시성은 서로 상충관계에 있다고 볼 수 있으며, 제공하려는 서비스가 어느 수준의 읽기 일관성을 유지해야 하는지에 따라 트랜잭션의 동시성을 더 높일 수 있는지 아닌지가 결정된다.
동시성 제어에는 2가지 방법이 있다. 첫 째는 비관적 동시성 제어 (Pessimistic Concurrency Control)이고, 둘 째는 낙관적 동시성 제어 (Optimistic Concurrency Control)이다.
후자는 사용자들이 같은 데이터를 동시에 수정하지 않을 것이라고 가정한 제어 방식이고, 이에 따라 데이터를 읽는 시점에 LOCK을 걸지는 않지만 값을 수정하려는 시점에 해당 값이 다른 사용자에 의해 변경되었는지 검사하게 된다.
어느 제어 방식을 목표로 하는지에 따라 격리 수준을 달리 정하여 운용할 수 있어야 한다. 단, 동시성을 제공하면서 제어를 하지 않는 경우에는 Lost Update, Dirty Read, Inconsistency, Cascading Rollback / Unrecoverability 등의 문제가 발생할 수 있다.
Read Phenomena를 어디까지 허용하는지에 따라 격리 수준이 4개로 나뉜다. Cubrid에서 지원하는 격리 수준은 총 6개이지만, 그 중에서 주로 사용되는 격리 수준은 4개이다. 나머지 2개는 다른 DBMS에서는 존재하지 않는 개념이다.
| 현상 | 허용 여부 |
|---|---|
| Dirty Read | O |
| Non-Repeatable Read | O |
| Phantom Read | O |
| 현상 | 허용 여부 |
|---|---|
| Dirty Read | X |
| Non-Repeatable Read | O |
| Phantom Read | O |
| 현상 | 허용 여부 |
|---|---|
| Dirty Read | X |
| Non-Repeatable Read | X |
| Phantom Read | O |
| 현상 | 허용 여부 |
|---|---|
| Dirty Read | X |
| Non-Repeatable Read | X |
| Phantom Read | X |
각 격리 수준에 주어진 괄호의 숫자는 Cubrid에서의 넘버링을 의미한다. 따라서 격리 수준을 설정할 때 위 넘버링을 이용한다. SQL을 이용하여 설정하려는 경우엔 아래와 같이 작성하면 된다. 아래 구문은 격리 수준을 4인 READ COMMITED로 설정한다.
SET TRANSACTION ISOLATION LEVEL 4
혹은 Cubrid 설정 자체를 바꾸고 싶다면, cubrid.conf의 isolation_level을 설정하면 된다. 다시 한 번 상기하자면, 격리 수준에서 설정되는 것은 트랜잭션이 운용하는 데이터의 읽기 일관성이다. 데이터의 쓰기 일관성과는 무관하다는 것에 유의해야 한다.
Cubrid에서는 격리 수준에 따른 동시성들을 지원하기 위해 다양한 LOCK을 제공한다. LOCK의 대상에는 테이블, 인덱스, 레코드 등이 있으며, 대상에 따라 LOCK의 종류가 달라진다.
S_LOCK은 트랜잭션이 레코드를 대상으로 읽기 연산을 수행하기 전에 획득하는 LOCK이다. 또한 S_LOCK은 특이하게도 동시 획득이 가능한 LOCK이다. 예를 들어 트랜잭션1이 먼저 S_LOCK을 획득하면 트랜잭션2 와 트랜잭션3도 S_LOCK을 획득하여 읽기 연산은 할 수 있지만, 어떠한 트랜잭션도 S_LOCK이 걸려있는 레코드에 대해 쓰기 연산은 수행할 수 없게 된다.
X_LOCK은 레코드를 대상으로 트랜잭션의 쓰기 연산 수행을 위해 획득하는 LOCK이며, 단 하나의 트랜잭션만 획득할 수 있다. 쓰기 작업이 완료되더라도 트랜잭션의 Commit 전까지는 X_LOCK을 해제하지 않는다.
Cubrid의 U_LOCK은 Update, Delete의 SQL에서 사용되며, 두 종류의 질의문에서 WHERE에 해당되는 레코드를 작업하는 동안 다른 트랜잭션에서 해당 레코드를 읽지 못하도록 만들기 위해 이용되는 LOCK이다. U_LOCK은 S_LOCK을 취득한 상태에서 얻을 수 있으며, U_LOCK으로 잠긴 레코드는 다른 트랜잭션에서 S_LOCK 취득이 불가능하다.
예를 들어 트랜잭션1이 U_LOCK을 취득햇다면, 후에 이어지는 트랜잭션2에서는 S_LOCK 취득이 불가능하다. 다만 반대로, 트랜잭션이1이 S_LOCK을 먼저 획득한 상태이고 그 이후에 트랜잭션이2가 U_LOCK을 얻는 것은 가능하다. 이 때 트랜잭션2가 쓰기 작업을 수행하기 위해선 추가적으로 X_LOCK을 얻어야 하는데 어차피 트랜잭션1의 S_LOCK이 해제되고 나서야 X_LOCK을 얻을 수 있으므로, U_LOCK 획득 이전에 선행적으로 취득된 S_LOCK에 대해선 크게 걱정할 필요가 없다. (S_LCOK과 X_LOCK은 공존할 수 없으므로)
스키마 잠금이란 테이블을 대상으로 하는 LOCK을 의미한다. 이는 레코드와 관련 있는 의도 잠금 (Intent Lock)과 DDL 조작 시 사용되는 스키마 잠금 (Schema Lock) 두 종류로 나뉘며, 편의 상 두 잠금 모두 테이블과 관련이 있기 때문에 스키마 잠금으로 분류하였다.
Cubrid에서는 DDL (CREATE, ALTER, DROP 등 ...) 작업에 대해서 스키마 잠금을 획득하도록 되어 있다. 스키마 잠금에는 크게 SCH_S (스키마 안정 장금), SCH_M (스키마 수정 잠금)으로 나뉜다.
Cubrid에서는 레코드에 대한 작업 수행 시, 레코드 보다 상위 계층인 테이블에 대해서도 잠금을 획득한다. 이를 통해 상위 계층에 대한 변경을 방지한다. 예를 들어, 특정 레코드에 X_LOCK을 획득하면 해당 레코드의 상위 계층의 테이블에서도 Intent Lock을 획득하여 테이블을 변경하지 못하도록 만든다. Intetn Lock에는 IS_LOCK (의도 공유 잠금), IX_LOCK (의도 배타 잠금), SIX_LOCK (공유 의도 배타 잠금)으로 총 3가지가 있다.
키 잠금에서의 키는 인덱스를 의미하며, 키 잠금 자체는 인덱스를 대상으로 하는 LOCK을 의미한다. 레코드와 비슷하게, 다음 키의 공유 잠금을 NS_LOCK, 다음 키의 배타 잠금을 NX_LOCK이라 한다.
NS_LOCK은 INSERT에 이용되고, INSERT 연산에서 자신과 다음 인덱스 값에 대해 NS_LOCK을 획득한다. INSERT 직후에는 다음 인덱스 값의 NS_LOCK은 해제되고, 자신에게 해당되는 NS_LOCK만 유지한다.
NX_LOCK은 UPDATE 및 DELETE에 이용되고, 해당 연산에서 자신과 다음 인덱스 값에 대해서 NX_LOCK을 획득한다. NS_LOCK과 달리 연산 직후에도 두 영역에 대한 NX_LOCK은 유지된다.
(1) CREATE TABLE tbl (id INT PRIMARY KEY);
(2) INSERT INTO tbl VALUES (1), (2), (3), (20);
(3.1) Tx1 --> DELETE FROM tbl WHERE id < 10;
(3.2) Tx2 --> INSERT INTO tbl VALUES (8);
(3.3) Tx2 --> Commit;
(3.4) Tx1 --> SELECT * FROM tbl WHERE id < 10;
Without Key Lock : 별도의 잠금이 없기 때문에 3.2에서의 INSERT와 3.3의 Commit으로 레코드가 추가되고, 3.4에서 레코드를 조회 했을 때는 분명 10 이하의 값을 모두 지웠지만 새로운 레코드가 나타난다.
With Key : 3.1에서 10의 다음 인덱스인 20에 대해서 NX_LOCK이 걸린다. 그리고 3.2에서 8 다음의 인덱스인 20에 대해서 NS_LOCK을 잡으려 한다. 20이라는 데이터는 이미 락이 걸린 상태이므로 트랜잭션2는 NS_LOCK을 얻기 위해 대기하고, 만일 트랜잭션1의 Commit으로 NX_LOCK이 풀리게 되면 트랜잭션2는 NS_LOCK을 취득하게 된다. 따라서 3.3의 수행이 3.4보다 늦게 이뤄지게 되므로 기존처럼 3.4에서 Phantom Read가 나타나진 않는다.
잠금들의 호환 관계는 다음과 같다. 호환성이란 Lock Holder가 특정 객체에 대해 획득한 LOCK과 Locker Request가 특정 객체에 대해 요구하는 LOCK을 중복하여 얻을 수 있다는 것이다.
cubrid lockdb $DB-NAME을 이용하면 (1) 시스템 상의 LOCK 관련 설정 값, (2) 데이터베이스 서버에 접속 중인 클라이언트 정보, (3) LOCK 소유 상태 등을 볼 수 있다. 해당 명령어를 활용하는 예시가 167p - 171p까지 이어져 있으니, 각 LOCK의 의미가 파악되었다면 직접 트래킹 해보는 것을 권장한다.
-
Lock Escalation이란 레코드 수준 잠금을 테이블 수준 잠금으로 대체하는 것을 말한다. 레코드 수준의 잠금은 키 잠금을 포함하는데, 해당 값이 Lock Escalation에 설정된 값을 초과하게 되면 테이블 수준 잠금으로 바뀐다. 이는 레코드 수준의 잠금이 많아지면 이를 관리하는데 부담이 커지고 성능을 저하시킬 수 있기 때문이다. 한 트랜잭션만 테이블을 이용할 수 있게 만들어서 잠금 관리의 부담을 줄이는 것이다. 이 때, 테이블 당 수행되는 트랜잭션은 하나가 되므로 동시성은 Lock Escalation이 설정되기 전보다 떨어지게 된다.
cubrid tranlist를 이용하여 파악된 잠금을 보유하고 있는 트랜잭션을 cubrid killtran을 이용하여 중지시킬 수 있다. 이를 통해 트랜잭션이 보유하고 있는 잠금을 해제시킬 수 있다. -i 옵션의 인자로 트랜잭션의 아이디를 이용하면 된다.
cubrid tranlist를 통해 파악된 잠금 대기 유발 트랜잭션이 여럿 있다면, -i 옵션 뒤에 여러 개의 트랜잭션 아이디를 기재하는 것도 가능하다. Query 수행에 너무 오래 걸리는 트랜잭션들도 cubrid tranlist로 파악할 수 있으니 이들도 중지 시킬 수 있다.
데이터베이스에서의 Deadlock은 둘 이상의 트랜잭션이 서로 자신이 보유하고 있는 LOCK을 놓지 않으면서, 상대방의 LOCK을 원하는 구조가 순환적으로 이뤄질 때 발생한다. Deadlock은 다음과 같은 상태를 오래 유지하는 경우 발생할 가능성이 높아진다.
주어진 2개의 예시가 있는데, 이들의 순환 구조를 그림을 통해 이해할 수 있어야 한다. 실습을 통해 $CUBRID/log/server에 위치한 파일로 Deadlock이 발생했는지 확인할 수 있어야 한다.
(2) 주어진 예시를 살펴보면 데이터베이스 운용 시 Deadlock은 불가필 수 밖에 없다는 것을 알 수 있다. 이 때는 가장 최근에 실행된 트랜잭션을 롤백하고 (가장 적은 구문을 실행했을 가능성이 높으므로) 에러를 반환한다. 에러를 반환한 트랜잭션은 일정 시간 뒤에 (필요한 LOCK을 보유한 트랜잭션이 LOCK을 해제하는 시간을 감안하여) 재실행된다.
Cubrid 10.0 이전에는 위에서 소개된 LOCK을 이용하는 2 Phase Locking 기법으로 트랜잭션의 동시성을 제어하였다. Cubrid 10.0부터는 2 Phase Locking 대신 MVCC (Multi Version Concurrency Control)을 이용하여 트랜잭션의 동시성을 제어한다.
2PL에서는 LOCK을 선점한 트랜잭션이 끝나기 전까지 다른 트랜잭션이 접근하는데 제약이 있었던 반면에, MVCC는 이런 문제들을 거의 겪지 않는다. MVCC는 데이터에 쓰기 연산이 이뤄질 때, 데이터에 대한 여러 버전을 생성한다. 예를 들어, A가 D0에 쓰기 작업을 수행한다고 해보자. MVCC는 A의 쓰기 연산에 대해 D1을 만들어 내고, A의 쓰기 도중에 B가 D를 읽으려 하면 D0를 읽을 수 있게 해준다. 이처럼 MVCC는 트랜잭션에서 쓰기 중인 객체에 액세스하는 것이 허용된다. MVCC는 다른 동시성 제어보다 더 적은 비용으로 진정한 Snapshot Isolation을 구현할 수 있다.
2PL에서 MVCC로 넘어오면서 얻을 수 있는 장점은 LOCK을 이용한 DBMS보다 빠른 처리를 기대할 수 있다는 것이다. 하지만 데이터 버전이 충돌할 가능성이 있으며, 특히 데이터 버전과 같은 불필요한 데이터들이 많이 쌓일 수 있기 때문에 주기적으로 정리해주도록 별도의 시스템이 필요하다.
MVCC에서 불필요한 데이터를 관리하기 위한 기법은 Vacuum과 Undo Segment 총 2가지 방식이 있고, Cubrid 10.0 이후의 MVCC에서는 Vacuum을 이용한다.
- Vacuum : 데이터의 버전 내용들을 별도로 저장하고, 일정 시점마다 삭제하는 기법 (Postgre SQL, CUBRID 등)
- Undo Segment : 데이터의 최신 버전을 유지하되, 그 이전 버전들은 Undo Table에 보관하여 필요에 따라 필요에 따라 읽어오는 기법 (Oracle, InnoDB 등)
1. S_LOCK과 X_LOCK은 다른 디비에도 많은 것으로 확인했는데, 그렇다면 S_LOCK과 X_LOCK은 DB에선 일종의 공통된 LOCK이고, U_LOCK은 Cubrid만의 LOCK인가?
Answer
- S/X lock 은 DB 전반적인 기본 lock 입니다.
- U_LOCK 은 다른 제품에서 사용될 수 있다.
- U_LOCK 은 MVCC 도입하면서 사용하지 않는다. (8 번 질문과 연관성이 있습니다.)
Answer
- “S_LOCK 을 먼저 가져야”할 필요가 없습니다.
- S_LOCK 을 얻은 상태에서 다른 트랜잭션이 U_LOCK 획득 가능
- 그 반대는 불가능. 이유는 deadlock 에서 찾을 수 있습니다.
Answer
계층적으로 더 낮은 모든 객체에 설정된 공유 잠금을 보호하고, 계층적으로 더 낮은 일부 객체에 대한 의도 배타 잠금을 보호하기 위하여 상위 객체에 내재적으로 설정되는 잠금입니다.
4. 키 잠금의 예시를 보면 인덱스가 적용된 데이터에 대해서 키 잠금이 이뤄지는 것처럼 나타나 있다. 인덱스 역시 일반 데이터들처럼 별도의 볼륨으로 유지되고 있는 것으로 알고 있는데, 키 잠금은 인덱스 볼륨을 대상으로 하는지 혹은 일반 볼륨을 대상으로 이뤄지는지 궁금함
Answer
- 볼륨과는 상관 없습니다.
- 물리적인 개념이 아니라 논리적인 개념으로 키 잠금은 진행됩니다.
Answer
떨어지지 않는다. 동시성보다 lock 여러개 잡는게 리소스가 더 큽니다.
Answer
- 상위 잠금으로 변환 할수 없어서 차단된 트랜잭션 수입니다.
- ex) SCH_S_LOCK 에서 SCH_M_LOCK 로 변환이 안되는 경우
Answer
- https://www.cubrid.org/manual/ko/11.0/admin/troubleshoot.html?highlight=%EC %8A%AC%EB%A1%9C%EC%9A%B0%20%EC%BF%BC%EB%A6%AC#id3
- 해당 메뉴얼에서 슬로우 쿼리가 발생하는 경우가 있습니다.
8. MVCC를 도입하더라도 여전히 트랜잭션에 대한 LOCK은 필요하므로 X_LOCK이 이용되는 것까지는 이해되는데, S_LOCK은 거의 이용이 되지 않을 것 같다는 생각이 듬. (1) S_LOCK이 이용되는 곳이 있는지, 그리고 (2) U_LOCK은 여전히 이용이 되는지 궁금함, 그리고 (3) 스키마 잠금과 키 잠금 등도 여전히 이용되는지 궁금하고 만일 이용된다면 LOCK들이 버전별로 적용이 되는 것인지 궁금함
Answer
(1) MVCC 에서 S_LOCK 을 일부에서 사용합니다. 카탈로그정보 등에서는 여전히 사용
(2)U_LOCK 또한 사용되고 있지 않습니다. S와 WLock의 경합이 없어서 안쓰는 것으로 생각합니다.
(3)스키마 잠금은 사용, 키 잠금은 없다. 키 잠금은 다른 버전의 레코드를 확인하지 못하는 방식으로 제한하기 때문에 사용하지 않습니다.
Answer
- Vacuum 은 MVCC 이전의 데이터를 제거하는 역할을 하고, CUBRID 에서는 Undo Segement 를 사용하지 않습니다.
- 서로 비교 불가능한 주제여서 답변을 드리기 어렵습니다.
Answer
- 해당 데이터의 이전 버전 데이터를 볼 수 있는 active 트랜잭션이 모두 commit 되면 지울 수 있다.
11. Cursors Holdability : Commit 이후에 SELECT 질의 결과의 레코드 셋을 유지하여 다음 레코드를 읽을(Fetch) 수 있도록 하는 것이라고 찾을 수 있었고, 여기에 덧붙여, 연결 수준 또는 문장 수준으로 커서 유지 기능을 설정할 수 있다라고 나와있는데 커서가 필요한 이유에 대해서 궁금함
Answer
- 커서는 select 결과 값들을 저장한 메모리 값을 의미합니다.
- 이런 커서들을 유지함으로서, 매번 select 구문을 실행시키기 위한 리소스를 사용하지 않고 커서이동을 통해서 일련의 데이터를 쉽게 읽을 수 있습니다.
13. REPEATABLE READ와 PHANTOM READ의 INSERT 연산에서 LOCK 획득이 어떻게 되는지 궁금함 (원래 의도는 PHANTOM READ가 아닌 PHANTOM READ가 발생하지 않는 SERIALIZABLE과의 비교를 원했는데, 질문을 급하게 잘못 작성하여 제대로된 답변을 얻지 못했음)
Answer
- (“REPEATABLE READ 의 격리 수준에서 INSERT 연산을 수행한다. 그런데 PHANTOM READ 가 발생한 경우 LOCK 획득 상태는 어떤가?” 의 의도로 질문 한것으로 해석했습니다.)
- REPEATABLE READ : 격리수준, PHANTOM READ : 현상
- REPEATABLE READ 수준으로 격리된 트랜잭션은 row 에 대해서 S-LOCK 을 가지고 있습니다.
- 하지만, 새롭게 추가되는 key 에 대해서 lock 이 잠겨있지 않기 때문에 PHANTOM READ 가 발생하는 것이고, 즉 해당 INSERT row 에는 LOCK 이 잠겨 있지 않습니다.