[SQL] MySQL 잠금(2) - InnoDB 스토리지 엔진 잠금

InnoDB 스토리지 엔진 잠금

• InnoDB스토리지 엔진 잠금은 레코드 기반 잠금 기능을 제공
  - ✅ MyISAM보다 뛰어난 동시성 처리 제공
• InnoDB에서는 트랜잭션과 잠금, 그리고 잠금 대기 중인 트랜잭션 목록 조회 가능
  - INNODB_TRX,INNODB_LOCKS, INNODB_LOCK_WAITS라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고, 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인 가능

InnoDB 엔진 잠금 종류

✅ 레코드 락
✅ 갭 락
✅ 넥스트 키 락
✅ 자동증가 락

레코드 락

• 레코드 자체만을 잠그는 락으로, 실질적으로 잠그는 것은 레코드 자체가 아닌 인덱스의 레코드를 잠금
• 인덱스가 없는 테이블이라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정

레코드 락 예시

• session1, session2이 있다고 가정
• select for update 사용 : 가장 먼저 LOCK을 획득한 session의 select된 row들이 update쿼리 후 commit되기 이전까지 다른 session들은 해당 row들을 수정하지 못하도록 하는 기능
• 가장 먼저 LOCK을 획득한 session1이 commit되기 전까지 다른 session들은 해당 row를 수정할 수 없고 읽기는 가능
• 동일한 row를 서로 다른 session이 접근하게 되는 경우 먼저 접근한 session1의 commit이 완료될 때까지 session2는 LOCK WAIT TIME동안 대기

# session1
start transaction;
SELECT * FROM book WHERE id=1 FOR UPDATE;
+----+-------+-------+
| id | title | price |
+----+-------+-------+
|  1 | Tile  | 10000 |
+----+-------+-------+
1 row in set (0.00 sec)
UPDATE book SET price=10010 WHERE id=1;
# session2
start transaction;
SELECT * FROM book WHERE id=1 FOR UPDATE;
-- 대기 중--
+----+-------+-------+
| id | title | price |
+----+-------+-------+
|  1 | Tile  | 10010 |
+----+-------+-------+
1 row in set (49.84 sec)

갭 락

• 레코드 자체가 아니라 레코드와 레코드 사이의 간격만을 잠그는 것을 의미
  - 간격에 새로운 레코드가 생성(INSERT)되는 것을 제어

갭 락 예시

• 주문 번호가 100부터 200까지의 범위에 갭 락 설정
• 이 범위 내의 레코드를 조작하거나 다른 트랜잭션에서 갭을 락 가능

START TRANSACTION;
SELECT * FROM orders_test WHERE order_id >= 100 AND order_id <= 200 FOR UPDATE;
COMMIT;

넥스트 키 락

• 레코드 락 + 갭 락
• InnoDB의 갭 락이나 넥스트 키 락은 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될 때 소스 서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 하는 것이 주 목적

넥스트 키 락

• 주문 번호 102의 레코드와 그 다음 레코드에 넥스트 키 락을 설정
• 이로 인해 주문 번호 102와 103 사이에 락이 걸리고, 이 범위 내의 레코드를 다른 트랜잭션에서는 조작할 수 없게 됨
• 이 범위 내의 레코드를 조작하거나 다른 트랜잭션에서 갭을 락할 수 있음

START TRANSACTION;
SELECT * FROM orders_test WHERE order_id >= 102 FOR UPDATE;
COMMIT;

자동 증가 락

• AUTO_INCREMENT라는 컬럼 속성에서 insert 시 일련번호 값을 가져야 함
• InnoDB엔진에서는 이를 위해 내부적으로 AUTO_INCREMENT라고 하는 테이블 수준의 잠금을 사용
• 자동증가 락은 INSERT, REPLACE쿼리 문장과 같이 새로운 레코드를 제정하는 쿼리에서만 필요
• UPDATE, DELETE에서는 발생하지 않음

자동증가 락 예시

• orders_test테이블에 새로운 데이터를 삽입하고 방금 삽입한 자동증가 열 값을 획득
• 방금 삽입한 주문을 읽거나 수정이 가능
• 이후에 다른 트랜잭션에서 같은 주문을 수정하려고 시도하면 대기 상태

CREATE TABLE orders_test (
    order_id INT PRIMARY KEY auto_increment,
    order_date DATE,
    customer_name VARCHAR(50),
    order_total DECIMAL(10, 2)
);
INSERT INTO orders_test (order_id, order_date, customer_name, order_total)
VALUES
    (100, '2023-10-18', 'Customer A', 100.50),
    (101, '2023-10-19', 'Customer B', 75.20),
    (102, '2023-10-20', 'Customer C', 250.75),
    (103, '2023-10-20', 'Customer D', 300.00),
    (104, '2023-10-21', 'Customer E', 45.60),
    (105, '2023-10-22', 'Customer F', 75.25);

START TRANSACTION;
INSERT INTO orders_test (order_date, customer_name, order_total) VALUES ('2023-10-23', 'Customer G', 50.00);
SELECT LAST_INSERT_ID() INTO @new_order_id;
SELECT * FROM orders WHERE order_id = @new_order_id FOR UPDATE;
COMMIT;

참조

Real MySQL 8.0 (1권)
https://jinhokwon.github.io/mysql/mysql-select-for-update/
https://doomdevlog.tistory.com/29