sqlserver分布式事务

启动服务中的Distributed Transaction Coodinator后

创建链接服务器ender-pcsubx

设定连接服务器RPC OUT 以及RPC属性为True

实验一下代码

创建表

CREATE TABLE [dbo].[yuf](
	[id] [uniqueidentifier] NOT NULL,
	[namx] [nvarchar](50) NOT NULL,
) ON [PRIMARY]

第一种方法显示调用begin distributed transaction　　

set XACT_ABORT on 
begin distributed transaction distributedtran

begin

declare @rowcounts int =0;

insert into dbo.yuf(id,namx) values(newid(),N'test1');
select * from dbo.yuf;

set @rowcounts=@rowcounts+(select @@ROWCOUNT);

insert into [ender-pcsubx].subt.dbo.yuf(id,namx) values(newid(),N'test1'+REPLICATE(N'1',50));


set @rowcounts=@rowcounts+(select @@ROWCOUNT);

if @rowcounts=2

commit transaction distributedtran;

else

rollback transaction distributedtran;

end

go

select * from dbo.yuf;

　

第二种方法跟调用本地事务一样

USE test
EXEC sp_configure 'remote proc trans', 1 ;  
GO  
RECONFIGURE ;  
GO  

　

1）调用存储过程方式

CREATE PROCEDURE xt
	@l int
AS
BEGIN
	set xact_abort on;
	begin tran
	

	insert into dbo.yuf(id,namx) values(newid(),N'test1');

	insert into [ender-pcsubx].subt.dbo.yuf(id,namx) values(newid(),N'test1'+REPLICATE(N'1',@l));
	commit tran;

    
END
GO

　　

exec dbo.xt @l=55;--插入超长字符串，导致两个表数据回滚
GO
exec dbo.xt @l=20;--两表插入正常

2）直接脚本调用方式

set xact_abort on
begin tran

declare @rowcounts int =0;

insert into dbo.yuf(id,namx) values(newid(),N'test1');
select * from dbo.yuf;

set @rowcounts=@rowcounts+(select @@ROWCOUNT);

insert into [ender-pcsubx].subt.dbo.yuf(id,namx) values(newid(),N'test1'+REPLICATE(N'1',20));


set @rowcounts=@rowcounts+(select @@ROWCOUNT);

commit transaction