12장 쿼리 종류별 잠금(1)

용어 정리

• 배타적 잠금 - 내가 쓰기를 하는 동안 남들이 쓰지 못하게 하는 것이다.
• 공유 잠금 - 내가 읽는 동안 남들이 내가 읽고 있는 데이터를 변경하거나 삭제하지 못하게 하는 것이다.
• 트랜잭션이란 논리적인 작업셋의 완전성을 보장하기 위한 기능이고, 잠금이란 동시성을 제어하기 위한 기능

InnoDB의 기본 잠금 방식

• InnoDB에서는 각 쿼리의 패턴별로 사용하는 잠금이 다르다.

https://balldev.tistory.com/58

트랜잭션 격리 수준

select

• REPEATABLE-READ 이하의 트랜잭션 격리 수준에서 InnoDB 테이블에 대한 SELECT 쿼리는 기본적으로 아무런 잠금도 사용하지 않는다.
• 또한 이미 잠긴 레코드를 읽는 것도 아무런 제약이 없다.

SELECT 쿼리로 읽은 레코드를 잠그는 방법은 읽기 모드와 쓰기 모드 잠금으로 두 가지

SELECT * FROM sample_tbl WHERE col1 = 10001 LOCK IN SHARE MODE;
SELECT * FROM sample_tbl WHERE col1 = 10001 FOR UPDATE;

• LOCK IN SHARE MODE는 읽기 잠금만 걸기 때문에 잠금을 획득한 트랜잭션에서도 변경하려면 쓰기 잠금을 다시 획득
• 그래서 변경이 같이 필요하다면 처음부터 FOR UPDATE를 거는 것이 좋다.
• 위 잠금 읽기는 COMMIT이나 ROLLBACK이 실행되면 잠금이 해제되므로 반드시 하나의 트랜잭션에서만 유효
• 만약 하나의 트랜잭션 안에서 두 가지 요청이 일어나는 경우, 첫 번째 요청에서 FOR UPDATE 등의 잠금이 걸린 채 과정이 종료되면 해당 커넥션은 대기 상태에 빠지는 위험
• 잠금 읽기는 항상 FINALLY 구문으로 트랜잭션의 종료를 보장

INSERT, UPDATE, DELETE

• InnoDB에서는 UPDATE, DELETE 문장으로 실행할 때 SQL 문장이 조건에 일치하는 레코드를 찾기 위해 참조하는 인덱스의 모든 레코드에 잠금을 건다.
• 이는 해당 쿼리의 WHERE 조건에 일치하지 않는 레코드라도 잠금의 대상이 될 수 있음을 의미
• 레코드에 잠금을 거는 주체는 InnoDB 스토리지 엔진이고, 업데이트할 레코드를 최종 결정하는 것은 MySQL 엔진인데, MySQL 엔진은 WHERE 절의 모든 조건이 아니라 인덱스를 사용할 수 있는 조건만 InnoDB 스토리지 엔진으로 전달하기 때문이다. 따라서 InnoDB 스토리지 엔진은 해당 인덱스의 모든 레코드에 잠금을 걸 수밖에 없다.
• UPDATE, DELETE 실행 시에도 인덱스의 생성을 고려 ⇒ UPDATE, DELETE 쿼리가 이용할 인덱스가 없다면 InnoDB 스토리지 엔진은 모든 레코드를 대상으로 잠금

테이블의 레코드 건수가 적어서 인덱스를 생성하지 않아도 된다"라는 생각 보다는 "테이블의 레코드가 적으므로 인덱스를 더 생성해도 무리가 가지 않을 것이다"라고 생각을 바꿔야 한다.

SQL 문장별 잠금

select

select - from

• 만약 읽어야 할 레코드가 다른 트랜잭션에 의해 변경되는 중이라면 언두 로그를 사용해 레코드를 읽는다.

select - from - lock in share mode

• WHERE 절에 일치하는 레코드뿐 아니라 검색을 위해 접근한 모든 레코드에 대해 공유 넥스트 키 락(Shared next-key lock)을 필요로한다.
• 읽기 잠금을 걸어야 하는 레코드가 쓰기 잠금이 걸려 있다면 해당 잠금이 풀릴 때까지 대기해야 하며, 읽기 잠금이 걸려있을 때는 상호 호환이 되므로 별도의 대기 없이 읽기 잠금을 획득할 수 있다.

select - from - for update

• WHERE 조건절에 일치하는 레코드를 검색하기 위해 접근한 모든 레코드에 대해 베타적 넥스트 키 락(Exclusive next-key lock)을 걸게 된다.
• 언두 로그를 통한 읽기가 불가능하기 때문에 일관된 읽기를 할 수 없다.

insert

• 배타적 레코드 잠금(쓰기 잠금)을 사용
• 유니크 키가 존재한다면 중복 체크를 위해 공유 레코드 잠금(읽기 잠금)을 먼저 획득
• 인설트 인텐션 락을 사용하기도 함
  • INSERT를 실행할 의도를 지닌 쿼리가 획득해야 하는 잠금
  • 인서트 인텐션 락끼리는 서로 호환되기 때문에 여러 트랜잭션이 동시에 해당 잠금을 획득
  • innodb에서는 innodb의 갭 락으로 인한 동시성 감소를 최소화 하기 위해 사용

insert into - on duplicate key update

• 중복된 키 값이 이미 있는지 판단하기 위해 공유 잠금을 걸어야 한다.
• 레코드가 존재한다면 배타적 잠금을 걸고 업데이트 수행, 없다면 인서트 인텐션 락을 걸고 insert 수행

replace

• 중복된 키 값이 이미 있는지 판단하기 위해 공유 잠금을 건다.
• 존재한다면 배타적 잠금을 걸고 레코드 삭제

insert into tb_new - select .. from tb_old

• tb_new 테이블에는 새로 INSERT되는 레코드에 배타적 레코드 락을 획득하고, tb_old 테이블의 대상 레코드에는 공유 넥스트 키 락을 설정한다.
• 넥스트 키 락으로 간격까지 잠그는 이유는 대상 레코드의 범위를 동결해 복제의 무결성을 보장해 주기 위함
• 이 방법은 다른 쿼리들에 영향을 주는 공유 잠금이 걸리기 때문에 두 개의 쿼리로 나눠서 실행하는 것이 좋다.

update, delete

• WHERE 절의 UPDATE, DELETE는 참조한 모든 레코드에 배타적 넥스트 키 락이 걸린다.
• 넥스트 키 락을 거는 이유는 WHERE절의 처리 범위를 고정하기 위해서이다.