Истории о данных: случай с фантомным дубликатом

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

Порой мне кажется, что опытом работы с режимом SQLCMD обладает очень узкая группа специалистов. Поэтому я решил начать статью с небольшого введения в тему.

В свое время разработчики версии SQL Server 2005 ввели в состав продукта новое средство командной строки SQLCMD, заменившее инструмент OSQL. Новое средство базировалось не на интерфейсе ODBC, а на OLE-DB и располагало более широким набором возможностей. К примеру, внутри сценария можно было задействовать команды операционной системы.

С появлением среды SQL Server Management Studio пользователь получил возможность выполнять запросы в режиме SQLCMD. А это позволило встраивать в сценарии SQL команды других типов. Так, следующий сценарий не мог выполняться в обычном окне запросов:

SELECT TOP (2) * FROM sys.tables;
!! DIR

Но мы можем вместо режима запросов выбрать режим SQLCMD (см. экран 1).

Экран 1. Запуск режима SQLCMD

Теперь команда! !DIR подсвечивается, и сценарий выполняется (см. экраны 2 и 3).

Экран 2. Команда !!DIR подсвечивается

Экран 3. Сценарий выполняется

В режиме SQLCMD пользователю доступно достаточное количество полезных команд:

[!!:]GO [count]
!! 
: exit (statement)
: Quit
: r 
: setvar  
: connect server [\instance] [-l login_timeout] [-U user [-P password]]
: on error [ignore|exit]
: error |stderr|stdout
: out |stderr|stdout

За годы работы с продуктом я выбрал для себя самую полезную команду. Она указывает, на какой сервер будут пересылаться команды. Так, если в данный момент я подключен к серверу SERVER1, я могу выполнить следующий сценарий:

: CONNECT ProdServer
SELECT @@SERVERNAME;

Этот сценарий обеспечит подключение к серверу ProdServer и выполнение команд на указанном сервере. Именем сервера также может быть псевдоним SQL Server. Использование псевдонимов поможет уменьшить зависимость сценариев от имен серверов. Для выполнения процедуры аутентификации (если вы можете ограничиться аутентификацией Windows) от вас не потребуется никаких дополнительных сведений. В иных случаях для выполнения аутентификации SQL Server вы можете предоставить имя пользователя и пароль.

Теперь пора приступать к рассмотрению примера, приведенного в начале статьи. Итак, наш неустрашимый администратор (пусть это будет девушка по имени Мэри) работает с базой данных ReferenceData. Компания располагает четырьмя версиями этой базы данных, и они выполняются в различных средах: Development, Staging, UAC и Production.

Время от времени Мэри должна вводить новые контрольные значения в одну и ту же таблицу во всех четырех копиях базы данных. Предположим, ей нужно было ввести новое название страны в таблицу Countries. Самое молодое государство в мире — Южный Судан. Давайте возьмем для примера эту страну.

На каждом сервере Мэри приходится выполнять следующий запрос:

INSERT ReferenceData.dbo.Countries
    (CountryName, ShortISOCode,
     LongISOCode, PhonePrefix)
    VALUES (N'South Sudan', N'SS', N'SSD',
    N'211');

Похоже, что в режиме SQLCMD эта проблема решается просто. Мэри написала сценарий, который приведен в листинге 1.

Когда этот сценарий выполнялся внутри базы данных ReferenceData на сервере DevServer, появилось сообщение об ошибке (см. экран 4).

Экран 4. Сообщение об ошибке

Таблица была определена так, как показано в листинге 2.

Мэри была озадачена: она была убеждена, что эти данные еще не были представлены в таблице, но было ясно, что ограничительное условие на уникальность для столбца LongISOCode не соблюдается. Быстрая проверка показала, что Мэри была права (результат показан на экране 5):

SELECT *
FROM dbo.Countries
WHERE LongISOCode = 'SSD';

Экран 5. Результат проверки

Всякий раз, когда Мэри выполняла данный сценарий, на экране появлялось сообщение о том, что эти сведения уже содержатся в таблице.

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

вводимые оператором данные уже представлены в таблице;
оператор пытается ввести данные неоднократно; возможно, это происходит внутри одной и той же инструкции.

Мы знаем, что первый вариант объяснения в данном случае не подходит, так что мы имеем дело со вторым вариантом. Но как операции INSERT могут приводить к неоднократному введению одного и того же значения?

Все дело в том, каким именно образом среда SQL Server Management Studio (SSMS) осуществляет обработку пакетов. Сценарий T-SQL может включать в себя один или несколько пакетов. Оператор GO в сущности не является оператором T-SQL, это разделитель пакетов. Поэтому, когда вы выполняете такой, например, сценарий T-SQL:

SELECT @@VERSION;
GO
SELECT @@SERVERNAME;
GO
SELECT GETDATE ();
GO

среда SSMS не направляет процессору базы данных весь текст сценария. Она выявляет все разделители GO и с их помощью разбивает сценарий на фрагменты. Может показаться, что весь сценарий выполняется «за один проход», но это не так. На сервер направляется и выполняется сначала команда SELECT @@VERSION; затем направляется на сервер и выполняется команда SELECT @@SERVERNAME; и, наконец, на сервер направляется и выполняется команда SELECT GETDATE ();.

Здесь важно отметить, что оператор GO так и не направляется на SQL Server. Он не относится к категории команд T-SQL. Этот оператор имеет смысл только для средства SSMS. В сущности, вместо него вы можете использовать другое значение. Если в меню Tools вы откроете пункт Options, то увидите то, что показано на экране 6.

Экран 6. Меню Tools, пункт Options

