[Real MySQL] 12장 쿼리 종류별 잠금

12장 쿼리 종류별 잠금

12.1 InnoDB의 기본 잠금 방식

12.1.1 SELECT

• 레코드를 잠그는 방법

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

• 다른 커넥션의 트랜잭션에서 변경하지 못하게 막는 역할
• LOCK IN SHARE MODE는 읽기 잠금만 걸기 때문에 잠금을 획득한 트랜잭션에서도 변경하려면 쓰기 잠금 다시 획득 필요
• 읽기 잠금을 가진 상태에서 다시 쓰기 잠금을 획득하는 과정은 데드락을 유발하는 가장 일반적인 형태
• 잠금 해제
• COMMIT이나 ROLLBACK이 실행되면 잠금 해제
• 하나의 작업 단위가 여러 번의 사용자 요청으로 완료되는 프로그램에서는 읽기 잠금을 사용해서는 안됨
• TRY ~ FINALLY 구문으로 작성하여 FINALLY 구문에서 COMMIT 이나 ROLLBACK을 사용해 트랜잭션 종료가 보장되는 프로그램 작성 필요

 

12.1.2 INSERT, UPDATE, DELETE

• 기본적으로 쓰기 잠금을 사용, 필요 시에는 읽기 잠금 사용 가능
• AutoCommit 모드 : 자동으로 트랜잭션이 시작되고 종료
• AutoCommit 비활성 모드 : 실행과 함께 자동 트랜잭션 시작, 종료는 반드시 COMMIT이나 ROLLBACK 명령을 사용해 수동으로 종료
• AutoCommit, BEGIN이나 START TRANSACTION 명령으로 명시적 트랜잭션 : COMMIT이나 ROLLBACK 명령을 이용해 수동으로 트랜잭션을 종료
• InnoDB의 잠금 방식
• 인덱스를 사용할 수 있는 조건만 일치하면 모두 잠금 -> 잠금 대상 레코드가 변경 대상 레코드보다 범위가 크거나 같다
• 사용가능한 인덱스가 없다면, 테이블의 모든 레코드를 잠금

 

12.2 문장별로 사용하는 잠금

12.2.1 SELECT 쿼리의 잠금

• SELECT … FROM …
• 기본 형태의 SELECT 쿼리는 별도의 잠금 사용X
• 다른 트랜잭션에 의해 변경되거나 삭제되는 중이라면 언두 로그를 이용해 레코드 읽음
• DDL 문장으로 테이블 구조가 변경되는 중에도 처리 가능
• 트랜잭션의 격리 수준이 SERIALIZABLE인 경우에는 LOCK IN SHARE MODE 옵션이 자동으로 덧붙혀서 실행
• SELECT … FROM … LOCK IN SHARE MODE
• 검색을 위해 접근한 모든 레코드에 대해 공유 넥스트 키 락을 필요로 함
• 다른 트랜잭션에 의해 쓰기 잠금이 걸려 있다면 그 잠금이 해제될 때까지 대기
• 다른 트랜잭션에 의해 읽기 잠금이 걸려 있을 땐 대기 없이 잠금 획득 가능
• SELECT … FROM … FOR UPDATE
• 접근한 모든 레코드에 대해 배타적 넥스트 키 락
• 대상 레코드가 다른 트랜잭션에 의해 읽기 잠금이나 쓰기 잠금으로 사용되고 있다면 해제될 때까지 대기

 

12.2.2 INSERT 쿼리의 잠금

• 배타적 레코드 잠금 사용
• 해당 테이블에 p or u key 가 존재한다면 중복체크를 위해 공유 레코드 잠금을 먼저 획득 해야함
• 여러 트랜잭션이 동시에 인서트 인텐션 락(일종의 갭락) 획득 가능
• 참고 : Insert Intention Lock  Intention Lock
• Intention Lock : InnoDB 에서 MyISAM 테이블 락과의 호환을 위해 가지고 있는 잠금 / InnoDB 에서 사용되는 테이블 레벨의 잠금
• Insert Intention Lock :  동시 처리 가능하게 해주는 락? 밑에 설명 참고

 

CREATE TABLE tb_test123 (

fdpk INT NOT NULL,

PRIMARY KEY(fdpk)

);

 

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

 

BEGIN;  //  트랜잭션 시작

INSERT INTO tb_test123 VALUES(5);

 

BEGIN;

INSERT INTO tb_test123 VALUES (3);

 

BEGIN;

INSERT INTO tb_test123 VALUES (4);

 

• 인서트 인텐션 락이 없다면?
• 순차적으로 실행됨
• 1과 6사이의 간격을 배타적 갭락으로 잠금
• 새로운 p 값 345 삽입
• 새로운 p 값 345에 배타적 레코드 잠금
• 동시에 실행되지 못하고 순차적으로 실행
• 인서트 인텐션 락 사용하면?
• 1 6사이의 간격에 대한 인서트 인텐션 락을 동시에 획득
• 충돌하는 값 집어넣지 않으면 동시 처리 가능

 

주의 : 공유잠금을 가지고 있는 상태에서 다시 배타적 잠금을 걸게 되면 데드락 발생 할 수 도

[참고]  배타적 잠금 : 해당 트랜잭션에서 레코드나 간격을 변형하기 위해 획득하는 잠금

          공유 잠금 : 레코드나 간격을 읽을때 다른 트랜잭션이 변경하지 못하게 하는 용도의 잠금

 

12.2.3 UPDATE 쿼리의 잠금

