Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 дедлоки...  [new]
no56892
Member

Откуда:
Сообщений: 590
Есть таблица, туда загружается большой файл. То есть примерно 40 потоков параллельно делают следущее:

BEGIN TRAN
SELECT * FROM T WHERE FIELD=?
если есть запись
UPDATE T SET ... WHERE FIELD=?
иначе
INSERT INTO T...
COMMIT

Часть записей отваливается с ошибкой что то вроде deadlock detected victim...
Я подозреваю происходит это из-за:
поток 1 выполнил селект на запись SHAREDLOCK, второй(ые) выполняют селект и ставят опят SHAREDLOCK на ту же запись, никто из них не может выполнить UPDATE из-за наставленных SHAREDLOCK ов.

SELECT WITH(UPDLOCK) не помогает.
Как здесь организовать что то наподобие ораклового SELECT FOR UPDATE ? Что бы ставил сразу WRITELOCK при селекте до конца транзакции? Или может есть лучше варианты?
3 мар 18, 00:23    [21234022]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
не помогает
Guest
no56892,

Надо профайлером поймать граф дедлока.
Перед этим можно посмотреть на фактический план выполнения подобных запросов.

Большой файл надо балком запульнуть в staging-таблицу и всякой построчной фигней заниматься на staging-таблице.
3 мар 18, 00:40    [21234042]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
Hello world!11
Guest
на поле FIELD есть индекс?

грузите болшой файл построчно?
3 мар 18, 00:42    [21234043]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
no56892
Member

Откуда:
Сообщений: 590
автор
Большой файл надо балком запульнуть в staging-таблицу и всякой построчной фигней заниматься на staging-таблице.

Не вариант

автор
грузите болшой файл построчно?

Да, там условно в нем присутствуют элементы, которые по одному читаются и раскидываются по 40 потокам, чья задача собственно и запихнуть в базу по одному.

автор
на поле FIELD есть индекс?

Делал, не помогло. Может не того типа? Nonclustered, поле VARCHAR
3 мар 18, 00:49    [21234046]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
sparrow
Member

Откуда: Россия, Красноярск.
Сообщений: 21759
надо повышать уровень изоляции
3 мар 18, 08:06    [21234165]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
sparrow
Member

Откуда: Россия, Красноярск.
Сообщений: 21759
заранее
3 мар 18, 08:07    [21234166]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
sparrow
Member

Откуда: Россия, Красноярск.
Сообщений: 21759
или новые индексы создавать.
3 мар 18, 08:08    [21234167]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
sparrow
Member

Откуда: Россия, Красноярск.
Сообщений: 21759
какой тип там у FILEID? int?
3 мар 18, 08:10    [21234168]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
no56892
Member

Откуда:
Сообщений: 590
Varchar(30). Опытным путем установлено, что select with (update) ставит U блокировку на ROW, а также IU на PAGE. Я так понял IU этонорм и от нее не избавиться? Тоесть все ок вроде бы, пока не закоммитится селект, вторая транзакция висит на селекте ждет, что ок. Правда тестировал на другом скл сервере, мб настройки разные у них, но это вряд ли...
3 мар 18, 09:36    [21234251]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
invm
Member

Откуда: Москва
Сообщений: 9116
1. Бестолку обсуждать дедлок без его графа.
2.
no56892
BEGIN TRAN
SELECT * FROM T WHERE FIELD=?
если есть запись
UPDATE T SET ... WHERE FIELD=?
иначе
INSERT INTO T...
COMMIT
Эту красоту заменить на один MERGE. Или, по крайней мере, убрать SELECT.
3 мар 18, 09:48    [21234268]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
SHAREDLOCK
Guest
no56892,

автор
пока не закоммитится селект

Это не быстро. Надо подождать пока это придумают.
3 мар 18, 10:31    [21234313]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
no56892
40 потоков параллельно делают следущее:

SELECT * FROM T WHERE FIELD=?
на вскидку...

- раскидывайте по потокам данные с разными FIELD чтобы вероятность их пересечения минимизировалась

- делать в три шага (если допустимо с точки зрения транзакционности):
1. получить из файла уникальные FIELD
2. прочитать таблицу пометив какие FIELD есть каких нет (читать из таблицы PK если есть и складывать в
памяти в структуру маппинга FIELD - PK)
3. делать update
4. делать инсерт

- таблицу секционировать чтобы отдельный поток лился в свою секцию (секции потом объединять если нужно)

это так, вообще а, конкретика только после графа дедлока
3 мар 18, 11:03    [21234357]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
sparrow
Member

Откуда: Россия, Красноярск.
Сообщений: 21759
Varchar(30) классный тип,
Вы нормализацию пробовали делать?
3 мар 18, 11:26    [21234417]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
sparrow
Member

Откуда: Россия, Красноярск.
Сообщений: 21759
там Чё гуид?
3 мар 18, 11:50    [21234447]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
aleks222
Guest
no56892
автор
Большой файл надо балком запульнуть в staging-таблицу и всякой построчной фигней заниматься на staging-таблице.

