[Database] MySQL InnoDB lock

MySQL InnoDB 엔진은 상황에 따른 여러가지 lock을 통해 동시성을 제어해준다.

트랜잭션이 획득한 lock들은 해당 트랜잭션이 commit or rollback될 때까지 유효하다.

종류

Shared lock (S)

row-level lock
select 위한 read lock
shared lock이 걸려 있는 동안 다른 트랜잭션이 해당 row에 대해
- X lock(exclusive lock) 획득 불가능
- S lock 획득 가능 (읽기는 가능)
select ... lock in share mode 사용 (MySQL 8.0부터는 select ... for share 사용 가능)

Exclusive lock (X)

row-level lock
update, delete 위한 write lock
exclusive lock이 걸려 있는 동안 다른 트랜잭션이 해당 row에 대해
- X lock 획득 불가능
- S lock 획득 불가능
select ... for update 사용

Intention lock (I)

table-level lock
테이블 내에서 나중에 어떤 row-level lock을 걸 것이라는 의도를 알려주기 위해 미리 table-level에 걸어두는 lock

select ... for share 사용 시,

먼저 table-level에 IS lock이 걸림
이 후 row-level에 S lock이 걸림

select ... for update 사용 시,

먼저 table-level에 IX lock이 걸림
이 후 row-level에 X lock이 걸림

IS, IX lock은 여러 트랜잭션에서 동시에 접근 가능하다. 그러나 alter table ... 등 table-level 동작이 실행될 때는 IS, IX를 모두 block하 는 table-level lock이 걸린다.

이렇게 table-level lock → row-level lock 두 단계를 거치는 이유는?

: row-level lock을 걸기 위해 table-level lock을 걸어, alter table과 같은 table-level의 동작을 방지할 수 있다.

다음은 각 lock이 각각 다른 트랜잭션에서 사용될 때 충돌(Conflict), 호환(Compatible)이 되는지에 대해 정리한 표이다.

	X	IX	S	IX
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IX	Conflict	Compatible	Compatible	Compatible

적용 범위에 따른 종류

Record lock

PK 혹은 unique index로 조회해서 하나의 인덱스 레코드에(row)만 lock을 거는 것을 의미한다.

ex) select * from t where id = 10 for update → id = 10인 레코드에 대해서만 X lock이 걸린다.

Gap lock

인덱스 레코드 사이의 범위에 lock을 거는 것을 의미한다.

gap lock을 통해 select 쿼리를 두번 실행했을 때, 그 사이에 다른 트랜잭션에서 데이터를 insert하더라도 같은 결과를 조회하는 것을 보장 할 수 있다. (Phantom Read 방지)

Next-key lock

범위를 지정한 쿼리를 실행하게 되면 실제로는 record lock과 gap lock이 복합적으로 사용된다.

범위에 해당하는 인덱스 레코드들에 gap lock 적용
인덱스 레코드들 하나하나에 record lock 적용

Insert Intention lock

insert 구문이 실행될 때 획득하는 특수한 형태의 gap lock이다.

여러 개의 트랜잭션들이 gap 안에 다른 위치에 insert를 동시 수행할 때 기다릴 필요가 없도록 하는 것이 목적이다.

만약 한 트랜잭션에서 특정 위치에 insert를 할 때 일정 범위의 insert intention lock을 획득한다면,

다른 트랜잭션에서 해당 범위 내에 다른 위치에 insert하면 대기 없이 바로 진행이 가능하다.

비관적 락 vs 낙관적 락

공부를 하다보니 비관적, 낙관적 락에 대한 내용이 있어서 이에 대한 내용을 간단하게 언급한다.

비관적 락

비관적 락은 Repeatable Read 또는 Serializable 정도의 격리성 수준을 제공한다.

비관적 락이란 트랜잭션이 시작될 때 S lock 혹은 X lock을 걸고 시작하는 방법이다.

