ВЫБЕРИТЕ ОБНОВЛЕНИЕ с SQL Server

Я использую базу данных Microsoft SQL Server 2005 с уровнем изоляции READ_COMMITTED а также READ_COMMITTED_SNAPSHOT=ON,

Теперь я хочу использовать:

SELECT * FROM <tablename> FOR UPDATE

... чтобы другие подключения к базе данных блокировались при попытке доступа к той же строке "FOR UPDATE".

Я старался:

SELECT * FROM <tablename> WITH (updlock) WHERE id=1

... но это блокирует все другие соединения, даже для выбора идентификатора, отличного от "1".

Какой правильный совет, чтобы сделать SELECT FOR UPDATE как известно для Oracle, DB2, MySql?

РЕДАКТИРОВАТЬ 2009-10-03:

Это операторы для создания таблицы и индекса:

CREATE TABLE example ( Id BIGINT NOT NULL, TransactionId BIGINT, 
    Terminal BIGINT, Status SMALLINT );
ALTER TABLE example ADD CONSTRAINT index108 PRIMARY KEY ( Id )
CREATE INDEX I108_FkTerminal ON example ( Terminal )
CREATE INDEX I108_Key ON example ( TransactionId )

Многие параллельные процессы делают это SELECT:

SELECT * FROM example o WITH (updlock) WHERE o.TransactionId = ?

РЕДАКТИРОВАТЬ 2009-10-05:

Для лучшего обзора я записал все опробованные решения в следующей таблице:

механизм | ВЫБРАТЬ на разных блоках строк | ВЫБРАТЬ на тех же блоках строк
-----------------------+--------------------------------+--------------------------
ROWLOCK                | нет | нет
апдлок, rowlock       | да | да
xlock,rowlock          | да | да
повторяемый | нет | нет
DBCC TRACEON (1211,-1) | да | да
Rowlock, Xlock, Holdlock | да | да
апдлок, блокировка | да | да
UPDLOCK,READPAST       | нет | нет

Я ищу | нет | да

18 ответов

Решение

Недавно у меня возникла проблема взаимоблокировки, потому что Sql Server блокирует больше, чем необходимо (страница). Вы ничего не можете сделать против этого. Теперь мы ловим тупиковые исключения... и я бы хотел, чтобы вместо этого был Oracle.

Редактировать: Мы используем изоляцию моментальных снимков, которая решает многие, но не все проблемы. К сожалению, чтобы иметь возможность использовать изоляцию моментальных снимков, она должна быть разрешена сервером базы данных, что может вызвать ненужные проблемы на сайте клиента. Теперь мы не только перехватываем исключения взаимоблокировок (которые, конечно же, все еще могут возникать), но и проблемы одновременного выполнения снимков для повторения транзакций из фоновых процессов (которые не могут повторяться пользователем). Но это все еще работает намного лучше, чем раньше.

У меня похожая проблема, я хочу заблокировать только 1 строку. Насколько я знаю, с UPDLOCK опция SQLSERVER блокирует все строки, которые нужно прочитать, чтобы получить строку. Таким образом, если вы не определите индекс для прямого доступа к строке, все предыдущие строки будут заблокированы. В вашем примере:

Предположим, что у вас есть таблица TBL с id поле. Вы хотите заблокировать строку с id=10, Вам нужно определить индекс для идентификатора поля (или любых других полей, которые вы выбираете):

CREATE INDEX TBLINDEX ON TBL ( id )

И затем, ваш запрос для блокировки ТОЛЬКО строк, которые вы прочитали:

SELECT * FROM TBL WITH (UPDLOCK, INDEX(TBLINDEX)) WHERE id=10.

Если вы не используете опцию INDEX(TBLINDEX), SQLSERVER необходимо прочитать все строки в начале таблицы, чтобы найти вашу строку с id=10поэтому эти строки будут заблокированы.

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

Возможно, решение этой проблемы может быть решено сделать постоянным mvcc (в отличие только от конкретной партии: SET SNAPSHOT УРОВЕНЬ ИЗОЛЯЦИИ СДЕЛКИ):

ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

[ПРАВКА: 14 октября]

Прочитав это: лучше параллелизма в Oracle, чем SQL Server? и это: http://msdn.microsoft.com/en-us/library/ms175095.aspx

Когда опция базы данных READ_COMMITTED_SNAPSHOT включена, механизмы, используемые для поддержки этой опции, активируются немедленно. При установке опции READ_COMMITTED_SNAPSHOT в базе данных разрешено только соединение, выполняющее команду ALTER DATABASE. В базе данных не должно быть других открытых соединений, пока не завершится ALTER DATABASE. База данных не обязательно должна быть в однопользовательском режиме.

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

ALTER DATABASE yourDbNameHere SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

Полный ответ может вникнуть во внутренности СУБД. Это зависит от того, как работает механизм запросов (который выполняет план запросов, сгенерированный оптимизатором SQL).

Однако одно возможное объяснение (применимо по крайней мере к некоторым версиям некоторых СУБД - не обязательно к MS SQL Server) состоит в том, что в столбце идентификатора нет индекса, поэтому любой процесс, пытающийся обработать запрос с помощью 'WHERE id = ?в результате выполняется последовательное сканирование таблицы, и это последовательное сканирование блокирует блокировку, примененную вашим процессом. Вы также можете столкнуться с проблемами, если СУБД применяет блокировку на уровне страниц по умолчанию; Блокировка одной строки блокирует всю страницу и все строки на этой странице.

Есть несколько способов разоблачить это как источник проблем. Посмотрите на план запроса; изучить показатели; попробуйте SELECT с идентификатором 1000000 вместо 1 и посмотрите, не заблокированы ли другие процессы.

Попробуй (апдлок, rowlock)

Создайте поддельное обновление для принудительной блокировки строк.

