들어가기에 앞서 트랜잭션과 락을 공부함에 있어서 확신을 가질 수 없었다.
분명 오라클에서는 read-commited 와 MVCC를 통한 해당 데이터의 정합성을 보장하는 것과 다르게 Lock이 걸려서 이해할 수가 없었다.
기존에 MVCC 라는 읽기시에 락을 걸지 않고 빠른 속도를 제공하기 때문에 락이 걸릴 이유가 없기 때문이다. MVCC 에 대해서 좀 더 공부해본 결과 특정 UPDATE/DELETE 의 경우 MVCC를 생성하지 않는다고 한다. 이는 데이터가 U/D 를 실행할 때 마다 MVCC를 생성하게 되면 snapsoot too old처럼 스냅샷이 비대해 질 뿐만 아니라 데이터의 정합성도 보증하기에 어렵기 때문이다.
개인 생각으로는 아래와 같이 오라클이 작동한다고 생각하는데 틀린부분이 있으면 댓글로 알려주세요 😀
0)먼저 데이터를 가져오기 전에 버퍼에 데이터가 있는지 확인
1) 버퍼에 존재하거나 undo 버퍼에 존재하는 경우 값 가져오기 (락 발생하지 않음)
2) 없는 경우에 저장공간 ex) HDD에 데이터를 가져오기 전에 해당 데이터의 ITL을 확인하여 대기중 트랜잭션 확인
2-1) 트랜잭션 확인시 동일한 Share Lock 인 경우 데이터 가져오고 , 베타락이 걸려있는 경우 대기 -> 락이 발생
이 처럼 비슷한 데이터를 가져오더라도 각 상황에 따라서 락이 걸리거나 아니면 MVCC에서 값을 가져와 락을 발생시키지 않는다.
추가적으로 락과 트랜잭션은 하나의 관점차이이며
락은 동시 접근을 방지하기 위함이고 트랜잭션 독립성은 읽기 수준 일관성 을 보장하기 위함으로 생각해야 하며 둘이 엄연히 다르다는 것 또한 알아두면 좋겠다.
여기서는 주로 DML락에 대해서 다루며 DDL락에 대해 간단하게 정리하고 넘어가고자 한다.
락의 종류
오라클에서 주로 다루는 락은 아래와 같다.
- DML 락 : 테이블 안에 있는 데이터를 보호
- TX 락 : dml로 인해 해당 로우에 걸리는 락
- TM 락 : lock을 획득한 선행 트랜잭션이 해당 테이블에서 어떤 작업을 하는 중 임시 푯말
(뒤에서 자세히 알아보자 )
- DDL 락 : 테이블의 구조 및 스키마를 보호
- Exclusive DDL 락 : 베타적 DDL락은 다른 세션이 ddl 또는 dml 잠금을 방지
ex) alter table 도중에 drop table을 실행 할 수 없다. - Share DDL 락 : 유사한 DDL 작업에 대한 데이터 동시성을 허용
ex) Create procedure 문이 실행 될때 포함한 트랜잭션에 참조한 테이블에 대해 공유 DDL을 획득
- Exclusive DDL 락 : 베타적 DDL락은 다른 세션이 ddl 또는 dml 잠금을 방지
이 외에도 락은 아니지만 오라클 내부 SGA 버퍼 캐시를 가져오는데 발생하는 래치나 리눅스에서 사용하는 세마포어에서 리소스를 한 쓰레드 뮤텍스에 대해서도 알아보면 좋다.
DATA LOCK
우리가 알고 있듯이 DML을 중에서 INSERT , DELETE , UPDATE 와 SELECT ~ FOR UPDATE 를 사용하게 되면 변경되는 ROW에 걸리는 ROW LOCK 과 해당 테이블에 어떤 락이 걸려 있는지 보여주기 위해 TABLE LOCK이 걸리게 된다.
LOW LOCK (TX LOCK)
앞서 말한 DML로 인해 해당 로우에 걸리는 LOCK을 말하며 이는 다른 유저가 변경하지 못하도록 EXCLUSIVE LOCK 이 형성된다. 이 말은 해당 LOCK을 건 사람이 COMMIT 혹은 ROLLBACK을 하기 전에는 다른 사용자가 변경할 수 없다.
TABLE LOCK (TM LOCK)
TX LOCK 이 걸리게 되면 해당 테이블에 걸리는 LOCK을 말한다. 이런 TX락에는 아래와 같이 다양한 락이 존재한다. 아래와 같은 테이블 락을 걸고 싶다면
LOCK TABLE TBL_NAME IN LOCK_NAME MODE;
명령어를 통해 설정 할 수 있다.
RS (ROW SHARE)
SELECT ~ FOR UPDATE 문으로 걸리는 락으로 다른 테이블락과 호환 되지만 해당 ROW에 TX락이 걸려 다른 DML은 WAIT을 하게 된다.
RX (ROW EXCLUSIVE)
SELECT ~ FOR UPDATE 구문을 제외한 나머지 DML을 시행하게 되는 경우 걸리는 락으로 RS, RX와는 호환 되지만 아래의 3개의 락에 대해서는 호환되지 않는다.
S (SHARE)
해당 락은 RS , S 즉 SHARE 로 끝나는 락끼리만 호환되며 나머지 락과는 호환되지 않는다.
SHARE 락 , EXCLUSIVE 락은 서로 호환되지 않는다가 여기서 파급된거 같은데 뒤에서 SAHRE 락에 대해서 다시 고민해보자
SRX (SHARE ROW EXCLUSIVE)
해당 락이 머리에 잘 안들어온다. SHARE 인거 같으면서도 EXCLUSIVE가 같이 들어가있는 LOCK 이름이기 때문이다 :)
해당 LOCK은 RS를 제외한 어떤 락과도 호환되지 않아서 UPDATE, DELETE , INSERT를 막을 수 있지만 SELCT ~ FOR UDPATE 는 사용가능하다.
X (EXCLUSIVE)
이름만 봐도 강력하게 생겼다. 어느 락과도 호환되지 않으며 이는 DDL 문장에 의해 생기는 LOCK을 말한다.
해당 부연 설명은 이곳 에 잘 정리되어 있다.
추가적으로 MYSQL에서는 UPDATE문은 EX락을 걸고 DELETE, INSERT는 S락을 겁니다.
이외에도 앞에서 설명한 GAP-LOCK이나 Intention Locks, AUTO-INC Locks 와 같은 INNO DB 기반 MYSQL 락은 이곳을 참고해 주세요 😃
mysql db와 혼돈의 시작
최근에 동시성 문제 해결하기라는 좋은 블로그를 읽게 되었다. 쭉 읽다가 아래와 같은 구문에서 이해를 할 수 없었다.
일단 이 글을 읽은 당시에 mysql와 oracle 락이 거의 다르지 않을 것이라고 생각해서 혼돈이 왔었다.
그래서 필자는 해당 락에 대해 알아봤고 일반적으로 말하는 share락과 오라클에서 사용하는 share락의 차이에 대해 말하고자 한다.
오라클에서의 Share 락은 3가지로 나뉜다.
1.DDL에서의 share DDL 락
2.TX락이라고 불리는 테이블 락은 사실 Shared Lock 으로 불린다.
3.TX락 중 세부적인 Shared Lock 이 존재한다.
위의 답변처럼 우리가 주로 말하는 TX락을 말할 통 틀어서 Shared Lock 으로 자주 부른다. 그리고 안에 세부적인 Row share , share 가 있다 정도로 이해하면 될 거 같다.
DML LOCK 부연 설명
TX 락은 내부적으로 Enqueue Lock 으로 구현되어 있으며
TM 락은 로우 단위 LOCK과 테이블 LOCK을 조합해서 구현되어 있다.
Enqueue 는 우리가 알고 있는 선입선출 자료구조로 먼저 Lock을 획득한 세션이 먼저 푸는 구조이다.
Enqueue 리소스는 Shared Pool에 저장되며 SGA에서 버퍼 캐시를 찾아갈 때 해시 버켓을 통해서 찾아가는 것처럼 이 구조체 또한 Hash_function을 통해서 찾아가게 된다.
위의 그림에서 알 수 있듯이 해당 LOCK 소유자가 S락이면 다같이 LOCK을 획득 할 수 있지만 베타 락은 대기자 목록에서 기다리는 것을 알 수 있다.
앞에서 트랜잭션 수준 읽기 일관성을 통해서 블로킹 없이 읽기 작업을 수행한다고 배웠는데 만일 하나의 레코드에 대해서 변경이 동시에 일어나게 된다면 엑세스 직렬화가 필요하며 이 때 트랜잭션 LOCK을 사용한다.
TX 락 이벤트 명
위의 사진에 보이는 것처럼 TX락에는 여러가지 종류가 있으며 주로 발생하는 것이 ROW LOCK CONTENTION 이다.
DML 로우 LOCK은
DML 도중에 호환되지 않는 다른 DML 또는 DDL 오퍼레이션의 수행을 방지하는 역할을 한다.
무결성 제약 위배 가능성은
DML 작업 할 시에 나오며 동시에 같은 주키 값을 가지는 데이터가 INSERT 되는 경우 혹은 FK제약 조건이 걸린 마스터 테이블 레코드가 삭제됨과 동시에 데이터가 들어오는 경우가 있다.
비트맵 인덱스은
대용량 데이터에서 해당값이 있는 여부를 0,1 즉 비트로 매핑되어 있는 인덱스를 말하며 해당 인덱스는 삭제, 삽입에 성능이 낮아 엔트리가 갱신되는 상황에 발생하는 LOCK-EVENT이다.
ITL 부족은
블록에 레코드를 INSERT/UPDATE/DELETE 하려면 ITL (INTERESTED TRANCACTION SLOT) 슬롯을 먼저 할당받아야 하는데 비어있는 ITL 슬롯이 없다면 ITL 슬롯을 사용 중인 트랜잭션 중 하나가 커밋 또는 롤백될 때까지 기다려야 하며 이때 여유 공간이 없으면 발생하는 이벤트를 말한다.
인덱스 분할은
인덱스 분할이 진행중인 블록에 다른 트랜잭션이 로우를 삽입하려고 할 때 발생한다.
DML 락 하나를 테스트 해보자
위의 사진은 주키가 하나 있는 테이블에 대해 다른 세션이랑 동일한 값에 대해 INSERT 하면 LOCK_EVENT가 발생하는 것을 알 수 있다. 당연히 다른 주키에 대해 INSERT를 하게 되면 LOCK_EVENT가가 발생하지 않는다.
공부와 실제 걸리는 락이 많이 다른거 같다.
어느 정도 락에 대해 공부했다고 생각하고 주키가 있는 테이블에 간단한 INSERT를 하고 나서 테이블에는 TM, TX 락이 걸리는 것과 그중에서 RX만 걸린다고 생각했는데 위의 사진처럼 SHARE 락과 RS가 걸린 것을 보고 MYSQL처럼 해당 주키가 중복되는 지 찾아보면서 RS 락이 걸린 것 같다.
아래의 사진은 주키가 없는 테이블에 INSERT 이후에 걸린 락이다.
오라클 select * from temp for update;
앞에서 언급했던 것처럼 오라클에서 for update 를 실행하면 해당 row에 대해서도 락이 걸려야 하며 그와 동시에 Table Lock 이 생기는 것을 알 수 있다.
하지만 필자는 이곳 처럼 해당 로우에 대해서 for update를 사용해도 락이 걸리지 않아 공부를 하는데 혼선이 생겼다. 하지만 이곳 에서 정답을 알 수 있었고 답변은 아래와 같다.
Enabling auto-commit may be more convenient, but gives you less control. For example, you have no option to roll back changes. In addition, some SQLJ or JDBC features are incompatible with auto-commit mode. For example, you must disable the auto-commit flag for update batching or SELECT FOR UPDATE syntax to work properly.
이처럼 auto-commit 을 False로 설정해두어야 Lock이 걸리는 것을 확인 할 수 있다.
참고 사이트
https://aozjffl.tistory.com/197
http://wiki.gurubee.net/pages/viewpage.action?pageId=3902548
