MySQL - 12장. 쿼리 종류별 잠금

12.1. InnoDB의 기본 잠금 방식

• MySQL에서 사용가능한 스토리지 엔진 가운데 InnoDB를 제외한 스토리지는 테이블 잠금을 지원하기 때문에 쿼리가 사용하는 잠금이 복잡하지 않음
• InnoDB에서는 쿼리별로 사용하는 잠금이 달라서 복잡함

 

InnoDB에서 SELECT이 어떤 잠금을 필요로 할까?

• REPEATABLE-READ 이하의 격리 수준에서는 아무런 잠금을 사용하지 않음
• SERIALIZABLE 격리 수준에서는 기본적으로 LOCK IN SHARE MODE를 사용하기 때문에 읽기 잠금을 걸고 읽는다. 그렇기 떄문에 MySQL 서버의 성능이 떨어지게 된다.

잠그는 방법은 읽기 모드와 쓰기 모드 두가지가 있다.

SELECT * FROM employees WHERE emp_no=10001 LOCK IN SHARE MODE;
SELECT * FROM employees WHERE emp_no=10001 FOR UPDATE;

• LOCK IN SHARE MODE : 읽기 잠금만 걸기 때문에 같은 트랜잭션 내에서도 변경을 하려면 쓰기 잠금을 다시 획득해야 한다. 즉, 쓰기 잠금을 획득하기 위해 기다려야 하는데, 이게 일반적으로 데드락을 유발하는 형태이다.
• FOR UPDATE : 읽은 다음 변경까지 해야 한다면 쓰기 잠금을 선택하는게 좋다.

INSERT,UPDATE,DELETE

 

InnoDB에서 레코드를 잠그는 방식을 에제로 살펴보자.

UPDATE employees
SET last_name='...'
WHERE first_name='Georgi' AND gendor='F';

• WHERE 절에 해당되는 레코드만 잠근다(X) -> InnoDB 스토리지 엔진은 WHERE 절에 포함된 모든 조건이 아니라 인덱스를 사용할 수 있는 조건만 Mysql 엔진으로부터 전달받음
• 인덱스(first_name)로만 락을 잡기 때문에 아래와 같이 불필요하게 레코드가 잠기게 된다

• UPDATE,DELETE 문장이 제대로 된 인덱스를 사용하지 못하면 쿼리는 상당히 비효율적으로 실행되고 쿼리의 동시성도 상당히 나빠진다. -> SELECT 쿼리 튜닝 뿐만 아니라, UPDATE,DELETE 쿼리 튜닝도 꼭 필요한 작업이다.
• InnoDB 스토리지 엔진에서 인덱스는 빠른검색이나 정렬뿐만 아니라, 레코드 잠금 기준으로도 사용된다

12.2 SQL 문장별로 사용하는 잠금

SELECT .... FROM...

• SELECT 쿼리는 별도의 잠금을 사용하지 않는다
• 읽어야 할 데이터가 다른 트랜잭션에서 UPDATE, DELETE 중이라면 InnoDB에서 관리하고 있는 데이터의 변경 이력(undo log)를 이용해 레코드를 읽는다
• 그렇기 때문에 SELECT 시에는 별도의 대기가 없음
• DDL 문장으로 테이블이 변경되는 중이더라도 SELECT는 가능

SELECT .... FROM .... LOCK IN SHARE MODE

•  

12.3 InnoDB에서 Deadlock 만들기

InnoDB는 다른 DBMS와는 다르게 레코그 간의 간격을 잠그는 gap lock 이나 next key lock이 있다.순수한 레코드 레벨의 잠금만 사용하는 DBMS보다는 잠금의 범위가 넓은 편이라 데드락이 더 자주 발생하는 편이다.

 

패턴 1. 상호 거래 관련

A, B 사용자가 서로 동시에 10포인트를 보냈다고 가정해보자.

트랜잭션 1	트랜잭션 2
BEGIN;
	BEGIN;
UPDATE tb_user SET point_balance = point_balance-10 WHERE user_id='A';
	UPDATE tb_user SET point_balance = point_balance-10 WHERE user_id='B';