Не вариант


Ну... продолжайте маяться фигней... в сорок потоков.

no56892
Как здесь организовать что то наподобие ораклового SELECT FOR UPDATE ? Что бы ставил сразу WRITELOCK при селекте до конца транзакции? Или может есть лучше варианты?

with(holdlock)


no56892
Или может есть лучше варианты?


Как вам уже докладывали, лучше - BULK INSERT. Хоть в сорок потоков.
А потом... merge в один.
3 мар 18, 12:10    [21234471]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
sparrow
Member

Откуда: Россия, Красноярск.
Сообщений: 21759
aleks222,
нету, ключ длинный
3 мар 18, 12:28    [21234505]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
no56892
Member

Откуда:
Сообщений: 590
main.BMSG:
BMSGID NUMMERIC(18,0) NOT NULL
BMSG_NUMBER VARCHAR(30) NOT NULL
BMSG_PAYLOAD VARCHAR(255) NULL

NONCLUSTEREDINDEX (NON UNIQUE, NONCLUSTERD) ON BMSG)NUMBER

К сообщению приложен файл (123.xdl - 23Kb) cкачать
4 мар 18, 18:11    [21236034]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
.Евгений
Member

Откуда:
Сообщений: 493
invm
Эту красоту заменить на один MERGE. Или, по крайней мере, убрать SELECT.

40 потоков MERGE? OMG...

Как насчет секционирования или даже разбиения исходной таблицы на несколько?
4 мар 18, 21:39    [21236345]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
no56892
Member

Откуда:
Сообщений: 590
Опытным путем установлена доп инфа:
Select for update with (updlock) where x=y а затем update where x=y работают как и ржидалось. Тоесть пока не закоммитится транзакция с select with updlock, останые select with updlock висят в ожидании, все супер, тоесть такую ситуацию создает insert как то косвенно, вот бы понять как...вариант без селекта, тоесть фигачим апдейт если 0 rows аffected то insert если unique constraint то снова update не желательно, тк тут soa и все дела, айдишники выдает сторонний сервис, перерасход нежелателен...никак не могу понять, как инсерт создает дедлок здесь, так же есть вариант, если deadlock, то еще раз повторить, но это все полумеры какие то
4 мар 18, 22:18    [21236395]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
aleks222
Guest
Тредстартеру 40 потоков важнее эффективности.

Можно расходиться.
5 мар 18, 05:53    [21236674]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
no56892
Member

Откуда:
Сообщений: 590
Выше я выкладывал дедлок граф, по нему вообще ничего не понятно? А что скл сервер не умеет работать параллельно с десятком сессий над одной таблицей? Хмм, выше все предложенное как уже писал на мой взгляд полумеры, хотя инсерты + мердж даже будут побыстрее, но тут есть некоторые ограничения (исторические) на вызов самого метода кот все это делает(читает и заливает файл)
5 мар 18, 09:17    [21236844]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
.Евгений
Member

Откуда:
Сообщений: 493
Дарю для размышлений кейс решения подобной задачи.

Кролик слал сообщения в таблицу БД через вызов ХП (внутри 1 insert), максимальная скорость составляла 60 штук в секунду. На определенном этапе эта скорость оказалась недостаточной. Проблему стали решать двумя способами. Первый сводился к многопоточной записи, дал скорость около 150 в секунду и был принят во всей организации, кроме моей делянки.

Второй способ, разработанный мной, позволил SSIS лезть напрямую в шину и выкачивать сообщения пачкой, после чего разбираться с ними в целом. Средняя скорость обработки (с учетом пауз) доходила до 1000 в секунду и выше.

Описание сильно в общих чертах, потому опускаю ожидание, подтверждение, логирование и прочие удовольствия. Побочный эффект - коллеги обратились с жалобой, что очередь никто не слушает. А, нет, есть слушатель... Ой, снова нет! Правильно, после получения пачки SSIS отсоединялся (хотя можно было этого и не делать). После перенастройки Заббикса коллеги перестали тревожиться.
5 мар 18, 09:38    [21236896]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
no56892
Выше я выкладывал дедлок граф, по нему вообще ничего не понятно?
у вас несколько процессов взаимоблокируют друг друга на операциях SELECT и UPDATE пытаясь наложить U блокировку на ключ индекса.
типа так:
process1 SELECT ждёт "А"
process2 UPDATE заблокировал "Б" ждёт "А"
process3 SELECT ждёт "Б"
process4 UPDATE ждёт "Б" заблокировал "А"
5 мар 18, 09:43    [21236910]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
покажите планы ваших запросов, как у вас используется индекс.
5 мар 18, 09:49    [21236924]     Ответить | Цитировать Сообщить модератору
 Re: дедлоки...  [new]
no56892
Member

Откуда:
Сообщений: 590
А как может заблокировать апдэйт Б держа лок на А? Селект и апдейт идут по одному и тому же значению, те если выполняется апдейт на Б то он уже захватил U при предыдущем селекте.план запросо постараюсь сделать
5 мар 18, 10:06    [21236966]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить