[DB] Lock

 

<SQL BOOSTER>의 내용을 바탕으로 작성된 개인 공부를 위한 기록용 포스트입니다.

---

들어가기 전에

락(Lock)은 말 그대로 데이터에 잠금을 걸어 놓는 장치다. 데이터를 잠그면, 데이터를 잠근 세션 외에 다른 세션들은 잠긴 데이터에 접근할 수 없다. 데이터를 변경할 때 해당 로우에 락을 걸면 트랜잭션이 COMMIT 또는 ROLLBACK할 때까지 유지하는데 변경 락이 발생한 로우는 락을 생성한 트랜잭션만 변경 작업을 할 수 있다. 

 

선행 트랜잭션에서 변경 작업을 할 때 후행 트랜잭션이 대기 상태에 들어가는 것 또한 락에 의해서 발생된다. 첫 번째 세션에서 변경한 데이터에 락을 걸어서 두 번째 세션에서는 락이 풀리기 전까지 같은 데이터를 변경하지 못하고 대기 상태에 빠지게 것이다.

 

락을 사용했을 때의 문제점은 없을까? 변경 락이 많이 발생할수록 많은 세션이 대기 상태에 빠지고 그로 인해서 시스템이 느려진다거나 장애로 이어질 수 있다. 하지만 락이 없이는 동시적으로 여러 트랜잭션이 하나의 데이터에 접근할 때 데이터의 정확성이나 일관성을 확보할 수 없기 때문에 적절하게 락을 사용해야 한다.

 

 

Lock의 종류

1. Optimistic Lock

데이터 갱신 시 충돌이 발생하지 않을 것이라고 낙관적으로 보는 관점이기 때문에 우선적으로 락을 걸지 않는다. 

출처 : https://vladmihalcea.com/optimistic-vs-pessimistic-locking/

• Lock 대신 스냅샷 버전을 사용해서 관리한다.
• 스냅샷 버전 정보가 일치하지 않아서 충돌이 발생하면 ROLLBACK을 하여 업데이트 손실을 방지한다.
• 어플리케이션 레이어에서 락을 건다.

 

2. Pessimistic Lock

데이터 갱신 시 충돌이 발생할 것이라고 비관적으로 보는 관점이기 때문에 미리 잠금을 건다. 

출처 : https://vladmihalcea.com/optimistic-vs-pessimistic-locking/

• 데이터를 가져올 때 데이터베이스에서 락을 건다.
• 데이터의 무결성을 보장한다.
• 데드락의 위험성이 있다.
• 예) Shared Lock, Exclusive Lock

 

비관적 락을 사용하는 방식을 보기 위해 SELECT~FOR UPDATE에 대해 알아보겠다.

 

SELECT~FOR UPDATE

READ COMMITTED는 변경된 데이터에 로우 단위로 락을 생성하는데 락이 걸려있는 데이터를 조회하면 변경 이전의 데이터를 볼 수 있다. 이와 같은 특징은 처리 방법에 따라 업무적으로 잘못된 데이터가 발생할 수 있는데 이때 데이터의 일관성을 확보하기 위한 방법으로 SELECT~FOR UPDATE를 활용할 수 있다.

 

업무적으로 잘못된 데이터가 발생할 수 있다는 이야기는 뭘까? 만약 첫 번째 세션에서도 'ACC1' 계좌에서 잔액을 조회하고 4000원을 출금하려고 하고, 두 번째 세션에서도 'ACC1' 계좌의 잔액을 조회한 후에 똑같은 금액을 출금하려고 한다고 생각해보자. 참고로 'ACC1' 계좌의 잔액은 4000원이다.

 

첫 번째 세션에서 4000원을 출금하려고 하고 두 번째 세션도 똑같은 작업을 하려고 한다.

1. 두 번째 세션은 4000원을 조회한 후에 첫 번째 세션의 UPDATE 작업으로 인해 대기 상태에 빠질 것이다.

