Transaction & InnoDB Lock 개념/예제

현업 개발에서 JPA또는 Query를 짜면서 DB작업을 해오면서 최근에 DB에 대한 지식이 부족한 것 같아 이번에 트랜잭션과 Lock 관련된 내용과 예제를 정리해놓으려한다.

공부하면서 정리하다보니 잘못된 정보가 있을 수 있다. 만약 잘못된 내용이 있다면 댓글로 알려주시길 바랍니다..

---

Transaction이란?

DB의 상태를 변환시키는 하나의 논리적 기능을 수행하기 위한 작업의 단위 또는 한꺼번에 수행되어야할 일련의 연산

다시 말하면 여러 SELECT, UPDATE, DELETE SQL 구문이 모두 처리되어야하는 하나의 프로세스가 있다면 이 프로세스가 완전히 성공하거나 중간에 실패하면 프로세스를 실행하기 전 상태로 롤백하는 기능이다.

---

Transaction 특징(ACID)

Atomicity(원자성)

• 트랜잭션이 DB에 모두 반영 또는 전혀 반영되지 않아야함
• 트랜잭션 내 모든 명령은 완벽히 수행돼야하며 하나라도 오류가 발생하면 전부 취소 돼야함

Consistency(일관성)

• 트랜잭션의 작업 처리 결과가 항상 일관성이 있어야함

Isloation(독립성)

• 둘 이상의 트랜잭션이 동시에 실행되고 있을 경우 어떤 하나의 트랜잭션이라도 다른 트랜잭션 연산에 끼어들 수 없음
• 수행 중인 트랜잭션은 완전히 완료될 때까지 다른 트랜잭션에서 수행 결과를 참조할 수 없음

Durability(지속성)

• 트랜잭션이 성공적으로 완료됐을 경우, 결과는 영구적으로 반영됨

---

Lock

DB Isolation level을 설명하기 전에 InnoDB에서 사용 하는 Lock의 종류를 먼저 설명하고자 한다.

Lock이란 ACID 원칙과 동시성을 최대한 보장하기 위한 방법이다.

Lock 종류는 다음과 같다

• Shared and Exclusive Locks
• Intention Locks
• Record Locks
• Gap Locks
• Next-Key Locks
• AUTO-INC Locks

 

1. Shared and Exclusive Locks (Row-Level Lock)

---

테이블 행(Row)마다 걸리는 Lock을 Row-Level Lock(행 기반)이라 하고,

Shared Lock(S-Lock), Exclusive Lock(X-Lock) 두 종류가 있다.

 

S-Lock은 행을 읽기(read) 위해 획득해야하는 Lock이다.

일반적인 SELECT 구문은 Lock을 획득하지 않아도 읽기가 가능하다.

하지만 SELECT FOR SHARED등과 같은 구문은 각 Row에 S-Lock을 건다.

 

X-Lock 은 쓰기(Write)에 대한 Lock이다. 즉, DELETE, UPDATE 등을 수행할 때 걸린다.

또한 SELECT FOR UPDATE 또한 X-Lock을 건다.

 

S-Lock 과 X-Lock은 다음과 같은 특징을 가진다.

• 특정 Row에 S-Lock을 걸 경우 다른 트랜잭션도 해당 Row에 S-Lock을 걸 수 있다. 즉 여러 트랜잭션에서 SELECT FOR SHARED구문을 사용해 같은 행을 동시에 읽을 수 있다.
• 특정 Row 에 S-Lock을 걸 경우 다른 트랜잭션이 해당 Row에 X-Lock을 걸 수 없다. 즉 S-Lock이 걸린 행에는 UPDATE, DELETE을 수행할 수 없다.
• 특정 Row에 X-Lock을 걸 경우 다른 트랜잭션은 S-Lock, X-Lock 모두 걸 수 없다. 즉 수정, 삭제되는 Row는 다른 트랜잭션에서 SELECT FOR SHARE UPDATE, DLETE 모두 불가능하다.

정리하면 S-Lock끼리는 서로 같은 Row를 접근할 수 있지만 X-Lock 걸린 Row는 무조건 해당 트랜잭션에서만 접근 가능하다.

 

2. Intention Locks

---

InnoDB는 multiple granularity lock(MGL)을 지원한다. MGL은 Row Lock 과 Table Lock이 공존하는 것을 허용한다.

Intention Lock은 *Table-Level Lock* 을 의미한다.
Intention Lock은 두가지가 있다.

