[MySQL] LOCK TABLES 완벽 가이드 사용법(테이블 잠금)

MySQL을 사용하다 보면 동시에 여러 사용자가 같은 테이블을 수정하는 경우가 발생할 수 있다.
이때 데이터 무결성을 유지하고 경쟁 조건(Race Condition) 및 충돌을 방지하기 위해
특정 테이블을 잠그는(Lock) 기능이 필요하다.

MySQL에서는 LOCK TABLES 명령어를 사용하여 테이블을 읽기 또는 쓰기 전용으로 잠글 수 있다.
이 기능을 활용하면 트랜잭션이 없는 MyISAM 엔진에서도 동시 수정 문제를 방지할 수 있다.

1. LOCK TABLES란?

LOCK TABLES는 MySQL에서 특정 테이블을 잠궈 데이터 변경을 제어하는 명령어이다.
이를 통해 다른 세션(Session)에서 동일한 테이블에 대한 동시 접근을 제한할 수 있다.

기본 구문

LOCK TABLES 테이블명 [READ | WRITE];

• READ: 테이블을 읽기 전용으로 잠금
• WRITE: 테이블을 읽기 및 쓰기 모두 가능하도록 잠금

MySQL의 기본적인 동작 방식은 잠금 없이 다중 사용자가 테이블을 읽고 쓸 수 있도록 허용하는 것이다.
하지만 동시 수정이 필요한 경우 LOCK TABLES 명령어를 사용하면, 데이터 무결성을 보장할 수 있다.

반응형

2. LOCK TABLES가 필요한 경우

다음과 같은 경우 LOCK TABLES 명령어가 유용하게 사용된다.

1. 동시에 여러 사용자가 동일한 데이터를 수정하는 경우
2. MyISAM 엔진 사용 시 트랜잭션을 대체하는 방법이 필요할 때
3. 중요한 데이터를 안전하게 백업할 때
4. 대량의 데이터를 삽입, 삭제, 갱신할 때

3. LOCK TABLES 사용 예제

예제 1: 특정 테이블을 읽기 전용(READ)으로 잠금

LOCK TABLES employees READ;
SELECT * FROM employees;
UNLOCK TABLES;

• 위 명령어를 실행하면 employees 테이블이 읽기 전용으로 잠긴다.
• 다른 세션에서 쓰기(INSERT, UPDATE, DELETE) 작업을 시도하면 차단된다.
• UNLOCK TABLES; 명령어를 실행해야 다른 사용자가 테이블을 수정할 수 있다.

예제 2: 특정 테이블을 쓰기 전용(WRITE)으로 잠금

LOCK TABLES employees WRITE;
INSERT INTO employees (id, name, salary) VALUES (101, 'John Doe', 5000);
UPDATE employees SET salary = 6000 WHERE id = 101;
DELETE FROM employees WHERE id = 101;
UNLOCK TABLES;

• 위 코드에서 LOCK TABLES employees WRITE;를 실행하면
  해당 테이블에 대한 다른 사용자의 모든 읽기 및 쓰기 작업이 차단된다.
• UNLOCK TABLES;를 실행해야 다른 사용자가 테이블을 사용할 수 있다.

반응형

4. LOCK TABLES 실행 시 주의할 점

1. LOCK TABLES를 실행한 후에는 반드시 UNLOCK TABLES를 실행해야 한다.
   • UNLOCK TABLES;를 실행하지 않으면 테이블이 계속 잠긴 상태로 유지될 수 있다.
2. 트랜잭션과 함께 사용할 수 없다.
   • MySQL에서 LOCK TABLES는 트랜잭션을 자동으로 종료시키므로,
     트랜잭션 내에서는 LOCK TABLES를 사용할 수 없다.
   • InnoDB를 사용하는 경우 대신 SELECT ... FOR UPDATE를 사용하는 것이 좋다.
3. MyISAM 엔진을 사용할 때 유용하다.
   • InnoDB 엔진에서는 행(Row) 단위 잠금을 지원하므로 굳이 테이블 단위로 LOCK TABLES를 사용할 필요가 없다.
   • 하지만 MyISAM 엔진은 테이블 단위 잠금만 지원하기 때문에
     LOCK TABLES 명령어를 활용하면 동시 수정 문제를 방지할 수 있다.

5. 트랜잭션과 LOCK TABLES 비교

기능 비교	InnoDB 트랜잭션	MyISAM + LOCK TABLES
잠금 단위	행(Row) 단위 잠금	테이블 단위 잠금
성능	동시 처리가 가능하여 성능 우수	동시 처리에 제약이 있음
사용 목적	일반적인 CRUD 처리에 적합	단순한 대량 데이터 처리에 유용
트랜잭션 지원	지원	지원 안 함

• InnoDB를 사용할 경우 LOCK TABLES 대신 트랜잭션을 활용하는 것이 더 효과적이다.
• MyISAM을 사용할 경우 LOCK TABLES를 적절히 사용하여 동시 수정 문제를 해결해야 한다.

6. UNLOCK TABLES 사용법

LOCK TABLES 명령어로 테이블을 잠근 후,
작업이 끝나면 반드시 UNLOCK TABLES 명령어를 실행해야 한다.

예제 3: UNLOCK TABLES 사용법

LOCK TABLES employees WRITE;
INSERT INTO employees (id, name, salary) VALUES (102, 'Alice', 7000);
UNLOCK TABLES;

• LOCK TABLES 명령어를 실행한 후, UNLOCK TABLES;를 반드시 실행해야
  다른 세션에서 테이블을 사용할 수 있다.

반응형

7. LOCK TABLES를 대체할 수 있는 방법

InnoDB를 사용할 경우, SELECT ... FOR UPDATE 또는 SELECT ... LOCK IN SHARE MODE 를
사용하면 행 단위 잠금을 수행할 수 있다.

예제 4: SELECT ... FOR UPDATE 사용 (InnoDB 전용)

START TRANSACTION;
SELECT * FROM employees WHERE id = 102 FOR UPDATE;
UPDATE employees SET salary = 8000 WHERE id = 102;
COMMIT;

• SELECT ... FOR UPDATE를 사용하면 해당 행(Row)만 잠그기 때문에
  LOCK TABLES보다 성능이 뛰어나고 동시 처리가 가능하다.

8. 마무리

LOCK TABLES는 MySQL에서 테이블을 잠궈 동시 접근을 제어하는 중요한 기능이다.
특히 MyISAM 엔진을 사용할 경우, 테이블 단위의 잠금 기능이 필수적이며,
백업 작업이나 동시 수정이 필요한 경우 유용하게 활용할 수 있다.

하지만 InnoDB 엔진을 사용하는 경우에는
트랜잭션을 활용하여 행 단위 잠금을 수행하는 것이 더 효율적이다.

공식 가이드 문서

MySQL LOCK TABLES 공식 문서:
https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html