🚀 DBeaver에서 Commit을 하지 않아 발생한 Lock 문제

❗ 문제 상황

입사한지 얼마되지 않아 select절도 commit 해줘야 한다는걸 모르고 데이터를 보다 block 문제가 발생했습니다..ㅠㅠ  DBeaver를 사용하여 데이터베이스를 관리할 때, 트랜잭션을 실행한 후 COMMIT을 수행하지 않으면 다른 사용자나 프로세스에서 해당 데이터에 접근하지 못하는 LOCK 문제가 발생할 수 있습니다.  결국 저는 완전히 로그아웃을 해서 커밋되지 않은 트랜잭션을 롤백하는 방식으로 해결했습니다. 이 일이 있고 난 후에 select 절을 하고 나면 바로 commit을 하고 사용하지 않는 db의 경우 연결을 끊어 놓습니다...ㅎㅎ

🤔 왜 이런 문제가 발생할까?

DBeaver는 기본적으로 트랜잭션을 명시적으로 COMMIT하거나 ROLLBACK하지 않으면 변경 사항을 유지한 채 세션을 유지합니다. 이 상태에서:

• 🔒 변경된 데이터가 LOCK 상태가 되어 다른 트랜잭션이 접근할 수 없음
• ⏳ 다른 사용자가 동일한 데이터를 수정하려고 하면 BLOCK이 발생하여 대기 상태로 전환됨
• ⚙️ AUTO COMMIT이 꺼져 있는 경우, 명시적인 COMMIT 또는 ROLLBACK이 필요함

🔍 BLOCK이 발생하는 주요 원인

1. 긴 트랜잭션 유지: 트랜잭션을 실행한 후 COMMIT 또는 ROLLBACK을 하지 않으면 세션이 열린 상태로 유지되어 해당 데이터가 LOCK됨.
2. 데드락(Deadlock) 발생: 두 개 이상의 트랜잭션이 서로가 필요로 하는 리소스를 점유한 상태에서 COMMIT이 되지 않으면 교착 상태(Deadlock)가 발생하여 블로킹될 수 있음.
3. 동시성 제어로 인한 대기: 하나의 트랜잭션이 특정 행을 업데이트 중일 때, 다른 트랜잭션이 해당 데이터를 수정하려 하면 완료될 때까지 대기함.
4. AUTO COMMIT 비활성화: AUTO COMMIT이 꺼져 있는 경우, 명시적인 COMMIT이 필요하지만 사용자가 이를 수행하지 않아 블로킹됨.

🛠️ 해결 방법

✅ 1. 수동으로 COMMIT 수행하기

트랜잭션을 실행한 후 반드시 COMMIT을 수행해야 합니다. DBeaver에서 SQL 창에서 아래 명령어를 실행하세요.

COMMIT;

만약 변경을 취소하고 싶다면 ROLLBACK을 실행하면 됩니다. 🔄

ROLLBACK;

✅ 2. AUTO COMMIT 활성화하기

DBeaver에서 AUTO COMMIT을 활성화하면 트랜잭션이 자동으로 COMMIT되어 BLOCK 문제가 발생하지 않습니다. 🏆

활성화 방법:

1. ⚙️ DBeaver 상단 메뉴에서 Edit → Preferences 선택
2. 🔄 Database → Transactions 항목으로 이동
3. ✅ Auto-commit 옵션을 체크하여 활성화
4. 💾 Apply 및 OK를 눌러 설정 저장

또는 세션별로 AUTO COMMIT을 활성화할 수도 있습니다.

1. 🖱️ 데이터베이스 연결 패널에서 마우스 오른쪽 클릭
2. 🔧 Connection Settings 선택
3. 🎛️ General 탭에서 Auto-commit 활성화

✅ 3. 현재 블록된 세션 확인 및 해제하기

BLOCK이 발생한 경우, 현재 실행 중인 트랜잭션과 LOCK 정보를 확인하여 필요 시 강제 해제할 수 있습니다. 🔎

(1) PostgreSQL에서 실행 중인 트랜잭션 확인 🧐

SELECT * FROM pg_stat_activity WHERE state = 'active';

(2) 특정 세션 강제 종료 (PostgreSQL 기준) 🛑

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active';

(3) MySQL에서 실행 중인 트랜잭션 확인 🔍

SHOW ENGINE INNODB STATUS;

(4) 특정 세션 강제 종료 (MySQL 기준) ❌

KILL <SESSION_ID>;

🔰 예방 방법

• ✅ 트랜잭션 실행 후 반드시 COMMIT 또는 ROLLBACK 실행
• ✅ AUTO COMMIT을 활성화하여 자동으로 COMMIT 적용
• ✅ 데이터베이스 세션을 장시간 유지하지 않고, 필요할 때만 접속
• ✅ 주기적으로 pg_stat_activity 또는 SHOW PROCESSLIST를 확인하여 불필요한 세션 종료

 

DBeaver에서 COMMIT을 하지 않으면 BLOCK이 발생하여 다른 트랜잭션을 방해할 수 있습니다. 이를 방지하려면 AUTO COMMIT을 활성화하거나, 수동으로 COMMIT을 실행하는 습관을 들이는 것이 중요합니다. 💡 만약 BLOCK이 발생했다면 현재 실행 중인 트랜잭션을 확인하고 적절한 조치를 취하세요.