SELECT FOR UPDATE의 권한 범위

SELECT FOR UPDATE는 테이블 내 레코드의 동시성 제어를 위해 사용할 수 있는 SQL 구문입니다. 하지만 SELECT FOR UPDATE를 사용했을 때, 락이 걸려있는 레코드에 대한 접근이 어디까지 제한되는지 직접 실습을 통해 확인해보고자 합니다.

 

1. 실습 환경 구성하기

먼저, 하나의 DBMS에 두 개의 세션 연결을 해야하며, 하나의 세션에서 트랜잭션을 시작하고, SELECT FOR UPDATE를 사용하면, 다른 세션에서 해당 레코드에 접근했을 때, 어디까지 접근이 되는지 확인해야 합니다. 이를 위해, 두 가지 방법을 사용할 수 있습니다.

1. MySQL Workbench를 활용하여, 두 개의 탭을 띄운다. (본 게시글에서는 1번을 사용할 것입니다.)
2. 하나는 MySQL Workbench를 활용하여 접근하고, 나머지 하나는 MySQL CLI(터미널)를 활용하여 새로운 세션으로 접근한다.

저는 MySQL Workbench 환경에서 진행하는 1번을 활용할 것입니다. 먼저, 테스트용 데이터베이스에 접근하겠습니다.

 

1) Workbench로 접속했을 때, 세션 확인하기

DBMS에 접근한 세션을 확인하는 용도로 CLI를 활용하였으며, show processlist로 세션들을 확인할 수 있습니다.

이제 MySQL Workbench로 접근해보겠습니다.

 

Workbench로 접속했을 경우, 두 개의 세션(64053, 64054)가 연결되었음을 알 수 있습니다.

 

이제 하나의 탭을 추가로 열어 DB에 접근해보겠습니다.

64097번과 64098번, 두 개의 세션이 추가되었음을 확인할 수 있습니다. Workbench를 통해 접속했을 때만 2개의 세션이 생성되는건지 확인해보기 위해, CLI로 접근했을 때의 세션도 확인해보겠습니다.

 

2) CLI로 접속했을 때의 세션 확인하기

Workbench와 달리 CLI로 접속했을 때는 세션이 1개만 생성됨을 확인할 수 있습니다! Workbench를 사용할 경우, 쿼리 실행 이외에도 기타 백그라운드 작업을 처리하기 위해 추가로 세션이 연결된게 아닐까하는 생각이 드네요.

 

아무튼! MySQL Workbench를 활용하면 두 개의 세션을 통해 동시성 제어 시나리오를 하나의 컴퓨터로 실행할 수 있습니다.

 

2. SELECT WHERE FOR UPDATE를 통해 권한 확인하기

1) SELECT 문

먼저, ID가 27번인 세션에서 특정 레코드에 대한 FOR UPDATE문을 사용하였습니다.

 

그리고, 31번인 세션에서 FOR UPDATE로 락이 걸린 레코드에 대해 SELECT문을 사용했을 때, 정상적으로 조회가 되었습니다. 이를 통해, FOR UPDATE로 락이 걸리더라도 조회는 가능하다는걸 알 수 있습니다.

 

2) UPDATE, DELETE 문

그렇다면, UPDATE와 DELETE문을 사용하면 어떻게 될까요?

UPDATE의 경우, FOR UPDATE로 인해 다른 세션으로부터 특정 레코드에 락이 걸려있기 때문에 수정을 할 수 없습니다. 만약, 27번 세션에서 COMMIT 혹은 ROLLBACK을 하게 되면, 정상적으로 UPDATE 쿼리가 실행됩니다.

 

DELETE의 경우에도 마찬가지로 쿼리가 나가지 않습니다. 이를 통해, FOR UPDATE 문은 다른 세션에서 특정 레코드에 대한 조회는 가능하지만 수정/삭제는 불가능하다는 걸 알 수 있습니다. 이처럼 다른 세션에서 쓰기 작업을 제한하는 락을 배타적 락이라고 합니다.