MySQL InnoDB locking(락의 종류와 예시)

MySQL InnoDB Locking

 

- Shared Lock (S) : 읽기 락

  - row-level

  - Shared Lock이 걸린 동안 다른 트랜잭션이 해당 row에 대해 X락 획득 불가능하고 대기

  - 여러 트랜잭션이 동시에 S락 획득 가능

  - SELECT ... FOR SHARE

 

- Exclusive Lock (X) : 읽기, 수정, 삭제 락

  - row-level

  - Exclusive Lock이 걸려있으면 다른 트랜잭션이 해당 row에 대해 X, S락 획득 불가능하고 대기

  - SELECT ... FOR UPDATE, UPDATE, DELETE

 

- Intension Lock : 어떤 트랜잭션이 다른 트랜잭션에게 row에 lock걸 예정임을 알려주는 용도

  - table-level, multiple granularity locking(다중세분성 잠금)

  - 나중에 Row에 어떤 유형(S or X)의 락이 걸릴 것인지 나타낸다.

  - 

  - Intention Shared Lock (IS) : 나중에 S락 걸거야.

    - SELECT ... FOR SHARE

      - 테이블에 IS 락 + 로우에 S락

  -Intention Exclusive Lock (IX) : 나중에 X락 걸거야

    - SELECT ... FOR UPDATE

      - 테이블에 IX 락 + 로우에 X락

  - TABLE Lock table `test`.`t` trx id 10080 lock mode IX

  - Intension Lock끼리는 서로 block 하지 않는다. 단, full table requests는 제외

  - 테이블에 1번, 로우에 1번 lock을 적용하는 이유 : 

    - full table requests(LOCK TABLES, ALTER TABLE, DROP TABLE)와 Intension Lock의 관계를 이해

    - UPDATE, DELETE 등의 쿼리가 실행되고 있을때 테이블 스키마가 변경되는 것을 막기 위해  IS, IX를 모두 block하는 table-level 락이 걸린다.

 

 

- Record 락

  - index record에 거는 락 (pk, uk)

  - 인덱스가 없더라도 InnoDB에서는 hidden clustered index를 사용한다.

  - SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE : t.c1 = 10인 모든 row에 대해 추가, 수정, 삭제를 막는다.

 

  - Gap Lock

    - index records 사이의 gap에 대해 / 첫번째 값의 이전 gap에 대해 / 마지막 값의 이후 gap에 대해 거는 record 락

    - SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 는 c1 = 15를 추가하는 다른 트랜잭션을 막는다.

    - InnoDB의 Linked B+Tree 특성으로 레코드 사이에 락을 걸어, Range or Like 작업 도중 트랜잭션을 보장하기 위함

    - Primary Key, Unique Index의 경우 Gap Lock 없이 Record Lock이 사용된다. Index만 걸려있으면 gap lock이 필요하다.

      - Index인 경우 row를 찾기 위해 스캔했던 index range에 대해 gap lock 적용

      - Index가 없으면 풀스캔으로 모든 row에 대해 Lock 적용

    - Gap Lock의 사용 이유

      - Repeatable Read 격리 수준 보장 (Phantom read 방지)

      - Replication 일관성 보장

      - Foreign Key 일관성 보장

 

   - READ-COMMITTED 격리 수준에서 

     - TR1                                                                                                         - TR2

     - SELECT * WHERE id BETWEEN 1 AND 3 FOR UPDATE;

                                                                                                                      - INSERT INTO VALUE 2

    - SELECT * WHERE id BETWEEN 1 AND 3 FOR UPDATE;

    의 예제에서는 SELECT의 결과가 다르다 -> Gap Lock이 없다 -> READ-COMMITTED 격리 수준에서는 동일 트랜잭션 내에서도 다른 결과가 나올 수 있다.

 

    - Repeatable Read :

      - 하나의 트랜잭션내에서 똑같은 SELECT 쿼리를 실행했을때는 항상 같은 결과를 가져와야 한다.

      - 트랜잭션마다 트랜잭션 ID를 부여하여 트랜잭션 ID보다 작은 트랜잭션 번호에서 변경한 것만 읽게 된다.

      - Undo 공간에 백업해두고 실제 레코드 값을 변경한다.

        - 백업된 데이터는 불필요하다고 판단된 시점에 주기적으로 삭제, Undo에 백업된 레코드가 많아지면 MySQL 성능 저하

 

- Phantom Read

데이터베이스에서 트랜잭션이 같은 쿼리를 실행하는 동안 서로 다른 결과를 반환하는 현상.

  트랜잭션이 동일한 쿼리를 실행하는 도중 다른 트랜잭션이 데이터를 추가,수정,삭제하는 경우 발생

  READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ에서 발생

 