• Intention Shared Lock(IS)
• Intention Exclusive Lock(IX)

SELECT FOR SHARED 구문이 실행되면 테이블에 IS 를 걸고 나서 S-Lock 걸린다.

IS 는 S-Lock 을 걸 의향이 있음을 의미한다.

 

SELECT FOR UPDATE, INSERT, UPDATE, DELETE 구문이 실행되면 테이블에 IX 걸고 나서 X-Lock이 걸린다.

IX 또한 X-Lock을 걸 의향이 있음을 의미한다.

 

IX, IS 서로는 동시에 접근 가능하지만 S-Lock, X-Lock을 걸 경우 접근 제어를 하게됨

 

EX) 특정 Row를 수정할 때 다른 트랜잭션에서 해당 Row를 삭제하는 경우 각 트랜잭션은 IX을 가지지만

X-Lock은 수정 트랜잭션이 가지고 있기에 삭제 트랜잭션은 접근할 수 없다.

 

LOCK TABLES, ALTER TABLE, DROP TABLE 구문이실행되면 IS, IX를 모두 막는 Lock이 걸린다.

반대로 IS, IX 걸려있는 테이블에는 위 구문을 실행할 수 없다

 

그렇다면 왜 Intention Lock을 사용할까? 굳이 IX, IS를 사용하지 않더라도
X-Lock, S-Lock선에서 접근 못하게 막을 수 있을텐데?

https://stackoverflow.com/questions/33166066/why-do-we-need-intent-lock

위 Stackoverflow의 답변을 이해하면 다음과 같다
해당 테이블에 X-Lock, S-Lock가 존재한다는 것을 즉시 알 수 있음(해당 테이블 자체에 대한 변경이 필요할 때 row lock을 모두 살펴서 X-Lock,S-Lock 가 존재하는지 확인하는 것은 성능상 좋지 않음)

즉 간단히 말하면 Intention Lock이 있는 이유는 트랜잭션이 해당 테이블에 대해 잠금을 획득했는지 여부를 바로 알기 위해서다.

아래는 각 Lock들간의 호환성을 나타낸다.

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Complatible	Conflict	Complatible
S	Conflict	Conflict	Complatible	Complatible
IS	Conflict	Complatible	Complatible	Complatible

 

3. Record Lock

---

Record Lock은 PK, Unique Index 등을 통해 Index 참조로 접근하는 데이터에 거는 Lock이다.

SELECT c1 FROM t where c1 = 10 FOR UPDATE; 구문을 수행하면 t.c1=10인 Index에 X-Lock이 걸린다.

만약 Index가 없으면 InnoDB에서 가상의 Clustered Index를 생성해 해당 Index를 통해 Lock을 건다.

 

4. Gap Locks

---

Gap Lock 은 인덱스 레코드들 사이에 있는 영역에 대한 잠금이다.

아래와 같이 id=3,7 만 있는 테이블에 Index가 걸려있다 해보자.

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

이 상태에서는 id < 3, 3 < id < 7, 7 < id 부분에 Index Record가 없는데 이 부분을 Gap이라 하고 여기에 Lock을 거는 것을 Gap Lock 이라 한다.

Gap Lock은 해당 Gap에 새로운 Row가 1*삽입*되는 것을 방지한다.

 

5. Next-Key Lock

---

Next-Key Lock은 Gap Lock, Record Lock 을 함께 사용하는 Lock이다.

 

해당 Lock은 REPEATABLE READ에서 발생하는 Phantom Read를 막기 위해 사용된다.

 

좀 더 자세히 설명하면 Next-Key Lock은 Record-Lock이 걸린 Row의 앞 뒤 인덱스까지 Gap Lock을 거는 것을 의미한다.

 

예를 들어 위 Gap Lock처럼 Id=3,7 있는 Table에 Id=4 FOR UPDATE를 검색후 다른 세션에서 Id=5 Insert를 시도하면 Lock이 걸리게 된다.

 

이유는 Id=4를 기준으로 앞뒤의 가장 근접한 Index Row는 3,7이기에 3 < x < 4, 4 < x < 7에 Gap Lock이 걸리기 때문에 Id=5 Insert에 Lock이 걸린다

Gap Lock, Next-Key Lock 테스트를 봐보자.

 

테스트

C Table에 위와 같이 데이터 존재

 

상황1 : BETWEEN 3 AND 7 일시 Id=4 Insert Lock

Session2에서 BETWEEN 3 AND 7 수행 후 Session1에서 Id=4 Insert 수행시 Gap Lock 걸린 것을 확인

 

상황2 : BETWEEN 5 AND 5 일시 Id=4, 6 Insert

위 처럼 Session2에서 BETWEEN 5 AND 5 수행 후 Session1에서 Id=4, Id=6인 Insert를 수행하면 Gap Lock이 걸리는 것을 확인

→ Next-Key Lock으로 인해 Id=5의 전, 후 Index Row Id=3, 7 Record Lock과 3 < gap lock < 5 < gap lock < 7 이 걸리기 때문이다.

 

상황4(중요) : BETWEEN 3 AND 7 수행 후 Id=2 Insert , BETWEEN 2 AND 7 수행 후 Id=1,9 Insert

위 사진을 보면 Id=2를 삽입시 Gap Lock 없이 바로 수행되고, Id=1, 9 삽입 할 때는 Gap Lock이 걸린 것을 확인할 수 있다. 자세한 내용은 밑에서 설명하겠다

 

상황5 : BETWEEN 3 AND 7 수행후 Id=6,7 검색

위 처럼 Session2에서 BETWEEN 3 AND 7 수행 후 Session1에서 Id=6 FOR UPDATE(X-Lock) 구문이 실행되는 것을 확인. → Gap Lock은 Row가 Insert되는 것을 방지하기 때문

하지만 위 처럼 id=7 검색하면 X-Lock이 걸리는 것을 확인 → id=7은 Index Record이므로 Record Lock이 걸림

 

상황4 설명

상황4 테스트를 보고 의아한 사람이 있을 수 있다. 내가 그랬기 때문이다. 내가 처음에 이해한 Gap Lock대로라면 3~7 범위로 잡혔다면

3의 이전 Index(Infi)와 7의 다음 Index(Infinite) 즉 전 범위에 Gap Lock이 걸릴꺼라 생각했는데 3~7범위에 Id=2가 삽입됐다. 하지만 2~7범위에서 Id=1,9는 모두 Gap Lock이 걸렸다. 그래서 좀 더 생각해보면서 가설을 세웠다.

가설 : 마지막 Index의 다음 값(+)까지 Gap Lock 건다

 

3~7: 마지막 인덱스는 3→7 , 7→Infi 이렇게 될 경우 Id=1,2는 삽입되지만 7이후로는 모두 Gap Lock

 

2~7: 2까지 봤을 때의 이전 Index는 Infi(3과 7이 Index이기 때문에 2이전 Index는 Infi) 이럴 경우 전 범위 Gap Lock(상황4의 Id=1,9 Gap Lock걸린 이유)

 

5~7: 5까지의 봤을 때의 이전 Index는 3, 7 다음 Index(Infi) 즉 3~Infi Gap Lock Id=4,9 삽입 안되는 것 확인

 

5~6: 5 이전 Index(3), 6이후 Index(7) → 3~7범위가 됨 Id=2,8 삽입되는 것 확인

정리해보면 2~7 범위인 경우 2를 기준으로 Index(Infi)이므로 Infi~3 Gap Lock, 7을 기준으로 Index(7)~Index(Infi)까지

Gap Lock을 한다는 것을 알 수 있다.

 

6. Lock이 해제되는 타이밍

---

Transaction이 진행되는 동안 걸린 많은 Lock들은 Transaction이 Commit되거나 Rollback될 때 함께 해제된다.

 

---

Transaction Isolation Level

이제 Transaction Isolation Level에 대해 알아보자.

• READ UNCOMMITTED(커밋되지 않은 읽기)
• READ COMMITTED(커밋된 읽기)
• REPEATABLE READ(반복 가능한 읽기)
• SERIALIZABLE(직렬화 가능)

 

Consistent Read

---

Tansaction Isolation Level을 얘기하기전에 Consistent Read 개념에 대해 알아보자.

Consistent Read : SELECT 구문을 수행할 때 현재 DB 값이 아닌 특정 시점의 DB Snapshot을 읽어오는 것.

InnoDB 엔진은 쿼리를 실행할 때마다 Log를 남기게되는데 Consistent Read는 Log를 이용해 특정 시점의 DB Snapshot을 복구해 가져온다.

 

Read Uncommited

---

각 트랜잭션에서의 변경 내용이 Commit이나 Rollback 여부에 상관 없이 다른 트랜잭션에서 노출되는 Level

 

 

