MS-SQL Server

자율적인 트랜잭션 (AUTONOMOUS TRANSACTION, 독립적인 트랜잭션)

임현수 2010. 12. 9. 23:49
어느날 개발자 분께서 트랜잭션 실패시에도 로그를 남겼으면 좋겠는데 방법이 없는지 문의를 주셨습니다.
저는 늘 답변했듯이 오라클을 사용할 경우 autonomous trasaction 처리가 가능하지만 mssql에서는 제공을
하지 않아서 안된다고 이야기드렸는데, 왠지 그 날따라 찜찜하여 찾아보니 linked server를 사용하는 방법이
있어 공유드립니다.

[Autonomous Trasaction이란?]
* 블록에서 자신의 작업을 수행하기 위해서 그 블록 고유의 트랜잭션을 생성하는 경우에 해당하며 그 블록의 
  트랜잭션의 결과가 자신을 포함하거나 호출한 트랜잭션에의 상태에 의해 영향을 받지 않는 속성을 가지는 
  트랜잭션을 의미한다.
* 마스터 트랜잭션과는 lock, resource, commit 에 관련된 의존성을 가지지 않는다.

[SQL Server]

-- 1. 연결된서버추가

USE MASTER

GO

EXEC sp_addlinkedserver @server = N'loopback',@srvproduct = N' ',@provider = N'SQLNCLI', @datasrc = @@SERVERNAME

GO

EXEC sp_serveroption loopback, N'remote proc transaction promotion','false'
                                                 
-- SQL Server 2008
에서 추가된 옵션(분산트랜잭션사용안함)

go

EXEC sp_serveroption loopback, N'RPC OUT', 'TRUE' -- Enable RPC to the given server.

go

-- 2. 임시테이블생성

use tempdb

go

CREATE TABLE ErrorLogging (logTime DATETIME, msg VARCHAR(255))

CREATE TABLE TestAT (id INT PRIMARY KEY)

GO

-- 3. 프로시져생성

CREATE PROCEDURE usp_ErrorLogging

      @errNumber INT

AS

      INSERT INTO ErrorLogging VALUES (GETDATE(), 'Error ' + CAST(@errNumber AS VARCHAR(8)) +' occurred.')

GO


-- 4. 테스트하기(TestAT에 동일한PK를 입력하여 오류를 발생시킴)

DECLARE @ERROR AS INT

INSERT INTO TestAT VALUES (1)

BEGIN TRAN OuterTran

      INSERT INTO TestAT VALUES (1) -- This will raise primary key constraint violation error

     

      SELECT @ERROR = @@ERROR

      IF @ERROR <> 0

      BEGIN

            BEGIN TRAN InnerTran

                  EXEC loopback.dbTest.dbo.usp_ErrorLogging @ERROR -- 여기서 연결된서버를 활용함

            COMMIT TRAN InnerTran

           

            ROLLBACK TRAN OuterTran

      END

 

IF @@TRANCOUNT > 0     

COMMIT TRAN OuterTran

GO

 

Result>

(1 행이 영향을 받음)

메시지 2627, 수준 14, 상태 1, 6

PRIMARY KEY 제약 조건 'PK__TestAT__3213E83F173876EA'() 위반했습니다. 개체 'dbo.TestAT' 중복 키를 삽입할 없습니다.

문이 종료되었습니다.

 

(1 행이 영향을 받음)

-- 5. 로그가 쌓였는지 체크

SELECT * FROM TestAT

SELECT * FROM ErrorLogging

 

Result>


 

[Oracle] (간단히 문법만 공유드립니다. ^^)

declare

    pragma autonomous_transaction;

begin

    commit;  -- 자율적인 트랜잭션은 반드시 commit/rollback으로 종료해야 한다.

end;


[출처] (테이블 변수를 활용한 방법 정리되어 있으니 관심 있는 분은 원문 참고 부탁드립니다.)
http://blogs.msdn.com/b/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx