Выберите для обновления с помощью 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?

изменить 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:

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

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
18 71
sql sql-server tsql sql-server-2005 read-committed-snapshot

18 ответов:

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

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

31

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

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

17

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

7

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

5

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

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

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

5

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

ALTER DATABASE yourDbNameHere SET READ_COMMITTED_SNAPSHOT ON;

[EDIT: 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;
5

OK, один select 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 для получения дополнительной информации

3

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

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

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

  -- Do SOMETHING

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

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

2

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

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

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

после этого "

2

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

SELECT * FROM <tablename> WITH ROWLOCK XLOCK HOLDLOCK

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

1

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

1

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


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


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


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

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

1

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

1

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

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

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

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

1

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

1

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

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

0

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

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

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

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

0