SELECT FOR UPDATE с SQL Server

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

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

SELECT * FROM <tablename> FOR UPDATE

... так что другие соединения с базой данных блокируются при попытке доступа к одной и той же строке "ДЛЯ ОБНОВЛЕНИЯ".

Я пробовал:

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

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

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

EDIT 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 = ?

EDIT 2009-10-05:

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

mechanism              | SELECT on different row blocks | SELECT on same row blocks
-----------------------+--------------------------------+--------------------------
ROWLOCK                | no                             | no
updlock, rowlock       | yes                            | yes
xlock,rowlock          | yes                            | yes
repeatableread         | no                             | no
DBCC TRACEON (1211,-1) | yes                            | yes
rowlock,xlock,holdlock | yes                            | yes
updlock,holdlock       | yes                            | yes
UPDLOCK,READPAST       | no                             | no

I'm looking for        | no                             | yes

Ответ 1

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

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

Ответ 2

У меня есть аналогичная проблема, я хочу заблокировать только 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, поэтому эти строки будут заблокированы.

Ответ 3

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

Ответ 4

Попробуйте (updlock, rowlock)

Ответ 5

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

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

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

Ответ 6

возможно, чтобы mvcc constant мог решить его (в отличие от конкретной партии: SET SCHPS):

ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

[EDIT: 14 октября]

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

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

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

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

Ответ 7

ОК, один выбранный wil по умолчанию использует изоляцию транзакции "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 для получения дополнительной информации

Ответ 8

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

Обертка вашего 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>[email protected]
  WHERE [email protected]
COMMIT

Ответ 9

Создайте фальшивое обновление, чтобы принудительно выполнить блокировку строк.

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

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

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

Ответ 10

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

SELECT * FROM <tablename> WITH ROWLOCK XLOCK HOLDLOCK

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

Ответ 11

В соответствии с этой статьей решение должно использовать подсказку WITH (REPEATABLEREAD).

Ответ 12

Повторите все ваши запросы, возможно, у вас есть запрос, который выберет без подсказки ROWLOCK/FOR UPDATE из той же таблицы, у которой есть ВЫБОР ДЛЯ ОБНОВЛЕНИЯ.

MSSQL часто увеличивает эти блокировки строк до блокировок на уровне страниц (даже блокировки на уровне таблицы, если у вас нет индекса в поле, которое вы запрашиваете), см. Это объяснение. Поскольку вы запрашиваете FOR UPDATE, я могу предположить, что вам нужна надежность уровня транзакции (например, финансовая, инвентарная и т.д.). Поэтому совет на этом сайте не применим к вашей проблеме. Это просто понимание, почему MSSQL расширяет блокировки.

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

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

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

Ответ 13

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

Ответ 14

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

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

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

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

Ответ 15

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

Ответ 16

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

Ответ 17

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

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

Ответ 18

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

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

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

Изменить 2: другое решение - использовать уровень изоляции SERIALIZABLE