2. 첫 번째 세션에서 COMMIT을 하면 'ACC1' 계좌의 잔액은 0원이다.

3. 두 번째 세션은 처음에 조회된 금액이 4000원이었기 때문에 그대로 UPDATE 문을 실행하고 COMMIT을 한다.

 

이렇게 되면 두 번째 세션의 작업으로 인해 마이너스 잔액이 생성되고 만다. 이와 같은 상황을 막기 위해 SELECT~FOR UPDATE를 사용할 수 있다. SELECT~FOR UPDATE는 조회된 로우 데이터에 변경 락을 생성해서 다른 트랜잭션이 같은 로우를 변경하는 것을 막는다.

 

 

위에서 다뤘던 예제를 SELECT~FOR UPDATE로 해결하자.

-- 첫 번째 세션
SELECT T1.bal_amt FROM m_acc T1
WHERE T1.acc_no = 'ACC1'
FOR UPDATE; -- 4000원이 조회된다.

-- 두 번째 세션
SELECT T1.bal_amt FROM m_acc T1
WHERE T1.acc_no = 'ACC1'
FOR UPDATE;

두 번째 세션은 첫 번째 세션이 이미 해당 로우에 대해 접근을 했기 때문에 실행되지 못하고 대기 상태에 빠진다. 그리고 첫 번째 세션이 COMMIT된 후에 0원을 조회하게 되면서 전과 달리 마이너스 잔액을 만드는 실수를 하지 않게 된다. 이렇듯 SELECT~FOR UPDATE로 데이터 조회 시점부터 변경 락을 생성해서 데이터의 일관성을 확보하게 할 수 있는 것이다. 실제로 계좌 이체, 상품 매매, 재고 입출고와 같은 프로세스에 사용되는 기술이다.

 

하지만 조회 시점부터 변경 락이 발생하기 때문에 동시성을 떨어뜨리는 단점이 있다. 조회 시점에 발생한 락이 트랜잭션 종료 시점까지 유지되기 때문에, 락을 얼마나 빨리 해소해 주느냐에 따라 데이터베이스의 동시성이 좌우된다. 오라클에서는 SELECT~FOR UPDATE에 NOWAIT과 WAIT [SECONDS]와 같은 옵션을 지정할 수 있다. 이 옵션으로 세션의 대기 시간을 줄여서 동시성을 높이는 데 도움을 준다. 참고로 MySQL에서는 NOWAIT과 SKIP LOCKED를 지원한다.

 

옵션	설명
NOWAIT	대기 상태에 빠지자마자 예외가 발생한다.
WAIT SECONDS	대기할 초를 설정할 수 있다.

위의 옵션을 사용하면 예외가 발생했을 때 트랜잭션을 ROLLBACK 처리하고 빠져나오거나 재처리를 유도할 수 있어서 대기 시간을 줄일 수 있지만 대기 상태에서 빠져나오는 것이 업무적으로 타당한지에 대한 고민이 필요하다.

 

 

대기 상태

대부분 시스템은 데이터베이스 연결 및 접근에 폴링(Polling) 처리를 한다. 폴링은 WAS에서 특정 수만큼의 세션을 데이터베이스와 미리 연결해 놓고 여러 명의 사용자가 세션을 공유해 사용하는 방법이다. 

 

폴링을 사용하는 구조에서 연결된 세션이 모두 대기 상태에 빠지면 시스템은 더 이상 작동하지 않는다. 세션이 대기 상태에 빠지는 것은 느린 SQL과 종료(COMMIT or ROLLBACK)되지 않은 락에 의해서 발생한다. 제대로 종료되지 않은 트랜잭션은 세션이 무한정 대기 상태에 빠질 수 있어서 주의해야 한다.

 

만약 이런 쿼리문을 실행하고 COMMIT이나 ROLLBACK으로 종료하지 않는다면 어떤 일이 발생할까?

SELECT T1.* FROM m_acc T1 FOR UPDATE;

