Oracle row lock: 없는데, 있어요

1) 서론

왠지 그럴 것 같은데... 정말 그런지는 잘 모르겠는 경험을 해보신 적 있으신가요?
 
저는 햄버거를 좋아하는데요. 사실 햄버거는 대부분 비슷한 구성을 가지고 있습니다. 빵, 고기 패티, 치즈, 약간의 야채들입니다. 종종 유명한 햄버거 가게를 방문해서 먹을 때에는 막연히 맛있다고 생각하면서 먹게 됩니다. 다른 버거와 비교해서 무엇이 맛있는지, 다른 점은 무엇인지 딱히 생각하지는 않습니다. 하지만 우연히 유튜브에서 사장님께서 햄버거에 어떤 철학을 가지고 있고, 열정을 담아 만드는 모습을 보게 되면 더 맛있었던 이유에 대해서 이해가 되고, 다시 한번 방문하고 싶어 집니다. 물론 자세히 알게 되니 특별할 게 없어서 멀리까지 찾아가지 않게 되기도 합니다.
 
개발을 할 때에도 비슷한 것 같습니다. 특정 기술에 대해서 막연히 "그렇게 동작하더라"라고 생각하는 것과 실제로 어떻게 동작하는지 확인하고 이해하게 되는 것은 느끼는 바가 다릅니다. 실제 동작을 자세히 알게 되면 사용하지 말아야 하거나 주의해야 될 부분에 대해서 조심할 수 있는데요.
 
이번 오라클 DB의 insert에서 발생한 row lock으로 인한 에러의 트러블슈팅 과정을 작성합니다.
 

---

2) 상황

오라클 DB 테이블에 여러 트랜잭션에서 접근하여 특정 값을 증가시켜야 하는 동작이 있었습니다.
 
여러 트랜잭션에서 접근 시도하여 동시성 문제가 발생할 수 있기 때문에 row lock이 사용되고 있었는데요. Select for update 쿼리를 사용했습니다. 그리고 없다면 insert, 있다면 값을 증가 후 재시도 혹은 시도하지 않는 동작으로 되어 있습니다.
 
테스트에 사용되는 버전은 오라클 21C입니다. BOOK 테이블에는 도서 정보가 들어가 있습니다. 코드를 작성하지 않고 데이터그립에서 쿼리를 이용해서 테스트합니다.

 
아래의 흐름으로 존재하지 않는 '피자 만들기'라는 책을 DB에 적재하려고 합니다.

 
DB --> TX2로 응답 중 마지막 부분을 보면 ORA-00001: unique constraint violated 에러가 발생했습니다.
 
의도대로라면 TX1의 SELECT FOR UPDATE 쿼리에서 lock을 획득하고, TX2에서는 lock 획득을 하지 못했으니 다른 동작을 할 수 없고 대기할 것으로 예상했습니다. 그리고 TX1에서 이미 데이터가 적재되었기 때문에 TX2에서는 '피자 만들기'를 응답받고 무시하게 될 것으로 예상했습니다.
 
하지만 실제로는 로직이 순차적으로 수행되고 unique 제약조건을 위반했다는 에러를 받게 되었습니다. 즉 다수의 트랜잭션이 동시에 접근할 때 한 개의 트랜잭션만 성공하고, 나머지는 모두 오류 응답을 받게 된다는 것입니다.

---

2) 왜 TX2 SELECT FOR UPDATE는 lock 획득까지 기다리지 않을까요?

TX1에서 SELECT FOR UPDATE를 이용해서 lock을 먼저 잡았다고 생각했는데요. TX2는 왜 기다리지 않고 insert까지 수행하게 되었을까요?
 
오라클에서는 변경 전 데이터를 undo segment에 기록하고, 변경할 데이터를 row가 저장된 공간의 buffer에 넣게 됩니다. TX1에서 '피자 만들기' 데이터를 insert 하더라도 commit or rollback 되지 않았기 때문에 TX2에서는 undo segment를 읽고 0으로 응답할 수 밖에 없습니다.
 
오라클에서 row lock을 시도하면 어떤 동작들이 수행될까요? 오라클에서는 데이터베이스 block의 header에 row의 lock 정보가 변경되었을 때 기록합니다. 하지만 block header에 실제로 모든 정보를 기록하지는 않습니다.
 
