Вопрос по performance, sql-server – Операторы SQL Server SELECT, вызывающие блокировку

16

Мы используем базу данных SQL Server 2005 (без контроля версий строк) с огромным оператором select, и мы видим, что она блокирует выполнение других операторов (рассматривается с использованиемsp_who2). Я не осознавал, что операторы SELECT могут вызвать блокировку - могу ли я что-нибудь сделать, чтобы смягчить это?

Я не указываю уровень изоляции. Есть ли способ узнать, какой уровень изоляции используется? Neil Barnwell
Да, посмотрите sys.dm_exec_sessions. Столбец транзакции_изолирования. Remus Rusanu
Вы случайно используете более высокие уровни изоляции, например, повторяемое чтение или сериализацию? Некоторые компоненты ADO и CLR просматриваются в поисковике без вашего явного согласия ... На каком ресурсе блокировки (ключ, диапазон, таблица)? Remus Rusanu
Было бы полезно иметь некоторые описания таблиц и данных. Если вы можете оптимизировать запрос, то блокировки, очевидно, будут удерживаться в течение более короткого периода времени. Другой вариант, если это транзакция только для чтения, - это получить набор данных, с которыми вам нужно работать, во временные таблицы, а затем выполнить запрос к временным таблицам. Вы можете заполнить все свои временные таблицы с высоким уровнем изоляции, чтобы гарантировать транзакционный «снимок» живых таблиц. ahains

Ваш Ответ

6   ответов
29

данных и запрос вызовут только минимальную блокировку и не будут проблемой. «Обычная» подсказка WITH NOLOCK - почти всегда неправильный ответ. Правильный ответ - настроить запрос, чтобы он не сканировал огромные таблицы.

Если запрос неуправляем, то сначала подумайте о SNAPSHOT уровень ИЗОЛЯЦИИ, во-вторых, вы должны рассмотреть возможность использования DATABASE SNAPSHOTS и последний вариант должен быть DIRTY READS (и лучше поменять уровень изоляции вместо подсказки NOLOCK). Обратите внимание, что грязные чтения, как ясно указывает название, будут возвращать противоречивые данные (например, ваш общий лист может быть несбалансированным).

@ JustLook, у вас есть хорошие моменты, но я думаю, что ответ Ремуса - хороший ориентир - особенно. его первый аргумент, что для подсказок nolock что-то не так с общим дизайном или запросом. А как насчет БД, которая использует транзакции? Разве это не означает, что следует избегать nolock? Кажется, что это может создать много неясных и трудных для тестирования ошибок, потому что они периодически. Morgan T.
Цитирую это по другому вопросу: / Stackoverflow.com вопросы / 1018651 / ... instanceof me
Не поймите меня неправильно, отличный ответ, но я стараюсь не говорить абсолютно, не зная ничего об их применении и дизайне. Например, что если ваше приложение было приложением Enterprise, у которого была функция «Запрос»? Угадай, кто пишет твой запрос? К вашей базе данных? Клиент! Там недостаточно индексов! Уровни изоляции снимка? Сколько людей одновременно поражают ваши базы данных? Потому что давайте поговорим о ресурсах с этой опцией! Так что да, нам, немытым массам, которые не являются гуру БД, приходится использовать грязное чтение! JustLooking
«Обычная подсказка WITH NOLOCK - это почти всегда неправильный ответ». Что если мой вопрос такой: я хочу делать грязные, не блокирующие операторы SELECT, которые в настоящее время вызывают блокировку на 20-60 секунд. Тогда каков правильный ответ? Jonesome
0

уровень транзакции для чтения незафиксированным

Он такой же, как NOLOCK, и подходит для запросов только для чтения. Andomar
Нет, не подходит для запросов только для чтения, поскольку NOLOCK можеттольк применяется к запросам только для чтения, поэтому любая проблема, с которой он сталкивается (и имеет много), также относится и к «только для чтения». Несбалансированный отчет по-прежнему доступен только для чтения, но вряд ли он корректен. Remus Rusanu
Разве это не то же самое, что использование (NOLOCK)? Я действительно не хочу идти в этом направлении ... Neil Barnwell
14

документаци:

Shared (S) блокировки позволяют параллельным транзакциям читать(SELECT) ресурс под пессимистическим контролем параллелизма. Для получения дополнительной информации см.Types of Concurrency Control. Никакие другие транзакции не могут изменить данные, покаshared (S) блокировки существуют на ресурсе.Shared (S)локировки @ ресурса освобождаются, как только операция чтения завершается, если только уровень изоляции транзакции не установлен на повторяемое чтение или выше, или для сохранения @ используется подсказка блокировкshared (S) блокирует на время транзакции.

A shared lock совместим с другой общей блокировкой или блокировкой обновления, но не с исключительной блокировкой.

Это значит, что твойSELECT запросы будут блокироватьUPDATE а такжеINSERT запросы и наоборот.

A SELECT query поместит временную разделяемую блокировку, когда он читает блок значений из таблицы, и удалит ее, когда закончит чтение.

Пока существует блокировка, вы не сможете ничего сделать с данными в заблокированной области.

ДваSELECT запросы никогда не будут блокировать друг друга (если они не являютсяSELECT FOR UPDATE)

Вы можете включитьSNAPSHOT уровень изоляции в вашей базе данных и использовать его, но учтите, что это не помешаетUPDATE запросы заблокированыSELECT запросы (что, кажется, ваш случай).

Это, однако, помешаетSELECT запросы заблокированыUPDATE.

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

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

Очень четкое объяснение замков! Благодарность goku_da_master
Есть ли способ обойти общую блокировку в MySQL? Я пытался использовать каждый доступный уровень изоляции, но ни один из них не препятствовал блокировке запросов на обновление с помощью select (как в моем случае). Travis J
Не принимайте подробности реализации менеджера блокировок SQL Servere. Достаточно сказать, этон связанный список. Remus Rusanu
@ TravisJ: пожалуйста, опубликуйте его как отдельный вопрос, предоставляя воспроизводимый вариант использования. Quassnoi
0

Вы можете использоватьWITH(READPAST) настольная подсказка. Это отличается отWITH(NOLOCK). Он получит данные до начала транзакции и никого не заблокирует. Представьте, что вы запустили оператор до начала транзакции.

SELECT * FROM table1  WITH (READPAST)
2

 using (new TransactionScope(TransactionScopeOption.Required, 
 new TransactionOptions { 
 IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
 {
 //Your code here
 }

ил

SelectCommand = "SELECT * FROM Table1 WITH (NOLOCK) INNER JOIN Table2 WITH (NOLOCK) ..."

Помните, что вы должны писать WITH (NOLOCK) после каждой таблицы, которую вы хотите испорти

0

"тупики, включающие только одну таблицу"http: //sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/01/reproducing-deadlocks-involving-only-one-table.asp

и неверные результаты:

"Выбор в разделе ЧИТАТЬ СООТВЕТСТВУЮЩИЙ и ПОВТОРЯЮЩИЙСЯ ЧТЕНИЕ может вернуть неправильные результаты.&quo

http: //www2.sqlblog.com/blogs/alexander_kuznetsov/archive/2009/04/10/selects-under-read-committed-and-repeatable-read-may-return-incorrect-results.asp

Похожие вопросы