На таблицу, 25 млн записей, самая нагруженная в проекте чтение, обновление, добавление добавили индекс (не уникальный, не кластеризованный).
Таблица имеет кластеризованный первичный ключ по полю ID (uniqueidentifier). И множество других индексов и внешних ключей.
Почему собственно после добавления еще одного индекса посыпались дедлоки?
Я это вижу так:
Таблица имеет кластеризованный индекс и новый некластеризованный индекс, который включает столбец Column.
Один коннект нашел строку для обновления с помощью кластеризованного индекса и обновил страницу данных. Теперь он хочет обновить индексную страницу с новым значением Column.
Другой коннект нашел ту же строку в некластеризованном индексе и установил блокировку обновления для этой строки в индексе. Блокировка обновления — это блокировка чтения, которая может удерживаться только одним коннектом. Эта блокировка обновления блокирует первый коннект. Второй теперь хочет получить блокировку обновления также на странице данных, но заблокирован первым процессом.
Но как понять, тогда какой индекс вызовет дедлоки, а какой нет?
Честно говоря впервые ловлю дедлок после добавления индекса.
Здравствуйте, BlackEric, Вы писали:
BE>На таблицу, 25 млн записей, самая нагруженная в проекте чтение, обновление, добавление добавили индекс (не уникальный, не кластеризованный). BE>Таблица имеет кластеризованный первичный ключ по полю ID (uniqueidentifier). И множество других индексов и внешних ключей.
BE>Почему собственно после добавления еще одного индекса посыпались дедлоки? BE>Я это вижу так:
Верно видишь. Обновление лочит сначала данные, потом индекс. Чтение лочит сначала индекс, а потом данные.
BE>Но как понять, тогда какой индекс вызовет дедлоки, а какой нет?
1) Мониторить extended events https://www.mssqltips.com/sqlservertip/5658/capturing-sql-server-deadlocks-using-extended-events/
2) Просто посмотреть планы запросов, участвующих в дедлоке
BE>Честно говоря впервые ловлю дедлок после добавления индекса.
Все бывает в первый раз.
А теперь самое важное: Чтобы бороться с такими дедлоками не надо пытаться повышать уровень узоляции. Просто сделайте свой индекс покрывающим — добавьте в него колонки, которые вычитываей запрос, чтобы у вас из плана чтения ушел Key Lookup. Тогда запрос чтения будет полностью отдаваться из индекса, без чтения данных таблицы и дедлока возникать не будет.
Re: Ms Sql 2017. Deadlock после добавления индекса
BE>Таблица имеет кластеризованный первичный ключ по полю ID (uniqueidentifier).
Не делай так.
The clustered index as we know, helps in defining the physical order of the rows of the table. As GUIDs are randomly generated, defining the clustered index on the GUID column will lead to page splits in the page structure where data is entered in the middle of the page based on the value of the uniqueidentifier. This type of page split will have an impact on INSERTs and UPDATEs. It will lead to issues with SELECT statements as well due to heavy fragmentation. You can refer to this link by Ben Snaidero where he neatly compares page splits while using int, bigint or GUID data types.
Здравствуйте, yenik, Вы писали:
BE>>Таблица имеет кластеризованный первичный ключ по полю ID (uniqueidentifier).
Y>Не делай так.
Y>
Y>The clustered index as we know, helps in defining the physical order of the rows of the table. As GUIDs are randomly generated, defining the clustered index on the GUID column will lead to page splits in the page structure where data is entered in the middle of the page based on the value of the uniqueidentifier. This type of page split will have an impact on INSERTs and UPDATEs. It will lead to issues with SELECT statements as well due to heavy fragmentation. You can refer to this link by Ben Snaidero where he neatly compares page splits while using int, bigint or GUID data types.
BE>Таблица имеет кластеризованный первичный ключ по полю ID (uniqueidentifier).
Сделайте кластерный по дате или автоинкременту. BE>Но как понять, тогда какой индекс вызовет дедлоки, а какой нет?
Нужно получить граф дедлоков. SQL Profiler их умеет писать на диск кучей xml.
Re: Ms Sql 2017. Deadlock после добавления индекса
Здравствуйте, BlackEric, Вы писали:
BE>На таблицу, 25 млн записей, самая нагруженная в проекте чтение, обновление, добавление добавили индекс (не уникальный, не кластеризованный). BE>Таблица имеет кластеризованный первичный ключ по полю ID (uniqueidentifier). И множество других индексов и внешних ключей.
BE>Почему собственно после добавления еще одного индекса посыпались дедлоки?
BE>Я это вижу так:
BE>Таблица имеет кластеризованный индекс и новый некластеризованный индекс, который включает столбец Column. BE>Один коннект нашел строку для обновления с помощью кластеризованного индекса и обновил страницу данных. Теперь он хочет обновить индексную страницу с новым значением Column.
BE>Другой коннект нашел ту же строку в некластеризованном индексе и установил блокировку обновления для этой строки в индексе. Блокировка обновления — это блокировка чтения, которая может удерживаться только одним коннектом. Эта блокировка обновления блокирует первый коннект. Второй теперь хочет получить блокировку обновления также на странице данных, но заблокирован первым процессом.
BE>Но как понять, тогда какой индекс вызовет дедлоки, а какой нет? BE>Честно говоря впервые ловлю дедлок после добавления индекса.
Конкретно с guid как будто решается. Хотя не очень понятно, к чему эти хлопоты. Если используется не guid, а другой тип, то нужен другой подход. К примеру в IdentyServer используется рандомная строка, чувствительная к регистру. Тут лучше не мудрить, а просто делать некластерный индекс.
Re[4]: Ms Sql 2017. Deadlock после добавления индекса
С некластерным индексом проблема та же самая. Ну то есть не прям совсем та же самая, но аналогичная. Любой b-tree индекс строится эффективнее когда данные в него добавляются по возрастанию, а не рандомно.
