Lock을 사용하는 SELECT

for update, for share

InnoDB 테이블에서는 레코드를 select 할 때 레코드에 아무런 잠금을 걸지 않는데, 이를 잠금 없는 읽기(Non Locking Consistent Read)라고 한다.

Consistent read란 read operation을 수행할 때, 현재 DB의 값이 아닌 특정 시점의 DB Snapshot을 읽어오는 것이다.

select query를 이용해서 레코드를 읽고 변경하는 경우 다른 트랜잭션에서 해당 레코드에 변경을 막아야 한다.

이럴 때는 레코드를 읽으면서 강제로 잠금을 걸어 둘 필요가 있는데, 아래는 이때 사용하는 옵션이다.

• for share : select query로 읽은 레코드에 대해서 읽기 잠금을 건다.

  • select 된 레코드에 대해 읽기 잠금(공유 잠금, Shared Lock)을 설정하고 다른 세션에서 해당 레코드를 변경하지 못하게 한다. 다른 세션에서 잠금이 걸린 레코드를 읽는 것은 가능하다.

• for update : select query로 읽은 레코드에 대해서 쓰기 잠금을 건다.

  • 쓰기 잠금(배타 잠금, Exclusive Lock)을 설정하고, 다른 트랜잭션에서는 그 레코드를 변경하는 것뿐만 아니라 읽기(for share 절을 사용하는 select query)도 수행할 수 없다.

  • 다른 세션에서 일반적인 잠금 없는 읽기는 가능하다.

한 가지 주의할 사항은 for update/for share 절을 가지지 않는 select query의 작동 방식이다.

위에서 언급했던, InnoDB의 테이블에서는 잠금 없는 읽기가 지원되기 때문에 특정 레코드가 ‘select … for update’ query에 의해 잠겨진 상태라도 단순 select query는 아무 대기 없이 실행된다.

일반 select query

잠금있는 select query

잠금 테이블 선택

위 쿼리는 사원 정보를 조회하는 select … for update query이다. query에서는 employees 테이블과 dept_emp, departments 테이블을 조인하여 읽으면서 for update 절을 사용한다.

그래서 InnoDB 엔진은 3개의 테이블에서 읽은 레코드에 대해 모두 쓰기 잠금(Exclusive Lock)을 걸게 된다.

🤔 나머지는 참고용으로 하고, 실제 쓰기 잠금은 employees 테이블에만 걸고 싶으면 어떻게 해야 할까?

MySQL 8.0 버전부터는 잠금을 걸 테이블을 선택할 수 있는 기능으로 개선됐다.

select *
from emplyees e
	inner join dept_emp de on de.emp_no=e.emp_no
	inner join departments d on d.dept_no=de.dept_no
where e.emp_no=10001
for update of e;

그래서 다음과 같이 for update 뒤에 “of table_name” 절을 추가하면 해당 테이블에 대해서만 잠금을 걸 수 있다.

• 테이블에 대해 별칭(Alias)을 사용한 경우에는 별칭을 명시해야 한다.

• for update/share 절 모두 적용할 수 있다.