[mysql] 데이터베이스 배타 락/레코드 락

DBMS에서 특정 데이터에 동시에 접근하는 경우에 무결성을 지키기 위해 해당 row 레벨에서 잠금을 걸 수 있다.

멀티 쓰레드 환경이나 멀티 프로세스에서도 동시성 제어를 하기 위해서 사용할 수 있는 방법인데 DB 차원에서 어떻게 동작하는 지 확인해보자.

두 명의 사용자(사용자 A, 사용자 B)가 하나의 테이블에 동시에 정보를 업데이트 하는 경우를 알아보자. (Mysql 8.0)

테스트를 위해 테이블(t_user)을 하나 생성하자.

배타락 테스트

CREATE TABLE t_user (
    id varchar(10) primary key,
    name varchar(10)
);

INSERT into t_user values('hong', '홍길동');
commit;

1. 사용자 A가 베타락을 건 상태에서 사용자 B가 조회를 하면

# 사용자 A 트랜잭션
SELECT * FROM t_user where id = 'hong' for update; # id = 'hong'인 row에 배타 락
UPDATE t_user set name = '김민수' WHERE id = 'hong'

# 사용자 B 트랜잭션
SELECT name FROM t_user where id = 'hong'; # '홍길동' 으로 표시 (아직 사용자 A가 commit하기 전 단계)

사용자 A	사용자 B	결과
SELECT * FROM t_user where id = 'hong' for update; UPDATE t_user set name = '김민수' WHERE id = 'hong'
	SELECT name FROM t_user where id = 'hong'; 사용자 A가 배타락을 걸었지만 mysql에서 잠금 없는 읽기가 지원된다. 즉 조회가 가능하다는 뜻이다. oracle도 동일하게 조회가 된다.	홍길동
commit;
	SELECT name FROM t_user where id = 'hong';	김민수

2. 사용자 A와 사용자 B가 모두 배타락을 걸면

사용자 A	사용자 B	결과
SELECT name FROM t_user where id = 'hong' for update;
	SELECT name FROM t_user where id = 'hong' for update;	사용자 B는 사용자 A가 commit/rollback하기 전까지 대기를 하게 된다.

mysql 레코드 락

일반적으로 row level 락은 테이블 레코드를 잠그는 락이다. 하지만 mysql에서의 레코드 락은 테이블 레코드를 잠그지 않고 인덱스의 레코드를 잠근다는 점이 다르다.

즉, select for update로 가져온 데이터에서 where 조건에 해당되는 데이터의 레코드를 인덱스를 통해 조회되는 모든 레코드를 모두 잠근다.

아래와 같이 테스트해보자.

CREATE TABLE t_user (
    id varchar(10) primary key,
    name varchar(10), 
    age int
);

CREATE INDEX idx_user_name ON t_user (name);

INSERT into t_user values('hong', '홍길동', 10);
INSERT into t_user values('kim1', '김길동1', 20);
INSERT into t_user values('kim2', '김길동2', 30);

commit;

여기서 select for update로 배타락을 걸어보자.

# [사용자 A] kim1인 사용자만 배타락이 걸릴 것으로 예상
SELECT * FROM t_user where name LIKE '김길동%' and age = 20 for update;

#  [사용자 B] 위와 상관없는 kim2에 select for update 실행하면 어떻게 될까?
SELECT * FROM t_user where id = 'kim2' for update;

결과는 사용자 B는 사용자 A의 트랜잭션이 끝날때까지 대기가 된다. 왜냐하면 사용자 A의 레코드에 락을 거는 게 아니라 사용자 A의 인덱스로 가져온 인덱스 자체에 락이 걸리기 때문에 kim1, kim2 레코드 모두에 락이 걸리는 것이다. 그래서 사용자 B가 kim2를 조회하면(select for update로) 대기가 걸리게 된다.