Публикация выполнения хранимой процедуры в транзакционной репликации

Область применения: SQL Server Управляемый экземпляр SQL Azure

Если существует одна или несколько хранимых процедур, выполняемых на издателе и влияющих на опубликованные таблицы, рассмотрите возможность включения в публикацию этих хранимых процедур в виде статей выполнения хранимых процедур. Определение процедуры (инструкции CREATE PROCEDURE) копируется Подписчику при инициализации подписки; когда процедура выполняется у Издателя, репликация выполняет соответствующую процедуру у Подписчика. Это может обеспечить значительное повышение производительности в случаях, когда выполняются крупные пакетные операции, поскольку реплицируется только выполнение процедуры и исключается необходимость репликации отдельных изменений для каждой строки. Например, предположим, что создана следующая хранимая процедура в базе данных публикации:

CREATE PROC give_raise AS  
UPDATE EMPLOYEES SET salary = salary * 1.10

Процедура на 10 процентов увеличивает зарплату каждого из 10000 сотрудников компании. При выполнении этой хранимой процедуры у издателя обновляется зарплата каждого сотрудника. Без репликации выполнения хранимой процедуры обновление будет отправлено подписчикам в виде большой многошаговой транзакции:

BEGIN TRAN  
UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE PK = 'emp 1'  
UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE PK = 'emp 2'

И это повторяется в течение 10 000 обновлений.

С помощью репликации выполнения хранимой процедуры отправляется только команда для выполнения хранимой процедуры на подписчике вместо записи всех обновлений в базу данных распространителя и последующей отправки этих значений по сети на подписчик:

EXEC give_raise

Внимание

Репликация хранимых процедур подходит не для всех приложений. Если статья отфильтрована по горизонтали так что у Издателя и Подписчика разные наборы строк, выполнение одной и той же хранимой процедуры на обоих приведет к разным результатам. Аналогично, если обновление основано на подзапросе к другой, нереплицируемой таблице, выполнение одной и той же хранимой процедуры как на издателе, так и на подписчике приводит к разным результатам.

Публикация выполнения хранимой процедуры

Изменение процедуры у абонента

По умолчанию определение хранимой процедуры у издателя распространяется на каждого подписчика. Тем не менее вы также можете изменить сохранённую хранимую процедуру на стороне подписчика. Эта возможность используется, когда требуется разная логика для выполнения процедуры на подписчике и на издателе. В качестве примера рассмотрим sp_big_delete, хранимую процедуру на издателе, которая выполняет две функции: процедура удаляет 1 000 000 строк из реплицируемой таблицы big_table1 и обновляет нереплицируемую таблицу big_table2. Чтобы уменьшить необходимый объем сетевых ресурсов, следует передать удаление 1 миллиона строк в виде хранимой процедуры посредством публикации sp_big_delete. На подписчике можно изменить хранимую процедуру sp_big_delete, чтобы удалить только 1 миллион строк и не выполнять последующее обновление таблицы big_table2.

Примечание.

По умолчанию, все изменения, внесённые с помощью ALTER PROCEDURE у Издателя, передаются Подписчику. Чтобы предотвратить это, отключите распространение изменений схемы перед выполнением ALTER PROCEDURE. Сведения об изменениях в схеме см. в разделе Внесение изменений в схемы баз данных публикации.

Типы статей выполнения хранимых процедур

Существует два способа, с помощью которых можно опубликовать выполнение хранимой процедуры: статья выполнения сериализуемой процедуры и статья выполнения процедуры.

  • Рекомендуется использовать сериализуемый вариант процедуры, поскольку для него выполнение процедуры реплицируется только в том случае, когда процедура выполняется в контексте сериализуемой транзакции. Если хранимая процедура выполняется вне сериализуемой транзакции, изменения данных в опубликованных таблицах реплицируются в виде серий DML-инструкций. Такое поведение позволяет согласовывать данные на подписчике с данными на издателе. Эта возможность особенно удобна для пакетных операций, таких как большие операции очистки.

  • С помощью параметра выполнения процедуры можно реплицировать выполнение процедуры на все подписчики независимо от того, удачно ли были выполнены отдельные инструкции хранимой процедуры. Более того, поскольку изменения данных, совершаемые хранимой процедурой, могут произойти в нескольких транзакциях, возможна несогласованность данных на подписчиках с данными на издателе. Для устранения этих проблем необходимо, чтобы подписчики были доступны только для чтения, и использовать уровень изоляции выше read uncommitted. Если используется изоляция read uncommitted, изменения данных в опубликованных таблицах реплицируются в виде последовательности DML-инструкций.

В следующем примере показывается, почему рекомендуется настройка репликации процедур в виде сериализуемых статей процедур.

BEGIN TRANSACTION T1  
SELECT @var = max(col1) FROM tableA  
UPDATE tableA SET col2 = <value>   
   WHERE col1 = @var   
  
BEGIN TRANSACTION T2  
INSERT tableA VALUES <values>  
COMMIT TRANSACTION T2

В предыдущем примере предполагается, что оператор SELECT в транзакции T1 выполняется перед INSERT в транзакции T2.

Если процедура не выполняется в сериализуемой транзакции (с уровнем изоляции, установленным как SERIALIZABLE), транзакции T2 будет разрешено вставить новую строку в диапазон, охватываемый оператором SELECT в T1, и зафиксировать её раньше T1. Это также означает, что процедура будет применяться на подписчике до транзакции Т1. Когда T1 применяется на подписчике, SELECT потенциально может вернуть иное значение, чем у издателя, и это может привести к результату, отличному от UPDATE.

Если процедура выполняется в рамках сериализуемой транзакции, транзакции T2 не будет разрешено вставлять строки в диапазон, охватываемый оператором SELECT в T2. Это будет заблокировано до тех пор, пока T1 не зафиксирует транзакцию, что обеспечит те же результаты у подписчика.

Блокировки удерживаются дольше, когда процедура выполняется в рамках сериализуемой транзакции, и могут привести к снижению степени параллелизма.

Параметр XACT_ABORT

При репликации выполнения хранимой процедуры параметр сеанса, выполняющего хранимую процедуру, должен указывать XACT_ABORT ON. Если для XACT_ABORT задано значение OFF и при выполнении процедуры у Издателя возникает ошибка, то та же ошибка возникнет у Подписчика, что приведет к сбою агента распространения. Указание XACT_ABORT ON гарантирует, что все ошибки, возникшие во время выполнения в Publisher, вызывают откат всего выполнения, избегая сбоя агент распространения. Дополнительные сведения о настройке XACT_ABORTсм. в разделе SET XACT_ABORT (Transact-SQL).

Если требуется параметр XACT_ABORT OFF, укажите параметр -SkipErrors для агент распространения. Это позволит агенту продолжить применение изменений на подписчике, даже если возникнет ошибка.

Связанный контент

  • Last updated on