UPDATE <tablename> (ROWLOCK) SET <somecolumn> = <somecolumn> WHERE id=1

Если это не блокирует ваш скандал, бог знает, что будет.

После этого "UPDATE"Вы можете сделать свое SELECT (ROWLOCK) и последующие обновления.

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

Обертывание SELECT в транзакции с использованием подсказки блокировки WITH (XLOCK,READPAST) даст желаемые результаты. Просто убедитесь, что эти другие параллельные чтения НЕ используют WITH (NOLOCK). READPAST позволяет другим сеансам выполнять тот же SELECT, но в других строках.

BEGIN TRAN
  SELECT *
  FROM <tablename> WITH (XLOCK,READPAST) 
  WHERE RowId = @SomeId

  -- Do SOMETHING

  UPDATE <tablename>
  SET <column>=@somevalue
  WHERE RowId=@SomeId
COMMIT

ОК, один выбор по умолчанию будет использовать изоляцию транзакции "Read Committed", которая блокирует и, следовательно, останавливает запись в этот набор. Вы можете изменить уровень изоляции транзакции с помощью

Set Transaction Isolation Level { Read Uncommitted | Read Committed | Repeatable Read | Serializable }
Begin Tran
  Select ...
Commit Tran

Это подробно объясняется в SQL Server BOL

Ваша следующая проблема заключается в том, что по умолчанию SQL Server 2K5 будет усиливать блокировки, если у вас более 2500 блокировок или вы используете более 40% "нормальной" памяти в транзакции блокировки. Эскалация переходит на страницу, затем блокировка таблицы

Вы можете отключить эту эскалацию, установив "флаг трассировки" 1211t, см. BOL для получения дополнительной информации.

Блокировки приложений - это один из способов создать собственную блокировку с настраиваемой степенью детализации, избегая при этом "полезного" повышения блокировки. Смотрите sp_getapplock.

Вопрос - доказано ли, что этот случай является результатом эскалации блокировки (т. Е. Если вы отслеживаете с помощью профилировщика события эскалации блокировки, это определенно то, что вызывает блокировку)? Если так, есть полное объяснение и (довольно экстремальный) обходной путь, включающий флаг трассировки на уровне экземпляра для предотвращения эскалации блокировки. См. http://support.microsoft.com/kb/323630 флаг трассировки 1211

Но это, вероятно, будет иметь непредвиденные побочные эффекты.

Если вы намеренно блокируете строку и держите ее заблокированной в течение длительного периода времени, то использование механизма внутренней блокировки транзакций - не лучший способ (по крайней мере, в SQL Server). Вся оптимизация в SQL Server ориентирована на короткие транзакции - входите, обновляйтесь, выходите. Это и есть причина эскалации блокировки.

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

Согласно этой статье, решение состоит в том, чтобы использовать подсказку WITH(REPEATABLEREAD).

Попробуйте использовать:

SELECT * FROM <tablename> WITH ROWLOCK XLOCK HOLDLOCK

Это должно сделать блокировку исключительной и удерживать ее в течение всей транзакции.

Я решил проблему блокировки строк совершенно другим способом. Я понял, что sql-сервер не может удовлетворительно управлять такой блокировкой. Я решил решить эту проблему с программной точки зрения с помощью мьютекса... waitForLock... releaseLock...

Пересмотрите все ваши запросы, может быть, у вас есть запрос, который выбирается без подсказки ROWLOCK/FOR UPDATE из той же таблицы, в которой вы выбрали SELECT FOR UPDATE.


MSSQL часто переводит эти блокировки строк в блокировки на уровне страниц (даже блокировки на уровне таблиц, если у вас нет индекса по полю, к которому вы обращаетесь), смотрите это объяснение. Поскольку вы запрашиваете ОБНОВЛЕНИЕ, я могу предположить, что вам нужна надежность на уровне транзакций (например, финансовая, инвентарная и т. Д.). Так что совет на этом сайте не относится к вашей проблеме. Это просто понимание того, почему MSSQL усиливает блокировки.


Если вы уже используете MSSQL 2005 (и выше), они основаны на MVCC, я думаю, у вас не должно возникнуть проблем с блокировкой на уровне строк с помощью подсказки ROWLOCK / UPDLOCK. Но если вы уже используете MSSQL 2005 и выше, попробуйте проверить некоторые из ваших запросов, которые запрашивают ту же таблицу, которую вы хотите FOR UPDATE, если они увеличивают блокировки, проверяя поля в их предложении WHERE, если у них есть индекс.


PS
Я использую PostgreSQL, он также использует MVCC FOR UPDATE, я не сталкиваюсь с той же проблемой. Повышение блокировок - это то, что решает MVCC, поэтому я был бы удивлен, если бы MSSQL 2005 все еще увеличивал блокировку таблицы с предложениями WHERE, которые не имеют индекса в своих полях. Если это (повышение блокировки) все еще имеет место для MSSQL 2005, попробуйте проверить поля в предложениях WHERE, если они имеют индекс.

Отказ от ответственности: мое последнее использование MSSQL - только версия 2000.

Вы должны иметь дело с исключением во время фиксации и повторить транзакцию.

Вы пробовали READPAST?

Я использовал UPDLOCK и READPAST вместе при обработке таблицы как очереди.

Как насчет того, чтобы сначала сделать простое обновление в этой строке (без реального изменения каких-либо данных)? После этого вы можете продолжить с строки, как в был выбран для обновления.

UPDATE dbo.Customer SET FieldForLock = FieldForLock WHERE CustomerID = @CustomerID
/* do whatever you want */

Изменить: вы должны заключить его в транзакцию, конечно

Редактировать 2: другое решение заключается в использовании SERIALIZABLE уровня изоляции

Другие вопросы по тегам