MySQL InnoDB - About Locks.. and Transaction Isolation level

AWS Aurora 를 사용하는데, MySQL InnoDB 엔진 기반으로 사용하고 있다. 따라서, 적어도 알고는 쓰자! 라는 관점에서 정리를 해봤다.

공식 document 를 훑어보고, 이거저거 찾아보고 정리를 해보았다.

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-shared-exclusive-locks


Shared Locks

다른 트랜잭션이 해당 lock 이 걸린 row 를 read 할 수 있도록 하는 lock, 또한 해당 row 에 다른 shared lock 을 걸 수 있음. 하지만, write(CUD) 는 불가능함.


Exclusive Locks

어떤 다른 트랙잭션도 exclusive lock 이 걸린 row 에는 exclusive lock 을 걸 수 없음. 또한, transaction isolation level 에 따라, exclusive lock 이 걸린 row 에 대해서는 다른 트랜잭션이 write(CUD) 뿐만 아니라 read 를 수행할 수 없음.

MySQL InnoDB engine 의 default isolation level 은 REPEATABLE READ 이며, 이는 exclusive lock 을 지닌 rows 들을 다른 트랜잭션이 read 는 가능하도록 하는 level 로써, 높은 동시성을 가능하게끔 해주며 이를 consistent read 라 한다.

s lock, e lock 예시

만약 Transaction T1 이 row R 에 대하여 shared(S) lock 을 획득하고, Transaction T2 가 row R 에 대하여 접근 했을 때, T2 는 아래 2가지가 가능하다.

  1. T2 는 R 에 대하여 S lock 을 즉시 획득 가능. → T1, T2 R 에 대하여 S lock 을 가진다.
  2. T2 는 R 에 대하여 exclusive (X) lock 을 즉시 가지지 못한다.

만약 Transaction T1 이 row R 에 대하여 exclusive(X) lock 을 획득한다면, Transaction T2 은 S, X 어느 lock 도 획득할 수 없다. 대신에, T2 는 T1 이 R 에 대한 lock 을 해제할 때 까지 기다려야만 한다.


phantom rows

동일한 Transaction 상에서 same query 로 다른 시점에 질의했을 때 다른 set of rows 가 반환되는 현상이다.

SELECT * FROM child WHERE id > 100 FOR UPDATE;

t
id 
--
90
102

해당 query 는 id index 가 100 보다 큰 row 들을 scan 한다. scanned index range (해당 경우 90 ~102) 에 대해 획득한 lock 이 해당 gap 에 대해 insert 를 잠그지 않는 경우, 다른 Transaction 이 id 101 로 insert 할 수 있다. 그 경우 새로운 row id 101 을 확인할 수 있는데, 해당 row 를 phantom row 라고 한다.

phantoms 을 예방하기 위해서, InnoDB 는 next-key lock 을 사용한다. exclusive 와 shared lock 으로 index-record lock 과 gap lock 이 동시에 적용한다. 따라서 해당 gap 에 insert 를 막는다.

또한 index 마지막 레코드 이후 gap 을 잠글 수 있다. 해당 예제에서 id 가 102 다음에 오는 gap 에 대하여도 잠금이 포함된다.


Consistent read

snapshot 을 이용하여, 동시에 실행 중인 다른 트랜잭션에서 수행 한 변경 사항에 관계없이 특정 바로 그 시점을 기반으로 query 결과를 표시 해주는 read operation 이다.

만약, query 했던 data 가 다른 트랜잭션에 의해서 변경 되었다면, origin data 는 undo log 내용에 기반하여 재구성 된다. 이 기술은 트랜잭션이 다른 트랜잭션이 완료 될 때까지 기다리도록 하여 동시성을 줄일 수있는 일부 잠금 문제를 방지한다.


Undo Logs

undo logs 는 단일 read-write transaction 의 기록에 대한 collection 이다. undo log record 는 clustered index record( 해당 pk row) 에 대한 transaction 에 의해 최근에 변경을 어떻게 undo 할 것인가? 에 대한 정보를 포함하고 있다.



만약에 다른 transaction 이 consistent read 를 수행하기 위해서 origin data 가 필요하다면, undo log records 로 부터 변경되지 않은 origin data 가 재구성 된다.

undo logs 는 rollback segments 내에 포함된 undo log segments 에 존재한다. rollback segments 는 undo tablespace 과 global temporary tablespace 에 있다.


Intention Locks

Table level 의 lock 으로, 이후에 해당 Table 의 Row 에 대하여 Transaction 에 필요한 (Shared or Exclusive) Lock 을 획득할지 나타낸다.

  • Intention Shared Lock (IS) 는 테이블의 로우에 대하여 shared 락을 획득 하겠다는 것을 나타낸다.
  • Intention Exclusive Lock (IX) 는 테이블의 로우에 대하여 exclusive 락을 획득 하겠다는 것을 나타낸다.

SELECT ... FOR SHARE, SELECT ... FOR UPDATE



