|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
Коллеги, добрый день! Имеется процедура: Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32.
Данная процедура может вызываться параллельно с одинаковым Url: поток 1: exec [dbo].[AppPageUrl] 'http:\\page1' поток 2: exec [dbo].[AppPageUrl] 'http:\\page1' ... поток N: exec [dbo].[AddPageUrl] 'http:\\page1' Необходимо гарантия, что не будет дедлоков и процедура ВСЕГДА вернет Id СУЩЕСТВУЮЩЕЙ строки. Т.е. ситуации быть не должно: 1) Поток 1 вставляет строку 'http:\\page1' 2) Поток 2 вставляет строку 'http:\\page1', но возникает исключение, т.к. Поток 1 уже вставил данную строку. 3) Срабатывает блок CATCH в Потоке 2 и 'Select @Id = Id from ...' вернет NULL 4) Происходит COMMIT в Потоке 1 и строка 'http:\\page1' теперь имеется в БД Возможна ли данная ситуация для приведенного выше кода? Я проверил несколько раз, запуская сотни потоков, все работает без ошибок, но что-то не уверен. С блокировкиами только начал знакомиться, пока не хватает времен погрузиться в материал, а уже нужно в ПРОД))) ... |
|||
:
Нравится:
Не нравится:
|
|||
16.12.2021, 21:56 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
notemac, insert ... output ... все остальное выкиньте. ... |
|||
:
Нравится:
Не нравится:
|
|||
16.12.2021, 22:18 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
notemac, А вы xто с помощью try catch определяете есть ли запись? ... |
|||
:
Нравится:
Не нравится:
|
|||
16.12.2021, 22:37 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
Владислав Колосов, почитал про output и что-то не пойму, как он поможет упростить код. Процедура всегда должна возвращать корректный @Id только что вставленной или уже существуещей записи, т.е. один из потоков обязательно раньше других вставит новую запись и вернет ее @Id, а остальные потоки просто вернут ее @Id. a_voronin, я хочу гарантировать, что ситуация из 4 пунктов, которая описана в посте, будет исключена. try catch для отлова исключения, если запись была уже вставлена. Но что-то не удается воссоздать ситуацию, чтобы сработал try catch. Код отрабатывает нормально при множестве потоков (запускаю в несколько потоков из C#), т.е. INSERT в первом потоке успевает отработать до SELECT'ов в остальных потоках? Хрень какая-то... Ладно, посмотрим что будет на проде. Есть вариант блочить в первом SELECT таблицу (HOLDLOCK, XLOCK) для конца транзакции: Код: sql 1.
А если параллельно какие-нибудь запросы для отчетов будут SELECT делать, не будет ли здесь дедлоков, когда в этот же момент будет попытка захвата HOLDLOCK, XLOCK ... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 04:35 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
XLOCK на INSERT, чтобы другие потоки не могли вставить запись, пока первый поток это делает Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31.
Либо убрать XLOCK и добавить UNIQUE constraint на поле Url ... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 04:58 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
notemac Коллеги, добрый день! Имеется процедура: Код: sql 1. 2. 3.
Прям слезу умиления выжимает. Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11.
... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 05:52 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
notemac, Спасибо поржал)) ... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 10:14 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
notemac В a_voronin, я хочу гарантировать, что ситуация из 4 пунктов, которая описана в посте, будет исключена. try catch для отлова исключения, если запись была уже вставлена. Если вы правильно напишите код, то такой ситуации в принципе не возникнет. Напишите MERGE. В нем распишите, что делаете, если запись есть, и что делать если нет. У MERGE есть OUTPUT. Поймите, любая нормальная БД ориентирована на массовую вставку. Создавая исключение вы тормозите БД. Есть блокировки. Через них все решается. Начните думать в архитектуре БД, а не языка программирования. ... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 10:52 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
И кстати, вы не хотите хеш сдать для @Url VARCHAR(300) ? ... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 11:11 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
notemac, авторпочитал про output и что-то не пойму, как он поможет упростить код, Да, не обратил внимание, что надо вернуть ID, вставлен он был или нет. В таком случае можно использовать merge + output. Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31.
... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 13:05 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
Владислав Колосов notemac, авторпочитал про output и что-то не пойму, как он поможет упростить код, Да, не обратил внимание, что надо вернуть ID, вставлен он был или нет. В таком случае можно использовать merge + output. Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31.
Если не вставил, то select ... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 13:24 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
aleks222 notemac Коллеги, добрый день! Имеется процедура: Код: sql 1. 2. 3.
Прям слезу умиления выжимает. Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11.
Эту задачу про тракинг урлов (а иногда и блобов, картинок) не раз встречал в работе и не раз спрашивал на собеседованиях и сам был спрашиваем. То, что здесь написали многие, вызовет недовольство грамотных собеседующих. Первое -- если объект крупный (больше 20 байт) сразу напрашивается поле с хешом и индекс на него. Если вы траките урлы, то скорее всего у вас будут группы урлов, у которых первые 20-60 символов совпадают. Без хеша дикая потеря производительности. Второе, не надо никаких хинтов. Вы просто пишите мердж, который отрабатывает две ветки -- новая -- не новая. Если не новая, то Select. Или всегда Select. Все это работает многопоточно, даже когда несколько потоков вставляют одно и тоже новое значение. ... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 13:32 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
aleks222 notemac Коллеги, добрый день! Имеется процедура: Код: sql 1. 2. 3.
Прям слезу умиления выжимает. Что не так с SET XACT_ABORT ON и BEGIN TRY, это разве не стандартный паттерн? https://www.sommarskog.se/error_handling/Part1.html Коллеги, на основе ваших ответов родил такое: Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32. 33. 34. 35. 36. 37. 38. 39. 40. 41. 42. 43. 44. 45. 46. 47. 48. 49. 50. 51.
... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 17:34 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
a_voronin aleks222 пропущено... Прям слезу умиления выжимает. Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11.
Эту задачу про тракинг урлов (а иногда и блобов, картинок) не раз встречал в работе и не раз спрашивал на собеседованиях и сам был спрашиваем. То, что здесь написали многие, вызовет недовольство грамотных собеседующих. Первое -- если объект крупный (больше 20 байт) сразу напрашивается поле с хешом и индекс на него. Если вы траките урлы, то скорее всего у вас будут группы урлов, у которых первые 20-60 символов совпадают. Без хеша дикая потеря производительности. Второе, не надо никаких хинтов. Вы просто пишите мердж, который отрабатывает две ветки -- новая -- не новая. Если не новая, то Select. Или всегда Select. Все это работает многопоточно, даже когда несколько потоков вставляют одно и тоже новое значение. Ну... собеседователь вы ишо тот. 1. Пока размер данных меньше границы индекса 900 байт - всякое "хэширование" зло и мартышкин труд. 2. Хинт тут никому не мешает, а помочь может. Ибо мы гарантированно ищем ОДНУ строку. ... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 18:02 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
notemac Что не так с SET XACT_ABORT ON и BEGIN TRY, это разве не стандартный паттерн? Бред невозможно комментировать. Им можно только любоваться. ЗЫ. Ну начнем с простого: "нафига тут транзакция?" Шобы было? ... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 18:09 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
a_voronin Второе, не надо никаких хинтов. Вы просто пишите мердж, который отрабатывает две ветки -- новая -- не новая. Если не новая, то Select. Или всегда Select. Все это работает многопоточно, даже когда несколько потоков вставляют одно и тоже новое значение. Подготавливаем таблицу Код: sql 1. 2. 3. 4. 5. 6. 7.
Далее открываем три сессии в SSMS В первой выполняем Код: sql 1. 2. 3. 4. 5. 6.
Во второй и третьей запускаем Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15.
Далее в первой выполняем commit и ждем пока во второй или третьей не возникнет Violation of PRIMARY KEY constraint И лечится это повышением TIL мержа до serializable ... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 18:19 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
notemac, авторЧто не так с SET XACT_ABORT ON и BEGIN TRY, это разве не стандартный паттерн? ON и OFF перепутали. Тут надо или крестик снять или штаны надеть. То есть выбрать какой-то один способ обработки ошибки. ... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 18:39 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
Владислав Колосов ON и OFF перепутали. Тут надо или крестик снять или штаны надеть. То есть выбрать какой-то один способ обработки ошибки. Что у него неправильно? Erland SommarskogПараметр XACT_ABORT необходим для более надежной обработки ошибок и транзакций. В частности, при настройках по умолчанию есть несколько ситуаций, когда выполнение может быть прервано без какого-либо отката транзакции, даже если у вас есть TRY-CATCH. Мы видели такой пример в предыдущем разделе, где мы выяснили, что TRY-CATCH не перехватывает ошибки компиляции, возникшие в той же области. Открытая транзакция, которая не была откачена из-за ошибки, может вызвать серьезные проблемы, если приложение работает дальше без завершения транзакции или ее отката. Для надежной обработки ошибок в SQL Server вам необходимы как TRY-CATCH, так и SET XACT_ABORT ON. Среди них инструкция SET XACT_ABORT ON наиболее важна. Если для кода на промышленной среде только на нее полагаться не стоит, то для быстрых и простых решений она вполне подходит. ... |
|||
:
Нравится:
Не нравится:
|
|||
17.12.2021, 22:10 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
alexeyvg Владислав Колосов ON и OFF перепутали. Тут надо или крестик снять или штаны надеть. То есть выбрать какой-то один способ обработки ошибки. Что у него неправильно? Erland SommarskogПараметр XACT_ABORT необходим для более надежной обработки ошибок и транзакций. В частности, при настройках по умолчанию есть несколько ситуаций, когда выполнение может быть прервано без какого-либо отката транзакции, даже если у вас есть TRY-CATCH. Мы видели такой пример в предыдущем разделе, где мы выяснили, что TRY-CATCH не перехватывает ошибки компиляции, возникшие в той же области. Открытая транзакция, которая не была откачена из-за ошибки, может вызвать серьезные проблемы, если приложение работает дальше без завершения транзакции или ее отката. Для надежной обработки ошибок в SQL Server вам необходимы как TRY-CATCH, так и SET XACT_ABORT ON. Среди них инструкция SET XACT_ABORT ON наиболее важна. Если для кода на промышленной среде только на нее полагаться не стоит, то для быстрых и простых решений она вполне подходит. На солнце тоже есть пятна. set xact_abort on; Делает try чуть более чем бесполезным. При любой ошибке транзакция переходит в нефиксируемое состояние. Так зачем ее "обрабатывать"? ... |
|||
:
Нравится:
Не нравится:
|
|||
19.12.2021, 14:56 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
aleks222 set xact_abort on; Делает try чуть более чем бесполезным. При любой ошибке транзакция переходит в нефиксируемое состояние. Так зачем ее "обрабатывать"? Xact_abort нужен безотносительно try-catch, он выполняет другую задачу - гарантированный откат транзакции на тех ошибках, которые в catch не попадают, т.к. вызывают batch termination. 207, 208 и некоторые другие. Когда хранимка валится с ошибкой, но при этом в соединении остается открытая транзакция - удовольствие крайне ниже среднего. ... |
|||
:
Нравится:
Не нравится:
|
|||
19.12.2021, 17:41 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
Ennor Tiegael aleks222 set xact_abort on; Делает try чуть более чем бесполезным. При любой ошибке транзакция переходит в нефиксируемое состояние. Так зачем ее "обрабатывать"? Xact_abort нужен безотносительно try-catch, он выполняет другую задачу - гарантированный откат транзакции на тех ошибках, которые в catch не попадают, т.к. вызывают batch termination. 207, 208 и некоторые другие. Когда хранимка валится с ошибкой, но при этом в соединении остается открытая транзакция - удовольствие крайне ниже среднего. Я спрашивал зачем нужен xact_abort? ... |
|||
:
Нравится:
Не нравится:
|
|||
19.12.2021, 18:12 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
notemac Коллеги, добрый день! Имеется процедура: Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32.
Данная процедура может вызываться параллельно с одинаковым Url: поток 1: exec [dbo].[AppPageUrl] 'http:\\page1' поток 2: exec [dbo].[AppPageUrl] 'http:\\page1' ... поток N: exec [dbo].[AddPageUrl] 'http:\\page1' Необходимо гарантия, что не будет дедлоков и процедура ВСЕГДА вернет Id СУЩЕСТВУЮЩЕЙ строки. Т.е. ситуации быть не должно: 1) Поток 1 вставляет строку 'http:\\page1' 2) Поток 2 вставляет строку 'http:\\page1', но возникает исключение, т.к. Поток 1 уже вставил данную строку. 3) Срабатывает блок CATCH в Потоке 2 и 'Select @Id = Id from ...' вернет NULL 4) Происходит COMMIT в Потоке 1 и строка 'http:\\page1' теперь имеется в БД Возможна ли данная ситуация для приведенного выше кода? Я проверил несколько раз, запуская сотни потоков, все работает без ошибок, но что-то не уверен. С блокировкиами только начал знакомиться, пока не хватает времен погрузиться в материал, а уже нужно в ПРОД))) Что бы это значило? SELECT @Id = Id FROM dbo.PageUrls WHERE Url = @Url; выбрать первую зись, у которой Url = @Url, ну, выберет всегда какую-то из 500 с Url = @Url и возьмет ее ид. Когда появилсь структура таблицы - тем более . Шайтан ... |
|||
:
Нравится:
Не нравится:
|
|||
19.12.2021, 20:36 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
aleks222 set xact_abort on; Делает try чуть более чем бесполезным. При любой ошибке транзакция переходит в нефиксируемое состояние. Так зачем ее "обрабатывать"? Я так не делаю, но в принципе нельзя же сказать, что там в обработчике будет неисполняемый код, т.к. до него никогда не дойдёт. ... |
|||
:
Нравится:
Не нравится:
|
|||
19.12.2021, 20:46 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
Можно попасть в неприятную ситуацию, выполнив неконтролируемый откат при помощи SET XACT_ABORT ON. Эта настройка годится лишь для примитивных ситуаций вроде работ драйвера или ad-hoc выполнений. Касаемо того, что не все ошибки ловит try-catch, то, во-первых, откат произойдет при таких ошибках независимо от XACT_ABORT, во-вторых, это аварийная ситуация и надо принимать меры по исправлению а не считать нормальным завершением работы. ... |
|||
:
Нравится:
Не нравится:
|
|||
19.12.2021, 21:29 |
|
Многопоточная вставка и выборка одной и той же строки. Блокировки
|
|||
---|---|---|---|
#18+
Владислав Колосов Можно попасть в неприятную ситуацию, выполнив неконтролируемый откат при помощи SET XACT_ABORT ON. Эта настройка годится лишь для примитивных ситуаций вроде работ драйвера или ad-hoc выполнений. Касаемо того, что не все ошибки ловит try-catch, то, во-первых, откат произойдет при таких ошибках независимо от XACT_ABORT, во-вторых, это аварийная ситуация и надо принимать меры по исправлению а не считать нормальным завершением работы. Это с try лехко залететь в "неприятную ситуацию". А xact_abort on - православное воплощение принципа транзакционности: "либо целиком, либо никак". Ваще то, надо читать мануалы до конца SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs. Эта установка вырубает пакет(процедуру) в точке ошибки. Никакие инструкции в текущем батче более не исполняются (ну.... окромя блока try). Это самый простой и логичный способ обработки ошибок. Без try. TRY делает его слишком замудреным. Нате вот, мусолили уже https://www.sql.ru/forum/1276292-2/pokritikuyte-smeshivanie-xact-abort-try-i-tran-v-odnoy-procedure ... |
|||
:
Нравится:
Не нравится:
|
|||
20.12.2021, 06:57 |
|
|
start [/forum/search_topic.php?author=Andr2&author_mode=last_topics&do_search=1]: |
0ms |
get settings: |
8ms |
get forum list: |
15ms |
get settings: |
10ms |
get forum list: |
14ms |
check forum access: |
3ms |
check topic access: |
3ms |
track hit: |
179ms |
get topic data: |
12ms |
get forum data: |
3ms |
get page messages: |
69ms |
get tp. blocked users: |
2ms |
others: | 295ms |
total: | 613ms |
0 / 0 |