Lock with Transaction Isolation Level

트랜잭션이란?

트랜잭션이란, DB의 데이터를 조작하는 작업의 단위이다.

트랜잭션은 이론적으로 ACID 원칙을 보장해야한다고 한다. 하지만 이는 실제로 종종 지켜지지 않는다. 그 이유는 ACID 원칙을 strice 하게 지키려면 동시성이 매우 떨어지기 때문이다.

그렇기 때문에 DB 엔진은 ACID 원칙을 희생하여 동시성을 얻을 수 있는 방법들을 제공하는데, 바로 트랜잭션의 isolation level이다.

isolation 원칙을 덜 지키는 level을 사용할수록 정합성 문제가 발생할 가능성은 커지지만 더 높은 동시성을 얻을 수 있다.

• Read Uncommitted, Read Committted, Repeatable Read, Serializable

DB 엔진은 isolation level에 따라 다른 locking 전략을 취한다.

isolation level이 높아질수록 더 많고 strick하게 lock을 건다.

InnoDB의 Lock

InnoDB는 트랜잭션의 ACID 원칙과 동시성을 최대한 보장하기 위해서 여러 종류의 lock을 사용한다.

• SELECT @@GLOBAL(or SESSION).transaction_isolation 쿼리로 확인 가능

• InnoDB의 경우 기본적으로 REPEATABLE_READ

row-level lock

기본적인 lock은 테이블의 row마다 걸리는 row lock이다. 여기에는 shared-lock과 exclusive-lock이 있다.

Shared-lock & Exclusive lock

ared-lock & Exclusive lock

Shared-lock(S lock)은 read에 대한 lock이다.

일반적인 select 쿼리는 lock을 사용하지 않고 DB의 레코드를 읽는다. 하지만 select … for share 등 일부 select 쿼리는 read 작업을 수행할 때 InnoDB가 각 row에 S-lock을 건다.

Exclusive-lock(X-lock)은 write에 대한 lock이다.

select … for update나 update, delete 등의 수정 쿼리를 질의할 때 각 row에 설정하는 lock이다.

둘의 규칙은 다음과 같다.

1. 여러 트랜잭션이 동시에 하나의 row에 S-lock을 걸 수 있다. 즉, 여러 트랜잭션이 동시에 하나의 row를 읽을 수 있다.

2. S-lock이 걸린 row에 다른 트랜잭션이 X-lock을 걸 수 없다. 즉, 다른 트랜잭션이 읽고 있는 row를 수정하거나 삭제할 수 없다.

3. X-lock이 걸려있는 row에 다른 트랜잭션이 S-lock과 X-lock 둘다 걸 수 없다. 즉, 다른 트랜잭션이 조작하고 있는 row는 읽기, 수정, 삭제가 전부 불가능하다.

따라서, S lock을 사용하는 쿼리끼리는 같은 row에 접근이 가능하지만 X-lock이 걸린 row는 다른 트랜잭션의 어떠한 쿼리도 접근이 불가능하다.

• shared와 exclusive 이름의 의미와 정확히 일치한다.

• exclusive lock(X-lock)이 걸리면 해당 레코드에 대해서 읽기, 쓰기 전부 불가능

Record lock

Record lock은 row가 아니라 DB index record에 걸리는 lock이다.

여기도 row-level lock과 마찬가지로 S, X-lock이 존재한다.

예를들어 c1이라는 컬럼을 가진 테이블 t1이 있다. 여기서 하나의 트랜잭션에서 다음과 같은 쿼리를 실행한다.

1. transaction A start();
2. select c1 from t1 where c1 = 10 for update;

그러면 t1.c1의 값이 10인 index에서 X lock이 걸린다.

이때 다른 트랜잭션에서 다음 쿼리를 실행한다면,

1. transaction B start();
2. delete from t1 where c1 = 10;

트랜잭션 B의 쿼리는 t1.c1 = 10인 index record에 X-lock을 걸려고 시도한다. 하지만 해당 index record에는 이미 트랜잭션 A가 X-lock을 건 상태이다. 따라서 트랜잭션 B가 실행한 쿼리는 트랜잭션 A가 commit 또는 rollback이 되기 전까지 t1.c1 = 10 인 row를 삭제할 수 없다.

delete 쿼리 뿐만 아니라, insert, update 쿼리도 동일하다.

정리:

