MySQL 잠금

InnoDB 기본잠금

• SELECT

  select * from employees where emp_no=100 lock in share mode;
  select * from employees where emp_no=100 for update ;
  

select 쿼리로 읽은 레코드를 잠그는 방법

• 읽기모드
• 쓰기모드

LOCK IN SHARE MODE

• 읽기 잠금만 걸기 때문에 잠금을 획득한 트랜잭션에서 변경할려면 쓰기 잠금을 다시 획득해야 한다.
• 다시 쓰기 잠금을 획득한다 -> 쓰기 잠금을 획득하기위해 기다려야할 수 도 있음
• 읽기 잠금을 가진 상태에서 다시 쓰기 잠금을 획득해야하는 상황은 데드락을 유발하는 가장 일반적인 형태

FOR UPDATE

• 읽은 후 변경도 해야한다면 for update 는 select후 쓰기 모드로 잠근다.

두 가지 잠금의 특징

Lock in share mode, for update같은 잠금 읽기 기능은 commit이나 rollback이 실행되면 잠금이 해제되므로 반드시 하나의 트랜잭션 에서만 유효하다

INSERT,UPDATE,DELETE

• 기본적으로 쓰기 잠금 획득
• InnoDB에서 Update,Delete문장을 실행할때 SQL문장이 조건에 일치하는 레코드를 찾기 위해 참조(스캔)하는 인덱스의 모든 레코드를 잠근다.
• 쿼리 문장에서 Where조건에 일치하지 않는 레코드도 잠금의 대상이 될 수 있다.

update student set last_name='...' where first_name="홍길동" and gender="M";
index -> ix_first_name으로 걸려 있음

• 위와 같은 테이블에서 해당 Update 쿼리를 날렸을 경우 first_name조건 때문에 1~6번 레코드 전체에 잠금이 걸리게 되지만 실제 변경되는 레코드는 4~6번이다. 이처럼 실제 쿼리가 반영되는 레코드와 잠금이 걸리게 되는 레코드가 차이가 날 수 있다.
• 작은 테이블이라고 인덱스 생성하지 않을때도 있는데 update,delete문장의 조건의 컬럼이 있다면 꼭 인덱스를 생성하라
• 만약에 인덱스가 걸려있지 않다면 테이블의 모든 레코드에 잠금을 걸어버리기 때문에 주의해야 한다.

SQL 문장별 잠금

• select … from …
  • 별도의 잠금을 걸지 않음
• select … from … lock in share mode
  • where절에 일치하는 레코드뿐만 아니라 검색을 위해 접근한 모든 레코드에 공유 넥스트 키락을 건다.
  • 만약 읽기 잠금을 걸어야 하는 레코드가 다른 트랜잭션에 의해 쓰기 잠금에 걸려 있다면 그 잠금이 해제될때까지 기다려야 한다.
  • 다른 트랜잭션에 의해 읽기 잠금이 걸려 있다면 읽기 잠금끼리는 상호 호환이 되므로 별도의 대기 없이 읽기 잠금을 획득할 수 있다.
• select … from … for update
  • for update옵션이 사용된 select 쿼리 문장도 where조건절에 일치하는 레코드를 검색하기 위해 접근한 모든 레코드에 대해 베타적 넥스트 키락을 건다.
  • 대상 레코드가 다른 트랜잭션에 의해 읽기 잠금이나 쓰기 잠금이 걸려 있다면 반드시 그 잠금이 해제될 때까지 대기 해야한다.
  • for update가 사용되면 일관된 읽기가 무시된다.

잠금간의 호환성

RDB Dead Lock 발생 경우

INSERT 쿼리의 잠금

• INSERT쿼리는 기본적으로 배타적 레코드 잠금을 사용한다.
• 해당 테이블에 프라이머리 키나 유니크 키가 존재한다면 중복 체크를 위해 공유 레코드 잠금을 먼저 획득해야한다.
• INSERT 쿼리는 인서트 인텐션 락을 사용한다.

Example)

CREATE TABLE tb_test(
pk INT NOT NULL
PRIMARY KEY(pk)
);

INSERT INTO tb_test VALUES (1),(6),(8),(9);

--// 트랜잭션 -1
START TRANSACTION
INSERT INTO tb_test VALUES(5);

--// 트랜잭션 -2
START TRANSACTION
INSERT INTO tb_test VALUES(3);

--// 트랜잭션 -3
START TRANSACTION
INSERT INTO tb_test VALUES(4);

tb_test 테이블에 4개의 레코드가 존재하고 새로운 레코드 3개를 각각 다른 트랜잭션에서 INSERT한다고 가정해본다.

인서트 인텐션 락이 없다면

