Есть вопрос архитектурного характера. Допустим есть у нас БД и табличка 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, либо пользовательскими блокировками — второе в данном случае думаю будет дешевле и правильнее. Если боишься за производительность — откат транзакции в случае исключения штука очень дорогая, так что не переживай.
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>>Ну и просто 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 не так уж и плохо.
В принципе наверное да, плюс этого подхода в том что он тупой и простой.
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 я бы это дело все равно обернул, если за дубликаты страшно..