Всем привет,

Есть вопрос архитектурного характера. Допустим есть у нас БД и табличка Person в которой у каждого Person-а есть имя.
Есть WEB приложение которое пытается добавлять в табличку Person-ов.
Задача простая, сделать так что бы при добавлении уже существующего Person-а выводилось соответствующая ошибка пользователю.
При этом:
1. По полю Name у Person-ов построен уникальный индекс.
2. Использовать IF EXIST в запросе не предлагать

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

Собственно дальше только вопросы. Где исключение лучше перехватывать, ведь перехватить его можно прямо в TSQL и потом сделать RAISE, либо уже перехватить в C# и как ьто анализировать поля, но опять же вопрос в том как правильно.

В общем спасибо за мысли

Здравствуйте, Tom, Вы писали:

Tom>Предпологается что я должен перехватить на каком то уровне сгенерированной SQL-ем исключение, как то проверить его поля и понять что это не баг в программе а нормальная ситуация и исключение агенерировано потому, что пользователь уже существует.
В таких сценариях до исключения лучше не доводить. Надо явно проверять, что пользователь уже существует.
Строго говоря, это не исключительный сценарий, а совершенно стандартный.

Здравствуйте, IB, Вы писали:

IB>Здравствуйте, Tom, Вы писали:

Tom>>Предпологается что я должен перехватить на каком то уровне сгенерированной SQL-ем исключение, как то проверить его поля и понять что это не баг в программе а нормальная ситуация и исключение агенерировано потому, что пользователь уже существует.
IB>В таких сценариях до исключения лучше не доводить. Надо явно проверять, что пользователь уже существует.
IB>Строго говоря, это не исключительный сценарий, а совершенно стандартный.
Как желать такую проверку? Учитывая различные параллельные сценарии (aka IF EXISTS не предлагать)?

Здравствуйте, Tom, Вы писали:

Tom>Всем привет,

Tom>Есть вопрос архитектурного характера. Допустим есть у нас БД и табличка Person в которой у каждого Person-а есть имя.
Tom>Есть WEB приложение которое пытается добавлять в табличку Person-ов.
Tom>Задача простая, сделать так что бы при добавлении уже существующего Person-а выводилось соответствующая ошибка пользователю.
Tom>При этом:
Tom>1. По полю Name у Person-ов построен уникальный индекс.
Tom>2. Использовать IF EXIST в запросе не предлагать

Tom>Предпологается что я должен перехватить на каком то уровне сгенерированной SQL-ем исключение, как то проверить его поля и понять что это не баг в программе а нормальная ситуация и исключение агенерировано потому, что пользователь уже существует.

Tom>Собственно дальше только вопросы. Где исключение лучше перехватывать, ведь перехватить его можно прямо в TSQL и потом сделать RAISE, либо уже перехватить в C# и как ьто анализировать поля, но опять же вопрос в том как правильно.

Tom>В общем спасибо за мысли

Не слушайте никого.
Надо обрабатывать исключение и определять, какие поля не уникальны. Идеально, если вы можете сами создать констрейнт в базе, дать ему имя, а затем распарсить исключение (способ зависит от СУБД) и отловить конкретно эту ошибку по имени констрейнта. IF EXIST и прочие варианты не помогут тут. Любые другие способы — усложнение. Стоит о них думать только если нельзя определить, что конкретно это условие уникальности нарушено (в нормальных СУБД определяется достаточно легко).

Здравствуйте, Tom, Вы писали:

IB>>В таких сценариях до исключения лучше не доводить. Надо явно проверять, что пользователь уже существует.
IB>>Строго говоря, это не исключительный сценарий, а совершенно стандартный.
Tom>Как желать такую проверку? Учитывая различные параллельные сценарии (aka IF EXISTS не предлагать)?
Я тоже согласен с IB — стандартный сценарий, до исключения не доводить. Лучше в бизнес-логике сделать отдельный рычаг IsNameExist, чтобы потом не мучиться из разных похожих сценариев. Что касается паралельности, то тут вопрос уже в том, что пользователю совершенно наплевать, что эксцепшион придет раз в 5 лет, и что он соревнуется за долю милисекунд с кем то(если пользователь человек, и это не быстрая OLTP).
Однако, ежели уже на этапе разгона, данная реализация начинает мучить тормознутостью (иногда такое бывает), то кто мешает сделать запрос:
INSERT INTO A (id, name, ...) select @id, @name.... from A b where @name not in (select name from A) (не уверен что в таком виде будет работать, не проверял) . Если не пришел эксцепшион, а в то же время кол-во измененных равно нулю, то проверяем причину. Если причина ясна (параметр только name), то выдаем результат. В случае наличия нескольких полей, дополнительно разбираемся с причиной.

Здравствуйте, Tom, Вы писали:

Tom>Как желать такую проверку? Учитывая различные параллельные сценарии (aka IF EXISTS не предлагать)?
А почему не предлагать? Параллельные сценарии отсекаются либо serializable IL, либо пользовательскими блокировками — второе в данном случае думаю будет дешевле и правильнее. Если боишься за производительность — откат транзакции в случае исключения штука очень дорогая, так что не переживай.

