[DB] 트랜잭션과 데이터베이스 락(Transction & DB Lock) | 민민의 하드디스크 - 티스토리

서론 (개인적인 생각)

대부분 웹프로그래밍 업무는 CRUD가 기본 프로세스, 그 외에 전자결재 API, 외부 연계(인터페이스), 내부 연계, 기관 연계 등등 개발의 중점보다는(기본은 되는 정도) 업무(도메인)의 중점(중요도)이 더 크다고 생각한다.

예시로,

신청과 관리- 내부에서도 승인(결재 프로세스)와 같은 구분이 있어야 visible 되는 기능 또는 화면도 있고,  어떤 흐름에 따라 변경이 잦은 프로세스도 있을 것이다. 신청페이지와 관리자페이지의 상호작용이 중요하다고 생각이 들었다..

업무를 잘못 이해하면 개발을 아무리 잘해도 고객의 만족도를 높이지 못한다는 것.

그래서 더 많은 테스트를 해보고, JUnit과 같이 코드로 진행하는 테스트로 먼저 검증하고 사용자 테스트를 거치거나 하는 식이다. (공공SI는 대부분 테스트코드 안 짜긴 함...)

하지만, 그 전에 개발자가 테스트하고 [개발완료]를 찍을 것이다. 요구사항에 맞게 개발되었는지, 개발 중에도 테스트해보고 해당 프로세스의 흐름대로 잘 작동되나 기능을 테스트 할 것이다. 나는 개발 테스트 중 이상한 상황이 발생했다...

 

 

상황

1. MERGE INTO 문을 화면에서 테스트하기 전에, 임의의 값을 넣고 DBeaver 툴에서 돌렸다.

2. INSERT / UPDATE 잘 되는 것을 확인

3. 바로 xml 매퍼에 적용해서 로직만들고, 빌드 후 로컬서버에서 돌려봄

4. (저장)버튼을 누르니 debug 걸린 것 마냥 서버가 멈췄음. (화면도 눌린 상태에서 먹통)

위 상태에 이해가 안 됐는데, 알고보니 트랜잭션이 메모리에만 반영되고, 실제 DB에 저장하지 않아서 트랜잭션 접근제한에 걸린 것이었다. (DBeaver Auto Commit 끈 상태 + Commit 안 날려줌) 

 

 

이유

• DBeaver에서 INSERT 실행
  → 트랜잭션이 자동 커밋되지 않는 상태: 데이터가 메모리에만 반영되고, 아직 실제 DB에는 확정되지 않은 상태.
• COMMIT 없이 테스트 진행
  → 트랜잭션이 열린 상태에서 COMMIT 또는 ROLLBACK을 하지 않으면, 해당 레코드는 현재 세션에서만 보이고 다른 세션에서는 접근이 제한
  → DBeaver 세션이 아직 트랜잭션을 종료하지 않았기 때문에, 해당 레코드는 락(Lock)에 의해 점유된 상태가 됨.
• 애플리케이션에서 매퍼를 통해 같은 테이블에 접근
  → 애플리케이션의 커넥션 풀(Connection Pool)에서 새로운 DB 커넥션을 생성해 같은 테이블의 데이터를 읽거나 변경하려고 하면, 락이 걸려서 대기 상태(Blocking)로 멈추는 현상이 발생할 수 있음.
• DB Lock 문제 해결 (Commit/Rollback 실행 후 정상 작동)
  → DBeaver에서 COMMIT 또는 ROLLBACK을 수행하면 트랜잭션이 종료되면서 락이 해제됨.
  → 이후 애플리케이션에서 다시 실행하면 정상적으로 동작하게 됨.

 

나에게 발생한 DB Lock의 내용은 위와 같은 문제였다.. 그래서 알아봤다. DB Lock이 뭐고 왜 일어나는지?

 

 

 

원인 분석 (DB Lock의 종류)

1) 테이블 레벨 락(Table-Level Lock)

• INSERT, UPDATE, DELETE 후 트랜잭션을 종료하지 않으면, 해당 테이블 전체가 잠길 수 있다.
• 다른 세션에서 해당 테이블을 조회하거나 수정하려고 하면 대기 상태(Block)로 멈춤.

2) 행(Row-Level Lock)

• 특정 행에 대한 UPDATE 또는 SELECT ... FOR UPDATE 수행 시, 해당 행이 잠겨 다른 트랜잭션이 접근하지 못하는 상태.
• MySQL InnoDB 같은 경우 **MVCC(멀티 버전 동시성 제어)**로 인해 SELECT는 영향을 덜 받지만, UPDATE는 블록될 수 있음.

3) 트랜잭션이 끝나지 않아 발생하는 잠금 (Uncommitted Transaction Lock)

• DBeaver에서 INSERT 후 COMMIT을 하지 않으면, 다른 세션이 해당 데이터에 접근할 수 없음.
• 애플리케이션에서 동일한 데이터에 접근할 때 락이 걸려서 응답이 멈춘 것처럼 보이는 현상이 발생.

 

 

해결 방법

1) DBeaver 트랜잭션 설정 확인

• DBeaver에서 Auto Commit 옵션이 꺼져 있으면, SQL 실행 후에도 트랜잭션이 계속 유지됨.
• 해결책: Auto Commit을 활성화하거나, 명시적으로 COMMIT 또는 ROLLBACK을 수행.

Auto Commit 설정 방법 (DBeaver)

1. DBeaver에서 Database → Transaction → Auto Commit을 활성화.
2. 또는 COMMIT을 수동으로 실행 (CTRL + ENTER로 실행하지 말고 COMMIT; 입력 후 실행).

(개인프로젝트 아니면 추천 안 함.)

 

2) 트랜잭션을 명확히 관리

• 애플리케이션에서 가능한 한 빠르게 트랜잭션을 종료(즉, COMMIT 또는 ROLLBACK 수행).
• 트랜잭션이 필요한 경우, 명확한 로직 안에서만 유지하고 필요할 때 즉시 종료.

 

3) 락이 걸렸을 때 확인하는 방법

ex) DB: MySQL

3.1. 현재 락 확인

SELECT * FROM information_schema.innodb_trx;

 

3.2. 특정 세션 강제 종료 (MySQL)

KILL [SESSION_ID];

 

 

3.3. PostgreSQL에서 락 확인

SELECT * FROM pg_stat_activity WHERE state = 'active';

 

3.4. 락 해제 (PostgreSQL)

SELECT pg_terminate_backend([PID]);

 

 

결론

• DBeaver에서 트랜잭션이 열린 상태에서 COMMIT을 하지 않으면, 락이 걸려 애플리케이션에서 접근이 차단될 수 있음.
• Auto Commit 설정을 확인하고, 테스트 후에는 반드시 COMMIT 또는 ROLLBACK을 수행해야 함.
• DB 락이 걸렸을 때는 information_schema나 pg_stat_activity 등을 활용해 원인을 찾아볼 수 있음.

이제부터는 테스트 후에는 항상 COMMIT 또는 ROLLBACK을 습관적으로 수행해야겠다...