1. select … for update

   • 지정된 레코드에 대해 베타적 잠금(exclusive lock)을 설정

   • 다른 트랜잭션은 해당 레코드는 읽을 수 있지만, 수정은 불가능

   • 잠금은 트랜잭션이 commit, rollback 될 때까지 유지

2. record lock의 다른 예시

   • update, delete, insert 해당 레코드를 수정하려고 할 때 잠금이 생성

• mysql의 isolation level이 read committed로 설정되어 있다면, inset, delete, update 쿼리에는 자동으로 exclusive lock이 설정된다.

Gap lock

Gap lock은 DB index record의 gap에 걸리는 lock이다.

여기서 gap은 index 중, DB에 실제 record가 없는 부분이다.

만약, id 컬럼만 존재하는 테이블이 있고, id 컬럼에 index가 걸려있다고 하면, 현재 테이블에는 id = 3인 row와 id = 7인 row가 있다.

그러면 DB와 index table은 다음과 같은 상태이다.

    Index table               Database
-------------------          ---------
| id  | row addr  |          |  id   |
-------------------          ---------
|  3  | addr to 3 |--------->|   3   |
|  7  | addr to 7 |--------->|   7   |
-------------------          ---------

이 경우 현재 id ≤ 2, 4 ≤ id ≤ 6, 8 ≤ id에 해당하는 부분에는 index record가 없다. 이 부분이 바로 index record의 gap이다.

gap lock은 이러한 gap에 걸리는 lock이다. 즉, gap lock은 해당 gap에 접근하려는 다른 쿼리의 접근을 막는다.

record lock이 해당 index를 타려는 다른 쿼리의 접근을 막는것과 동일하다. 둘의 차이라면 record lock은 이미 존재하는 row가 변경되지 않도록 보호하는 것이고, gap lock은 조건에 해당하는 새로운 row가 추가되는 것을 방지하기 위함이다.

예를들어 c1이라는 컬럼이 존재하는 테이블 t1이 있다. 여기에는 c1 = 13, c1 = 17이라는 두 row가 존재한다.

1. Trasnaction A start();
2. select c1 from t1 where c1 between 10 and 20 for update;

위 쿼리를 실행했다면, t1.c1의 값이 10과 20 사이인 gap에 lock이 걸린다. 즉, 10 ≤ id ≤ 12, 14 ≤ di ≤ 16, 18 ≤ id ≤ 20에 해당하는 gap에 lock이 걸린다.

이 상태에서 다른 트랜잭션이 t1.c1 = 15인 row를 삽입하려고 하면 gap lock 때문에 트랜잭션 A가 commit 되거나 rollback 되기 전까지 삽입되지 않는다. insert와 update, delete 쿼리에서도 동일하다.

Gap은 하나의 index 값일 수도, 여러 index의 값일 수도, 아예 아무 값도 없을 수도 있다.

Lock이 해제되는 타이밍

트랜잭션이 진행되는 동안 InnoDB 엔진은 위처럼 실행되는 쿼리에 맞는 수많은 lock을 DB에 걸게 된다.

이러한 lock은 모두 트랜잭션이 커밋되거나 롤백될 때 함께 unlock된다.

Consistent Read

Consistent read란 read operation을 수행할 때, 현재 DB의 값이 아닌 특정 시점의 DB Snapshot을 읽어오는 것이다.

• 이 스냅샷은 commit된 변화만이 적용된 상태를 의미한다.

Consistent read는 어떤 방법을 통해 이루어질까?

가장 단순한 방법은 읽어온 row에 lock을 걸어 다른 트랜잭션이 할 수 없도록 하는 방법일 것이다. 하지만 InnoDB 엔진은 consistent read를 하기 위해 lock을 사용하지 않는다. 왜냐하면 동시성이 매우 떨어지기 때문이다.

InnoDB 엔진은 실행했던 쿼리의 log를 통해 consistent read를 지원한다. InnoDB 엔진은 각 쿼리를 실행할 때마다 실행한 쿼리의 log를 저장한다. 그리고 나중에 consistent read를 할 때, 이 log를 통해 특정 시점의 DB Shapshot을 복구해서 가져온다.

이 방식은 비록 복구 비용이 발생하기는 하지만 lock을 사용하는 방식보다 높은 동시성을 얻을 수 있다.

Isolation Level

Reapeatable Read

repeatable read는 반복해서 read operation을 수행하더라도 읽어 들이는 값이 변화하지 않는 정도의 isolation을 보장하는 level이다.

