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 이 된다.