MySQL InnoDB 잠금

MySQL InnoDB 잠금

데이터베이스의 격리수준 뿐만 아니라 동작의 종류에 따라서 잠금이 사용되기도 되지 않기도한다.  

 

DQL(Data Query Language)

• 일반적인 Select문으로 MVCC를 사용하여 잠금없는 읽기로 동시처리한다. 해당 쿼리문은 잠금이 필요없는 동작이므로 Lock 획득을 위한 대기가 필요없이 동작한다. 
• 스냅샷을 구성하여 읽으므로 repeatable read에서는 일관적인 데이터 읽기가 가능하다

 

DML(Data Manipulation Language)

• Insert, Update, Delete 그리고 Select for Share/Update (locking read라고 불림) 와 같은 동작은 MVCC 메커니즘이 아니라 잠금(Lock)이 관여한다.  상황에 따라 잠금 획득을 기다려야할 수도 있다. 
• MVCC를 통한 snapshot 기법이 아니므로 committed된 최신의 데이터를 접근한다(트랜잭션의 순서와 무관하게,unrepeatable할지라도).  때문에 repeatable read의 mvcc 메커니즘과 달리 데이터 읽기의 일관성이 붕괴될 수 있다.

 

잠금의 종류 : Shared Lock과 Exclusive Lock

• S락과 X락은 트랜잭션 간 Lock을 공유할 수 있느냐가 핵심개념

X Lock (Exclusive)	다른 트랜잭션의 S,X-lock 획득시도를 차단, 주로 쓰기작업
S Lock (Shared)	다른 트랜잭션의 X-lock 획득시도를 차단, S-lock 획득은 허용, 주로 읽기작업

• S락은 주로 읽는 동안 레코드의 수정이 발생하지 않게 잠그는데 사용됨.
  
  • 예시) Select for Share문, Duplicate-key error 등
  • 예시) Serializable 격리수준에서 transaction 상황시 Select 문을 Select for Share로 바꾸어 S락 획득
  • 일반적인 읽기는 MVCC기반의 '잠금없는 읽기'로 동작하므로 S락이 없어도 읽을 수 있으니 혼동하지말 것
• X락은 주로 쓰는 동안 레코드의 수정이 발생하지 않게 잠그는 데 사용됨.
  • 예시) 일반 CUD 동작, Select for Update문 

잠금의 대상 : Record Lock,  GapLock ,NextKey Lock

• Record Lock : InnoDB는 기본적으로 레코드 기반의 잠금방식을 채택하고 있음.
• GapLock : 레코드와 레코드 사이의 간격을 잠그는 갭락이 존재하며, 오로지 한 트랜잭션의 범위 조회간 Insert를 방지하기 위해서 사용. S-lock과 X-lock 모두 동일하게 동작한다. 
• NextKey Lock : 레코드락과 갭락이 합쳐진 형태로, 갭락은 혼자서 사용되지 않고 항상 넥스트키락 형태로 사용된다.  Phantom read를 방지하기 위한 목적의 락이다. 
  • InnoDB의 격리수준 Repeatable Read에서부터 적용됨
  • Index의 범위 스캔이 발생되는 locking read operations (SELECT ... FOR UPDATE or SELECT ... FOR SHARE) 와 write operations (UPDATE, DELETE)에서 사용됨.
    • LockingRead는 Insertion에서 Duplicate Key 체크, Delete/Update에서 대상 조회에서 사용된다. 
    • 높은 격리성수준에서의 범위 탐색의 locking read는 조심해서 사용해야한다는 결론 

• InsertIntention Lock :
  • 레코드가 아직 존재하지 않는 상태에서 트랜잭션의 레코드 삽입 예정 의도를 알리는 갭 잠금입니다.
  • 갭락이지만 서로 동일한 레코드에 대한 삽입이 아니라면 동시처리가 가능하며 배타적이지 않습니다.
  • InsertIntention Lock은 X-lock으로 승격하며, X-lock은 Insert-Intention과 공존할 수 없어서 두 트랜잭션이 동일한 InsertIntention Lock을 갖는다면 데드락이 발생합니다.
• Insert 시 잠금의 순서 : : (1) Insert Intention Lock (gap), (2) (Optionally) Next-Key Lock if there’s a range/phantom-protection scenario, (3) X-lock on the newly inserted record.

잠금의 지속기간

• DB의 Lock은 트랜잭션 내에서 쿼리가 시작되면 획득하고 트랜잭션이 commit/rollback으로 완료되었을때 주로 반납된다.

https://serverfault.com/questions/241823/setting-a-time-limit-for-a-transaction-in-mysql-innodb

 

인덱스와 잠금

• InnoDB에서 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 형식으로 처리된다. 
• '값1'이라는 인덱스를 찾아서 업데이트 하는 쿼리가 있다면 값1이라는 인덱스를 갖는 수많은 레코드가 모두 잠금이 걸리게 된다.
• 만약, 특정 값을 찾는데 인덱스가 걸려있지 않다면 풀스캔을 수행하게되고  모든 인덱스에 잠금이 걸리게된다.