참조: Real MySQL(도서)

InnoDB의 잠금 방식

참고

Pessimistic locking(비관적 잠금)

현재 트랜잭션에서 변경하고자 하는 레코드에 대해 잠금을 획득하고 변경 작업을 처리하는 방식, 현재 변경하고자 하는 레코드를 다른 트랜잭션에서도 변경할수 있다라는 비관적인 가정을 하기 때문에 먼저 잠금을 획득한 것이다. 일반적으로 높은 동시성 처리에는 비관적 잠금이 유리하다고 알려져 있으며 InnoDB는 비관적 잠금방식을 채택하고 있다.

Optimistic locking(낙관적 잠금)

기본적으로 각 트랜잭션이 같은 레코드를 변경할 가능성이 희박할 것이라고 가정한다. 그래서 우선 변경 작업을 수행하고 마지막에 잠금 충돌이 있었는지 확인해 문제가 있었다면 ROLLBACK처리하는 방식을 말한다.

InnoDB의 잠금 종류

InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공하며, 잠금 정보가 상당히 작은 공간으로 관리 되기 때문에 레코드 락이 페이지 락으로 또는 테이블 락으로 레벨업되는 경우는 없다.

MySQL의 격리수준

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	발생하지 않음	발생	발생
REPEATABLE READ	발생하지 않음	발생하지 않음	발생(Inno_db는 발생x)
SERIALIZABLE	발생하지 않음	발생하지 않음	발생하지 않음

READ UNCOMMITTED

READ UNCOMMITTED 격리 수준에서는 각 트랜잭션에서의 변경 내용이 COMMIT이나 ROLLEBACK여부에 상관 없이 다른 트랜잭션에서 보여진다.

어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있게 되는 현상을 더티 리드(Dirty read)라 하고, 더티 리드가 허용되는 격리 수준이 READ UNCOMMITTED다. 더티 리드 현상은 데이터가 나타났다가 사라졌다 하는 현상을 초래하므로 애플리케이션 개발자와 사용자를 상당히 혼란스럽게 만든다. 또한 더티 리드를 유발하는 READ UNCOMMITTED는 RDBMS 표준에서는 트랜잭션의 격리 수준으로 인정하지 않을 정도로 정합성에 문제가 많은 격리 수준이다. MySQL을 사용한다면 최소한 READ COMMITTED 이상의 격리 수준이 권장된다.

READ COMMITTED

READ COMMITTED는 오라클 DBMS에서 기본적으로 사용되는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준이다. READ COMMITTED에서는 더티 리드와 같은 현상은 발생하지 않는다. 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문이다.

emp_no	first_name
499	홍길동
500	둘리

사용자A	테이블	사용자B
BEGIN
update set fist_name=’마이클’ where emp_no=500
	emp_no=500인 곳의 first_name이 둘리 -> 마이클로 변경
	undo영역에 변경 전 데이터 백업, emp_no=500 first_name=’둘리’
		select * where emp_no=500
		결과 1건 first_name=’둘리’
COMMIT
		select * where emp_no=500
		결과 1건 first_name=’마이클’

실제 테이블 값이 아닌 undo영역에 백업된 레코드에서 가져온다. READ COMMITTED격리 수준에서는 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 다른 트랜잭션에서 그러한 변경 내역을 조회할 수 없기 때문이다.

READ COMMITTED 격리 수준에서도 NON-REPEATABLE READ 라는 부정합 문제가 있다.

employees

emp_no	first_name
499	홍길동
500	둘리

사용자A	사용자B
	BEGIN
	select * where first_name=’마이클’
	->결과 없음
BEGIN
update set first_name=’마이클’ where emp_no=500
COMMIT
	select * where first_name=’마이클’
	->결과 1건

사용자 B가 begin명령으로 트랜잭션을 시작하고 first_name이 마이클인 사용자를 검색했는데 일치하는 결과가 없었다. 하지만 사용자 A가 사원번호 500인 사원의 이름을 마이클로 변경하고 커밋을 실행한 후, 사용자 B는 똑같은 select쿼리로 다시 조회하면 이번에는 결과가 1건 조회된다. 이는 사용자 B가 하나의 트랜잭션내에서 똑같은 select 쿼리를 실행했을 때 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성에 어긋하는 것이다.

REPEATABLE READ

REPEATABLE READ 는 MySQL의 InnoDB 스토리지 엔진에서 기본적으로 사용되는 격리 수준이다. 바이너리 로그를 가진 MySQL의 장비에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다. 이 격리 수준에서는 READ COMMITTED 격리 수준에서발생하는 NON-REPEATABLE READ 부정합이 발생하지 않는다. InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 언두(Undo)공간에 백업해두고 실제 레코드 값을 변경한다.

REPEATABLE READ와 READ COMMITTED의 차이는 언두영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하는지에 있다.

모든 InnoDB의 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가하는 값)를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있습니다. 그리고 언두 영역의 백업된 데이터는 InnoDB스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다. REPEATABLE READ 격리 수준에서는 MVCC를 보장하기 위해 실행중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 보호가 앞선 언두 영역의 데이터는 삭제할 수가 없다. 그렇다고 가장 오래된 트랜잭션 번호 이전의 트랜잭션에 의해 변경된 모든 언두 데이터가 필요한 것은 아니다. 정확하게는 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존돼야 하는 것이다.

모든 select 쿼리는 트랜잭션 번호가 자신의 트랜잭션번호 보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다.

TRX-ID : 트랜잭션 id

TRX-ID	emp_no	first_name
6	499	홍길동
6	500	둘리

사용자A	테이블	사용자B
		BEGIN TRX-ID : 10
		select * where emp_no=500
		결과 1건(둘리)
BEGIN TRX-ID : 12
update set first_name=’마이클’ where emp_no=500
	employees 테이블 emp_no=500 인 레코드 -> TRX-ID : 12 ,emp_no=500, first_name=’마이클’
	Undo영역 -> TRX-ID : 6 ,emp_no=500, first_name=’둘리’
COMMIT
		select * where emp_no=500
		변경전 데이터가 백업된 Undo 영역에 있는 결과(‘둘리’) 리턴

SERIALIZABLE

가장 단순하고 엄격한 격리 수준이다. 그만큼 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다.

InnoDB 테이블에서 기본적으로 순수한 select 작업은 아무런 레코드 잠금도 설정하지 않고 실행된다.

InnoDB 메뉴얼에서 자주 나타나는 “Non-locking consistent read(잠금이 필요없는 일관된 읽기)

라는 말이 이를 의미하는 말이다. 하지만 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면

읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지

못하게 된다. 즉, 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없는 것이다.

---