14.2.5 잠금 읽기 (SELECT ... FOR UPDATE 및 SELECT ... LOCK IN SHARE MODE)
데이터 쿼리를 실행하고 동일한 트랜잭션 내에서 관련 데이터를 삽입하거나 업데이트 할 경우 일반 SELECT 문에서 충분한 보호가 제공되지 않습니다. 다른 트랜잭션 쿼리가 실행 된 직후 동일한 행을 갱신하거나 삭제할 수 있습니다. InnoDB 는 추가의 안전성이 제공되는 두 가지 유형의 잠금 읽기 가 지원되고 있습니다.
SELECT ... LOCK IN SHARE MODE는 읽는 모든 행에 공유 모드 잠금을 설정합니다. 다른 세션도 그 행을 읽을 수 있지만 트랜잭션이 커밋 될 때까지 변경할 수 없습니다. 이러한 행 중 하나가 커밋되지 않은 다른 트랜잭션에 의해 변경된 경우, 쿼리는 트랜잭션이 끝날 때까지 기다렸다가 최신 값을 사용합니다.검색 인덱스 레코드가 발견되면
SELECT ... FOR UPDATE는 행과 관련된 모든 항목을 잠급니다. 이 동작은 이러한 행에UPDATE문을 발행 한 경우와 동일합니다. 다른 트랜잭션은 이러한 행의 갱신,SELECT ... LOCK IN SHARE MODE를 수행하거나 특정 트랜잭션 격리 수준에서 데이터의 읽기에서 차단됩니다. 일관성 독해는 읽은 뷰에있는 레코드에 설정된 잠금은 무시됩니다. (이전 버전의 레코드는 잠글 수 없습니다. 레코드 인 메모리 복사에서 Undo 로그 에 적용하여 재 구축됩니다.)
이 어구는 주로 단일 테이블 또는 여러 테이블로 분할 된 상태에서 트리 구조 또는 그래프 구조의 데이터를 처리 할 때 도움이됩니다. 에지 또는 트리 분기를 한 위치에서 다른 위치로 통과하고도 이러한 "포인터"다시 그 값을 변경할 수있는 권리를 보유하고 있습니다.
트랜잭션이 커밋 또는 롤백되면 LOCK IN SHARE MODE 및 FOR UPDATE 쿼리에 설정된 모든 잠금이 해제됩니다.
SELECT FOR UPDATE 를 사용하여 업데이트 할 행 잠금은 START TRANSACTION 에서 트랜잭션을 시작하거나 autocommit 을 0으로 설정하여 자동 커밋이 비활성화되어있는 경우에만 적용됩니다. 자동 위탁이 활성화되어있는 경우는 스펙과 일치하는 행이 잠겨 없습니다.
사용 예
child 테이블에 새 행을 삽입하고 아이 라인이 parent 테이블에 부모 행을 가지고 있는지 확인한다고 가정합니다. 응용 프로그램 코드를 사용하여이 작업 시퀀스 전체의 참조 무결성을 보장 할 수 있습니다.
첫째, 일관성 독해를 사용하여 PARENT 테이블에서 쿼리를 실행하고 부모 행이 있는지 확인합니다. CHILD 테이블에 아이 라인을 안전하게 삽입 할 수 있습니까? 모르는 사이에 다른 어떤 세션에서 SELECT 와 INSERT 사이에 부모의 행이 삭제 된 가능성도 있기 때문에 할 수 없습니다.
이러한 문제의 가능성을 방지하려면 LOCK IN SHARE MODE 를 사용하여 SELECT 를 실행합니다.
SELECT * FROM parent WHERE NAME = 'Jones'LOCK IN SHARE MODE;
LOCK IN SHARE MODE 쿼리에서 「Jones」 라는 부모가 반환되면 CHILD 테이블에 자식 레코드를 안전하게 추가 트랜잭션을 커밋 할 수 있습니다. PARENT 테이블의 응용 프로그램 행에서 읽거나 쓰려고 시도하는 트랜잭션은 사용자가 완료 될 때까지 (즉, 모든 테이블의 데이터가 일관성있는 상태가 될 때까지) 기다립니다.
또 다른 예는 CHILD 테이블에 추가 된 각 자에 고유 식별자를 할당 할 때 사용되는 CHILD_CODES 테이블의 정수 카운터 필드를 검토합니다. 일관성 읽기 또는 공유 모드 읽기를 사용하면 데이터베이스의 2 명의 사용자가 동일한 카운터 값을 참조 할 수 있으며, 2 개의 트랜잭션이 동일한 식별자를 가진 행을 CHILD 테이블에 추가하려고하면 중복 키 오류가 발생하기 때문에 카운터의 현재 값을 읽을 때는 사용하지 마십시오.
여기에서 2 명의 사용자가 카운터를 동시에 읽을 경우 적어도 1 명의 사용자가 카운터를 업데이트하려고하면 교착 상태가 발생하기 때문에 LOCK IN SHARE MODE 는 적절한 해결책이 없습니다.
카운터를 읽고 증가를 구현하려면 먼저 FOR UPDATE 를 사용하여 카운터의 잠금 읽기를 실행 한 후 카운터를 증가시킵니다. 예 :
SELECT counter_field FROM child_codes FOR UPDATE; UPDATE child_codes SET counter_field = counter_field + 1;
SELECT ... FOR UPDATE 는 사용 가능한 최신 데이터를 읽고 읽은 각 행에 배타적 잠금을 설정합니다. 따라서 검색된 SQL UPDATE 에 의해 행에 설정되는 경우와 같은 잠금이 설정됩니다.
위의 설명은 단순히 SELECT ... FOR UPDATE 가 어떻게 기능 하는지를 보여준 예입니다. MySQL에서는 테이블에 단일 액세스를 사용하는 것만으로 고유 식별자를 생성하는 특정 작업을 수행 할 수 있습니다.
UPDATE child_codes SET counter_field = LAST_INSERT_ID (counter_field + 1); SELECT LAST_INSERT_ID ();
이 SELECT 문은 단순히 (현재 연결 별) 식별자 정보를 취득 할뿐입니다. 어느 테이블에 액세스하지 않습니다.