repeatable read 트랜잭션은 처음으로 read(select) operation을 수행한 시간을 기록한다. 그리고 이후에 모든 read operation마다 해당 시점을 기준으로 consistent read를 수행한다. 그러므로 다른 트랜잭션 도중 다른 트랜잭션이 commit이 되더라도 새로이 commit된 데이터는 보이지 않는다. 첫 read 시의 스냅샷을 보기 때문이다.

일반적인 non-locking select 외에 lock을 사용하는 select, update, delete 쿼리를 실행할 때, repeatable read 트랜잭션은 gap lock을 사용한다. 즉, 내가 조작하려는 row의 후보군을 다른 트랜잭션이 건들지 못하도록 한다.

여기에 대해서는 Repeatable read vs read commited를 비교헤봐야 한다.

• Gap lock 뿐만 아니라 next-key lock 역시 활용한다. 또한 unique index와 unique search condition을 가진 쿼리에 대해서는 gap lock을 걸지 않는다. 다른 말로 표현하자면, where 조건대로 index를 탔을 때 반드시 row가 하나 이하만 걸릴 수 있는 쿼리에 대해서는 gap lock을 걸지 않는다. 어차피 row의 후보군이 하나 이하이기 때문이다.

정리:

• 트랜잭션 내 첫 읽기 작업 시점에서 스냅샷을 사용하기 때문에 phantom read가 발생하지 않음

• Gap lock 사용 (단, unique index와 unique search condition에는 미사용)

Read Committed

Read committed는 commit된 데이터만 보이는 수준의 isolation을 보장하는 level이다.

Repeatable read의 트랜잭션이 첫 read operation을 기준으로 consistent를 수행하는 반면, Read committed의 트랜잭션은 read operation 마다 DB Snapshot을 다시 뜬다.

그렇기 때문에 다른 트랜잭션이 commit을 수행한 다음에 다시 read operation을 수행하면, Repeatable read와 다르게 read committed의 트랜잭션은 해당 변화를 볼 수 있다.

• 이 말은 select 쿼리를 실행할 때, 중간에 다른 트랜잭션이 commit 했던 데이터를 읽어올 수 있다는 것이다.

• 단점으로는 트랜잭션 내에서 데이터 일관성이 완벽하게 보장되지 않을 수 있다는 것이다.

“commit 된 데이터만 보는 건 당연한거 아닌가?” 혹은 “select 쿼리마다 스냅샷을 다시 뜨면 다음 read에서 복구가 필요 없는데 스냅샷을 왜 뜨는거야?” 라고 할 수 있다.

실제 DB에는 아직 commit 되지 않은 쿼리도 적용된 상태이다. 따라서 commit 된 데이터만 읽어오기 위해서는 아직 commit 되지 않은 쿼리를 복구하는 과정이 필요하다.

즉, consistent read를 수행해야 한다.

일반적인 non-locking select 외에 lock을 사용하는 select, update, delete 쿼리를 실행할 때, read committed의 트랜잭션은 record lock만 사용하고 gap lock은 사용하지 않는다.

따라서 phantom read가 발생할 수 있다.

정리:

• Read Committed 트랜잭션에서는 각 읽기 작업마다 새로운 스냅샷을 사용하기 때문에 동일한 트랜잭션 내에서 여러 읽기가 있는경우, 각 읽기의 결과가 달라질 수 있다. (팬텀 리드)

• Gap lock 미사용, record lock만 사용

Repeatable Read vs Read Committed

아래는 Phantom read가 발생하는 상황이다.

c1 컬럼이 존재하는 테이블 t1이 있다. 현재 t1에는 t1.c1 = 13인 row와 t1.c1 = 17인 row가 존재한다. 여기서 Read Committed인 트랜잭션 A와 B가 다음과 같은 쿼리를 실행한다.

1. Transaction A start(); // read committed
2. select c1 from t1 where c1 between 10 and 20 for update;
3. select c1 from t1 where c1 between 10 and 20 for update;
4. commit;

1. Transaction B start(); // read committed
2. insert into t1 values(15);
3. commit;

--------------------------------------------------------------------
1. (A-1) select c1 from t1 where c1 between 10 and 20 for update;
2. (B-1) inset into t1 values(15);
3. (B-2) commit;
4. (A-2) select c1 from t1 where c1 = between 10 and 20 for update;
5. (A-3) commit;

두 트랜잭션은 위와 같은 순서로 실행된다.