- MVCC

  UNDO 로그에 저정할때 트랜잭션마다 고유의 ID를 기반으로 데이터의 가시성을 판단한다. 따라서 읽기 작업은 트랜잭션의 ID보다 작은 트랜잭션에서 수정된 데이터를 읽게 된다. 이로써 대부분의 Phandom Read 상황을 해결한다.

  그러나 SELECT .. FOR UPDATE 같은 쿼리를 실행하면 Phantom Read가 발생한다. (아래 Next-Key Lock 해결 예제 참고)

  READ COMMITTED 격리 수준에서는 다른 트랜잭션에서 변경 내역을 조회할 수 없고 UNDO 로그의 기록을 조회한다.

 

- Next-Key Lock

  - record lock on the index record & gap lock on the gap before the index record 의 조합

  - 공식 문서의 예시로 10, 11, 13, 20의 값을 갖고 있을때, 아래의 Gap Lock 리스트가 있다. (는 미포함, ]는 포함을 나타낸다.

    - (-∞, 10], (10, 11], (11, 13], (13, 20], (20, ∞) 

    - SELECT * WHERE id > 15 FOR UPDATE의 경우 gap lock이 없다면 15 ~ 15다음 레코드인 20의 사잇 값인 16, 17, 18, 19에 대해 INSERT를 막지 못한다.

    - 위 쿼리 실행시 ((13, 20], (20, ∞)) gap lock이 사용된다.

 

- Gap Lock으로 인한 특징과 주의 사항

  - Gap Lock은 MySQL 서버 내부적으로 Shared Lock 형태만 존재(Exclusive Gap Lock은 없음)

  - 순수하게 다른 트랜잭션이 대상 간격(Gap)에 새로운 레코드 INSERT를 막는 것이 주 목적

  - 따라서 여러 트랜잭션의 Gap Lock은 서로 호환된다. 즉, 2개의 트랜잭션에서 동시에 동일한 Gap Lock이 발생하는 쿼리를 실행해도 잠금 경합은 발생하지 않는다.

  - Gap Lock은 동시에 레코드도 잠근다. == Next-Key Lock

  - 이러한 특징으로 인해 데이터가 존재하지 않는 경우 Gap Lock이 걸리면 (UPDATE or SELECT * FOR UPDATE 등) 다른 트랜잭션에서 INSERT를 하는 쿼리는 Gap Lock이 해제될때까지 대기한다. 즉 레코드 건수가 적으면 적을수록 Gap Lock의 간격이 넓어져 역효과가 발생한다. 그래서 테이블의 레코드 건수가 적은 상태에서 동시 실행되는 트랜잭션이 많은 경우, 트랜잭션간 상호 간섭과 대기를 더 고려해야 할 떄도 있다.

 Q. DB에서 데이터가 적을때 동시 실행되는 트랜잭션이 많은 경우 고려해야할 사항은?

 

- 인덱스 종류별 동등 비교 조건

  - PK, UK :

    - 1건의 결과를 보장 : Gap Lock은 사용되지 않고 Record Lock만 사용됨

    - 1건의 결과를 보장 X : (레코드가 없거나, 복합 인덱스 중 일부로 WHERE 사용) : RecordLock + Gap Lock

  - Non Unique Secondary Index (PK를 제외한 모든 인덱스(Secondary Index)중 Unique가 아닌 인덱스)

   - 건수 상관 없이 항상 Record Lock + Gap Lock

 

- Gap Lock으로 인한 Dead Lock

  - id = 2인 레코드가 없는 상황

  - 1번 세션 : SELECT WHERE id = 2 FOR UPDATE; DELETE WHERE id = 2; INSERT INTO VALUE (2)

  - 2번 세션 : SELECT WHERE id = 2 FOR UPDATE; DELETE WHERE id = 2; INSERT INTO VALUE (2)

  SELECT OFR UPDATE, DELETE는 Record Lock없이 Gap Lock만 획득 => 1,2번 세션 서로 충돌하지 않음.

    + Gap Lock이 걸리는 이유 : PK, UK라도 id = 2인 레코드가 없기 때문에 동등 비교 저건에 의해

  INSERT는 INSERT Intention Gap Lock을 필요로 함. 이는 Gap Lock과 호환되지 않으므로 서로의 Gap Lock이 해제되길 기다림

 

- Gap Lock 사용 최소화 & Concurrency 향상

 바이너리 로그 포맷과 격리 수준에 의해 사용되지 않을 수도 있음. 아래 두개의 파라미터를 변경하면 Gap Lock 최소화

  - binlog_format = ROW

  - transaction_isolation = READ-COMMITTED  

 

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

MySQL :: MySQL 8.0 Reference Manual :: 15.7.1 InnoDB Locking