2022-04-09-TIL

Today I Learned

DB Lock

락은 여러 트랜잭션이 같은 데이터에 동시에 접근할 때 일관성을 지키기 위한 장치다. MySQL InnoDB 문서를 기준으로 종류를 정리하면 다음과 같다.

• 공유 락(S)과 배타 락(X): S 락은 행을 읽기 위해, X 락은 행을 수정하거나 삭제하기 위해 잡는다. S 락끼리는 함께 잡을 수 있지만 X 락은 S, X 모두와 충돌한다.
• 의도 락(IS, IX): 테이블 수준 락으로, 트랜잭션이 곧 행 단위로 S 또는 X 락을 잡겠다는 표시다. 테이블 전체 락을 잡으려는 쪽이 모든 행을 확인하지 않고도 충돌을 판단하게 해 준다.
• 레코드 락: 인덱스 레코드에 거는 락이다. 인덱스가 없는 테이블도 InnoDB가 내부적으로 만드는 숨은 클러스터드 인덱스에 건다.
• 갭 락: 인덱스 레코드 사이의 간격, 또는 첫 레코드 앞이나 마지막 레코드 뒤의 간격에 거는 락이다. 그 간격에 새 행이 삽입되는 것을 막는 용도라서, 갭 락끼리는 서로 충돌하지 않는다.
• 넥스트 키 락: 레코드 락과 그 레코드 앞 간격의 갭 락을 합친 것이다.

InnoDB의 기본 격리 수준인 REPEATABLE READ에서는 검색과 인덱스 스캔에 넥스트 키 락을 사용해 팬텀 행을 막는다. 락이 인덱스에 걸리기 때문에, 조건 컬럼에 적절한 인덱스가 없으면 스캔한 모든 행에 락이 걸려 사실상 테이블 전체가 잠기는 것처럼 동작할 수 있다. 링크 중 클러스터드 인덱스 자료가 함께 있는 것도 이 때문이다. MySQL의 GET_LOCK() 같은 locking function은 행이 아니라 이름 문자열에 거는 사용자 수준 락으로, 여러 애플리케이션 인스턴스 사이의 분산 락처럼 쓸 수 있다.

• https://sabarada.tistory.com/121
• https://www.mysqltutorial.org/mysql-table-locking/
• https://dev.mysql.com/doc/refman/5.7/en/locking-functions.html
• https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
• https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/
• https://dev.mysql.com/doc/refman/8.0/en/binary-log.html
• https://www.ibm.com/docs/en/ias?topic=indexes-clustered-non-clustered
• https://www.geeksforgeeks.org/indexing-in-databases-set-1/
• https://www.toolbox.com/tech/sap/blogs/drawbacks-of-generated-surrogate-keys-041712/
• https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html
• https://jaeseongdev.github.io/development/2021/06/16/Lock%EC%9D%98-%EC%A2%85%EB%A5%98-(Shared-Lock,-Exclusive-Lock,-Record-Lock,-Gap-Lock,-Next-key-Lock)/

---

MySQL SELECT FOR UPDATE

SELECT ... FOR UPDATE는 조회한 행에 배타 락을 걸어, 트랜잭션이 끝날 때까지 다른 트랜잭션이 그 행을 수정하거나 FOR UPDATE로 읽지 못하게 한다. 재고 차감처럼 “읽고 판단한 뒤 수정”하는 사이에 다른 트랜잭션이 끼어들면 안 되는 경우에 쓴다. 일반 SELECT는 MVCC 스냅샷을 읽는 일관된 읽기라 락을 기다리지 않는다. 공유 락이 필요하면 FOR SHARE(8.0, 이전 문법은 LOCK IN SHARE MODE)를 쓴다. MySQL 8.0부터는 락을 기다리지 않고 바로 실패하는 NOWAIT와 잠긴 행을 건너뛰는 SKIP LOCKED 옵션도 있다.

START TRANSACTION;
SELECT stock FROM product WHERE id = 1 FOR UPDATE;
UPDATE product SET stock = stock - 1 WHERE id = 1;
COMMIT;

• https://jinhokwon.github.io/mysql/mysql-select-for-update/

---

트랜잭션 격리수준

격리 수준은 동시에 실행되는 트랜잭션이 서로의 변경을 얼마나 볼 수 있는지를 정한다. SQL 표준은 네 가지 수준과 각 수준에서 허용되는 이상 현상을 정의한다.

격리 수준	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생
SERIALIZABLE	없음	없음	없음

표준과 달리 MySQL InnoDB의 기본값인 REPEATABLE READ는 일관된 읽기에서는 MVCC 스냅샷으로, 잠금 읽기에서는 넥스트 키 락으로 팬텀을 대부분 막는다. Oracle과 PostgreSQL의 기본값은 READ COMMITTED다. 격리 수준을 높일수록 일관성은 좋아지지만 락 대기와 동시성 저하가 커진다.

• https://velog.io/@jsj3282/%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98-%EA%B2%A9%EB%A6%AC-%EC%88%98%EC%A4%80Isolatoin-Level

참고한 자료외부 출처 1

외부 출처

• InnoDB LockingMySQL 8.0 Reference Manual