외래키와 관련해서는 어떨까?
Real MySQL3장에서는
"외래키는 부모테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로인해 데드락이 발생할 수 있다. 그래서 실무에서는 잘 사용하지 않는다."
이런 내용이 있다.
외래키와 관련된 제약사항은 너무 헷갈리는 부분이 많아서 직접 테스트를 해보기로 했다.
우선 MySQL에서 테스트할 테이블들을 만든다.
CREATE TABLE classroom (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
floor INT NOT NULL,
capacity INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
classroom_id INT, -- 외래키가 될 컬럼
grade INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (classroom_id) REFERENCES classroom(id)
);
INSERT INTO classroom (name, floor, capacity) VALUES
('1-1반', 1, 30),
('1-2반', 1, 30),
('2-1반', 2, 25),
('2-2반', 2, 25);
-- 학생 데이터
INSERT INTO student (name, classroom_id, grade) VALUES
('김철수', 1, 1),
('박영희', 1, 1),
('이민수', 2, 1),
('정수진', 3, 2);
벤치에서 두개의 세션을 띄운다.
둘다 트랜잭션이 계속 유지되도록 오토커밋을 비활성화한다.
SET autocommit = 0;
START TRANSACTION;
UPDATE classroom SET capacity = 36 WHERE id = 1;
START TRANSACTION;
UPDATE student SET classroom_id = 1 WHERE id = 3;
학급의 정보를 변경하는 상황에서, 학생의 반을 변경해버리면 이렇게 계속 대기를 하게 된다.
학급을 변경하는 트랜잭션을 commit하면 이렇게 학생의 정보를 변경하는 트랜잭션도 끝난다.
외래키가 아닌 다른 칼럼을 변경한다면?
그러면, 부모키를 변경하려고 할 때만 이런 문제가 발생하는걸까?
START TRANSACTION;
UPDATE classroom SET capacity = 40 WHERE id = 1;
START TRANSACTION;
UPDATE student SET name ='동수' WHERE id = 3;
이 경우는 대기 없이 바로 변경이 됐다.
외래키를 변경할 때 주의를 해야 하는 상황이다.
자식테이블을 변경, 부모 레코드는 삭제?
CREATE TABLE classroom (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
floor INT NOT NULL,
capacity INT NOT NULL
);
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
grade INT NOT NULL,
classroom_id INT,
FOREIGN KEY (classroom_id)
REFERENCES classroom(id)
ON DELETE CASCADE -- 이 옵션이 중요!
);CASCADE 삭제 설정을 추가한다.
START TRANSACTION;
UPDATE student SET name = '김철수2' WHERE classroom_id = 1;
START TRANSACTION;
DELETE FROM classroom WHERE id = 1;
이 경우는 부모의 레코드를 삭제할 때 계속 대기가 됐다.
자식 레코드를 commit해줘야 대기가 끝났다.
외래키를 쓸 때는
1)부모 레코드를 변경하는 트랜잭션이 시작됐는데, 해당 부모 레코드를 외래키를 갖고 있는 자식 레코드에서 외래키를 변경할 때
2)자식 레코드를 변경중인데, 해당 자식 레코드가 외래키로 갖고 있는 부모 레코드를 삭제하려고 할 때
이 두가지 상황을 주의해야 하는 것으로 보인다.
외래키 공유락 문제
START TRANSACTION;
INSERT INTO student (name, classroom_id, grade)
VALUES ('새학생', 1, 1);
START TRANSACTION;
UPDATE classroom
SET capacity = capacity - 1
WHERE id = 1;이렇게 하면
대기가 발생한다.
첫번째 트랜잭션을 커밋하면 두번째 트랜잭션의 대기가 끝난다.
SELECT * FROM performance_schema.data_locks;이걸통해서 트랜잭션 상태를 볼 수 있다.
이 부분을 보면서 고민한게 보통 예약 시스템에서 이런 문제가 자주 발생할 거 같다. 예약 레코드를 만들고, 콘서트 상품 재고를 콘서트 내부 칼럼으로 하나씩 줄인다면 트랜잭션이 길어질 떄 이런 문제가 발생할 수 있는 것이다.
CREATE TABLE concert (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
available_seats INT -- 재고를 concert 테이블 내부 칼럼으로 관리
);
CREATE TABLE reservation (
id INT PRIMARY KEY AUTO_INCREMENT,
concert_id INT,
user_id INT,
FOREIGN KEY (concert_id) REFERENCES concert(id)
);이런 테이블이 있다고 해보자.
두개의 세션을 켜놓고
세션하나에서 위 쿼리를 실행하다가 commit을 안하면
다른 세션에서 위 쿼리를 실행하기 전에 계속 기다려야 한다.
커밋을 하면 정상적으로 쿼리가 실행됐다.
동시에 여러 이용자가 콘서트를 예매하려고 할 때 이 문제가 발생할 것으로 보인다.
테이블을 분리해보자
그렇다면, 예매 가능한 좌석을 따로 테이블로 분리하는 건 어떨까?
CREATE TABLE concert (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100)
);
CREATE TABLE concert_inventory (
id INT PRIMARY KEY AUTO_INCREMENT,
concert_id INT,
available_seats INT,
FOREIGN KEY (concert_id) REFERENCES concert(id)
);
CREATE TABLE reservation (
id INT PRIMARY KEY AUTO_INCREMENT,
concert_id INT,
user_id INT,
FOREIGN KEY (concert_id) REFERENCES concert(id)
);
START TRANSACTION;
UPDATE concert_inventory
SET available_seats = available_seats - 1
WHERE concert_id = 1
AND available_seats > 0;
-- affected rows 확인
SELECT ROW_COUNT() INTO @affected_rows;
INSERT INTO reservation (concert_id, user_id)
SELECT 1, 100
WHERE @affected_rows > 0;
이렇게 해봤는데 결국 한곳에서 커밋을 안하면 이때 사용되는 락이 해제가 안 되는 것으로 보인다.
똑같이 대기 문제가 발생했다.
지금 생각나는 해결방법으로는 트랜잭션 범위를 최소화하는 것 정도다...
우선 문제가 생기면 그 때 다시 해결해보자.