(A-1) 쿼리가 실행된 경우, 결과는 당연히 t1.c1 = 13인 row와 t1.c1 = 17인 row 2개이다.

그렇다면 lock은 어떻게 걸려있을까?

Read committed 트랜잭션은 record lock만 걸고 gap lock은 사용하지 않는다. 따라서 (A-1)번 쿼리가 실행된 직후 걸려있는 lock은 t1.c1 = 13과 t1.c1 = 17 에 대한 record lock이다.

• for update는 선택된 행에 대하여 베타적 잠금(exclusive lock)을 걸겠다는 의미이다.

이 때 트랜잭션 B가 t1.c1 = 15인 row를 삽입하려고 한다. (B-1)

트랜잭션 A는 gap lock을 걸지 않았기 때문에 트랜잭션 B는 자유롭게 t1.c1 = 15인 row를 삽입할 수 있다. 이 상태에서 트랜잭션 B는 commit을 진행한다. (B-2)

다시 트랜잭션 A가 (A-2) 쿼리를 실행한다. 그러면 트랜잭션 A의 Isolation level은 Read committed 이기 때문에 새롭게 shapshot을 갱신해온다. 여기서 트랜잭션 B가 삽입한 t1.c1 = 15인 row를 읽어 들인다. 이것이 바로 phantom row이다.

• 이 상황이 phantom read인 이유는, 트랜잭션 A(같은 트랜잭션) 내에서 동일한 쿼리를 두 번 실행했을 때, (A-1), (A-2)의 실행 결과가 다르기 떄문이다. 즉, 두 번째 실행 결과에서 처음에 없던 레코드가 나타났기 때문이다.

만약 트랜잭션 A의 Isolation level이 Repeatable read라고 하면, (B-1) 쿼리가 실행될 때, t1.c1 = 15인 gap에 gap lock이 걸려있을 것이다.

따라서 트랜잭션 B는 트랜잭션 A가 commit 되어 lock을 해제할 때까지 기다리고, phantom read는 일어나지 않는다. 즉, 업데이트 하려는 10 ≤ t1.c1 ≤ 20에 해당하는 row의 후보군이 변화를 일으키지 않는다.

Read Uncommited

Read Uncommitted 트랜잭션은 기본적으로 Read committed 트랜잭션과 동일하다.

대신, select 쿼리를 실행할 때 아직 commit 되지 않은 데이터를 읽어올 수 있다.

1. 트랜잭션 A에서 row를 삽입

2. read uncommitted 트랜잭션 B가 해당 row를 읽는다.

3. 트랜잭션 A가 rollback 된다.

이 경우, 트랜잭션 B는 실제로 DB에 한 번도 commit 되지 않은, 존재하지 않는 데이터를 읽어 들였다. 이러한 현상을 Dirty Read라고 한다.

이것이 가능한 이유는 InnoDB 엔진이 트랜잭션을 commit 하는 방법 때문이다.

InnoDB 엔진은 일단 실행된 모든 쿼리를 DB에 적용한다. 그것이 아직 commit 되지 않은 트랜잭션이어도 적용한다. 즉, 특별히 log를 보고 특정 시점의 snapshot을 복구하는 consistent read를 하지 않고 그냥 해당 시점의 DB를 읽으면 dirty read가 된다.

Serializable

Serializable 트랜잭션은 기본적으로 repeatable read와 동일하다. 대신에 select 쿼리가 전부 select … for share로 자동으로 변경된다.

이는 repeatable read에서 막을 수 없는 몇 가지 상황을 방지할 수 있다.

1. (A-1) select state from account where id = 1;
2. (B-1) select state from account where id = 1;
3. (B-2) update account set state = 'rich', money = money * 1000 where id = 1;
4. (B-3) commit;
5. (A-2) update account set state = 'rich', money = money * 1000 where id = 1;
6. (A-3) commit;

먼저, (A-1)번 select 쿼리가 select … for share로 바뀌면서 id = 1 인 row에 S-lock이 걸린다. 그리고 (B-1)번 select 쿼리 역시 id = 1 인 row에 S-lock을 건다. 이 상황에서 트랜잭션 A와 B가 각각 2번의 update 쿼리를 실행하려고 하면, row에 X-lock을 걸려고 시도할 것이다. 하지만 이미 해당 row에는 S-lock이 걸려있기 때문에 deadlock 상황에 빠지고, 두 트랜잭션 모두 timeout으로 실패할 것이다. 따라서 money는 1로 안전하게 남아있다.