하나의 로우가 아닌 계좌정보 전체를 조회하면서 SELECT~FOR UPDATE를 사용하고 종료하지 않았으므로 해당 테이블에서 어떠한 작업도 수행할 수 없다. 계좌이체를 수행하는 세션이 모두 대기 상태에 빠지게 되기 때문이다. 이럴 때는 어쩔 수 없이 WAS나 DB를 재가동해야만 한다.

 

그래서 시스템의 동시성을 높이려면 불필요한 SELECT~FOR UPDATE 사용을 피하고, 트랜잭션은 항상 제대로 종료해야 한다. 그리고 트랜잭션은 최대한 빠르게 처리되도록 해야 한다.

 

 

DEAD-LOCK(교착상태)

데드락은 교착상태로 첫 번째 세션이 두 번째 세션의 작업이 끝나기를 기다리고 있고 두 번째 세션도 첫 번째 세션의 작업이 끝나기를 기다리는 상태다. 이처럼 서로가 기다리고 있으면 더는 작업을 진행할 수 없는데 이 상태를 교착상태라고 한다.

 

락에 의한 대기 상태와 데드락은 구분되는 다른 개념이다. 대기 상태는 데이터의 정확성을 위해 기다리는 정상적인 상태이지만, 데드락은 더는 트랜잭션을 진행할 수 없는 상태이다.

 

 

데드락이 발생하는 경우를 테스트해보자. 'ACC1' 계좌와 'ACC2' 계좌의 잔액을 모두 5000원으로 초기화했다.

-- ACC1, ACC2의 잔액 초기화
UPDATE m_acc SET bal_amt = 5000
WHERE acc_no IN ('ACC1', 'ACC2');

COMMIT;

 

첫 번째 세션은 'ACC1' 에서 'ACC2'로 2000원을 이체하고, 두 번째 세션은 'ACC2'에서 'ACC1'으로 3000원을 이체한다.

데드락 테스트 - 두 개의 세션에서 계좌이체 실행
첫 번째 세션	두 번째 세션
SELECT T1.bal_amt from m_acc T1 WHERE T1.acc_no = 'ACC1' FOR UPDATE;
UPDATE m_acc T1 SET T1.bal_amt = T1.bal_amt - 2000 WHERE T1.acc_no = 'ACC1';
	SELECT T1.bal_amt from m_acc T1 WHERE T1.acc_no = 'ACC2' FOR UPDATE;
	UPDATE m_acc T1 SET T1.bal_amt = T1.bal_amt - 3000 WHERE T1.acc_no = 'ACC2';

첫 번째 세션과 두 번째 세션 모두 우선 계좌이체를 위해 출금 계좌에서 잔액을 확인하고 원하는 만큼 출금했다. 그러면 이제 첫 번째 세션에서는 'ACC2'에 2000원을 입금하고, 두 번째 세션에서는 'ACC1'에 3000원을 입금할 차례이다.

 

첫 번째 세션	두 번째 세션
UPDATE m_acc T1 SET T1.bal_amt = T1.bal_amt + 2000 WHERE T1.acc_no = 'ACC2';
	UPDATE m_acc T1 SET T1.bal_amt = T1.bal_amt + 3000 WHERE T1.acc_no = 'ACC1';
SQL 오류: ORA-00060: deadlock detected while waiting for resource
ROLLBACK;
	ROLLBACK;

두 개의 세션 모두 원하는 로우에 이미 락이 걸려있기 때문에 대기 상태에 빠지게 되고 결국엔 데드락이 발생한다.

 

 

만약 이 로직에서 데드락을 피하려면 어떻게 해야 할까? 트랜잭션 시작 부분에서 'ACC1'과 'ACC2'에 동시에 락을 생성하면 된다.

