[MySQL] Lock

Optimistic Lock (낙관적 잠금)

설명

• 개발자가 직접 Lock을 얻어 처리하는 것이 아니라, 단일 쿼리는 원자적으로 동작하는 성질을 이용하여 동시성을 제어하는 방식이다.
• 최종 업데이트 과정에서만 락을 점유하기 때문에 락 점유 시간을 최소화하여 동시성을 높일 수 있다.
• 조회 후 업데이트 과정에서 데드락이 발생할 수 있다.
• 예시

  mysql> UPDATE product
      -> SET quantity = quantity - 1
      -> WHERE productSeq = 1
      -> AND quantity > 0;
  Query OK, 1 row affected (0.01 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  

  mysql> INSERT INTO InvestmentHistory (productId, userId, investingAmount, investAt)
      -> SELECT 1, 2, 10000, NOW()
      -> FROM InvestmentProduct AS prod
      -> LEFT OUTER JOIN InvestmentHistory AS hist
      -> ON prod.productId = hist.productId
      -> WHERE prod.productId = 1
      -> GROUP BY prod.productId, prod.totalInvestingAmount
      -> HAVING SUM(hist.investingAmount) IS NULL OR SUM(hist.investingAmount) <= prod.totalInvestingAmount - 10000;
  Query OK, 1 row affected (0.01 sec)
  Records: 1  Duplicates: 0  Warnings: 0
  

Pessimistic Lock (비관적 잠금)

• 개발자가 직접 Lock을 얻어 처리하는 방식
• Lock 획득 후 종료시까지 다른 세션에서는 접근하지 못하기 때문에 자유롭게 작업을 진행할 수 있다.
• Lock 획득부터 해제시까지 오랜 시간 점유하기 때문에 동시성이 비교적 떨어질 수 있다.
• 비관적 잠금 방식은 종류가 많아 아래에서 따로 설명

Global Lock

설명

• 모든 테이블의 모든 레코드에 변경을 불가능하게 하는 락을 말한다.
• 서버에 미치는 영향이 크기 때문에 가급적 사용하지 않는 것이 좋다.
• mysqldump 같은 명령어에서 내부적으로 사용하기도 한다.

명령어

• 잠금

FLUSH TABLES WITH READ LOCK;

• 해제

UNLOCK TABLES;

Table-Level Lock

설명

• MySQL 테이블이 MyIASM 엔진을 사용할 경우 Table-Level Locking을 사용할 수 있다.
• 한 테이블에서 하나의 데이터를 활용중일 경우 모든 데이터에 접근 불가
• 테이블 전체를 잠그는 방식이기 때문에 다중 사용자 환경에서는 사용하지 않는 것이 좋다.

READ LOCK

• 읽기는 허용하고, 추가/수정/삭제를 허용하지 않는 락
• UNLOCK 하기 전까지 다른 세션에서 추가/수정/삭제 요청시 Blocking 상태로 대기하게 된다.
• READ LOCK은 동시에 여러 세션에서 LOCK 획득이 가능하다.
• session1

  mysql> LOCK TABLES user READ; // 락 획득
  Query OK, 0 rows affected (0.00 sec)
  

• session2

  mysql> SELECT * FROM user; // 조회 가능
  +---------+----------+
  | userSeq | userName |
  +---------+----------+
  |       1 | john     |
  |       2 | tom      |
  +---------+----------+
  

  mysql> INSERT INTO user (userName) VALUES ('jane'); // Blocking 상태로 대기
  

• session1

  mysql> UNLOCK TABLES; // 락 해제, session2에서의 INSERT 쿼리가 처리됨
  Query OK, 0 rows affected (0.00 sec)
  

WRITE LOCK

• 읽기/추가/수정/삭제 모두 허용하지 않는 락
• UNLOCK 하기 전까지 다른 세션에서 모든(읽기/추가/수정/삭제) 요청시 Blocking 상태로 대기하게 된다.
• WRITE LOCK은 한 세션에서만 LOCK 획득이 가능하다. (다른 세션에서 LOCK 획득 시도시 Blocking 상태로 대기)
• session1

  mysql> LOCK TABLES user WRITE;
  Query OK, 0 rows affected (0.00 sec)
  

• session2

  mysql> LOCK TABLES user WRITE; // Blockinig 상태로 대기
  

  mysql> SELECT * FROM user; // Blocking 상태로 대기
  

  mysql> INSERT INTO user (userName) VALUES ('jane'); // Blocking 상태로 대기
  

• session1

  mysql> UNLOCK TABLES; // 락 해제, session2에서 Blocking 되던 쿼리 수행 가능
  Query OK, 0 rows affected (0.00 sec)
  

Row-Level Lock

설명

• MySQL 테이블이 InnoDB 엔진을 사용할 경우 Row-Level Locking을 사용할 수 있다.
• 한 테이블 내 하나의 데이터를 활용중일 경우 다른 데이터에는 접근을 허용하는 방식이다.

LOCK IN SHARE MODE

• 읽기는 허용하고 추가/수정/삭제를 허용하지 않는 락
• 트랜잭션 시작 후 LOCK IN SHARE MODE 키워드를 붙여 조회하여 락을 획득한다.
• 트랜잭션이 종료되기 전까지 락을 점유한다.
• 조건 필드에 인덱스가 없을 경우 전체 테이블에 LOCK이 걸리므로 주의해야한다.
• session1

  mysql> START TRANSACTION; // 트랜잭션 시작
  Query OK, 0 rows affected (0.00 sec)
  

  mysql> SELECT * FROM user WHERE userSeq = 1 LOCK IN SHARE MODE; // 조회 및 LOCK 획득
  +---------+----------+
  | userSeq | userName |
  +---------+----------+
  |       1 | john     |
  +---------+----------+
  

• session2

  mysql> START TRANSACTION; // 트랜잭션 시작
  Query OK, 0 rows affected (0.00 sec)
  

  mysql> SELECT * FROM user WHERE userSeq = 1 LOCK IN SHARE MODE; // SELECT는 가능
  +---------+----------+
  | userSeq | userName |
  +---------+----------+
  |       1 | john     |
  +---------+----------+
  

  mysql> UPDATE user SET userName = 'john2' WHERE userSeq = 1; // UPDATE는 Blocking
  

• session3

  mysql> ROLLBACK; // 트랜잭션 해제, session2의 Blocking 해제되고 수정 처리됨
  Query OK, 0 rows affected (0.00 sec)
  

FOR UPDATE

• 읽기/추가/수정/삭제 모두를 허용하지 않는 락
• 트랜잭션 시작 후 FOR UPDATE 키워드를 붙여 조회하여 락을 획득한다.
• 트랜잭션이 종료되기 전까지 락을 점유한다.
• 조건 필드에 인덱스가 없을 경우 전체 테이블에 LOCK이 걸리므로 주의해야한다.
• session1

  mysql> START TRANSACTION; // 트랜잭션 시작
  Query OK, 0 rows affected (0.00 sec)
  

  mysql> SELECT * FROM user WHERE userSeq = 1 FOR UPDATE; // LOCK 획득
  +---------+----------+
  | userSeq | userName |
  +---------+----------+
  |       1 | john     |
  +---------+----------+
  

• session2

  mysql> START TRANSACTION; // 트랜잭션 시작
  Query OK, 0 rows affected (0.00 sec)
  

  mysql> SELECT * FROM user WHERE userSeq = 2 FOR UPDATE; // session1에서 Lock 획득 상태가 아니므로 조회 가능
  +---------+----------+
  | userSeq | userName |
  +---------+----------+
  |       2 | tom      |
  +---------+----------+
  

  mysql> SELECT * FROM user WHERE userSeq = 1 FOR UPDATE; // session1에서 Lock 획득 상태이므로 Blocking
  

• session1

  mysql> ROLLBACK; // 트랜잭션 종료, session2의 Blocking 해제되며 조회가 가능해짐
  Query OK, 0 rows affected (0.00 sec)
  

User-Level Lock

설명

• MySQL에서 지원하는 GET_LOCK(), RELEASE_LOCK()과 같은 함수를 활용하는 방식
• Distributed Lock 구현시 사용할 수 있다.
  • https://sg-choi.tistory.com/292#mysql을-활용하여-동시성-이슈-해결

주요 함수

GET_LOCK(str, timeout)

• 입력 받은 이름(str)으로 timeout 초 동안 잠금 획득을 시도. (음수 입력시 무한 대기)
• 결과값은 1(성공), 0(timeout 초과), null(에러 발생)
• 예시

  mysql> SELECT GET_LOCK('user', 5);
  +---------------------+
  | GET_LOCK('user', 5) |
  +---------------------+
  |                   1 |
  +---------------------+
  

RELEASE_LOCK(str)

• 입력 받은 이름(str)의 잠금을 해제
• 결과값은 1(성공), 0(현재 세션의 lock이 아님), null(lock 없음)
• 예시

  mysql> SELECT RELEASE_LOCK('user');
  +----------------------+
  | RELEASE_LOCK('user') |
  +----------------------+
  |                    1 |
  +----------------------+
  

RELEASE_ALL_LOCKS()

• 현재 세션의 모든 lock을 해제하고 해제한 lock 개수 반환
• 예시

  mysql> SELECT RELEASE_ALL_LOCKS();
  +---------------------+
  | RELEASE_ALL_LOCKS() |
  +---------------------+
  |                   1 |
  +---------------------+
  

IS_FREE_LOCK(str)

• 입력한 이름(str)에 해당하는 잠금이 획득 가능한지 확인.
• 결과값은 1(가능), 0(불가능), null(에러 발생)
• 예시

  mysql> SELECT IS_FREE_LOCK('user2');
  +-----------------------+
  | IS_FREE_LOCK('user2') |
  +-----------------------+
  |                     1 |
  +-----------------------+
  

IS_USED_LOCK(str)

• 입력한 이름(str)의 lock이 사용중인지 확인.
• 결과값은 lock 존재시 connection id 반환, 없으면 null 반환
• 예시

  mysql> SELECT IS_USED_LOCK('user');
  +----------------------+
  | IS_USED_LOCK('user') |
  +----------------------+
  |                   44 |
  +----------------------+
  

참고

• https://offbyone.tistory.com/225
• http://intomysql.blogspot.com/2010/12/pessimistic-optimistic.html?m=1
• https://woowabros.github.io/experience/2019/05/30/mysql-user-level-lock.html