InnoDB 는 프라이머리 키값 1부터 6사이에 새로운 프라이머리 키 값을 INSERT하기 위해 간격을 잠가야 하며 이때 배타적 갭 락을 사용한다. 하지만 배타적 갭락을 사용하면 위의 3개의 INSERT 쿼리는 서로 전혀 충돌되지 않는 값을 INSERT함에도 순차적으로 실행되야한다. 즉 다음과 같은 순서로 실행된다.

1. tb_test 테이블에서 pk컬럼 값이 1인 레코드부터 6인 레코드까지의 간격을 배타적 갭락으로 잠근다.
2. 새로운 프라이머리 키 값(3,4,5)를 INSERT한다.
3. 새로INSERT된 프라이머 키값 (3,4,5)에 대해 배타적 레코드 잠금을 한다.

위의 예제 3개의 트랜잭션은 각 작업이 끝나기 까지 기다려야 하므로 직렬화되어 동시에 실행 되지 못한다.

인서트 인텐션 락을 사용한다면

이런 문제점을 해결하기위해 InnoDB는 인서트 인텍션 락이라는 잠금방식(일종의 갭락)을 도입했다. 위의 예제에서 3개의 트랜잭션이 모두 1부터 6사이의 간격에 대한 인서트 인텐션 락을 동시에 획득하게 된다. 서로 충돌하는 값을 INSERT하지 않는 이상 동일 간격에 대해 서로 간섭 받지 않고 동시에 INSERT가 처리 될 수 있다.

인서트 쿼리를 실행하는 도중 프라이머리 키나 유니크 키와 같이 중복이 허용되지 않는 컬럼에 대해 중복된 값이 존재한다면 InnoDB는 반드시 기존의 중복된 레코드에 공유 레코드 락을 걸어야한다. 왜냐하면 중복 키 오류를 발생시킨 트랜잭션이 commit or rollback명령으로 종료될때까지는 중복된 값을 가진 레코드가 다른 트랜잭션에 의해 변경되거나 삭제되면 안 되기 때문이다. 그래서 중복키 오류가 발생하면 해당 레코드에 공유 잠금을 먼저 획득해야 되는 것이다.

이 과정으로 인해 데드락이 발생할 수 있다. 공유 잠금을 획득한 상태에서 다시 배타적 잠금을 획득해야 하는 경우 데드락의 원인이 된다.

참고

베타적 잠금(Execlusive-Lock,Write-lock,X-Lock): 해당 트랜잭션에서 레코드나 간격을 변경하기위해 획득해야 하는 잠금
공유 잠금(Shared-Lock,Read-Lock,S-Lock): 레코드나 그 간격을 읽을때 다른 트랜잭션이 변경하지 못하게 하는 용도의 잠금이다.

요약하면 배타적 잠금은 내가 쓰기를 하는 동안 남들이 쓰지 못하게 하는 것이며 공유 잠금은 내가 읽는 동안
남들이 내가 읽고 있는 데이터를 변경하거나 삭제하지 못하게 막는 장치이다.

프라이머리 키나 유니크 키가 존재하는 테이블에 INSERT를 수행할때 공유 잠금을 걸어야 하는 이유는
INSERT 를 전제로 한 읽기 작업중에 다른 트랜잭션에서 레코드를 변경하거나 삭제하면 일관성이 깨지기 때문이다.

공유잠금 획득 상태에서 다시 배타적 잠금을 필요로 하는 경우

Example)

--// 트랜잭션 -1
START TRANSACTION
INSERT INTO tb_test VALUES(1);

--// 트랜잭션 -2
START TRANSACTION
INSERT INTO tb_test VALUES(1);

--// 트랜잭션 -3
START TRANSACTION
INSERT INTO tb_test VALUES(1);

INSERT쿼리가 실행되면 프라이머리 키나 유니크 키에 대해서 중복 체크를 수행해야 한다.

각 트랜잭션 별로 순차적으로 INSERT 쿼리가 실행된 후 commit or rollback명령이 수행되지 않으면 다음과 같아진다.

1. 1번트랜잭션에서 pk가 1인 레코드에 대해 배타적 레코드 잠금을 갖는다.
2. 트랜잭션 2번,3번은 1번에서 점유 한 레코드 잠금 때문에 공유 잠금을 획득하기 위해 대기한다.

이때 2번과 3번 트랜잭션은 공유 레코드 잠금을 획득하기 위해 잠금 요청 큐에 요청을 한채 멈춰있는다. 이때 1번 트랜잭션에서 Rollback을 수행한다.

--// 트랜잭션 -1
ROLLBACK;

1. 트랜잭션1번이 가지고 있던 배타적 레코드 잠금이 해제
2. 2번과 3번은 프라이머리 키값이 1인 레코드에 대해 공유 레코드 잠금을 건다.
3. 2번과 3번은 동시에 프라이머리 키값이 1인 레코드가 없다는 것을 알고 바로 레코드를 INSERT하기 위해 배타적 잠금을 요청하게 된다.
4. 이때 2번과 3번 트랜잭션 모두 배타적 잠금을 획득하지는 못한다.
5. 이미 2번,3번 트랜잭션 모두 읽기 잠금을 갖고 있기 때문에 어느 트랜잭션으로도 배타적 잠금을 허용해줄 수 없다.

