infoqoch's 개발 블로그

db의 lock에 대하여

• • •

Share lock, S lock

  • 하나의 로우에 대한 락을 여러 트랜잭션이 소유할 수 있다. select만을 한다면 s lock은 특별한 제약이 없다.
  • 다만, 두 개 이상의 트랜잭션이 s lock을 가진 상태에서 그 누구도 해당 레코드에 대해 x lock을 가질 수 없다. 그러니까 갱신을 할 수 없다.
  • select ... lock in share mode, select ... for share 의 쿼리를 작성하거나, 격리수준을 SERIALIZABLE로 한다.

Exclusive lock, X lock

  • S lock은 동일한 레코드에 대하여 다수의 트랜잭션이 락을 소유하고 동시에 select할 수 있었다. x lock은 선점한 트랜잭션만 select 할 수 있다. select에 대하여 배타적인 권한을 행사한다.
  • select ... for update의 형태로 작성한다.

Intention lock IX, IS

  • Table-level lock
  • X lock과 S lock이 걸리면 해당 테이블에 각각 IX, IS 락을 건다.
  • IX, IS은 여러 트랜잭션이 가질 수 있다.
  • 테이블의 갱신을 block한다. alter table, drop table 등을 사용할 수 없다. 테이블 갱신을 하려면 모든 트랜잭션이 테이블 락을 모두 해제해야 한다.
  • 테이블의 갱신이 일어날 때, 그 누구도 IX, IS락을 가질 수 없다.

gap lock, range lock

  • lock이 하나의 레코드, 로우가 아닌 범위에 걸린다.
  • 해당 범위에 대해서는 다른 트랜잭션에서 갱신이 불가능하다. 데이터의 정합성을 보장하며 phantom read를 완전하게 방지한다.
  • READ COMMITTED은 NON-REPEATABLE-READ이므로 갭락을 지원하지 않는다. select을 범위로 지정한다 하더라도 각각의 레코드에 대해서만 락을 획득할 뿐이다. pk, uk 등을 위배하지 않는 한 어디든 insert 가능하다.
  • REPEATABLE READ의 경우 쿼리에 락을 명시하거나, 묵시적으로 락을 획득하는 SERIALIZABLE에서 갭락이 동작한다.
  • mysql의 경우 REPEATABLE READ에서 락을 획득하지 않더라도 팬텀 리드를 제한적으로 방지한다. 스냅샷을 통해 락 없이 REPEATABLE READ를 제공하기 때문이다.
  • 갭락의 범위는 index의 존재에 따라 다르게 적용된다.
    • index가 있을 경우, 해당 데이터를 탐색할 때 사용한 index의 범위에 대해서만 갭락이 걸린다.
    • index가 적용되지 않을 경우, 모든 레코드에 락이 걸린다. 범위는 nagative infinity 부터 positive infinity 이며 테이블 자체에 insert가 불가능하다.

예제 쿼리

-- 공통 REPEATABLE READ;  

-- record lock  테스트
-- 트랜잭션 A 
SELECT * FROM TESTS WHERE id = 'a1' FOR share; 

-- 트랜잭션 B 1차
UPDATE tests SET name = 'record_lock_kim' WHERE id = 'a1'; -- 교착상태. lock이 걸린 상태에서 write는 불가능.

-- 트랜잭션 B 1차
UPDATE tests SET name = 'record_lock_kim2' WHERE id = 'a2'; -- 가능


-- select 및 update statement, gap lock

-- share 테스트 
-- 트랜잭션 A 
SELECT * FROM TESTS FOR share; 

-- 트랜잭션 B
SELECT * FROM TESTS; -- 가능 
SELECT * FROM TESTS FOR share;  -- 가능
SELECT * FROM TESTS FOR update;  -- 교착상태. s락을 가진 트랜잭션이 있는 한, 다른 어떤 트랜잭션도 x락을 얻을 수 없다.
UPDATE tests SET name = 'share_kim' WHERE id = 'a1'; -- 교착상태. lock이 걸린 상태에서 write는 불가능.

-- update 테스트
-- 트랜잭션 A 
SELECT * FROM TESTS FOR update;

-- 트랜잭션 B
SELECT * FROM TESTS;  -- 가능
SELECT * FROM TESTS FOR share;  -- 교착상태. x락은 s락을 막는다.
UPDATE tests SET name = 'share_kim' WHERE id = 'a1'; -- 교착상태. lock이 걸린 상태에서 write는 불가능.

gap lock 의 필요성

  • 각각의 레코드가 아니라 조회한 범위에 대한 갱신의 제한이 필요한 경우가 있다.
-- READ COMMITED
select * from tb; -- 내부 로직으로 Van Gogh의 작품이 10~20에 있음을 확인한다.
-- insert into tb values(15, 'Lautrec'); -- 어떤 트랜잭션이 갑자기 데이터를 삽입한다.
udpate tb set printer = 'Van Gogh' where seq between 10 and 20; -- 15가 Van Gogh로 오변경 된다. 

-- REPEATABLE READ
select * from tb for share;
-- insert into tb values(15, 'Lautrec'); -- 교착상태. insert 자체가 불가능하다. 데드락으로 db가 인식하고 세션이 kill 된다.
udpate tb set printer = 'Van Gogh' where seq between 10 and 20; -- 첫 번째 쿼리가 DB의 가장 최신의 상태이며, 기대하는 대로 동작한다.

Next-Key Lock

  • record lock과 gap lock이 동시에 걸린 복잡한 상태를 의미한다.
  • 락이 걸린 범위에서의 record는 record lock이 걸리고 나머지에 대해서는 gap lock이 발생한다.
  • select * from test where seq > 10; 이며 데이터는 2, 5, 6, 8, 12 이 있다고 가정하면,
    • 2, 5, 6, 8은 각각 record lock이 걸린다.
    • negative infinity < gap lock < 12 까지 gap lock이 걸린다.

Insert Intention Lock

  • insert 구문을 사용할 때 암묵적(implicit)으로 획득하는 gap lock.
  • Insert Intention Lock -> X lock (record) 순서로 락이 걸린다. Insert Intention Lock은 같은 범위 내에서 다중 발행된다.
  • 기존의 gap lock을 사용할 경우 :
    • tx1 : insert seq = 10 시도 -> 비어있는 5 - 15 까지 gap lock이 발생한다.
    • tx2 : insert seq = 11 시도 -> gap lock 이 해방 되기를 기다린다.
  • Insert Intention Lock을 사용할 경우
    • tx1 : insert seq = 10 시도 -> 비어있는 5 - 15 까지 Insert Intention Lock 이 발생한다.
    • tx2 : insert seq = 11 시도 -> Insert Intention Lock 을 공동 소유하며 insert는 특별한 문제 없이 수행된다.

AUTO-INC Lock

  • 시퀀스 자동 생성과 관련한 락

https://www.letmecompile.com/mysql-innodb-lock-deadlock/ https://www.slideshare.net/billkarwin/innodb-locking-explained-with-stick-figures