+) 즉, 앞서 언급한 InnoDB의 lock들은 모두 비관적 락이라고 볼 수 있다.

낙관적 락

낙관적 락은 DB에서 제공해주는 특징을 이용하는 것이 아닌 애플리케이션 level에서 잡아주는 lock이다.

애플리케이션 자체적으로 version이라고 하는 데이터를 관리한다.

예를 들어, 같은 row(version 1)에 대해 각기 다른 2개의 update 요청이 있을 때,

먼저 1개가 update 됨에 따라 version이 2로 변경되는데,

나머지 update 요청은 아직 version 1 상태이기 때문에 반영되지 않게 된다.

정리

낙관적 락은 DB와 관련이 없는 lock이기 때문에 트랜잭션이 필요없다. 따라서 성능적으로 비관적 락보다 좋다.

하지만 트랜잭션을 사용하지 않기 때문에, 롤백이 발생했을 때 개발자가 직접 롤백처리를 해줘야 한다는 위험이 있다.

따라서 충돌이 많이 일어나지 않을 것으로 보이는 곳에는 낙관적 락을 통해 좋은 성능을,

이체 기능과 같은 돈이 걸린 민감한 기능에 대해서는 비관적 락을 사용하는 것이 좋다고 생각한다.

JPA에 적용

비관적 락을 Spring Data JPA에 적용해보았다.

@Lock 애노테이션과 LockModeType을 통해 다양한 lock의 형태를 제공한다.

먼저 PESSIMISTIC_READ 옵션이다.

public interface AccountRepository extends JpaRepository<Account, String> {
    @Lock(LockModeType.PESSIMISTIC_READ) // 비관적 락
    Optional<Account> findWithLockByAccountNumber(String accountNumber);
}

쿼리를 찍어보면 다음과 같다.

select
    account0_.account_number as account_1_0_,
    account0_.balance as balance5_0_,
    account0_.name as name6_0_,
from
    account account0_
where
    account0_.account_number=? lock in share mode

select ... share mode를 통해 S lock을 획득하는 것을 알 수 있다.

다음은 PESSIMISTIC_WRITE 옵션이다.

public interface AccountRepository extends JpaRepository<Account, String> {
    @Lock(LockModeType.PESSIMISTIC_WRITE) // 비관적 락
    Optional<Account> findWithLockByAccountNumber(String accountNumber);
}

쿼리를 찍어보면 다음과 같다.

select
    account0_.account_number as account_1_0_,
    account0_.balance as balance5_0_,
    account0_.name as name6_0_,
from
    account account0_
where
    account0_.account_number=? for update

select ... for update를 통해 X lock을 획득하는 것을 알 수 있다.

데드락

두 개 이상의 트랜잭션들이 동시에 진행될 때, 서로가 서로에 대한 락을 소유한 상태로 대기 상태에 빠져 더이상 진행하지 못하는 상황을 말 한다.

데드락이 발생하는 예시로, 계좌 이체를 하는 경우,

A → B
B → A

두 계좌가 서로 이체를 할 때, 1번 상황은 A lock, 2번 상황은 B lock을 획득한 상황에서 서로의 lock을 원하는 경우에 발생한다.

나는 서로의 계좌에 동시에 이체를 하는 경우가 적을 것이라 생각했고,

금액이 걸려있는 민감한 기능인만큼 데드락 발생 시 락을 계속 대기하는 것 보다,

롤백을 시켜 일시적인 오류로 인해 다시 이체하도록 하는 것이 안전하다고 생각했다.

일반적인 DBMS에서는 데드락 탐지 기능을 제공하기 때문에, 데드락이 발견되면 자동으로 해소시켜준다.

MySQL innoDB의 경우 데드락이 발생하면 하나의 트랜잭션을 롤백해 데드락을 해소하고 그 정보를 로그로 남겨준다.

DB에서 알아서 롤백을 해주기 때문에 문제가 없을 것으로 생각되지만,

