← 지식 베이스로 돌아가기← 이전 질문다음 질문 →
프로그래밍SQL 엔지니어

SQL에서 비즈니스 논리의 무결성을 보장하기 위해 저장 프로시저에서 오류 처리 및 트랜잭션을 구현하는 방법은 무엇인가요? 다양한 DBMS에서 접근 방식의 차이점은 무엇인가요?

Hintsage AI 어시스턴트로 면접 통과

답변.

문제의 배경
비즈니스 논리 처리 작업에서는 종종 여러 관련 테이블에서 데이터를 변경해야 합니다. 만약 작업이 실패할 경우(예: 제약 조건 위반), 마지막 작업뿐만 아니라 논리 내의 모든 작업을 롤백하는 것이 중요합니다. 이를 위해 트랜잭션 메커니즘과 오류 처리를 사용합니다.

문제점
모든 DBMS가 기본적으로 다단계 프로시저에서 오류 발생 시 롤백을 보장하지 않습니다 (특히 TRY/CATCH, EXCEPTION 핸들러를 사용할 경우). 트랜잭션 논리를 잘못 작성하면 "부분적인" 변경이 발생할 수 있으며 (일부 데이터는 업데이트되고 일부는 업데이트되지 않음), 이는 비즈니스 무결성을 훼손합니다.

해결책
정확한 처리를 위해 명시적으로 트랜잭션을 시작하고 종료합니다 (BEGIN TRANSACTION, COMMIT/ROLLBACK) 및 오류를 포착합니다. 다양한 DBMS에서 구문 및 기능이 다릅니다.

MS SQL Server의 예:

CREATE PROCEDURE update_balances(@from INT, @to INT, @amount DECIMAL(10,2))
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;
      UPDATE accounts SET balance = balance - @amount WHERE id = @from;
      UPDATE accounts SET balance = balance + @amount WHERE id = @to;
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    INSERT INTO error_log(err_message, err_date) VALUES(ERROR_MESSAGE(), GETDATE());
    THROW; -- 오류를 다시 던진다
  END CATCH
END;

PostgreSQL의 경우:

CREATE OR REPLACE FUNCTION update_balances(from_id INT, to_id INT, amount NUMERIC)
RETURNS void AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  -- 예외가 발생하면 자동으로 트랜잭션이 롤백됩니다
EXCEPTION WHEN OTHERS THEN
  INSERT INTO error_log(err_message, err_date) VALUES(SQLERRM, NOW());
  RAISE;
END;
$$ LANGUAGE plpgsql;

주요 특징:

  • 트랜잭션을 열고 명시적으로 종료합니다.
  • catch/exception 블록에서 오류를 처리하고 변경 사항을 롤백합니다.
  • 감사용으로 오류 정보를 기록합니다.

교묘한 질문들.

저장 프로시저 내부의 오류가 항상 자동으로 트랜잭션을 롤백할 수 있나요? 아니요! 예를 들어, MS SQL Server에서는 이러한 롤백이 보장되지 않으며 명시적인 ROLLBACK이 필요합니다. PostgreSQL에서는 모든 오류가 발생하면 전체 트랜잭션이 "손상"되고 종료가 필요합니다.

하나의 프로시저 내에서 "부분적으로" 커밋할 수 있나요? 안 좋은 관행입니다. 비즈니스 작업의 끝에서 단 한번만 commit/rollback을 하는 것을 권장합니다. 일부 플랫폼에서는 "savepoints"를 허용하지만, 이는 특수 작업을 위한 것입니다.

SQL에서 TRY/CATCH/EXCEPTION을 통해 잡을 수 없는 오류 유형은 무엇인가요? 일부 시스템 장애(예: 서버와의 연결 끊김)는 핸들러에 도달하지 못할 수 있으며, 롤백을 불가능하게 만들 수 있습니다.

전형적인 오류 및 안티 패턴

  • 오류 처리 없이 COMMIT 남기기: 데이터베이스에 "손상된" 또는 부분적으로 변경된 데이터를 기록함
  • 하나의 비즈니스 작업 내에서 여러 번 COMMIT/ROLLBACK 수행
  • 오류 기록을 무시함

실제 사례

부정적인 사례

저장 프로시저가 세 개의 테이블을 업데이트하고 오류 처리가 없는 COMMIT으로 종료되었습니다. 결과적으로 두 번째 테이블에서 오류가 발생할 때 첫 번째 테이블은 이미 변경되었고, 백업에서 수작업으로 데이터를 "롤백"하는 데 몇 시간이 걸렸습니다.

장점:
간단하고 "작동"하나, 실패가 발생할 때까지.

단점:
불일치 위험; 오류 발생 시 빠르게 복구할 수 없음.

긍정적인 사례

명시적인 오류 처리 및 트랜잭션 롤백을 도입하고 모든 실패를 별도의 로그에 기록했습니다. 데이터베이스의 원래 상태로 복귀하는 데 몇 초밖에 걸리지 않으며, 오류가 분석되고 최소화됩니다.

장점:
장애 발생 시에도 데이터의 무결성을 보장; 투명한 논리.

단점:
초보자에게는 약간 복잡하며, 코드에서의 규율이 필요합니다.