동일한 block에 시도된 lock 획득에 대한 트랜잭션들은 LinkedList 형태로 관리되는데요. 해당 트랜잭션의 정보들은 ITL(Interested Transaction List)라고 불리는 공간에 기록됩니다. 그리고 각 트랜잭션마다 ITL slot을 할당받게 되는데요. Block header는 각 ITL slot 주소들만 가지고 있습니다.
 
아래 예시에서 Lck 1로 기록된 트랜잭션이 현재 lock을 잡고 있는것을 볼 수 있습니다.

각각의 ITL 정보들

ITL slots

 
여기서 문제는 데이터가 존재하지 않는 row에 대해서 SELECT FOR UPDATE로 lock 획득 시도하게 되면 slot이 할당되지 않습니다. 당연히 row가 존재하지 않기 때문에 기록할 lock 정보도 없습니다. 즉, 두 트랜잭션 모두 lock을 획득하지 못한 상태로 단순 select만 하게 되었습니다.

---

3) 왜 TX2 INSERT 쿼리는 수행되지 않고 응답을 대기할까요?

위에서 본 것처럼 두 트랜잭션이 lock을 획득하지 않은 것은 확실합니다. 그런데 TX2의 insert 쿼리는 왜 수행되지 않고 대기를 하게 될까요? 실제로 트랜잭션을 commit 하지도 않았는데, 명령어가 수행되지 않을 이유는 무엇이 있을까요?
 
ITL slot은 특정 block의 lock 정보가 변경될 때 기록됩니다. Lock 정보가 변경된다는 것은 새로운 데이터가 insert 되어서 block에 추가 space를 할당하거나, 존재하는 row에 대해서 변경을 위한 lock 획득할 때입니다.
 
위의 다이어그램에서 TX1에서 SELECT FOR UPDATE 할 때에는 변경될 데이터의 lock 정보가 없었지만, insert 수행하게 되면 새로운 데이터를 위해서 block space 필요하고 slot을 할당받게 됩니다.
 
마찬가지로 TX2에서 '피자 만들기' 동일한 값으로 insert 하면서 slot을 할당받으려고 합니다. 이때 오라클에서는 unique key 조건에 대해서 insert가 발생하면 다른 slot을 확인하여 동일한 변경 중인지 확인하는데요. TX1에서 아직 commit or rollback 동작을 하지 않은 active 상태이기 때문에 판단할 수 없고, 대기합니다.
 
이후 TX1에서 commit 하고 row를 기록합니다. TX2에서는 TX1 slot이 inactive 상태인 것을 확인하고, commit 하는데요. 하지만 이미 TX1이 '피자 만들기' 데이터를 적재했으므로 에러를 응답받게 됩니다.
 
즉 오라클에서는 row lock 관리를 위해서 일련의 정보를 기록합니다. Update 뿐만 아니라 insert를 할 때에도 row lock이 반드시 발생됩니다. 그리고 unique 조건에 대해서는 실제로 insert 하기 전 수행 중인 트랜잭션의 slot을 확인해서 중복 저장을 방지하고 있습니다.

---

4) 결론

특정 데이터가 없다면 적재, 있다면 증가와 같은 로직을 작성할 때에는 동시성 문제를 해결하기 위해서 lock을 걸고는 합니다. 특히 DB 데이터처럼 절대적으로 isolation을 지켜야 할 때에는 성능적인 부분을 희생하면서 row lock을 획득하는 합니다.
 
하지만 모든 상황에서 일단 SELECT FOR UPATE를 사용하면 안전할 것이라는 생각을 한다면, 이 글에서와 같이 예상치 못 한 오류를 발생시킬 수 있습니다.
 
Insert 상황에서 사용하게 된다면 아무런 도움이 되지 않을 것이고, 중복 조건 위반에 대한 오류가 발생하면 retry 혹은 fallback 등 적절히 handling 되어야 할 것입니다.

---

5) 참고 문헌

• proligence: https://www.proligence.com/itl_waits_demystified.html
• How Oracle Locking Works: https://arup.blogspot.com/2011/01/how-oracle-locking-works.html