이러한 과정이 너무 빈번하게 발생하는 것도 사용자에게 좋지 않은 경험을 준다.

따라서 데드락 해결은 DB에게 맡기고,

애플리케이션에서 데드락 발생 빈도를 최소화하는 방향으로 결정했다.

인덱스

더 적은 레코드를 스탠할수록 더 적은 락이 걸리기 때문에, 적절한 인덱스를 구성한다.

이체 서비스의 조회 쿼리를 참고해 적절한 인덱스를 걸어줬다.

@Entity
@Table(indexes = {@Index(name = "idx_usage_status", columnList = "usageStatus")})
public class Account {

조회 조건

사용여부(usageStatus), 유저아이디(userId)

+) MySQL은 외래키(userId)를 자동으로 인덱스로 생성해주기 때문에 따로 추가하지 않았다.

@Entity
@Table(indexes = {
    @Index(name = "idx_usage_status", columnList = "usageStatus"),
    @Index(name = "idx_withdrawal_account_number", columnList = "withdrawalAccount"),
    @Index(name = "idx_transfer_time", columnList = "transferTime")
})
public class Transfer {

조회조건

사용여부(usageStatus), 출금계좌(withdrawalAccountNumber), 이체시간(transferTime)

+) full scan을 최소화하기 위해 큰 범위에서 작은 범위 순으로 인덱스를 생성했다.

순차 lock

트랜잭션 안에서 여러 데이터를 수정할 때 발생하는 lock의 순서를 항상 순차적으로 만든다.

당행 이체 시에 두 개의 계좌를 수정해야 하는데, 이 때 항상 계좌번호 오름차순으로 lock을 획득하도록 설정해 lock이 꼬이는 상황을 최소 화했다.

<이체 서비스>

@Override
@Transactional
public TransferSaveResponseDto transfer(TransferSaveRequestDto requestDto) {
    Map<String, AccountInfo> accountInfoMap =
            inquiryAccountService.findTwoByAccountNumberForUpdate(requestDto.getWithdrawalAccountNumber(), requestDto.getDepositAccountNumber());
    AccountInfo withdrawalAccountInfo = accountInfoMap.get(requestDto.getWithdrawalAccountNumber());
    AccountInfo depositAccountInfo = accountInfoMap.get(requestDto.getDepositAccountNumber());
    ...
}

<계좌 조회 서비스>

public Map<String, AccountInfo> findTwoByAccountNumberForUpdate(String accountNumber1, String accountNumber2) {
    AccountInfo accountInfo1;
    AccountInfo accountInfo2;
    if (accountNumber1.compareTo(accountNumber2) < 0) {  // 데드락을 최소화하기 위해 계좌번호 오름차순으로 lock 적용
        accountInfo1 = findByAccountNumberForUpdate(accountNumber1);
        accountInfo2 = findByAccountNumberForUpdate(accountNumber2);
    } else {
        accountInfo2 = findByAccountNumberForUpdate(accountNumber2);
        accountInfo1 = findByAccountNumberForUpdate(accountNumber1);
    }
    Map<String, AccountInfo> accountInfoMap = new HashMap<>();
    accountInfoMap.put(accountNumber1, accountInfo1);
    accountInfoMap.put(accountNumber2, accountInfo2);
    return accountInfoMap;
}

참고자료

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

https://www.letmecompile.com/mysql-innodb-lock-deadlock/

https://sabarada.tistory.com/175

저작자표시 비영리 동일조건

'database' 카테고리의 다른 글

[Database] 트랜잭션 격리 수준 (0)	2023.12.24
PK: UUID vs Auto Increment (0)	2023.07.19
[Postgresql] PostGIS 설치 - MySQL이 아닌 PostgreSQL을 사용하는 이유 (0)	2023.01.11
H2 데이터베이스 설치 (0)	2021.02.10

종류