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

Статья
06/02/2023

Применимо к:Управляемому экземпляру SQL Server 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'

Это повторится для всех 10000 обновлений.

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

EXEC give_raise

Внимание

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

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

SQL Server Management Studio: публикация хранимой процедуры в публикации транзакций (SQL Server Management Studio)
Программирование Репликации Transact-SQL: выполнение sp_addarticle (Transact-SQL) и указание значения serializable proc exec (рекомендуется) или proc exec для параметра @type. Дополнительные сведения об определении статей см. в этой статье.

Изменение процедуры на подписчике

По умолчанию определение хранимой процедуры на издателе отправляется каждому подписчику. Тем не менее хранимую процедуру можно также изменить на подписчике. Эта возможность используется, когда требуется разная логика для выполнения процедуры на подписчике и на издателе. В качестве примера рассмотрим 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 транзакции Т1 выполняется раньше инструкции INSERT транзакции Т2.

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

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

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

Настройки XACT_ABORT

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

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

См. также

Article Options for Transactional Replication