📝INSERT, UPDATE, DELETE, SELECT 부하순서
// 일반적 부하순서
DELETE > UPDATE > INSERT > SELECT- DELETE
- 데이터를 삭제할 때는 실제 삭제 외에도 인덱스 갱신, 참조 무결성(FK) 체크, 트리거 실행, 로그 기록 등이 발생
- 대량 DELETE는 물리적으로 데이터를 지우고 공간을 재정리해야 하므로 부하가 큼
- UPDATE
- 데이터를 수정할 때는 읽기 + 쓰기 작업이 모두 필요
- 인덱스가 걸린 컬럼을 수정하면 인덱스 갱신 작업이 필요하므로 부하가 커질 수 있음
- INSERT
- 인덱스가 많은 테이블에 INSERT하면 인덱스를 모두 갱신해야 하므로 부하가 커질 수 있지만, 일반적으로 UPDATE보다 비용이 낮음
- PK 충돌 체크, 제약조건(FK, UNIQUE) 체크 과정은 발생
- SELECT
- 단순 SELECT는 읽기 작업만 수행하므로 상대적으로 부하가 적음
- 복잡한 JOIN, 대량의 데이터 스캔, 인덱스 미사용, 서브쿼리, 정렬(SORT) 등이 있으면 SELECT가 가장 무거워질 수도 있음
📝 트랜잭션이 길어지면 생기는 일 (UnDo, ReDo)
트랜잭션을 길게 유지하면 UnDo나 ReDo에 트랜잭션 롤백하기 위한 데이터들이 계속 쌓이고 유한하기 때문에 성능상 이슈가 생길 수 있으니 트랜잭션의 경우 짧게 유지하고 처리하고 끝내야한다.
📝 DB 파일 시스템 (Page)
Page는 데이터베이스가 디스크에서 데이터를 읽고 쓰는 기본 단위입니다. (메모리에서 Page단위로 로드)
대부분의 DBMS는 디스크 I/O 성능 최적화를 위해 일정 크기의 블록으로 데이터를 관리합니다. (일반적으로 페이지 크기는 4KB, 8KB, 16KB)
Page 안에는 레코드들이 들어가있습니다. 데이터를 1행 가져오더라도 페이지 단위로 메모리에 올립니다.
찾으려는 데이터가 페이지 하나에 다 있으면 좋은데 그렇지 않으면 이곳 저곳 페이지 단위를 다 올리기 때문에 조회 성능 이슈가 있습니다.
데이터 INSERT할때 순차적으로 들어가며 중간 데이터 삭제 되는 경우 비어져있습니다.
추후 DB 자체에서 VACCUM / OPTMIZE 작업을 하지만 부하가 있기 때문에 한가한 시간에 예약 실행 시키는 게 일반적입니다.
📝 DB락 (행, 페이지, 테이블, 범위)
DB락의 경우는 동시성 보장을 위해 락을 건다.
- 행 락 (Row Lock)
- 하나의 행(Row, 레코드)에만 걸리는 잠금
- 페이지 락 (Page Lock)
- 하나의 페이지(=디스크 I/O 단위, 보통 8KB)에 걸리는 잠금
- 테이블 락 (Table Lock)
- 전체 테이블에 락을 걸어 다른 트랜잭션이 읽기/쓰기 제한을 받게 함
- 범위락 (Range Lock)
- 조건에 해당하는 행 + 그 범위 내에 존재할 수 있는 잠재적 행들까지 락을 거는 방식 (현재 있는 행뿐 아니라, 그 범위에 새로 삽입될 행도 잠금 대상이 됨)
| RDBMS | 기본 락 범위 |
| Oracle | Row-level Lock |
| MySQL | Row-level Lock |
| PostgreSQL | Row-level Lock |
| MSSQL | Row-level 또는 Page/Table 자동 승격 |
📝 DB락 에스컬레이션
DB 에스컬레이션은 락이 많아질 경우 DBMS가 너무 많은 수의 소규모 락을 관리하는 오버헤드를 줄이기 위해 자동으로 더 큰 범위의 락으로 전환하는 과정이 있습니다.
| RDBMS | 락 에스컬레이션 |
| Oracle | ❌ 지원하지 않음 |
| SQL Server | ✅ 지원함 (자동) |
| PostgreSQL | ❌ 지원하지 않음 |
| MySQL (InnoDB) | ❌ 지원하지 않음 |
| DB2 | ✅ 지원함 (자동) |
| Sybase ASE | ✅ 지원함 |
📝 분산락
분산락은 여러 서버에서 동시에 공유 자원에 접근하지 못하도록 제어하는 메커니즘으로 분산 시스템에서 데이터 정합성과 무결성을 보장하는 데 필수적입니다. 참고로 DB에서의 동시성 처리를 위해 MVCC라는 기능이 있어서 어느정도 동시성 처리를 하지만 추가적으로 분산락까지 적용시켜야 동시성을 완전히 제어할 수 있습니다.
예를 들면 쿠폰을 100개만 발급해야하는데 99개일 때 3개의 동시성 요청이 들어간 경우 102개의 쿠폰이 발급 됩니다.
격리수준이 높더라도 동시성 문제 4개에 해당 안 되는 동시성 문제(더티리드, 반복 불가능 읽기, 팬텀리드, 갱신손실)이기 때문에 따로 정합성을 위해 개발이 필요합니다.
주의 사항
- 락 테이블을 가지고 직접 락 ID를 발급 받아서 검증을 해서 두번이상 안 들어가게끔 설계합니다.
- 이럴 경우 락 테이블은 Write DB 한군데에서만 처리해야합니다. (다른 DB에서 레플리카를 해도 정합성이 안 맞을 수 있음) 아래 방법은 DB 방식이지만 저러한 방식들로 구현되며 Redis를 이용한 락 관리를 많이 사용합니다.
- 아래 예제에는 락 무한기간 점유에 대해서는 설계가 되어있지 않습니다.
- 하트비트(락을 보유 중임을 주기적으로 연장하는 갱신으로 계속적으로 UPDATE를 보내 갱신), TTL 설정 필요
- 아래 예제는 뒤 늦은 업데이트에 대한 처리도 필요합니다.
- 펜싱토큰으로 처리합니다.
- A가 락을 잡고 있다가 멈춤 → TTL 만료로 B가 선점 → A가 뒤늦게 깨어나 예전 락이라고 착각하고 쓰기를 시도.
- TTL 기준으론 A·B 둘 다 ‘내가 주인’이라고 믿을 수 있음
- 이때 토큰 비교로 B(큰 번호)의 작업만 통과, A(작은 번호)는 거부되어 데이터가 안전합니다
- 펜싱토큰으로 처리합니다.
- 다양한 상황에 따른 추가 작업을 고려해야합니다.
방법1
BEGIN;
SELECT * FROM lock_table WHERE resource_name='job1' FOR UPDATE;
-- 처리 로직 수행...
COMMIT;쿠폰이 100개면 100개 행만큼 만든 이후에 DB에서 Row-Level로 행을 잠궈서 발급해 처리합니다.
특징
- 매번 한정 개수일 때 미리 만들어야하는 번거로움이 있습니다.
- 해당 행에 LOCK을 걸기 때문에 UPDATE 동시성에 대해서도 처리가 가능합니다.
방법2 (추천)
CREATE TABLE payments (
payment_id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL,
idempotency_key TEXT NOT NULL UNIQUE,
status TEXT NOT NULL DEFAULT 'PENDING',
amount NUMERIC(12,2) NOT NULL,
receipt_no TEXT
);
CREATE TABLE key (
key_id BIGINT AUTO_INCREMENT PRIMARY KEY,
idempotency_key TEXT NOT NULL UNIQUE
);DB에서 제약조건을 걸어 검증을 하는 방법입니다.
payments에 데이터를 넣기 위해서는 무조건 idempotency_key를 key 테이블에서 발급 받아서 사용해야합니다.
진행 프로세스
- key 테이블에서 키(idempotency_key) 발급 (INSERT)
- 해당 key 테이블의 키를 payments 테이블 idempotency_key에 넣으며 결제 테이블(payments)에 넣습니다.
특징
- 유니크 제약 조건이 있기 때문에 동일한 idempotency_key가 들어갈 수 없습니다.
- INSERT에만 유효하고 UPDATE 동시성의 경우 다른 방법 처리가 더 필요합니다. (버전 정보 추가 기입 등...)
방법3
-- 1. 락 획득 (my_lock이라는 이름, 최대 10초까지 기다림)
SELECT GET_LOCK('my_lock', 10) AS lock_acquired;
-- (lock_acquired = 1 이면 락 성공, 0이면 타임아웃, NULL이면 에러)
... (프로세스 처리)
SELECT RELEASE_LOCK('my_lock') AS lock_released;
-- 락 해제DB자체에서 락테이블 관리하고 DB에서 제공하는 함수들이 있습니다.
특징
- 특정 LOCK의 이름을 가지고 처리 후에 반납하기 때문에 이미 점유중인 특정 LOCK의 이름을 또 가지고 오려고하면 락을 획득 못합니다.
📝 DB CRUD의 멱등성
| 동작 | 멱등성 여부 | 이유 |
| C(Create) | ✅ (조건부) | 동일 데이터를 여러 번 INSERT하면 중복 행이 생김. (PK/Unique 제약으로 멱등성 확보 가능) |
| R(Read) | ✅ | 몇 번 조회하든 DB 상태 안 변함. |
| U(Update) | ✅ (조건부) | 같은 값을 여러 번 업데이트해도 결과가 동일. 하지만 UPDATE users SET balance=balance+100처럼 가변 연산이면 ❌ |
| D(Delete) | ✅ | 같은 레코드 삭제를 여러 번 시도해도 결과는 "없음"으로 동일. (단, 오류 처리 방식에 따라 API 결과는 다를 수 있음) |
🔗 참고 및 출처