mattielung Ответов: 1

Как принудительно заблокировать запись в SQL server, если по умолчанию используется блокировка таблицы

Я использую блокировку строк с SQL-сервером. У меня есть некоторые места на SQL Server Express и некоторые на SQL Server 2012 full. У меня есть таблица linked_invoices с первичным ключом и полем ticket_id. Поле ticket_id может быть продублировано внутри таблицы.

У меня есть некластеризованный индекс (ix_ticket_id) в поле ticket_id.

Запрос, который я использую для извлечения данных, таков

выберите * из linked_invoices с помощью (rowlock xlock), где ticket_id = 2000074703
Я обнаружил, что в таблицах, содержащих менее 500 записей, вся таблица заблокирована.

В местах с более чем 500 записями блокируются только записи с ticket_id = 2000074703.


Единственное решение, которое мне удалось придумать, - это добавить в таблицу поддельные записи. Что я ненавижу делать, потому что очень придирчиво отношусь к чистоте своей базы данных.

Кто-нибудь сталкивался с этой проблемой и, возможно, у вас есть другое решение?

Что я уже пробовал:

Я попытался принудительно использовать опцию with(index), чтобы заставить оптимизатор запросов использовать ix_ticket_id, но это не помогло.

ВЫБИРАТЬ *
Из linked_invoices с индексом (ROWLOCK XLOCK index (ix_ticket_id))
Где ticket_id = 2000084790
Я также попытался отключить параметр lock_escalation инструкции Alter таблицы linked_invoices набор (параметр lock_escalation=отключить)

Я также попытался перестроить индекс ix_ticket_id и отключить allow_page_locks

ALLOW_PAGE_LOCKS ИНСТРУКЦИИ = ВЫКЛ.

Jörgen Andersson

Намек на роулок-это именно намек. Оптимизатор может проигнорировать его.

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

Это было бы особенно верно, если ваш индекс не охватывает все выбранные столбцы.
Тогда ему нужно будет сначала найти индекс, а затем сделать поиск в таблице. Это будет вдвое больше операций и намного медленнее.

Проверить его еще раз предваряя запрос с set SHOWPLAN_TEXT на; и в результате плане.

mattielung

Вот план для таблицы, которая имеет 3 записи
|--Clustered Index Scan(OBJECT:([pcsmsLex].[dbo].[linked_invoices].[pk_linked_invoices]), где:([pcsmsLex].[dbo].[linked_invoices].[ticket_id]=(314000334225.)))

Вот план из таблицы, которая имеет 500 записей.
|--Clustered Index Scan(OBJECT:([pcsmsEugene].[dbo].[linked_invoices].[pk_linked_invoices]), где:([pcsmsEugene].[dbo].[linked_invoices].[ticket_id]=(314000334225.)))

Как вы можете видеть, план одинаков для обеих таблиц.
Однако с (rowlock) работает по-другому.

Я понимаю, что вы подразумеваете под уключиной-это всего лишь намек.

Однако "with (rowlock)" бесполезно для меня, если я не могу быть уверен, что он блокирует только 1 запись, а не всю таблицу.

Я не забочусь о повышении производительности, я забочусь о том, чтобы мои пользователи могли редактировать 1 запись в таблице, не мешая всем другим пользователям редактировать другие записи в этой таблице.

Jörgen Andersson

Я подозреваю, что разница зависит от того, помещается ли таблица на одной странице или нет.
Если таблица помещается на одной странице, это не будет иметь достаточно большой разницы, какой тип блокировки будет взят.
Сервер всегда записывает на диск целую страницу, независимо от того, сколько строк вы записываете внутри страницы. Это связано с тем, как работают жесткие диски.
Когда вы блокируете одну строку, вы не блокируете ее на жестком диске, вы блокируете ее в буферном кэше. Внесенные изменения все равно нужно записать на диск.
А когда страница записывается на диск, в буфере происходит блокировка страницы, чтобы вы не могли изменить ее во время записи.

mattielung

Спасибо за ответ. Это вписывается в мое "решение" проблемы. Добавление фиктивных записей в таблицу сделает таблицу достаточно большой, чтобы она не помещалась на одной странице.
Просто было бы неплохо, если бы где-нибудь в документации Microsoft по опции "with (rowlock xlock)" было бы сказано, что вы не можете гарантировать блокировку одной записи без блокировки всей таблицы.

Jörgen Andersson

Я сделал свой ответ ответом, чтобы добавить немного кода

1 Ответов

Рейтинг:
2

Jörgen Andersson

Я кое-что проверил и ошибся. Дело не в том, сколько страниц занимает таблица/индекс.
Но, похоже, речь идет о том, на сколько уровней глубок индекс.

Сначала я создал несколько таблиц разных размеров и проверил, сколько страниц они использовали этот запрос:

SELECT  t.NAME AS TableName
       ,p.rows AS RowCounts
       ,SUM(a.total_pages) AS TotalPages
       ,SUM(a.used_pages) AS UsedPages
       ,(SUM(a.total_pages) - SUM(a.used_pages)) AS UnusedPages
FROM    sys.tables t
JOIN    sys.indexes i ON t.OBJECT_ID = i.object_id
JOIN    sys.partitions p ON i.object_id = p.OBJECT_ID
    AND i.index_id = p.index_id
JOIN    sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.NAME = 'MyTableName'
GROUP BY t.Name,p.Rows
ORDER BY t.Name

И я вел себя так же, как и Вы, тоже на столах, занимающих четыре страницы.

Поэтому я проверил индексы таблиц с помощью этой процедуры:
SELECT  *
FROM    sys.dm_db_index_physical_stats(DB_ID('MyDatabase'),OBJECT_ID('MyTable'),NULL,NULL,'DETAILED')

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

mattielung

Спасибо за ответ, Йорген.
Просто и к твоему сведению, я ... vb.net программист. По необходимости я узнал о SQL server гораздо больше, чем мне хотелось бы.
Я думаю, что понимаю, что вы имеете в виду под индексами "один уровень глубины".
Вы предполагаете, что перепроектирование индексов в этих таблицах поможет решить мою проблему блокировки записей?

Jörgen Andersson

Нет, я предполагаю, что в этом случае вы мало что можете сделать, чтобы заставить шлюз работать. Sql-сервер собирается отказаться, потому что он пытается сохранить баланс между стоимостью блокировки и стоимостью параллелизма. https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#dynamic_locks
Когда у вас есть только один уровень в индексе, блокировка страницы/таблицы гораздо эффективнее, но чтобы объяснить, почему мне нужно объяснить, как работает индекс.
К счастью, кто-то уже сделал это лучше, чем я: https://use-the-index-luke.com/sql/anatomy/the-tree

mattielung

Спасибо за Ваш вклад.

Я думаю, что я остался с этим...
Предупреждение от docs.microsoft.com это объясняет табличные подсказки

"Внимание поскольку оптимизатор запросов SQL Server обычно выбирает оптимальный план выполнения запроса, мы рекомендуем использовать подсказки только в крайнем случае опытными разработчиками и администраторами баз данных."

это означает, что более поздняя запись в том же документе не гарантирует, что строка будет заблокирована, даже если она говорит: "все блокировки сняты".

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


Jörgen Andersson

Просто вспомнил, что есть еще одна вещь, которую ты можешь попробовать:
ALTER TABLE MyTableName SET ( LOCK_ESCALATION = DISABLE);

Но используйте его с осторожностью, если вы поставите его не на тот стол, у вас легко может закончиться память.