반응형


/**********************************************************************************************
-- Title : [2k5] 분산 트랜잭션 사용 예
-- Reference : hanbitbook.co.kr
-- Key word : set xact_abort on begin distributed transaction
**********************************************************************************************/

-- 1. SECOND 서버 창에서
USE tempDB;

CREATE TABLE bankBook
 (name NVARCHAR(10),
  money INT ,
  CONSTRAINT CK_money
  CHECK  (money >= 0)
 );

INSERT INTO bankBook VALUES ('당탕이', 0);


-- 2. 기본 서버 창에서
EXEC sp_addlinkedserver 
   @server='SECONDBANK',
   @srvproduct='SQLServer',
   @provider='SQLNCLI',
   @datasrc='MAPBAKDBA\SECOND';

USE tempDB;

DROP TABLE bankBook;

CREATE TABLE bankBook
 (name NVARCHAR(10),
  money INT ,
  CONSTRAINT CK_money
  CHECK  (money >= 0)
 );

INSERT INTO bankBook VALUES ('우재남', 1000);

SET XACT_ABORT ON; -- 분산 트랜잭션 사용
GO

BEGIN DISTRIBUTED TRANSACTION
 UPDATE bankBook SET money = money - 500 WHERE NAME = '우재남';
 UPDATE SECONDBANK.tempDB.dbo.bankBook SET money = money + 500 WHERE NAME = '당탕이';
COMMIT TRANSACTION

SELECT * FROM bankBook;
SELECT * FROM SECONDBANK.tempDB.dbo.bankBook;

SET XACT_ABORT ON
GO

BEGIN DISTRIBUTED TRANSACTION
 UPDATE bankBook SET money = money - 600 WHERE NAME = '우재남';
 UPDATE SECONDBANK.tempDB.dbo.bankBook SET money = money + 600 WHERE NAME = '당탕이';
COMMIT TRANSACTION

SET XACT_ABORT ON

GO
BEGIN DISTRIBUTED TRANSACTION
 UPDATE bankBook SET money = money - 300 WHERE NAME = '우재남';
 UPDATE SECONDBANK.tempDB.dbo.bankBook SET money = money + 300 WHERE NAME = '당탕이';
COMMIT TRANSACTION

SELECT * FROM bankBook;

 

반응형

+ Recent posts