[DB] 락(Lock) - MySQL 8.0 InnoDB

🧐 서론

데이터베이스에는 트랜잭션이 존재합니다.

트랜잭션을 통해 하나의 작업 단위를 묶어서 ACID 원칙을 지키기 위해 노력합니다.

 

하지만, ACID 원칙이 언제나 완벽하게 지켜지진 않습니다.

너무 완벽하게 지키기 위해 어떤 상황에서라도 지키려고 한다면, 동시성이 떨어지는 문제가 발생하기 때문이죠!

 

그렇기 때문에 ACID 원칙을 적절하게, 상황에 맞게 유지시키면서 동시성을 높일 수 있게 DB에서 기능을 제공합니다.

바로 Isolation level을 통해서요! (트랜잭션에 대한 추가 설명은 이전 글을 참고해주세요!)

 

결국, Isolation level 별 적절한 Lock을 진행하고, 이는 DBMS마다 다릅니다.

저는 MySQL InnoDB 기준으로 어떤 Lock이 있는지 알아보도록 하겠습니다.

---

🫢 본론

MySQL InnoDB 락(Lock)의 종류

락 적용 요소에 따른 분류

• Shared Locks (S)
• Exclusive Locks (X)
• Intention Locks

락이 적용되는 상황에 따른 분류

• Row Locks
• Record Locks
• Gap Locks
• Next-Key Locks
• Insert Intentation Locks
• AUTO-INC Locks

---

락 적용 요소에 따른 분류

Shared Lock (공유 잠금)

• Row Level Lock
• 특정 Row를 읽을 때 사용되므로 다른 말로 Read Lock 이라고도 한다.
• 다른 트랜잭션이 해당 row에 대해 베타 락은 획득이 불가능하지만, 공유 락은 획득이 가능하다.

방법

SELECT ... LOCK IN SHARE MODE (8.0부터는 FOR SHARE로 가능)

Exclusive Lock (베타적 잠금)

• Row Level Lock
• 특정 Row를 변경 혹은 삭제하고자 할 때 사용되므로 다른 말로 Write Lock 이라고도 한다.
• 다른 트랜잭션이 해당 row에 대해 공유 락 / 베타 락 모두 획득하지 못하도 대기하게 된다.

방법

SELECT ... FOR UPDATE

Intention Lock (의도 잠금)

• Table Level Lock
• 해당 테이블 row 중 나중에 row-level lock이 걸릴 것이라는 걸 미리 알려주기 위해 사용된다.

방법

LOCK TABLE ... READ;
SELECT ... LOCK IN SHARE MODE

1. Intention Shared Lock (IS)이 테이블에 걸린다.
2. row-level에 Shared Lock (S)이 걸린다.

LOCK TABLE ... WRITE;
SELECT ... FOR UPDATE (or INSERT, DELETE)

1. Intention Exclusive Lock (IX)이 테이블에 걸린다.
2. row-level에 Exclusive Lock (X)이 걸린다.

• IS, IX 락은 여러 트랜잭션에서 동시에 접근이 가능하다. (서로 block하지 않는다.)
  • 하지만 row-level의 실제 Lock(S or X)에서 접근 제어를 하게 된다.
• LOCK TABLES (or ALTER TABLE, DROP TABLE) 이 실행될 때는 IS, IX를 모두 block 하는 table-level 락이 걸린다.
  • 즉 IS, IX lock을 획득 하려는 트랜잭션은 대기 상태로 빠지게 된다.

Table-level에서 한 번, Row-level에서 한 번, 2단계(2-phase)로 Lock을 적용하는 이유는?

• A트랜잭션에서 이미 테이블에 대해 락이 걸려있는데, B 트랜잭션에서 해당 테이블의 특정 row에 lock을 거는 것을 원천적으로 방지할 수 있다.
  • 예) row-level의 write이 일어나고 있을 때 테이블 스키마가 변경되서는 안 된다. write query의 경우 이미 IX 락을 획득한 상태이기 때문에 해당 테이블의 스키마가 변경되는 것을 막을 수 있다.

 

Share Lock, Exclusive Lock, Intention Lock 호환 표

	X	IX	S	IS
X	충돌	충돌	충돌	충돌
IX	충돌	호환	충돌	호환
S	충돌	충돌	호환	호환
IS	충돌	호환	호환	호환

---

락이 적용되는 상황에 따른 분류

Row Lock

• 테이블 row에 걸리는 Lock을 의미한다.
• 각 row에 S-Lock, X-Lock을 사용할 수 있다.

Record Lock

• primary key, unique index로 조회해서 하나의 인덱스 레코드(=row)에만 lock을 거는 것을 의미한다.
• 각 Index record에 S-Lock, X-Lock을 사용할 수 있다.
  
  • 예) SELECT id FROM t WHERE id = 10 LOCK IN SHARE MODE
    • 위 쿼리를 실행하면 id=10 인 레코드에 대해 S lock이 걸린다.
  • 예) SELECT id FROM t WHERE id = 10 FOR UPDATE
    • 위 쿼리를 실행하면 id=10인 레코드에 대해 X lock이 걸린다.

Gap Lock (=Range Lock)

• 범위를 지정하기 위해 인덱스 레코드 사이 범위(gap)에 락을 거는 것을 의미한다.
• 최초 레코드 이전, 마지막 레코드 이후를 가상의 인덱스 레코드로 생각해서 lock을 적용하는 것이 가능하다.
• Gap lock을 통해서 같은 SELECT 쿼리를 두 번 실행했을 때 다른 트랜잭션에서 데이터가 수정되었더라도 같은 결과가 리턴되는 것을 보장할 수 있다. (Phantom read 방지)
  • 예) SELECT id FROM t WHERE id BETWEEN 10 and 20 FOR UPDATE
    
    • 위 쿼리를 실행하면 id = 10~20 사이에 X lock이 걸리기 때문에 다른 트랜잭션에서 id = 15를 가지는 데이터를 INSERT 하려면 대기 상태로 빠지게 된다.
