/**********************************************************************************************
-- 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;