Я часто вспоминаю своего друга, поклонника телесериала Star Trek, который в качестве разделителя пакетов использует слово ENGAGE. Его сценарии выглядят занятно, но проку от них, с точки зрения других пользователей, немного. Кстати, интересная идея для розыгрыша нового администратора баз данных: установить в качестве разделителя слово SELECT и посмотреть, как он будет искать ошибку.

Важно понимать, что оператор GO реализован в среде SSMS, но не в процессоре SQL Server. Именно по этой причине мы можем добавить к предложениям число повторений (см. экран 7).

Экран 7. Повторение предложения

Обратите внимание: система intellisense не понимает, что означает счетчик, помещенный после оператора GO. И происходит следующее: среда SSMS направляет соответствующий пакет в систему SQL Server 100 раз.

Так вот, в нашем случае проблема состоит в том, что оператор: CONNECT в режиме SQLCMD не является разделителем пакетов. Поэтому выполнение сценария, приведенного в листинге 3, вызывает проблемы.

Смотрите, что здесь происходит. Оператор: CONNECT опять-таки реализован в среде SSMS, но не в SQL Server, и все эти команды считаются входящими в один пакет. Поэтому конечный результат состоит в следующем: все четыре оператора INSERT выполняются на сервере ProdServer.

Мне такое поведение представляется странным. Как утверждается в статье Википедии «Принцип наименьшего удивления» (Principle of Least Astonishment), если необходимая функция обладает высоким «фактором удивления», возможно, ее необходимо спроектировать заново. Я сталкивался с такой проблемой в прошлом и написал разработчикам SQL Server, что ее необходимо решить (https://connect.microsoft.com/SQLServer/feedback/details/611144/sqlcmd-connect-to-a-different-server-should-be-an-implicit-batch-separator), но их ответ состоял в том, что решения не будет (Won’t Fix). Если вы разделяете мою точку зрения, то, возможно, захотите оставить комментарий и тоже донести свою позицию до разработчиков.

Но вернемся к нашему администратору. Мэри следовало бы выполнить команду как в листинге 4. В этом случае сценарий выполнялся бы в соответствии с замыслом администратора.

Листинг 1. Добавление страны в таблицу Countries в режиме SQLCMD

:CONNECT DevServer
INSERT ReferenceData.dbo.Countries
    (CountryName, ShortISOCode, LongISOCode, PhonePrefix)
    VALUES (N'South Sudan', N'SS', N'SSD', N'211');

:CONNECT StagingServer
INSERT ReferenceData.dbo.Countries
    (CountryName, ShortISOCode, LongISOCode, PhonePrefix)
    VALUES (N'South Sudan', N'SS', N'SSD', N'211');

:CONNECT UATServer
INSERT ReferenceData.dbo.Countries
    (CountryName, ShortISOCode, LongISOCode, PhonePrefix)
    VALUES (N'South Sudan', N'SS', N'SSD', N'211');

:CONNECT ProdServer
INSERT ReferenceData.dbo.Countries
    (CountryName, ShortISOCode, LongISOCode, PhonePrefix)
    VALUES (N'South Sudan', N'SS', N'SSD', N'211');

Листинг 2. Определение таблицы

CREATE TABLE dbo.Countries

(
    CountryID int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_dbo_Countries PRIMARY KEY,
    CountryName nvarchar(100) NOT NULL
        CONSTRAINT UQ_dbo_Countries_CountryNamesMustBeUnique UNIQUE,
    ShortISOCode nvarchar(2) NOT NULL
        CONSTRAINT UQ_dbo_Countries_ShortISOCodesMustBeUnique UNIQUE,
    LongISOCode nvarchar(3) NOT NULL
        CONSTRAINT UQ_dbo_Countries_LongISOCodesMustBeUnique UNIQUE,
    PhonePrefix nvarchar(10) NOT NULL
);

GO

Листинг 3. Сценарий с ошибкой

:CONNECT DevServer
INSERT ReferenceData.dbo.Countries
    (CountryName, ShortISOCode, LongISOCode, PhonePrefix)
    VALUES (N'South Sudan', N'SS', N'SSD', N'211');

:CONNECT StagingServer
INSERT ReferenceData.dbo.Countries
    (CountryName, ShortISOCode, LongISOCode, PhonePrefix)
    VALUES (N'South Sudan', N'SS', N'SSD', N'211');

:CONNECT UATServer
INSERT ReferenceData.dbo.Countries
    (CountryName, ShortISOCode, LongISOCode, PhonePrefix)
    VALUES (N'South Sudan', N'SS', N'SSD', N'211');

:CONNECT ProdServer
INSERT ReferenceData.dbo.Countries
    (CountryName, ShortISOCode, LongISOCode, PhonePrefix)
    VALUES (N'South Sudan', N'SS', N'SSD', N'211');

Листинг 4. Правильный сценарий

:CONNECT DevServer
INSERT ReferenceData.dbo.Countries
    (CountryName, ShortISOCode, LongISOCode, PhonePrefix)
    VALUES (N'South Sudan', N'SS', N'SSD', N'211');
GO

:CONNECT StagingServer
INSERT ReferenceData.dbo.Countries
    (CountryName, ShortISOCode, LongISOCode, PhonePrefix)
    VALUES (N'South Sudan', N'SS', N'SSD', N'211');
GO

:CONNECT UATServer
INSERT ReferenceData.dbo.Countries
    (CountryName, ShortISOCode, LongISOCode, PhonePrefix)
    VALUES (N'South Sudan', N'SS', N'SSD', N'211');
GO

:CONNECT ProdServer
INSERT ReferenceData.dbo.Countries
    (CountryName, ShortISOCode, LongISOCode, PhonePrefix)
    VALUES (N'South Sudan', N'SS', N'SSD', N'211');
GO