• 컬럼에 대한 WHERE 절로 하나의 레코드만 추출되었을 때, Record lock과 Gap lcok 어느 것이 사용될까?
  • 컬럼에 unique index가 걸려있는 경우에 gap lock이 필요 없다. (record lock이 사용된다.)
  • 컬럼에 index가 걸려있지 않거나, index가 걸려 있어도 unique하지 않는다면 gap lock이 필요하다.
    • index가 걸려 있다면 row를 찾기 위해 스캔했던 index range에 대해서 gap lock 적용
    • index가 걸려있지 않다면 결국 테이블 전체를 스캔해야 되기 때문에 모든 row에 대해 lock이 걸린다.

Next-Key Lock

• 실제 범위 지정 쿼리를 실행하면 위에서 설명한 Record Lock, Gap Lock이 복합적으로 발생하는 Lock이다.
• Repeatable Read인 상태에서는 phantom Read를 예방하기 위해 인덱스 scan을 하면 이 Lock이 발생한다.

SELECT * WHERE pk > 99 FOR UPDATE 실행 시 일어나는 일들

Insert Intention Lock

• INSERT 구문이 실행될 때 InnoDB 엔진 내부적으로 implicit(암시적)하게 획득하는 특수한 형태의 Gap Lock.
  • gap lock은 일반적으로 explicit(명시적)하게 locking read를 위한 SELECT 구문이 실행될 때 발생하지만
  • 이 lock은 INSERT 시점에 implicit 하게 자동으로 발생
• 여러 개의 트랜잭션이 gap 안의 다른 위치에 insert를 동시 수행할 때 기다릴 필요가 없도록 하는 것이 목적
  • INSERT Intention Lock들 간에는 충돌이 일어나지 않는다.
• INSERT 될 row에 대해서 exclusive lock을 걸기 전에 먼저 insert intention lock을 건다.
  • 예) pk = 3, pk = 6의 레코드가 존재하는 테이블이 존재
    • A트랜잭션에서 pk=5에 INSERT, B 트랜잭션에서 pk=4에 INSERT 시도
      • 만약 일반적인 gap lock을 사용한다면:
        • A트랜잭션이 pk=5를 INSERT 하는 과정에서 pk=3~5에는 gap lock이 걸림.
        • B트랜잭션이 pk=4에 INSERT 시도 시 pk=3~5에 gap lock이 걸려있기 때문에 A가 트랜잭션이 완전히 종료될 때까지 기다려야 한다.
        • 대기시간이 존재!
      • Insert Intention Lock 사용 시:
        • A트랜잭션이 pk=5를 INSERT하는 과정에서 pk=3~5에는 insert intention lock 걸림.
        • B트랜잭션이 pk=4에 INSERT 시도 시 pk=3~5에 insert intention lock이 걸려 있더라도 pk가 겹치지 않기 때문에 바로 진행 가능.
        • 대기시간 없음!
        • 실제 InnoDB의 동작방식

AUTO-INC Lock

• AUTO_INCREMENT 컬럼의 값을 일관성을 보장하기 위해 사용하는 Lock
• 하나의 트랜잭션이 해당 컬럼 내의 연속 숫자를 부여받기 위해서 해당 Lock을 걸고, 이로 인해 다른 트랜잭션들이 대기함으로써 순차적으로 연속 숫자를 부여받도록 해준다.

---

락으로 해결되지 않는 문제점

락은 이처럼 장점들이 존재하지만 만능은 아닙니다.

락을 걸어도 아래와 같은 문제점이 발생할 수 있습니다.

 

1. A트랜잭션과 B트랜잭션이 동시에 a row를 변경할 경우
   
   • A가 먼저 실행되고 B가 다음으로 실행됐다면, A에서 변경한 내용은 무시된다.
   • 예) 유저 테이블의 집 주소 칼럼에 대해 A-T은 서울시로 변경하고, B-T은 수원시로 변경할 경우 수원시로 최종 저장된다.
2. 데드락이 발생할 수 있다.
   • 아래 예시처럼 각각의 트랜잭션이 A, B를 각각 잠그고 서로 잠근 테이블에 요청할 경우 데드락(무한대기현상)에 빠지게 된다.

데드락 발생 예시

 

다음 포스터에서는 두 문제를 해결할 수 있는 방법인 '분산 락'에 대해서 알아보도록 하겠습니다.

---

🙇‍♂️ 결론

각 DB 별로 약간씩 락이 다릅니다.

자신이 사용하는 DB에 대해서는 파악하고 있는 게 좋을 것 같습니다.

 

잘못된 부분이 있다면 댓글로 알려주시면 감사하겠습니다! (__)

 

참고

https://dbknowledge.tistory.com/23

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

https://jaeseongdev.github.io/development/2021/06/16/Lock%EC%9D%98-%EC%A2%85%EB%A5%98-(Shared-Lock,-Exclusive-Lock,-Record-Lock,-Gap-Lock,-Next-key-Lock)/ 

https://taes-k.github.io/2020/05/17/mysql-transaction-lock/

https://velog.io/@soyeon207/DB-Lock-%EC%B4%9D%EC%A0%95%EB%A6%AC-1-InnoDB-%EC%9D%98-Lock#lock-%EC%9D%B4-%ED%95%B4%EC%A0%9C%EB%90%98%EB%8A%94-%ED%83%80%EC%9D%B4%EB%B0%8D

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

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-predicate-locks

https://krksap.tistory.com/541