나머지 update,delete 유사함으로 생략

InnoDB에서 데드락 만들기

데드락이 발생하는 일반적인 경우: 공유잠금을 가진 상태에서 다시 배타적 잠금을 얻으려고 하는 잠금 업그레이드 상황에서 자주 발생한다.

데드락 패턴

패턴 1 (상호 거래)

트랜잭션 1번에서는 user_id=’A’에 대해 배타적 잠금을 가지고 있고 동시에 트랜잭션 2번은 user_id=’B’ 에 대해 배타적 잠금을 갖고 있다. 이 상태에서 다시 각자 상대방 트랜잭션에서 가지고 있는 레코드를 변경하기 위해 배타적 잠금을 요청하면 데드락이 발생한다.

해결방법: 테이블의 프라이머리 키인 user_id기준으로 처리해주면 된다.트랜잭션 1번은 예제 순서대로 진행하고 트랜잭션 2번에서 B사용자의 포인트 차감 UPDATE 문장보다. A사용자의 포인트 증가 UPDATE문장을 먼저 실행하면 된다. 그러면 잠금에 대한 대기는 발생해도 데드락은 걸리지 않는다.

패턴 2(유니크 인덱스 관련)

공유잠금과 배타적 잠금이 혼합된 형태, 이 패턴은 테이블에 프라이머리키 또는 유니크 키가 존재할때 발생할 수 있는 데드락이다.

위의 시나리오에서 트랜잭션1번이 롤백을 실행하기 직전까지는 프라이머리 키가 9인 레코드에 대해 배타적 잠금은 트랜잭션 1번이 가지고 있고, 트랜잭션 2번과 3번은 공유 레코드 잠금을 획득하기 위해 대기하고 있는 상태이다. 이 상태에서 트랜잭션 1번에서 롤백을 실행하면 프라이머리 키가 9인 레코드가 없어짐과 동시에 트랜잭션 1번이 걸었던 배타적 잠금이 해제된다. 이때 트랜잭션2번과 3번은 동시에 가상의 레코드(실제로 존재하진 않지만 프라이머리 키 값이 9인 레코드) 에 대해 공유 잠금을 획득하고 프라이머리 키가 9인 레코드가 없다는 사실을 알게된다. 그래서 트랜잭션 2번과 3번은 새로운 레코드를 INSERT하기 위해 배타적 잠금을 걸려고 한다.

• 트랜잭션 2번과 3번중에서 어느 트랜잭션이 먼저 배타적 잠금을 요청하느냐에 관계없이 둘 중 아무도 배타적 잠금을 걸지 못한다.
• 이미 트랜잭션 2번과 3번이 각자 공유 잠금을 가지고 있기 때문에 서로의 공유 잠금으로 인해 배타적 잠금을 걸지 못하고 서로 대기하게 된다.
• 이는 공유 잠금끼리는 호환이 되므로 트랜잭션 2번과 3번이 동시에 공유 잠금을 획득할 수 있지만 공유 잠금과 배타적 잠금은 서로 호환되지 못하기 때문에 발생하는 데드락이다.

해결책

• 완전히 해결할 수는 없지만 줄일 방법은 유니크 인덱스의 사용을 자제하는 것이다.

패턴3 (서로 다른 인덱스를 통한 잠금)

단 하나의 UPDATE 쿼리가 포함된 트랜잭션에서 데드락이 발생할 수 있다.

EXAMPLE)

CREATE TABLE tb_user(
	user_id INT NOT NULL,
	user_name VARCHAR(20) NOT NULL,
	user_status TINYINT NOT NULL,
	PRIMARY KEY (user_id)
	INDEX ix_status (user_status)
);

INSERT INTO tb_user VALUES (1,'A',0),(2,'b',1),(3,'c',1),(4,'d',1),(5,'A',0);

이 시나리오에서 각 트랜잭션이 변경하고 있는 조건은 다르지만 사실 두 업데이트 문장은 공통적으로 user_id=2인 회원 정보를 변경하고 있다.

트랜잭션 1번의 업데이트 문장은 정상적으로 실행됬고 트랜잭션 2번은 데드락에 걸린다.

이런 패턴의 데드락은 발생 빈도가 낮지만 각 트랜잭션에서 UPDATE 쿼리 하나씩만 실행하는 과정 중에도 데드락이 발생할 수 있다. 또한 단일 레코드의 UPDATE 문장이더라도 InnoDB내부적으로는 절대 단일 작업이 아니라는 점을 기억하자