이 경우로 봤을 때, Serializable isolation level은 데이터를 안전하게 보호할 수 있지만, 굉장히 쉽게 deadlock에 걸릴 수 있다. 따라서 해당 격리 수준은 데드락이 걸리지 않는지 신중하게 파악하고 사용해야 한다.

Serializable이 아니면 update, delete에 주의해라.

하나 주의해야 할 부분은 DML, 즉 update나 delete는 consistent read의 적용을 받지 않는다.

즉, 같은 where 조건문을 사용하더라도, 수정하려고 select 쿼리로 읽어온 row와 해당 row들을 수정하기 위해 update 쿼리를 날렸을 때 실제로 수정되는 row가 다를 수 있다.

다음과 같이 2개의 repeatable read 트랜잭션이 실행된다고 하자.

1. Transaction A start(); // read committed
2. select count(c1) from t1 where c1 = 'xyz';
3. delete from t1 where c1 = 'xyz';
4. commit;

1. Transaction B start(); // read committed
2. insert into t1(c1, c2) values ('xyz', 1), ('xyz', 2), ('xyz', 3);
3. commit;

----------------------------------------------------------------------
1. (A-1) select count(c1) from t1 where c1 = 'xyz'
2. (B-1) insert into t1(c1, c2) values ('xyz', 1), ('xyz', 2), ('xyz', 3);
3. (B-2) commit;
4. (A-2) delete from t1 where c1 = 'xyz' // 3 rows deleted
5. (A-3) commit;

두 트랜잭션이 위와 같은 순서로 실행될 때, 처음에 테이블 t1이 비어있다면 (A-1)번 쿼리의 실행 결과는 0이다. 이 때 실행된 쿼리는 non-locking select 쿼리이므로 lock은 걸려있지 않다.

덕분에 트랜잭션 B는 자유롭게 t1.c1 = ‘xyz’인 row를 삽입할 수 있다. 따라서 (A-1)번 쿼리에서 t1.c1 = ‘xyz’인 row가 0개 였고, 같은 where 조건으로 delete 쿼리를 실행했음에도 (A-2)번 쿼리는 3개의 row를 삭제한다.

만약 위 상황처럼 consistent read에는 보이지 않는 row에 update와 delete 쿼리로 영향을 준 경우, 그 시점 이후로는 해당 row가 트랜잭션에서 보이기 시작한다.

1. Transaction A start(); // repeatable read
2. (A-1) select count(c1) from t1 where c1 = 'abc'; // 0
3. Transaction B start();
4. (B-1) insert into t1(c1, c2) values ('abc', 1), ('abc', 2), ('abc', 3);
5. (B-2) commit;
6. (A-2) update t1 set c1 = 'cba' where c1 = 'abc'; // 3 rows updated
7. (A-3) select count(C1) from t1 where c1 = 'cba'; // 3

트랜잭션 B가 c1 = ‘abc’ 인 row를 몇 개 삽입한다. 그러면 아까 전 예시와 동일하게 (A-2)번 쿼리는 (A-1) 쿼리에서 보이지 않았던 row를 수정할 것이다. 그러면 이 순간부터 트랜잭션 A에서는 이 row들이 보이기 시작한다. 따라서 (A-3)의 쿼리 결과는 3이 된다.

만약 두 예시에서 transaction isolation level이 serializable 이었다면 어떨까?

앞에 모든 select 쿼리는 select … for share로 자동으로 변경된다. 따라서 두 예시 모두에서 (A-1)번 쿼리는 record S-lock을 걸었을 것이다. 그러면 트랜잭션 B에서 update 쿼리를 실행할 때 X-lock을 걸려고 할 때, 이미 해당 record에는 S-lock이 걸려있으므로 수정되지 않고 대기 상태로 빠진다.*

따라서 트랜잭션 A의 (A-2)번 쿼리는 안전하게 (A-1)번 쿼리에서 본 row만 수정한다.

* 부분은 t1.c1 컬럼에 index가 걸려있는 상황을 가정한 상황이다. 하지만 index가 걸려있지 않은 컬럼에 대해서 테스트 했을 때에도 트랜잭션 B가 대기 상태로 빠졌다. 이때 걸려있는 lock을 확인해보면 트랜잭션 A가 pk 때문에 생성된 index에 lock을 건 것이다. 추측컨대, where 조건에 index가 없는 컬럼만 포함될 때 locking select를 할 경우 해당 row의 pk index에 lock을 거는 것으로 보인다.