Здравствуйте, GlebZ, Вы писали:

INSERT INTO A (id, name, ...) select @id, @name.... from A b where @name not in (select name from A)
Обшибка
INSERT INTO A (id, name, ...) select @id, @name.... from dual_table where @name not in (select name from A)
dual_table — таблица с одной строкой.

Здравствуйте, Romanzek, Вы писали:

R>Здравствуйте, Tom, Вы писали:

Tom>>Всем привет,

Tom>>Есть вопрос архитектурного характера. Допустим есть у нас БД и табличка Person в которой у каждого Person-а есть имя.
Tom>>Есть WEB приложение которое пытается добавлять в табличку Person-ов.
Tom>>Задача простая, сделать так что бы при добавлении уже существующего Person-а выводилось соответствующая ошибка пользователю.
Tom>>При этом:
Tom>>1. По полю Name у Person-ов построен уникальный индекс.
Tom>>2. Использовать IF EXIST в запросе не предлагать

Tom>>Предпологается что я должен перехватить на каком то уровне сгенерированной SQL-ем исключение, как то проверить его поля и понять что это не баг в программе а нормальная ситуация и исключение агенерировано потому, что пользователь уже существует.

Tom>>Собственно дальше только вопросы. Где исключение лучше перехватывать, ведь перехватить его можно прямо в TSQL и потом сделать RAISE, либо уже перехватить в C# и как ьто анализировать поля, но опять же вопрос в том как правильно.

Tom>>В общем спасибо за мысли

R>Не слушайте никого.
R>Надо обрабатывать исключение и определять, какие поля не уникальны. Идеально, если вы можете сами создать констрейнт в базе, дать ему имя, а затем распарсить исключение (способ зависит от СУБД) и отловить конкретно эту ошибку по имени констрейнта. IF EXIST и прочие варианты не помогут тут. Любые другие способы — усложнение. Стоит о них думать только если нельзя определить, что конкретно это условие уникальности нарушено (в нормальных СУБД определяется достаточно легко).

Вот чего бы очень нехотелось — это парсинга текста исключения. По многим причинам:
1. Способ без какой либо поддержки компилятора
2. Нудно писать и тестировать

Общий вопрос, допустим мы где то словили SqlException и даже распарсили текст и поняли что у нас произошла проблема с попыткой дважды добавить пользователя с одинаковым именем. Что дальше? Я предпологаю что надо выкинуть некоторое более специализированное исключение, но вот детали пока незнаю как сделать. А именно. Надо ли на каждый тип обьекта и операцию делать свой тип исключения, или для подобных проблем хватит одного типа исключения. Например, надо ли писать PersonNotFound/PersonAlreadyExists/... исключения или достатосного одного класса исключения для случая когда его обработка выливается в показ сообщения о ошибке.

Здравствуйте, Tom, Вы писали:

Tom>Всем привет,

Tom>Есть вопрос архитектурного характера. Допустим есть у нас БД и табличка Person в которой у каждого Person-а есть имя.
Tom>Есть WEB приложение которое пытается добавлять в табличку Person-ов.
Tom>Задача простая, сделать так что бы при добавлении уже существующего Person-а выводилось соответствующая ошибка пользователю.
Tom>При этом:
Tom>1. По полю Name у Person-ов построен уникальный индекс.
Tom>2. Использовать IF EXIST в запросе не предлагать

В SqlServer 2008 есть MERGE statement, которое позволяет в рамках одного запроса делать и анализ и insert (либо update, delete).
Это если речь о нем.

Tom>>Как желать такую проверку? Учитывая различные параллельные сценарии (aka IF EXISTS не предлагать)?
IB>А почему не предлагать?
Да фиг знает, например потому, что операции могут выполняться в больших batch-ах. Ну и просто IF EXISTS для меня попахивает не очень профессиональным подходом. Вообще если какой то подход применять — то единообразно, в UPDATE/DELETE — ах так же. Да и непонятно что делать если всё таки IF EXIST мы прошли а дальше всё таки свалились с ошибкой.

IB>Параллельные сценарии отсекаются либо serializable IL,
serializable IL нуууу в принципе да. Надо по/обдумать.

IB>либо пользовательскими блокировками — второе в данном случае думаю будет дешевле и правильнее. Если боишься за производительность — откат транзакции в случае исключения штука очень дорогая, так что не переживай.
Откатов я не боюсь так как они уж очень редки будут. А можно в 3-х строках кода как ты думаешь использовать APP блокировки?

Здравствуйте, Tom, Вы писали:

Tom>Да фиг знает, например потому, что операции могут выполняться в больших batch-ах.
Ну и что?

Tom>Ну и просто IF EXISTS для меня попахивает не очень профессиональным подходом.
С чего бы? профессиональнее некуда.

Tom>Вообще если какой то подход применять — то единообразно, в UPDATE/DELETE — ах так же.
Чего даст это единообразие?

Tom>Да и непонятно что делать если всё таки IF EXIST мы прошли а дальше всё таки свалились с ошибкой.
С какой ошибкой? если правильно сделать то это точно не будет ошибка из-за дубликата.