• UPDATE … WHERE …
• 참조한 모든 레코드에 배타적 넥스트 키 락
• 다른 트랜잭션에 의해 처리 범위의 레코드가 영향을 받지 않게 하기 위해
• UPDATE tb_test1 a, tb_test2 b ON … SET a.column = b.column
• UPDATE 되는 칼럼이 포함된 모든 테이블의 레코드에는 배타적 넥스트 키 락
• 단순 참조 테이블에는 공유 넥스트 키 락

 

12.2.4 DELETE 쿼리의 잠금

• DELETE FROM … WHERE …
• 참조한 모든 레코드에 배타적 넥스트 키 락
• 복제에서 마스터와 슬레이브의 동기화를 유지하기 위해
• 일반적으로 넥스트 키 락을 설정하는 이유는 이 처리가 수행되는 동안 다른 트랜잭션에 의해 처리 범위의 레코드가 변경되지 않게 하는 데
• DELETE a FROM tb_test1 a, tb_test2 b …
• 최종적으로 DELETE 되는 레코드는 배타적 넥스트 키 락
• 단순 참조용으로만 사용되는 테이블에는 공유 넥스트 키 락

 

12.2.5 DDL 문장의 잠금

• CREATE TABLE tb_new … SELECT … FROM tb_old …
• 읽어오는 테이블은 읽기 잠금
• INSERT되는 레코드는 배타적 레코드 락
• DDL 명령은 쿼리가 완료됨과 동시에 트랜잭션도 자동으로 COMMIT
• RENAME TABLE tb_test TO tb_backup, tb_swap TO tb_test, …
• RENAME TABLE절에 명시된 모든 테이블에 대해 네임 락
• 네임 락은 테이블 수준의 잠금
• 테이블의 이름을 변경하는 동안 해당 테이블의 레코드를 읽거나 쓰는 트랜잭션은 모두 대기

 

12.2.6 InnoDB에서 여러 쿼리 패턴 간의 잠금 대기

 

CREATE TABLE empl (

emp_no INT NOT NULL,

last_name VARCHAR(16) DEFAULT NULL,

PRIMARY KEY (emp_no)

) ENGINE= INNODB;

INSERT INTO empl VALUES (120, 'Hello'), (130,'Hi'), (140, 'Bye');

 

12.3 InnoDB 에서 데드락 만들기

12.3.1 패턴 1 (상호 거래 관련)

• A 사용자가 B 사용자에게 10 포인트 전달하고, B 사용자도 A 사용자에게 10포인트 전달하는 시나리오
• 트랜잭션 1, 2번이 각각 user_id A, B 에 대해 배타적 잠금 -> 데드락
• p.727 표
• 해결책 : 업무 순서가 아니라 테이블의 프라이머리 키 기준으로 처리

 

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

• 시나리오
• 세 트랜잭션이 각각 프라이머리 키나 유니크 키를 가진 테이블에 같은 값으로 INSERT를 시도
• 첫 번째 트랜잭션이 그 값에 대해 배타적 잠금을 가짐
• 두, 세 번째 트랜잭션은 공유 레코드 잠금을 획득하기 위해 대기
• 첫 번째 트랜잭션이 ROLLBACK 실행 -> 배타적 잠금 해제
• 두, 세 번째 트랜잭션은 동시에 그 값에 대해 공유 잠금 획득 & 배타적 잠금 시도
• 공유 잠금이 걸려있기 때문에 어느 트랜잭션도 배타적 잠금 획득 불가
• 데드락
• 해결책
• 이 데드락이 발생한 프로그램이 배치 프로그램이라면 실행 시간을 변경
• 웹 서비스와 같은 OLTP 환경의 프로그램이라면 프로그램 코드에 데드락에 대한 핸들링 코드 추가
• try ~ catch문으로 SQLException을 이용해 데드락 발생 여부 조사

 

12.3.3 패턴 3 (외래키 관련)

• 시나리오
• 부모 테이블과 부모 테이블을 참조하는 자식 테이블 존재
• 두 트랜잭션이 각각 자식 테이블에 값을 입력하고, 부모 테이블의 같은 컬럼에 업데이트를 시도
• 자식 테이블에 값을 입력할 때, 참조된 부모 테이블의 공유 잠금을 획득
• 부모 테이블을 업데이트 하기위해 배타적 잠금을 요청하게 되고, 패턴2와 같이 공유 잠금이 걸린 테이블에 배타적 잠금 요청을 하며 데드락에 걸림
• 해결책
• INSERT와 UPDATE 순서를 반대로 실행해주면 해결
• 사용량이 많은 서비스에서는 UPDATE하는 쿼리가 너무 빈번하게 실행되기 때문에 잠금 경합이 상당히 높아질 가능성
• UPDATE 하는 쿼리는 일정 주기로 모아서 실행하는 것 추천

 

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

• 시나리오
• 인덱스가 있는 테이블에 하나의 트랜잭션은 인덱스를 이용하여 UPDATE를 시도
• 다른 하나의 트랜잭션은 프라이머리 키를 이용하여 같은 값에 UPDATE 시도
• 첫 번째 트랜잭션은 인덱스의 레코드 잠금
• 두 번째 트랜잭션은 프라이머리 키의 레코드 잠금
• 서로 서로의 잠금을 대기 -> 데드락
• 결론
• 이런 패턴의 데드락은 발생 빈도는 낮지만 쿼리 하나씩만 실행하는 과정에도 데드락 발생 가능성 존재
• 절대 데드락을 유발하지 않으리라 보장하기는 불가능
• 에러 코드를 감지해서 재처리하도록 프로그래밍하는 것 추천