1. 개요
mysql(InnoDB) 8.0 버전을 기준으로 작성하였다.
2. Lock
lock을 건다는 것은 해당 lock을 획득함을 의미한다.
lock은 트랜잭션이 commit/rollback 될 때 lock을 반환한다.
Row-level Lock
기본적으로 row 마다 걸리는 row-level lock에는 두가지 타입이 있다.
-
shared lock(S lock)
- 읽기에 대한 lock
- 기본적인 SELECT 구문은 lock을 사용하지 않는다.(consistent read)
- SELECT ... FOR SHARE 구문을 통해 S lock을 걸 수 있다.
- S lock을 가지고 있는 트랜잭션이 해당 row를 읽을 수 있다.
- 하나의 row에 여러 트랜잭션이 S lock을 걸 수 있다. (S lock끼리는 block하지 않음)
- S lock이 걸려있으면 X lock을 걸 수 없다.
=> 읽기 중에는 읽기는 가능하지만 수정, 삭제는 불가능.
-
exclusive lock (X lock)
- 쓰기에 대한 lock
- SELECT ... FOR UPDATE, UPDATE, DELETE 등의 구문에서는 X lock을 건다.
- X lock을 가지고 있는 트랜잭션이 해당 row를 수정/삭제 할 수 있다.
- X lock이 걸려있으면 S lock 및 X lock 모두 걸 수 없다.
=> 수정, 삭제 중에는 읽기, 수정, 삭제 모두 불가능
Intention Lock
intention lock은 어떤 트랜잭션이 행을 잠그고 있거나 테이블의 행을 잠글 예정임을 알려주기 위해 거는 table-level lock이다.
row-level lock과 마찬가지로 두가지 타입이 있다.
-
intention shared lock(IS lock)
- SELECT ... FOR SHARE 구문이 실행 되면 테이블에 IS lock이 먼저 걸리고 행에 S lock이 걸린다.
- IS lock이 걸리면 어떤 트랜잭션이 테이블의 행에 S lock을 걸 예정임을 나타낸다.
- 여러 트랜잭션이 동시에 IS lock을 걸 수 있다.
- IS lock을 거는 첫번째 트랜잭션은 다른 트랜잭션이 X lock을 거는 것을 방지한다.
-
intention exclusive lock(IX lock)
- SELECT ... FOR UPDATE, UPDATE, DELETE 구문이 실행되면 테이블에 IX lock이 먼저 걸리고 행에 X lock이 걸린다.
- IX lock이 걸리면 어떤 트랜잭션이 테이블의 행에 X lock을 걸 예정임을 나타낸다.
- 여러 트랜잭션이 동시에 IX lock을 걸 수 있다.
- IX lock을 거는 첫번째 트랜잭션은 다른 트랜잭션이 S lock 또는 X lock을 거는 것을 방지한다.
- 호환성
-
다음 표에서 다른 트랜잭션에서 각 lock을 얻으려고 할 때 충돌하면 lock을 얻지 못하고 대기한다.
-
테이블의 row가 조회, 생성, 수정, 삭제되는 동안 테이블의 스키마의 변경을 막아야 한다.
-
LOCK TABLES, ALTER TABLE, DROP TABLE 구문이 실행 될 때 획득하는 table lock은 IS lock, IX lock 획득을 block한다.
-
IS lock, IX lock이 걸려있는 테이블에 LOCK TABLES, ALTER TABLE, DROP TABLE 구문을 실행하면 table lock 획득을 block 한다.
=> 테이블 스키마를 변경하려면 어떤 테이블에 row-level lock이 걸려있는지 확인해야 한다. S lock, X lock이 걸려있다면 IS lock, IX lock이 걸려있을 것이므로 table lock을 획득 할 지 대기할 지 결정 할 수 있다.
Record Lock
- 인덱스 레코드에 대한 lock이다.
- S lock, X lock 이 있다.
- ex)
-- 다음 구문에서 테이블 T 의 컬럼 c1 의 값이 10인 행의 인덱스에 X lock이 걸린다.
-- 다른 트랜잭션에 의한 해당 row 의 삽입, 업데이트, 삭제를 block한다.
SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;
Gap Lock
- 인덱스 레코드 간의 간격에 대한 잠금
- 첫 번째 인덱스 레코드 이전, 마지막 인덱스 레코드 이후의 간격에 대한 잠금
- 단일 인덱스 값, 여러 인덱스 값에 걸쳐 있거나 비어있을 수 있다.
- ex)
-- 다음 구문에서 c1 의 값이 11, 18 만 있다고 하자.
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
-- 10~20 범위에 있는 모든 값 사이의 갭이 잠겨 있기 때문에
-- 열에 값이 있는지 여부에 관계 없이 다른 트랜잭션이 해당 범위의 값을 삽입 하는 것을 block 한다.
-- (c1 에 15 의 값의 삽입을 block)-
gap lock은 트랜잭션 격리 수준에 따라 사용 유무가 다르다.(READ COMMITTED 이하의 격리수준 에서는 동일 SELECT 구문의 동일 결과를 보장하지 않기 때문에 gap lock을 사용하지 않는다.)
-
unique index가 걸려있는 컬럼의 고유 행을 검색하는 경우에는 gap lock이 아닌 record lock이 사용된다.
-
인덱싱이 되지 않았거나 고유하지 않은 인덱스가 있는 경우에는 gap lock을 사용한다.
-
다른 트랜잭션이 gap lock이 걸린 레코드에 삽입되는 것을 방지하는 것이 목적이다.(gap s lock과 gap x lock은 차이가 없다.)
-
따라서 gap lock은 s lock 과 x lock 의 공유가 가능하다.(레코드가 인덱스에서 제거되는 경우 다른 트랜잭션이 레코드에 보유하고 있는 갭 잠금을 병합해야 하기 때문이다.)
Next-Key Lock
- Next key lock은 record lock, gap lock의 복합적으로 적용된 lock 이다.
- ex)
-- c1 은 unique index가 걸려있고 인덱스 값은 10, 11, 13, 20이 있다고 가정
SELECT c1 FROM t WHERE c1 > 15 FOR UPDATE;
-- c1 = 20 에는 record lock
-- 13 < c1 < 20 에 gap lock
-- 20 < c1 < infinity 에 gap lock Insert Intention Lock
- INSERT 구문이 실행되기 전 걸리는 gap lock 의 한 종류이다.
- 여러 트랜잭션이 gap lock이 걸려있는 범위에 각 다른 위치에 INSERT를 수행할 때 대기 하지 않도록 한다.
- ex)
mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
-- chile 테이블의 id 값이 90, 102 삽입.
mysql> INSERT INTO child (id) values (90),(102);
-- 첫번째 트랜잭션 시작
mysql> START TRANSACTION;
-- 해당구문에서 90 < id < 102 에
-- gap lock(Insert Intention Lock) 이 걸린다.
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id |
+-----+
| 102 |
+-----+
-- 두번쨰 트랜잭션 시작
mysql> START TRANSACTION;
-- id = 101 에는 gap lock(Insert Intention Lock)
-- 이 걸려 있지만 겹치지 않으므로 대기 하지 않고 INSERT 구문을 수행한다.
mysql> INSERT INTO child (id) VALUES (101);
AUTO-INC lock
- AUTO_INCREMENT를 사용 하는 컬럼의 값을 INSERT할 때 사용하는 lock
- 여러 트랜잭션이 동시에 INSERT하는 경우 AUTO_INCREMENT의 값을 일관성 있게 증가시킨다.
3. Isolation Level
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
참조