지금 요청하는 Transaction 이 기존에 존재하는 lock 과 compatible 하다면 lock 을 부여 받는다. 그러나 기존에 존재하는 lock 과 loconfilct 라면 lock 을 부여 받지 못한다.

따라서, conflict 라면 기존에 존재하는 lock 이 해제될 때까지 지금 요청하는 Transaction 은 기다려야 한다.

만약 lock 을 획득 하려는 요청이 기존의 lock 과 conflict 라면 lock 을 부여 받지 못한다. 이는 deadlock 을 야기하며, error 을 발생시킨다.

Intention lock 은 full table requests (ex LOCK TABLES ... WRITE, ALTER TABLE, DROP TABLE) 를 제외하고는 어떤 것도 차단하지 않는다.

Intention lock 의 주된 목적은 누군가 row 나 table 에 있는 row 을 잠그려고 한다는 것을 보여주기 위함이다.


Record Locks

index record 에 대한 lock 이다.

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;

다른 Transaction 이 t.c1 의 값이 10인 행을 insert, update, delete 하지 못하도록 막는다.

테이블에 index 가 없어도, InnoDB 는 hidden clustered index (Secondary Index) 를 생성하고 record lock 을 사용한다.


Gap Locks

between index records 에 대한 lock 이다.

SELECT c1 FROM t WHERE c1 BETWEEN 10 AND 20 FOR UPDATE;

범위에 있는 모든 기존 값 사이의 간격이 잠긴다. 따라서 c1 에 이미 해당 값이 존재하는지 여부에 관계없이 다른 Transaction 이 t.c1 컬럼에 값 15 를 삽입하지 못하도록 막는다.

gap 은 단일 인덱스, 여러 인덱스 값에 걸쳐 있거나, 비어 있을 수도 있다.

gap lock 은 성능과 동시성 간의 tradeoff 관계 이다. 일부 transaction isolation level 에서만 사용되고, 일부에서는 사용되지 않는다.

unique index 를 사용하는 row 들 중에 unique row 을 찾는 statement 에서는 gap locking 이 필요하지 않다.

ex) id 컬럼이 unique index 를 가진다면, 아래의 statement 는 단지 record lock 만을 사용한다.

SELECT * FROM child WHERE id = 100;

만약에 id 컬럼이 index 를 사용하지 않고, nonunique index 를 사용한다면, gap lock 을 사용하게 된다.

서로 다른 transaction 들이 특정한 gap 에 대하여 conflicting locks 을 획득할 수 있다. 예를 들어, Transaction A 가 특정한 gap 에 대하여 gap S-lock 을 획득했을 때, Transaction B 가 동일한 gap 에 gap X-lock 을 획들할 수 있다. 이러한 conflicting gap lock 이 허용되는 이유는 단일 record 가 index 에서 제거 된다면, 다른 Transaction 에 의해 record 에 보유 된 gap lock 을 merge 해야하기 때문이다.

InnoDB 의 Gap lock 은 "purely inhibitive" 이다. 무슨 의미인가? 다른 Transaction 이 Gap 에 Insert 를 막는 것이 Gap Lock 의 유일한 목적이다. Gap Lock 은 공존할 수 있다. 한 Transaction 이 특정 Gap 에 Gap Lock 을 수행해도, 다른 Transaction 또한 동일한 Gap 에 Gap Lock 을 막지 않는다. shared gap lock 과 exclusive gap lock 사이의 차이점은 없다. 서로 충돌하지 않으며 동일한 기능을 수행한다.

Gap Lock 은 명시적으로 비활성화 할 수 있다. Transaction isolation level 을 READ COMMITED 로 변경하면 된다. Gap Lock 은 searches 와 index scan 에서 비활성화 되고, 외래 키 제약 조건 검사 및 중복 키 검사에만 사용된다. 또한 READ COMMITED 환경에서는 일치하지 않는 행에 대한 record lock 을 WHERE 문을 평가한 후에 해제한다. 그리고 UPDATE 문의 경우 InnoDB 는 "semi-consistent" read 를 수행한다, 이는 MySQL 이 최신 커밋 된 버전을 반환하여 해당 row 가 UPDATE 의 WHERE 조건과 일치하는지 여부를 확인할 수 있도록 한다.


Next-Key Locks

record lock 과 gap lock 의 combination 이다. Transaction T1 이 index 의 Row R 에 대하여 shared or exclusive lock 을 획득한 경우, 다른 Transaction T2 는 새로운 index Record 를 index 순서에서 R 의 앞 이전에 insert 할 수 없다.

default 로 InnoDB 는 REPEATABLE READ isolation level 에서 작동한다. searche 와 index scan 에 next-key lock 을 이용하여 phantom rows 를 예방한다.


Insert Intention Locks

gap lock 의 종류로 insert 전에 획득된다. 해당 lock 은 동일한 index gap 내의 동일한 위치에 insert 하지 않는 경우 서로를 기다릴 필요 없이 insert 하려는 의도를 나타내기 위해서 사용된다.