Tom>Откатов я не боюсь так как они уж очень редки будут. А можно в 3-х строках кода как ты думаешь использовать APP блокировки?
Как-то так:

BEGIN TRAN
sp_getapplock @Resource = '<Имя>', @LockMode = 'Exclusive';
IF NOT EXISTS(SELECT 1 FROM ... WHERE name = '<имя>')
    INSERT .....
sp_releaseapplock @Resource = '<Имя>';
COMMIT

Таким образом все операции проверить/вставить над одним именем сериализуются, что гарантирует отсутствие конфликтов.

Здравствуйте, Tom, Вы писали:

Tom>В общем спасибо за мысли

Вариант без исключения и без IF EXISTS — добавить во временную таблицу, а потом INSERT INTO ... SELECT ... WHERE и смотреть сколько записей добавилось

Tom>>Ну и просто IF EXISTS для меня попахивает не очень профессиональным подходом.
IB>С чего бы? профессиональнее некуда.
Если ты говоришь что это нормально, то я верю. Значит нормально.

Tom>>Вообще если какой то подход применять — то единообразно, в UPDATE/DELETE — ах так же.
IB>Чего даст это единообразие?
Единообразие всегдя хорошо, хотя бы потому что поддерживать просто

Tom>>Да и непонятно что делать если всё таки IF EXIST мы прошли а дальше всё таки свалились с ошибкой.
IB>С какой ошибкой? если правильно сделать то это точно не будет ошибка из-за дубликата.
Ну да, если сделать с блокировками то да, дубликатов не будет.

Tom>>Откатов я не боюсь так как они уж очень редки будут. А можно в 3-х строках кода как ты думаешь использовать APP блокировки?
IB>Как-то так:
IB>

IB>BEGIN TRAN
IB>sp_getapplock @Resource = '<Имя>', @LockMode = 'Exclusive';
IB>IF NOT EXISTS(SELECT 1 FROM ... WHERE name = '<имя>')
IB>    INSERT .....
IB>sp_releaseapplock @Resource = '<Имя>';
IB>COMMIT
IB>

IB>Таким образом все операции проверить/вставить над одним именем сериализуются, что гарантирует отсутствие конфликтов.
Как я понимаю то же и для DELETE/UPDATE?

В принципе идея проста и мне нравится тем что не надо парсить SqlException.

Спасибо, буду подумать.

Здравствуйте, IT, Вы писали:

IT>Вариант без исключения и без IF EXISTS — добавить во временную таблицу, а потом INSERT INTO ... SELECT ... WHERE и смотреть сколько записей добавилось

Кстати, можно обойтись и без временной таблицы.

IT>>Вариант без исключения и без IF EXISTS — добавить во временную таблицу, а потом INSERT INTO ... SELECT ... WHERE и смотреть сколько записей добавилось

IT>Кстати, можно обойтись и без временной таблицы.
Тут мне сказали что IF EXISTS не так уж и плохо.
В принципе наверное да, плюс этого подхода в том что он тупой и простой.

Здравствуйте, Tom, Вы писали:

declare @firstName varchar(50)
declare @lastName  varchar(50)

set @firstName = 'John'

INSERT INTO Person (FirstName, LastName)
SELECT
    FirstName, LastName
FROM
(
    SELECT @firstName FirstName, @lastName LastName
) AS t
WHERE
    t.FirstName NOT IN (SELECT p.FirstName FROM Person p)

ExecuteNonQuery вернёт 0, если запись с таким FiratName уже существует.

Здравствуйте, Tom, Вы писали:

Tom>Единообразие всегдя хорошо, хотя бы потому что поддерживать просто
не тот случай.. )

Tom>Как я понимаю то же и для DELETE/UPDATE?
Нет необходимости. По блокировкам они не совместимы и даже если получится INSERT-у или UPDATE-у вклиниться, то логическойй ошибки быть не должно.

Здравствуйте, Tom, Вы писали:

Tom>Вот чего бы очень нехотелось — это парсинга текста исключения. По многим причинам:
Tom>1. Способ без какой либо поддержки компилятора
Tom>2. Нудно писать и тестировать
А еще если вспомнить, что текст исключения может быть локализован, то вообще грустно становится.

Здравствуйте, IT, Вы писали:

IT>ExecuteNonQuery вернёт 0, если запись с таким FiratName уже существует.
Не смотря на то, что это одно выражение, все три оператора исполняться будут независимо, (с точки зрения доступа к данным) и между всеми тремя конструкциями вполне может пролететь еще одна транзакция целиком, если планировщику так больше понравится.
Как следствие, гарантии от дубликатов здесь нет и по семантике это выражение эквивалентно IF EXISTS.
Иными словами, в пользовательские блокировки или SERIALIZABLE я бы это дело все равно обернул, если за дубликаты страшно..

Здравствуйте, Tom, Вы писали:

Tom>Вот чего бы очень нехотелось — это парсинга текста исключения. По многим причинам:

Если сервер вменяемый, то парсить текст не надо. Должно хватить кодов ошибок.