SQL 2008 트랜잭션 복제에서 아티클 추가시 대기현상
@ 상황
t1 테이블을 replA DB -> replB DB로 복제하고 있습니다.
여기에 t2 테이블을 복제에 추가하려고 하는데 관계없는 t3 테이블의 대기 현상에 의해 t2 테이블을 아티클로 추가할 수 없는 현상이 발생합니다.
-- 1. 테스트를위해replA , replB DB초기값세팅
create database replA
create database replB
use replA
go
create table t1 (col1 int not null, col2 int)
create table t2 (col1 int not null, col2 int)
create table t3 (col1 int not null, col2 int)
alter table t1 add constraint pk_t1 primary key (col1)
alter table t2 add constraint pk_t2 primary key (col1)
alter table t3 add constraint pk_t3 primary key (col1)
GO
use replB
go
create table t1 (col1 int not null, col2 int)
create table t2 (col1 int not null, col2 int)
create table t3 (col1 int not null, col2 int)
alter table t1 add constraint pk_t1 primary key (col1)
alter table t2 add constraint pk_t2 primary key (col1)
alter table t3 add constraint pk_t3 primary key (col1)
-- 2. t1 테이블을replA DB -> replB DB로복제추가
한장비에서 테스트를 하며, 배포 구성은 되어있다고 가정합니다.
현재 테스트에 사용하는 SQL 2008은 TTEMPDB09-TEST\SQL2008 라는 인스턴스 입니다. ^^
|
Use master exec sp_replicationdboption @dbname = N'replA', @optname = N'publish', @value = N'true' exec [replA].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1 exec [replA].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1 GO -- 트랜잭션게시를추가하는중 use [replA] exec sp_addpublication @publication = N'pubt1', @description = N'게시자''TTEMPDB09-TEST\SQL2K8''의데이터베이스''replA''에대한트랜잭션게시입니다.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false' exec sp_addpublication_snapshot @publication = N'pubt1', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1 exec sp_grant_publication_access @publication = N'pubt1', @login = N'sa' exec sp_grant_publication_access @publication = N'pubt1', @login = N'NT AUTHORITY\SYSTEM' exec sp_grant_publication_access @publication = N'pubt1', @login = N'TTEMPDB09-TEST\Dkfhskals!#' exec sp_grant_publication_access @publication = N'pubt1', @login = N'distributor_admin' -- 트랜잭션아티클을추가하는중 exec sp_addarticle @publication = N'pubt1', @article = N't1', @source_owner = N'dbo', @source_object = N't1', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N't1', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [dbo].[sp_MSins_dbot1]', @del_cmd = N'CALL [dbo].[sp_MSdel_dbot1]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_dbot1]' -- 트랜잭션구독을추가하는중 exec sp_addsubscription @publication = N'pubt1', @subscriber = N'TTEMPDB09-TEST\SQL2K8', @destination_db = N'replB', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0 exec sp_addpushsubscription_agent @publication = N'pubt1', @subscriber = N'TTEMPDB09-TEST\SQL2K8', @subscriber_db = N'replB', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor' GO |
-- 3. 복제에사용되는SP 생성
-- 아래 쿼리의 결과 스크립트를 replB DB에서 수행하여 SP를 생성해 준다.
use replA
go
sp_scriptpublicationcustomprocs @publication = 'pubt1'
-- 아래 쿼리를 통해 데이터가 잘 넘어가는지 확인해 본다.
use replA
go
-- 데이터 입력
insert into t1 select 1,1
-- 복제 되었는지 확인
select * from replA..t1
select * from replB..t1
|
자. 복제가 잘 넘어간다면 이제 준비는 모두 되었습니다. 이제 t2 테이블을 복제에 추가할꺼고, t3 테이블에는 스키마 락을 걸 수 있는 동작을 하나 해 보겠습니다. 저는 트랜잭션을 걸고 인덱스를 하나 생성 하였습니다. 물론 commit , rollback 명령은 수행하지 않고 계속 Lock을 잡고 있도록 합니다. |
begin tran
create index idx_t3 on t3 (col2)
-- rollback
다른 세션을 생성하여 복제에 아티클을 하나 추가해 보겠습니다.
-- 트랜잭션 아티클을 추가하는 중
use [replA]
exec sp_addarticle @publication = N'pubt1', @article = N't2', @source_owner = N'dbo', @source_object = N't2', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N't2', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [dbo].[sp_MSins_dbot2]', @del_cmd = N'CALL [dbo].[sp_MSdel_dbot2]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_dbot2]'
GO
exec sp_addsubscription @publication = N'pubt1', @subscriber = N'TTEMPDB09-TEST\SQL2K8', @destination_db = N'replB', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
GO
-- use [replA]
-- exec sp_dropsubscription @publication = N'pubt1', @article = N't2', @subscriber = N'all', @destination_db = N'all'
-- GO
-- use [replA]
-- exec sp_droparticle @publication = N'pubt1', @article = N't2', @force_invalidate_snapshot = 1
-- GO
sp_addarticle 수행시 추가하려는 t2가 아닌 t3 테이블의 대기에 의해 t2 테이블이 아티클로 추가되지 않습니다. (제거는 됩니다. ^^;)
혹시나 이때 대기가 발생한 것을 알지 못하고 시간이 오래 걸린다고 sp_addarticle을 수행중이던 세션에서 중지 버튼을 누르셨다면..
t3 테이블에 대기 걸고 있던 작업은 끝난 상태에서 다시 복제를 추가하려고 한다면 아래 오류를 만나실 수 있습니다.
|
메시지14030, 수준16, 상태1, 프로시저sp_MSrepl_addarticle, 줄1263 아티클't2'이(가) 게시'pubt1'에있습니다. 메시지15002, 수준16, 상태1, 프로시저sp_MSrepl_addsubscription, 줄388 트랜잭션내에서프로시저'sp_addsubscription'을(를) 실행할수없습니다. |
이때는 당황하지 마시고 rollback을 수행해 주시면 됩니다.
아티클을 추가하면서 트랜잭션이 시작되고 중지 버튼에 의해 자동으로 rollback을 하지 못하여 발생하는 문제 입니다.
rollback 후 다시 아티클을 추가하면 잘 추가되는 것을 확인할 수 있습니다.
이 현상은 SQL 2000에서는 발생하지 않는 것을 확인 하였습니다.
아마도 SQL 2008에서는 복제 추가시 달라진 부분이 있는 것 같습니다.
복제 작업을 다른 작업과 병렬로 수행해야 할 때 조심해야 할 것 같습니다.
== 추가 내용 ==
트레이스 걸어보면 아래와 같이 쿼리를 조회 하면서 대기가 발생 하는것을 확인할 수 있습니다.
시스템 테이블에 인덱스가 필요해 보입니다.
IF EXISTS (SELECT so1.name
FROM sys.objects as so1
join sys.objects as so2
on so2.object_id = so1.parent_object_id
WHERE so1.parent_object_id = @tabid
AND so1.type = 'PK'
AND so2.type = 'U')