예를 들어, 값이 4와 7인 index record 가 있다고 가정한다. 각각 5와 6을 insert 하려는 Transaction 들은 이미 존재하는 행에 대한 exclusive lock 을 획득하기 전에 insert intention lock 을 획득하여 4 와 7 사이의 gap 을 잠근다. 하지만 이 각각은 행이 충돌하지 않기 때문에 서로를 막지 않는다


AUTO-INC Locks

AUTO_INCREMENT 컬럼이 있는 테이블에 insert 하려는 transaction 에 의해 행해지는 table-level 의 lock 이다. 단적인 예로, insert 하려는 transaction 이 있는 경우, insert 하려는 다른 transaction 은 기다려야 한다. 그래야만 연속적인 키 값을 받을 수 있다.

innodb_autoinc_lock_mode 설정을 통해서 동시성을 증가시킬수 있다.


ACID

atomicity(원자성), consistency(일관성), isolation(고립성), durability(영구성)

Transaction 은 commit 또는 roll back 이 가능한 일련의 원자적인 작업의 단위이다. Transaction 이 database 에 여러 테이블에 대하여 여러 변화를 일으킬 때, 전부 성공하거나 전부 실패해야 된다.

database 는 항상 일관된 상태를 유지해야 한다. Transaction 들이 진행중인 상황에서 각각이 commit 이나 rollback 후 관련된 data 는 예전 값과 새로운 값의 혼재가 아닌 단지 새로운 값으로 일관되어야 한다.

Transaction 들은 진행중에 서로 격리 되어야 한다. 서로를 간섭하거나 서로의 commit 되지 않은 data 를 볼 수 없다. 해당 isolation 은 lock 메커니즘을 통해서 구현된다. 숙련된 사용자는 isolation level 조정을 통해서 성능과 동시성의 tradeoff 관계를 효율적으로 다룰 수 있다.

Transaction 의 결과는 지속해야 한다. commit 이 일단 성공하면, 정전 시스템 충돌 등 database 외부 요인에 의한 위험에도 변경은 지속적으로 유지되어야 한다. 이는 disk storage 의 쓰기 작업과 관련이 되어 있다.


READ UNCOMMITTED

SELECT 는 lock 을 사용하지 않는다. 그러므로 read consistent 를 보장하지 않는다. dirty read 를 유발한다.

dirty read

다른 transaction 에서 아직 commit 되지 않은 data 가 또 다른 transaction 에서 SELECT 질의문을 통해서 read operation 이 수행된다. 이는 ACID 원칙에 위배된다.


READ COMMITTED

동일한 Transaction 에서도 새로운 snapshot 생성한다.

locking read (SELECT with FOR UPDATE or FOR SHARE), Update, Delete 문을 사용할 때, InnoDB 는 index records lock 만 사용하고 gap lock 은 사용하지 않는다. 이는 locked records 의 gap 에 insert 가 자유롭다. Gap lock 은 foreign-key 제약 조건 검사와 duplicate-key 제약 조건 검사에서만 사용된다.

gap locking 이 비활성화 되므로, phantom row 문제가 발생할 수 있다.

또한 row-based binary logging 만이 지원된다.

Update, Delete 문은 오직 해당 대상 rows 에 대하여 lock 을 가진다. Where 절 평가를 통해 매칭 되지 않는 rows 들은 released 된다.

Update 문 사용 시, 이미 해당 row 가 잠겨진 경우, InnoDB 는 "semi-consistent" read 를 수행한다. 이는 가장 최근에 commit 된 결과를 return 한다.


REPEATABLE READ

InnoDB 엔진의 default isolation level 이다. Transaction 경계 안에서의 첫 번째 read 를 통해 생성된 snapshot 을 이용하여 Consistent reads 가 가능하다. (Nonlocking Read)

locking read (SELECT with FOR UPDATE or FOR SHARE), Update, Delete 문은 unique index 의 사용하는 statement 는 unique search condition 또는 a range-type search condition 이다.

  • unique index with a unique search condition 의 경우 InnoDB 는 gap lock 이 없고 특정 index record 에 lock 을 획득한다.
  • index range scan 의 rows 에 대해서는 gap lock 또는 next-key locks 을 통해 다른 transaction 이 해당 gap 에 insert 를 하지 못하도록 잠근다. 이는 결과적으로 phantom row 를 방지한다.


SERIALIZABLE

InnoDB 는 묵시적으로 모든 SELECT 문을 SELECT ... FOR SHARE 로 shared lock 을 사용하도록 변환 하고, autocommit 은 비활성화 된다. autocommit 을 활성화 시키면, SELECT 문 그 자체가 transaction 이 된다.


댓글

이 블로그의 인기 게시물

About JVM Warm up

About idempotent

About Kafka Basic

About ZGC

sneak peek jitpack

Spring Boot Actuator readiness, liveness probes on k8s

About Websocket minimize data size and data transfer cost on cloud

About G1 GC

대학생 코딩 과제 대행 java, python, oracle 네 번째