Transactional Replication2：在Subscriber中，主键列是只读的

在使用Transactional Replication时，Subscriber 被认为是“Read-Only”的 ， All data at the Subscriber is “read-only” (transactional replication does not enforce this at the Subscriber)，不能更新Subscriber数据的主键，否则，某些数据更新操作会失败。

一，在Transactional Replication中，How Changes Are Propagated for Transactional Articles？

默认的是使用系统自动生成的存储过程来更新（delete，update，insert）数据的。

transactional replication should script out and subsequently call a stored procedure to propagate changes to Subscribers (the default).

By default, transactional replication propagates changes to Subscribers through a set of stored procedures that are installed on each Subscriber. When an insert, update or delete occurs on a table at the Publisher, the operation is translated into a call to a stored procedure at the Subscriber. The stored procedure accepts parameters that map to the columns in the table, allowing those columns to be changed at the Subscriber.

例如，在Programmability catalog下，有三个sp，分别是：dbo.sp_MSdel_dbodt_study，dbo.sp_MSins_dbodt_study，dbo.sp_MSupd_dbodt_study，用以对subscriber端的 dbo.dt_sutdy进行delete，insert和update 操作，这三个sp每次执行时必须保证update，insert或update的数据行数是1，如果更新的记录数量不是1，那么Replication报错。用于Publication的Table必须创建PK，PK起到唯一标识一条记录的作用。

1，查看 dbo.sp_MSdel_dbodt_study 的源代码

delete data command 使用 PK column作为Delete的Filter condition，保证删除一条记录，如果删除的数据行数为0，则报错。

ALTER procedure [dbo].[sp_MSdel_dbodt_study]
        @pkc1 int
as
begin  
    delete [dbo].[dt_study]
    where [id] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end 

These  commands ensure that if there is no matching record then error 20598 must be raised. "if @@microsoftversion>0x07320000" verifies that you are using a version of SQL Server above SQL 7.

if @@rowcount = 0 
　　if  @@microsoftversion>0x07320000 
　　　　exec sp_MSreplraiserror 20598 

2,示例分析，在Publisher端有如下数据

如果在Publisher端使用如下脚本，删除所有的4个记录

delete dbo.dt_study_publication

那么在Subscriber端，等价的操作是在一个transaction中调用4次dbo.sp_MSdel_dbodt_study，如果在Subscriber端，数据有丢失，比如，PKColumn=4 的Record不存在，那么则会导致整个transactino失败，导致数据同步失败。

set XACT_ABORT on

begin tran
    exec dbo.sp_MSdel_dbodt_study @pkc1=1
    exec dbo.sp_MSdel_dbodt_study @pkc1=2
    exec dbo.sp_MSdel_dbodt_study @pkc1=3
    exec dbo.sp_MSdel_dbodt_study @pkc1=4
commit


set XACT_ABORT off

3，查看dbo.sp_MSupd_dbodt_study的源代码

使用@bitmap参数check是否更新主键，@pkc1 是主键列的值。update data command 使用 PK column作为update的Filter condition，保证每次只更新一条记录，如果更新的数据行数为0，则报错。

ALTER procedure [dbo].[sp_MSupd_dbodt_study]
        @c1 int = NULL,
        @c2 nvarchar(50) = NULL,
        @c3 bit = NULL,
        @pkc1 int = NULL,
        @bitmap binary(1)
as
begin  
if (substring(@bitmap,1,1) & 1 = 1)
begin 

update [dbo].[dt_study] set
        [id] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [id] end,
        [name] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [name] end,
        [sex] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [sex] end
    where [id] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end  
else
begin 

update [dbo].[dt_study] set
        [name] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [name] end,
        [sex] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [sex] end
    where [id] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end 
end 

4， 查看 [dbo].[sp_MSins_dbodt_study] 的源代码

每次插入一条数据，如果插入失败，Insert command报错。

ALTER procedure [dbo].[sp_MSins_dbodt_study]
    @c1 int,
    @c2 nvarchar(50),
    @c3 bit
as
begin  
    insert into [dbo].[dt_study](
        [id],
        [name],
        [sex]
    ) values (
    @c1,
    @c2,
    @c3    ) 
end  

三，更新Subscriber端数据的非主键属性

1，查看Publisher和Subscriber data snapshot

Publisher Data Snapshot

subscriber Data Snapshot

2， 在Publisher 端insert数据

insert into dbo.dt_study
(
ID,
name,
sex
)
values(5,'abc',0)

在Subscriber端查看数据更新

3， 在Publisher 端更新数据

update dbo.dt_study 
set name='update5'
where id=5

在Subscriber端查看数据更新

在Subscriber端update数据

update dbo.dt_study 
set name='update6'
where id=5

在Publisher 端更新数据

update dbo.dt_study 
set name='update7'
where id=5

在Subscriber端查看数据更新

可以看出，如果在Subscriber端对数据的非主键属性进行更新，那么不影响transaction replication同步数据。

四，更新Subscriber端数据的主键属性

1，在Subscriber端删除ID=5的记录

delete dbo.dt_study
where id=5

2，在Publisher 端 删除 dbo.dt_study 中ID>=5的所有记录

delete dbo.dt_study
where id>=5

3，subscriber Data Snapshot如下，subscriber数据不会更新，数据同步失败。

4，查看 Replication Monitor

在Distributor To Subscriber History Tab 发现Error Message：

The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)

5，WorkAround：在Subscriber 端中将缺失的Row PK 补上即可。

insert into dbo.dt_study
(ID,name,sex)
values(5,null,null)

6，执行失败Transaction和commands，Replication不会将其从Distribution database中移除，Distributor Agent 会多次重新执行失败的Transaction。

Transaction 何时从Distribution database中移除？

Transaction commands are stored in the distribution database until they are propagated to all Subscribers or until the maximum distribution retention period has been reached. Subscribers receive transactions in the same order in which they were applied at the Publisher.