위 사진을 보면 Isolation Level을 Read Uncommited로 변경후 Session1에서 Insert를 수행후 Commit, Rollback을 수행하지 않았지만 Session2에서 조회시 Id=1이 조회되는 것을 확인할 수 있다.

Rollback 수행후 다시 조회하면 Id=1은 삭제된 것을 확인할 수 있다.

이렇게 Commit되지 않은 데이터를 다른 트랜잭션에서 조회되는 현상을 Dirty Read 라 한다.

Dirty Read가 발생하면 데이터의 정합성에 많은 문제가 발생할 확률이 높기에 거의 사용하지 않는 Level이다.

 

Read Commited

---

Commit 된 데이터만 보이는 수준의 Isolation을 보장하는 Level

 

Read Commited는 트랜잭션 안에서 SELECT 구문을 수행할 때마다 Snapshot을 새로 만든다.

 

트랜잭션A가 SELECT 수행 후 트랜잭션B에서 데이터 변경후 Commit한 다음 트랜잭션A가 다시 SELECT를 수행하면 데이터가 변경된 것을 감지할 수 있다.

 

여기서 내가 생각한 의문은 왜 굳이 Select마다 Snapshot을 새로 읽냐는 것이었다

일단 InnoDB는 Insert,Update등으로 수정된 데이터를 Commit을 하지 않아도 실제 DB에 적용시킨다.

 

Read Commited == Commit된 데이터만 보여준다

 

즉 트랜잭션에서 Select를 할 때마다 다른 트랜잭션에서 Commit된 데이터를 알기 위해서는 DB Snapshot을 계속 읽어야한다.

 

** Read Commited는 Gap Lock을 사용하지 않는다. **

 

이로 인해 Read Commited에서는 Phantom Read가 발생할 수 있다.

→ Phantom Read는 트랜잭션 안에서 같은 Select를 두 번 수행했을 때 처음 수행때는 안보였던 Row가 두번째 수행때 나타나는 것을 의미한다.

위 사진을 보면 Session1 에서 Insert를 수행 후 Session2에서 Select를 수행하면 아직 Commit이 되지 않았기 때문에 Id=1 Row가 노출되지 않는 것을 확인할 수 있다.

이제 Session1에서 Commit후 Session2에서 Select를 수행하면 Id=1 Row가 노출되는 것을 확인할 수 있다.

만약 아래처럼 쿼리가 수행한다면 Session2에서는 없던 Phantom Read가 발생하게 된다.

또한 위 사진을 통해 Session2에 Between 3 And 7을 수행해도 Gap Lock이 걸리지 않기에 Id=6이 삽입되는 것을 확인할 수 있다.

 

Repeatable Read

---

트랜잭션에서 반복된 Select를 수행해도 값이 변하지 않을정도로 Isolation을 보장하는 Level


Repeatable Read 는 트랜잭션이 처음 Select를 수행한 시간을 기록하여 이후 Select마다 해당 시점을 기준으로 Consistent Read를 수행한다.

 

이로 인해 다른 트랜잭션에서 데이터 삽입,수정후 Commit되더라도 변경된 데이터는 보이지 않는다.

또한 Read Commited에서 사용되지 않는 Gap Lock을 Repeatable Read에서는 Lock을 사용하는 구문에서 활용된다.

위 사진 Session2에서 Select For Share를 통해 c Table 전체에 Gap Lock을 걸었기에 Session1에서 Insert구문을 수행시 Lock이 걸리는 것을 확인할 수 있다. 그렇기에 Session2에서 Phantom Read가 발생하지 않는다(Commit할 때까지 Gap Lock이 걸려있기에)

 

Serializable

---

한 트랜잭션을 다른 트랜잭션으로부터 완전히 분리하는 Level

 

Serializable은 기본적으로 Select 구문이 모두 Select For Share 로 변경된다.

이로 인해 DeadLock이 쉽게 걸릴 수 있다.

위 사진처럼 각 세션이 Select 구문을 날려 S-Lock을 획득하고 서로 Update를 수행하면 DeadLock이 걸리는 것은 확인할 수 있다.

 

참고

https://code-lab1.tistory.com/51

https://suhwan.dev/2019/06/09/transaction-isolation-level-and-lock/

https://singun.github.io/2019/03/10/mysql-innodb-locking/

https://velog.io/@soyeon207/DB-Lock-총정리-1-InnoDB-의-Lock.

https://www.letmecompile.com/mysql-innodb-lock-deadlock/