데드락 피하기 - 두 개의 세션에서 계좌이체 실행
첫 번째 세션	두 번째 세션
SELECT T1.bal_amt from m_acc T1 WHERE T1.acc_no IN ('ACC1', 'ACC2') FOR UPDATE;
UPDATE m_acc T1 SET T1.bal_amt = T1.bal_amt - 2000 WHERE T1.acc_no = 'ACC1';
	SELECT T1.bal_amt from m_acc T1 WHERE T1.acc_no IN ('ACC2', 'ACC1') FOR UPDATE;
	UPDATE m_acc T1 SET T1.bal_amt = T1.bal_amt - 3000 WHERE T1.acc_no = 'ACC2';
COMMIT;
	-- 두 번째 세션 진행 --

첫 번째 세션의 1번 SQL을 보면 'ACC1'과 'ACC2'를 동시에 SELECT~FOR UPDATE하고 있다. 두 로우에 대한 변경 락을 동시에 소유하는 것이다. 그러면 두 번째 세션의 3번 SQL에서는 정상적으로 대기 상태에 빠지게 되고 서로 간에 교착상태에 빠지지 않게되는 것이다. 이와 같은 방법은 트랜잭션의 시작부터 락을 생성해서 동시성에 저하를 가져오는 단점이 있으나 데드락을 해결하기 위해 고려할 수 있다.

 

 

그외 DEAD-ROCK 해결 방법

• 트랜잭션의 진행 방향을 같은 방향으로 처리한다.
• 트랜잭션의 처리 속도를 최적화한다.
  • 대기하는 세션이 많아질수록 시스템은 점차 느려지므로 데이터 변경이 포함된 트랜잭션은 최적화가 필요하다. SQL 단위로 최적화가 필요하며 유사하게 반복 실행되는 SQL을 합쳐서 트랜잭션 길이를 최소화해야 한다.
    • 예) 'ACC1' 과 'ACC2'의 잔액 처리하는 SQL문을 하나의 SQL문으로 만들어서 처리하기
    • 모든 프로젝트에 적용하기 어려우나 적어도 시스템의 핵심 트랜잭션에는 이와 같은 노력이 필요하다.
  • 방어로직을 사용해서 데이터 조회 시점에 변경 락을 발생시키지 않는다.
    • 예) 계좌 잔액 조회 시 잔액을 변수에 저장하고 조회 때 저장된 잔액과 현재 잔액이 같을 때만 처리하기
  • 불필요한 트랜잭션의 분리
    • 외부 시스템과 연계하는 경우 별도의 트랜잭션으로 분리하는 것도 고려해야 한다.
• LOCK TIMEOUT을 이용하여 잠금해제 시간을 조절한다.

 

 

번외. MySQL InnoDB Lock의 종류

1. Exclusive Locks(배타 락; X Lock)

• 쓰기 락(Write Lock) - 읽기 연산과 쓰기 연산을 실행할 수 있다.
• 수정 작업이 이루어질 때 각 row에 걸리는 Lock
• X Lock이 걸려있으면 다른 트랜잭션은 S Lock, X Lock 모두 걸 수 없다.
• SELECT~FOR UPDATE로 Lock을 걸 수 있다.

 

2. Shared Locks(공유 락; S Lock)

• 다른 사용자가 동시에 읽을 수는 있지만 Update, Delete를 방지한다.
• 일반적인 SELECT문이 아닌 SELECT~LOCK IN SHARE MOCE 혹은 SELECT~FOR SHARE를 사용해 read 작업을 할 때 사용
• S Lock이 걸려있는 row에 다른 트랜잭션이 S Lock은 걸 수 있으나 X Lock은 걸 수 없다.

 

3. Record Lock(레코드 락)

• index에 걸리는 Lock
• 테이블에 index가 없다면 clustered index를 사용하여 락을 건다.

 

4. Gap Lock(갭 락)

• index record의 gap에 걸리는 Lock
• Gap이란 index recore가 없는 부분
• 조건에 해당하는 새로운 row가 추가되는 것을 방지하기 위한 것

 

 

 

참고

• MySQL 8.0의 공유 락과 배타 락
• [10분 테코톡] 마루의 데이터베이스 Lock
• [10분 테코톡] 오즈의 데이터베이스 Lock