UPDATE tb_user SET point_balance = point_balance+10 WHERE user_id='B';
	UPDATE tb_user SET point_balance = point_balance+10 WHERE user_id='A';
====== 데드락 발생 지점 =========	====== 데드락 발생 지점 =========
COMMIT;
	COMMIT;

• 일반적인 애플리케이션 개발 : 차감 ->증가 이런식으로 개발됨 ( 빈번하진 않지만 데드락 가끔 발생 )
• 데드락을 피하는 방법 : 차감,증가 순서가 아니라 user_id 값을 기준으로 처리해주면 됨

패턴 2. 유니크 인덱스 관련

이번에 살펴볼 데드락은 공유 잠금과 배타적 잠금이 혼합된 상태이다.

트랜잭션 1	트랜잭션 2	트랜잭션 3
BEGIN;	BEGIN;	BEGIN;
INSERT INTO tb_test VALUES(9);
	INSERT INTO tb_test VALUES(9);
		INSERT INTO tb_test VALUES(9);
ROLLBACK;
-- 데드락 발생 지점 --

• 트랜잭션 1번이 ROLLBACK을 실행하기 직전까지는 primary key(9)에 대한 exclusive lock을 가지고 있음
• 트랜잭션 1번이 ROLLBACK을 실행하면 -> 2,3번은 동시에 가상의 레코드(실제로 존재하지 않지만 primary key 값이 9인 record )에 대해 shared lock을 획득
• 트랜잭션 2,3번이 INSERT 하기 위해 서로 exclusive lock을 잡으려 함
• 이미 트랜잭션 2,3번이 공유 잠금으로 인해 exclusive lock을 걸지 못하고 서로 대기하게 된다

이러한 종류의 패턴이 더 자주 발생한다. 이럴 때 최대한 유니크 인덱스의 사용을 자제해야 한다. 그렇다고 primary key까지 생략할 수는 없다. 이러한 데드락이 자주 발생한다면 아래와 같이 해보자.

• batch 프로그램이라면 실행 시간을 변경
• 웹 서비스라면 try, catch를 이용하여 MySQL 에러 번호나 SQL-STATE 값을 체크하고 데드락이 발생했는지 조사해야 함.

패턴 3. 외래키 관련

실제 DBMS에서 FOREIGN KEY를 생성해서 사용하는 시스템은 별로 경험한 적은 없다. 이번 예제는 FOREIGN KEY 때문에 잠금이 관련 테이블로 전파되면서 발생하는 deadlock에 대해 살펴보자

 

트랜잭션 1	트랜잭션 2
BEGIN;	BEGIN;
INSERT INTO tb_article VALUES (1,1); // (article_id, board_id ) // 게시물 삽입
	INSERT INTO tb_article VALUES (2,1); // 게시물 삽입
UPDATE tb_board SET article_count=article_count+1 WHERE board_id=1; // 게시판의 목록 테이블 업데이트 ( 게시글 수 )
	UPDATE tb_board SET article_count=article_count+1 WHERE board_id=1;
---- 데드락 발생 지점 ----

• 트랜잭션 1번 INSERT 문 실행 : tb_article 테이블에 article_id=1인 레코드에 exclusive lock 걸림 + tb_board 테이블의 board_id=1인 레코드가 shared lock이 걸림
• 트랜잭션 2번 INSERT 문 실행 : tb_article 테이블에 article_id=1인 레코드에 exclusive lock 걸림 + tb_board 테이블의 board_id=1인 레코드가 shared lock이 걸림
• 트랜잭션 1,2번이 UPDATE 문 실행을 하려 해도 tb_board에는 shared_lock을 각각 가지고 있기 때문에 서로 exclusive lock을 잡지 못함

2번 예제와 같은 현상이지만 차이점은 읽기 잠금이 사용자에 의해 걸린게 아니라 FOREIGN KEY에 의해 걸린 점이다.

해결방법은 INSERT와 UPDATE 순서를 반대로 실행해주면 해결이 된다. 하지만 tb_board 테이블에 lock contention(잠금 경합)이 높아지기 때문에 카운트를 증가시켜주는 UPDATE하는 쿼리를 일정 주기로 모아서 실행하는 것이 좋다.